Having a Disaster Recovery site is mandatory for most of the companies that cannot afford having their systems down for a long time because of unplanned outages caused by non-recoverable disasters and natural calamities. A Disaster Recovery (DR) site allows a company to continue their operations using a mirror copy of their system located at a different physical location than the production site.
One of our tasks as IT professionals is to guarantee that the DR site works properly. We also must be able to document every step required if switching to the DR Site is necessary. This includes scheduling DR tests where we can simulate every event, load, and service that will be enabled on the DR site when it is performing the role of primary site while production is down.
After the DR test is completed, one of the most common questions is what we can do to avoid having to rebuild the DR Site from scratch. One of the most useful features provided by Oracle is Flashback Database (available for Enterprise Edition). Setting Flashback on the DR site will reduce significantly the time and effort needed to restore a standby database to a point in time before the DR test begin. It won’t add complexity to the DR test. Using flashback for a DR test doesn’t need very specialized resources for the execution and will ultimately save money.
One of the biggest benefits of using flashback is to avoid recreating the standby database after the DR Test. Under ordinary circumstances, the copy of the production backup is not easy to transfer from site to site. For some companies, it may take days and a huge amount of resources to copy the files over the network. Other companies cannot use the network to transfer the backup, so it will have to be shipped to the DR location. There is no need, however, to recreate the standby database after the DR test if Flashback Database was enabled and a restore point was created before the activity. While rebuilding the standby database may take several hours, rolling back the standby database using Flashback may take minutes.
Flashback Database won’t add complexity to the DR test. After enabling Flashback Database on the standby, we can create a restore point of the database before the DR Test starts with a single command:
CREATE RESTORE POINT before_DRTEST GUARANTEE FLASHBACK DATABASE;
Once the test is done, we can easily roll back all the changes and restore the standby database to the restore point created at the beginning of the test by using the following command:
FLASHBACK DATABASE TO RESTORE POINT before_DRTEST;
Using Flashback when testing DR will also save money. If we summarized all the costs involved on rebuilding a DR Site, we may clearly see big savings using Flashback. There won’t be all the extra cost on shipping and copying the backup from site to site, no specialized resources are involved in restoring the database to the created restore point, and no extra space is needed to restore or mount the backup tape on the DR site. Also, if we consider the risk of having the primary database operating without a DR site and the financial and data losses for the company in case of a disaster happening while the DR is being rebuilt, we’ll be able to see that the savings for the company can be huge.
Finally, setting and using Flashback for a DR test doesn’t need very specialized resources for the execution. The process can be executed by any person who is in charge of the DR test overall. Of course, we always need to have good documentation of every step, since reacting to any unplanned outage requires following very clear procedures for each scenario.
You can find the steps to set Flashback Database on the standby, enabling the standby for DR test and the steps to rollback all the changes on DR database, below.
Enabling Flashback Database on Standby:
A. Cancel the recovery process. SQL> alter database recover managed standby database cancel;
B. Set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes. SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 scope=spfile; #3days
C. Restart the standby database and enable Flashback. SQL> shutdown immediate; SQL> startup mount SQL> alter database flashback on;
Enabling the standby database for DR test:
On Your Primary Database:
A. Perform manual switches. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
B. Make sure the standby and primary database are in sync. SQL> select max(sequence#) from v$log_history;
C. Stop synchronization and shipping of archives from the primary database. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STAT2_2=DEFER;
On Your Standby Database:
A. Cancel the recovery process. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
B. Create a guarantee restore point. SQL> CREATE RESTORE POINT before_DRTEST GUARANTEE FLASHBACK DATABASE;
C. Activate the standby database. SQL > ALTER DATABASE ACTIVATE STANDBY DATABASE;
D. Open the standby database. sql > alter database open NOTE: The DR database can be used for testing. All the changes made during DR test will be rolled back when the test is completed.
Rollback Changes on DR and Post-DR Test Tasks:
On your Standby Database: SQL> shutdown immediate; SQL> startup mount SQL> Flashback Database to restore point before_DRTEST; SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
On your Primary Database: A. Enable the archive destination. sql> alter system set log_archive_dest_stat2_2=enable;
On your Standby Database: A. Start the recovery process. sql> alter database recover managed standby database disconnect from session;
Oracle is Easy! Right?