An interesting situation with Standby database.

We have a data warehouse database sized more than 2TB using Oracle 9i on HP Superdom machine.

Couple of days back, I have come across of a very tricky situation with its standby database. Yes, you read it correctly. We have the standby database in place for the this database. For god sake, don’t argue with this as there are N number of reasons behind having a standby database for the data warehouse database.

The situation was that the mount point, which has all the standby database files, on standby database server became 100% full and the media recovery background process died/crashed (whatever you say) and archive applying was stopped. I went to the UNIX team and asked them increase the size of the mount point. They gave me a big surprise, saying that the mount point is already 2TB size and on HP Superdom the mount can’t exceed 2TB size. Therefore, increasing the size of the mount point was not possible.

After hearing their comments, I have initially thought that I can open the standby database in READ ONLY mode, move some datafiles to a new mount point in order to have some free space on this mount point. The idea doesn’t work as I was not able to open the database. Oracle was complaining about the media recovery when I tried to open the database in read only mode.

I was in a impression that this would be a dead end and I have ended of recreating the standby database again. Again a standby database which is more than 2TB…. Uffffssss

One of our UNIX admin guy gave me an idea. It was like moving few datafiles from the old location to the new location and creating the OS level link for the datafiles in the old location pointing to the new location.

I thought, I could try this and it worked beyond my imagination. I have shutdown the standby database, allowed him to move the datafiles (for him, it was like flat files) to new location and create the links for the files in the old location pointing to the new location.

I was bit worried while bringing the standby database up and starting media recovery process. The idea worked out and Oracle started applying archived logs on the standby database from the point where they were stopped.

I felt like in my career, every problem is a learning experience and give new ideas to deal with the situation.

That’s all now,

Happy reading,

Jaffar

V$ View Definitions Library - Metalink Note : 220021.1

Today, I have come across of a metalink note : 220021.1 that has all the v$ views with their defination.

The note put all the V$ views together at one place and moverover with their defination. Umm.. this could useful when you get everything at one place.

If you have not metalink aceess, well, Oracle documentation does has the same.


Visit Metalink Note : 220021.1

Happy Reading

Jaffar

Oracle Database Sql Expert beta exam

This morning I have attended the 1Z0-047 Database Sql Expert beta exam and expecting around 80% of it. The exam has 139 question with 3hrs of time. However, I have completed the exam just before the give time, i.e. within 2hr 45 minutes.

This was my first beta exam and I have felt that the duration of the exam is too long with plenty of . There were couple of questions with absolutely wrong choices(answers) and few of the questions were redundant.

But, the exam prepration has refreshed my sql basics once again and also helped me to learn new SQL aggregated function like, GROUPING, GROUPING_SETS, ROLLUP,CUBE, and other few new feature of SQL (WITH CLAUSE, Multitable Inserts, MERGE).

Well, I have to wait 2 or more months in order to receive the score sheet and ofcourse the result whether I am through or not.

In my opinion, this exam is more suitable for the Developer who are in touch with writing queries and upto date of sql funtion. Definately, a piece of cake for them with NO REALL CHALLENGES.

Happy reading,

Jaffar

Oracle ACE Director - Now includes Database and Development categories

Well, sometime back, Tim had discussed about the new look and feel of Oracle ACE page on the OTN and also about Oracle ACE Director Program at his blog http://www.oracle-base.com/blog/2007/07/23/oracle-ace-director/. His question was about the Oracle ACE Directors category and point out that none of the existing ACE Directors are nor Database neither Development community. All from Fusion Middleware community.

This morning I have noticed an email from the Oracle ACE Program team stating that the Oracle ACE Director is now opened for Database and Developers community as well.

“Not only will Oracle Fusion Middleware Regional Directors now be known as Oracle ACE Directors, but we have also opened it up to include Oracle Database performance/management and application development experts.”

What this means to you as an Oracle ACE
The Oracle ACE program will continue to be managed as it has been. The new benefit is that you may be eligible for the Oracle ACE Director level. Oracle ACE Directors are well known experts in their field and have committed to participating in a dialog with Oracle and their local technical community.

