12.10.2007

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

8 comments:

Patrick said...

Good one. Thank you for the info.

Patrick said...

Jaffar

I forgot to ask, are you using BROKER or EM Grid to manage your dataguard?

Looks like EM Grid..

P-

The Human Fly said...

Patrick,

Neither we are using DG Broker nor EM Grid to manage the standby. We setup and matain manually.

Jaffar

Anonymous said...

Good article.Keep writing..

few typos to correct :

Command to FLASHBACK :
FLASHBACK DATABASE to restore point before_rw;

Command to activate redo shipping at primary :

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The Human Fly said...

Hello Sandeep,

Thanks for nice comments and correcting the typing mistakes.

Yadu said...

Hi Jaffer,

Thanks for sharing this tip with us.I feel the same thing(restore points) happens in background in the Oracle11g snapshot database feature.

I am small time blogger and I have given link to your document in my blog.Hope you dont mind.If you feel uncomfortable I can always delete that.Thanks

Yadu

MsJ said...

Jaffar it is nice excellent job.

Patrick said...

ditto...good info again!

P