Tuesday, May 13, 2014

Converting a Failed Over Standby Database Back to Standby Database

I have seen some articles on the internet for reinstating a primary database after a failover (using flashback database technology – if FLASHBACK RETENTION time has not exceeded), but I was not able to find a document to revert or reinstate a “failed-over” standby database (which has now become primary) back to a standby database (using FLASHBACK technology). 

Some time back I had a scenario where I needed a failover of standby database for testing purposes, and after failover; I needed to rebuild the standby again. I did not want to rebuild the standby because of huge size of the database; as it would have required a very long time for me to rebuild it - then I came up with this idea and tried it, which worked. Detailed steps to perform this are bellow.

Following exercise was performed on Oracle 10.2.0.4 running on Windows 2008. Same steps should also work on any other platform and other Oracle versions greater than 10.2.0.4. For Standby RAC, this should be performed from one node while keep other instance(s) shutdown.


Before Failover

On Primary
Disable log shipping from primary by setting log_archive_dest_state_n to "defer" (we need to make sure that archives remain available on the disk so that these could be sent to standby later, when required)
SQL> ALTER SYSTEM SET log_archive_dest_2=’defer’;

On Standby

Perform following tasks on standby database before performing a failover.
Set up flash recovery area (if not set up already).
SQL> alter system set db_recovery_file_dest_size=40g;
SQL> alter system set db_recovery_file_dest='d:\test\fra\';

For RAC, FRA should be on a shared location - for example on an ASM diskgroup accessible to all instances.
SQL> alter system set db_recovery_file_dest='+data';


Enable standby flashback with flashback retention to a sufficient amount of time based on how long it will take to do the testing on this database after the failover, and before reverting it back to standby.
SQL> shutdown immediate
SQL> startup mount
SQL> alter system set  DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days (in minutes)
SQL> alter database flashback on;

Create a restore point.
SQL> create restore point before_failover;

Perform the Failover
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup

Now we can do the testing on this this failed over stabndby database

Reinstating the opened database back to Standby Role.
After we are done with testing, we can now revert back this failedover standby database, back to standby database using following commands
Flashback database back to Restore Point
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT before_failover;

Convert database back to Standby
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Enable log shipping from Primary
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ‘enable’

Related Article

No comments:

Post a Comment