Well, its good to know that Oracle is considering critics, whether it is an open letter to Larry about the AWR licencing or including database and development categories for Oracle ACE Director program and taking quick actions.

Happy Reading,


Jaffar

Presenting RMAN 1 day seminar in Sweden and Denmark

I will be presenting one day seminar on RMAN basic and advanced concepts in Sweden and Denmark on 26th & 27th September respectively.

The course index is as follows:

Backup & Recovery Concepts

• What is backup and why backups are important?
• What is recovery?

Backups Methods

• Logical Backups (Export/Import and Data Pumps)
• Cold Backups Hot Backups (Consistent & Inconsistent Backups)

Recovery Manager (RMAN) Architecture & Concepts

• RMAN Introduction and Architecture
• Performing Backups using RMAN Recovery Catalog Maintenance

Different levels of Backups

• Mechanism of Incremental Backups
• Block Change Tracking Concepts

Flash Recovery Area (FRA)

• Introduction FRA Concepts and Benefits

Recover Database

• Recovering from different scenarios Instance/Crash Recovery
• Media Recovery
• Complete Recovery
• Incomplete Recovery

Block Recovery RMAN Advanced Topics

• Automatic Point-in-Time Recovery (TSPITR)
• Cloning Database
• Creating Standby Database/Rolling Upgrade
• Transportable Tablespaces from RMAN Backups Cross- Platform
• Transportable Tablespaces Cross Platform Database

Anyone wants to register for Denmark seminar, use the following url to register for the seminar.

http://www.oracle.com/education/emea_eblasts/local_campaign/dk/dk_syed_jaffar_hussain_1day_seminar_27_september_ballerup_120707_ol.html

Those who are in Sweden and willing to attend this seminar, can use the following url and register for the seminar.

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D16314_955393&p_org_id=40&lang=S&source_call=

Since this is my ever first international seminar, I am bit nervous and at the same time excited about the opportunity.

Happy Reading,

Jaffar

alert.log file in 11g

According to metalink note - 438148.1, beginning with Release 11g, the alert log file is written as XML formatted and as a text file (like in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, yet another new dump dest in 11g).

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, 'u01/oracle/product/ora11g/log' (depends on ORACLE_HOME settings).

The location of an ADR home is given by the following path, which starts at the ADR base directory: ADR_BASE/diag/product_type/product_id/instance_id

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

Within the ADR home directory are subdirectories:
alert - The XML formatted alertlog
trace - files and text alert.log file
cdump - core files

The XML formatted alert.log is named as 'log.xml'

Happy Reading,

Jaffar

11g, Whitepappers on OTN.

I have seen many people talking about the arrival and launch of Oracle 11g.

I found few of 11g Whitepapers on OTN. Following is the URL for the PDFs.

http://www.oracle.com/technology/products/database/oracle11g/index.html

Jaffar

1Z0-047 Oracle Database SQL Expert

Well, after learning about this beta exam from Laurent, I couldn't stop myself registering this exam. I am going to take this exam on 25th of this month. I knew, its too far, but, due to the time constraints and busy schedule, I had to register the exam on the specified date.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_047

I am not so found of this expert certification word, as I don't believe in simply reading books, attending the exam (selecting right answer from the list of answers) and passing the expert exam.

It would have brought more interest and value to the certification if Oracle consider EXPERT exams as practical ones where cnadidate can show his/her reall skills rather than the temporary knowledge got from some testing questions or etc for the sake of passing the exam.

Jaffar

Change behavior of GROUP BY clause in Oracle 10g.

After successful upgrade of one of the busiest 9i database to 10g, we have received a very first
change of behavior from the users.

Users complained that after upgrading the database, some of the queries which were returning results in an order are now not returning the data in any order now.

The queries have GROUP BY clause, and after some research, we found out that
starting with 10g, Oracle uses the new HASH GROUP BY mechanism which will not guarantee the result in any order unless ORDER BY clause is specified.

HASH GROUP BY algorithm is explained in the following URL (looks old stuff)
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000428.htm

