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