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