In a nutshell, in Oracle 9i, GROUP BY clause gives result in sort order, this behavior has
changed from 10g onwards.

The general and Oracle recommendation is, to add the ORDER BY clause in order to have 100% guaranteed order by result.

Following is the test which I have carried out on 9i and 10g database (UNIX PLATFORMS):

>>on 9i database

select owner,count(1) from dba_segments group by owner;

OWNER COUNT(1)
------------------------------ ----------
BARABC 42
BASABC 10
BIPABC 82
CPABC 23
DMIABC 40
DMI_TRGTABC 247


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'



>>on 10g database

OWNER COUNT(1)
------------------------------ ----------
SYSTEM 353
CISABCD 46
OEABCD 35
SCABCD 71

-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 18020 299K 81 (7)
1 HASH GROUP BY 18020 299K 81 (7)
2 VIEW DBA_OBJECTS 18020 299K 79 (4)


As you can see, 9i query returns the result in the ascending order, where as 10g, didn't.

If the ORDER BY clause is added on 10g database, the explain plan looks like below, an extra sorting is being performaned:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 SORT (GROUP BY)
3 2 VIEW OF 'DBA_OBJECTS'

The default behavior of GROUP BY clause in 9i, provides an internal sorting when the GROUP BY clause is used, avoiding an extra sorting, which in my opinion could improve the performance of the query.

I don't know the internal behavior of HASH GROUP BY algorithm, but, this will be useful when
you don't want data in any order after the GROUP BY clause.

After further investigations, I have found out that HASH GROUP BY has few bugs which
leads to an inaccurate result. As per Oracle, the bug it is resolved in 11g version.

Metalink : Bug 4604970 WRONG RESULTS WITH 'HASH GROUP BY 'AGGREGATION ENABLED .

Alex had discussed about this behavior at his blog:
http://www.oracloid.com/2006/05/hash-group-by-can-give-wrong-result-in-oracle-102/

Disable HASH GROUP SORT in 10g:

Oracle recommend setting the _gby_hash_aggregation_enabled parameter = false OR
optimizer_features_enabled=9.2.0 to get rid of HASH GROUP BY mechanism.

alter session set "_gby_hash_aggregation_enabled" = FALSE;

Id Operation Name Rows Bytes Cost (%CPU)
---------------------------------------------------------------------------------
0 SELECT STATEMENT 2181 37077 997 (2)
1 SORT GROUP BY 2181 37077 997 (2)
2 VIEW SYS_DBA_SEGS 2181 37077 996 (1)

Julian Dyke has a brief explanation note and a example at his site:

http://julian.dyke.users.btopenworld.com/com/Optimisation/Operations/HashGroupBy.html

My colleague did a small test on 9.2.0.7 database where he found out an interesting point that the ordering of the result is different when ORDER BY clause is not specified with the GROUP BY.

drop table test;
create table test(a varchar2(30));

insert into test values ('ahmed');
insert into test values ('zubair');
insert into test values ('11');
insert into test values ('-11');
insert into test values ('Zubair');
insert into test values ('test');

commit;


SQL> set autotrace on exp
SQL> select a, count(*) from test group by a;

A COUNT(*)
------------------------------ ----------
-11 1
11 1
Zubair 1
ahmed 1
test 1
zubair 1

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'TEST'



SQL> select a, count(*) from test group by a order by a;

A COUNT(*)
------------------------------ ----------
-11 1
11 1
ahmed 1
test 1
Zubair 1
zubair 1

Please note that the Schema names are modified to show the test case.

References:

Refer Metalink Note : 345048.1 - 'Group By' Doesn't not Sort If you don't use order by in 10g.
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251893,00.html
Update : (14 July)

I was reading the Oracle University Introduction to SQL course PPTs and in the 'Aggregate Data using The Group Functions' it did mention that the rows are sorted by ascending order of the columns included in the GROUP BY list.

I personally think that was a great idea, it indeed avoid an extra sorting (comes using by ORDER BY clause.


Happy Reading,

Jaffar