1Z0-048 Oracle Database 10g: Real Applications Clusters Administrator Certified Expert - CLEARED

Well, this morning I have passed the 1ZO-048 RAC expert exam.

Let me reveal about my preparations before my mail box gets filled by hundreds of mails asking me to provide documents and etc stuff. For me, Oracle RAC documentation, Oracle university (RAC course) material and of course the extensive production practice knowledge of RAC certainly helped me a lot to prepare and pass this exam. I am sorry, I can't offer anything more than this.

After the exam, a small thought comes into my mind. Since this exam comes under EXPERT category, I thought, it would have been more better doing a day practical lab exam than simply doing the multiple choice questioning exam.

Have nice time,

Jaffar

Odd issues while preparing Physical Standby

After an uphill task of upgrade/migration of 2.5TB sized data warehouse Oracle database from 9i to 10g and then from one OS to another (cross platform), the subsequent challenge is building a DR (Standby Database) for this database.

The DR (Disaster Recovery) site is around 3 km away from the HO. Since the size of the database is huge, we thought of moving tapes (physically) or duplicating the backup tapes from HO to the DR site, but, none of the ideas was materialized due to few technical/ un-technical difficulties. Fortunately, the speed of the lease line (leaser link) between the two sites is good enough and we decided trigger the duplicate database (standby) command from HO.

Ufff.. It took 3 days to finish the restore. However, the subsequent recovery on the standby database failed by the following problem:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/19/2007 05:16:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-19625: error identifying file /datafile1.dbf
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 2

Surprisingly, the datafile, datafile1.dbf (filename is changed here) which Oracle complaints was exists on the mount point. Then, thought of starting the MRP (recover managed standby database disconnect from session), unfortunately, the MRP terminated by the following errors:

Errors in file /dbdata/oradba/admin/OFDMP/bdump/ofdmp_dbw0_1077358.trc:
ORA-01157: cannot identify/lock data file 1479 - see DBWR trace file
ORA-01110: data file 1479: '/mountpoint/
datafile2.dbf'

Again, the complained file exists on the mount point, however, this time the problem was that the file has ‘carriage return’ before the name, (this may happen when you try to create/add a datafile from OEM). The workaround was following:

mv /mountpoint/ datafile2.dbf’ /mountpoint/datafile2.dbf

and then, change file name in the controlfile with the following command:

(make sure the standby_file_management is set to MANUAL on the standby).

alter database rename file ‘/mountpoint/ datafile2.dbf’ /mountpoint/datafile2.df’;

After the above workaround, the MRP (Media Recovery Process) was started successfully and looking for the archive gap.

When I tried to restore required archived log on the standby database from the backups (archived backups), the restored command failed with the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/20/2007 13:44:20
RMAN-20242: specification does not match any archive log in the recovery catalog

I thought of just testing the restore on production, the restore (tried t restore single archive log) was successfully.

I lately realized that the missing archives are generated after creating the standby controlfile and obviously the standby controlfile was not having the information about these archived logs. (Backups were performed with nocatalog option).

Following is the workaround:

1. Create a catalog and register production database.

2. On standby, using the catalog (rman target / catalog). Now, standby can get the info about the backups through the catalog.

3. Restoring required archived logs.

Earlier, I have done dozens of DG configuration facing different challenges. Well, everything it’s a new challenge and learning experience.

Happy reading,

Jaffar

Simulating 11g Snapshot Standby Database feature on Oracle 10g?

As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

What if you want to achieve the same on Oracle 10g? Well, I absolutely don’t have any clue about others, but, we have come across of such situation couple of days ago when our DR (Disaster Recovery Solution) team came to us with a request to test our standby database. They want the standby database in read write mode to do some real scenario tests and once the testing is done, they want the database to be back to standby mode.

We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.

The procedure as follows:

1. Set the following parameters on the standby database:

db_recovery_file_dest_size & db_recovery_file_dest

- Make sure the values are reflected.

2. Stop the media recovery process, if active.

3. When the standby is in MOUNT mode, Create a guaranteed restore point:

CREATE restore point before_rw guarantee flashback database;

3. Stop the log shipping on the primary database. (for safer side)

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

4. Failover the standby database using the following command:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

-Make sure the media recovery process is turned off

-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.

5. Open the database (read write mode).

AT THIS POINT, YOU CAN USE THIS DATABASE AS NORMAL READ WRITE DATABASE.

Reverting the database back to standby mode:

• Shutdown the database
• Startup database in mount mode
• Flash back database to restore point using the following:

FLASH BACK DATABASE TO RESTORE POINT before_rw;

• Convert the database back to standby mode using the following:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

• Shutdown the standby database and remove the previously set parameters.
• Start the standby database in mount state and drop the restored point.
• Enable the Media Recovery on the Standby database.
• Activate log shipping on the primary using the following:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENALE;

Since, we have done very little changes in the database, after converting to read write mode, the time which took to revert the database back to standby mode took few minutes only. Well, it is definitely need to be seen the time that take during the conversion to standby mode after huge changes in the read write database.

It worked well with us and Mr. Asif definitely deserved an appreciation

The tests have been carried out on AIX 64 bit with Oracle 10.2.0.3 release.

In the Part II, I will be posting my testing.

Happy Reading,

Jaffar

Back from vacation

I just back to office after a month vacation and going through the back log/pending work. Currently, having an uphill task of upgrading an Oracle 9i data warehouse database (3 TB size) to Oracle 10g and also migrating this database from HP to AIX.

Its been nearly two months that I didn't write anything to my blog also didn't participate in any kind of database forums as I was gearing up for my every first seminar (RMAN). I have successfully delivered my one exclusive RMAN seminars in Sweden and Denmark respectively in September month. Good amount of participants were present, 20 participants in Sweden and 28 participants in Denmark. Although, the seminars went well, I felt still there was a lot room for improvement. I was bit worried about the time constraint to complete the seminar. In the modified RMAN seminar contents, now, more practical examples with 11g RMAN new features and changes are also added.

The first thing which I did was installing 11g on Windows OS in my PC. I am more interested to test RMAN changes and testing Active Standby Database(snapshot database) in 11g database. I am also busy in preparing a new one day exclusive seminar on High Availability Data Guard.

In my next blog, I am going to discuss about my experience with Active Standby Database and findings.

Thank for your time,

Jaffar

11g Database Creation with DBCA

After successfully completing Oracle 11g on Red Hat Linux(v4), I thought of creating a new database using DBCA utility instead creating manually. While using DBCA utility to create a new database, I have found following three new changes:

1. If no listener is configured, DBCA utility prompts a message to configure a listener first.
2. Security Settings gives the following options:
- Keep the enhanced 11g default security settings(recommended)These settings include
enabling auditing and a new default password profile.
- Revert to pre-11g default security settings
3. Automatic maintenance task tasks
such as optimizer statistics collection and proactive advisor reports.
The default maintenance windows are 10:00 pm to 2:am on weekdays and all weekend long.

When I count records from v$system_event, surprisingly, I found only 77 records? Is it okay?

Jaffar