How To Open Physical Standby For Read Write Testing and Flashback
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
FRA Location: /u01/oracle/flashback
Restore Point Name: STANDBY_FLASHBACK_TESTING
Sample Table: testing
Sample data: 'testing for flashback on standby database'
*****************
Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.
Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.
Oracle 11g has a feature in this area called : Snapshot standby database. A Snapshot Standby Database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database.
A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
Using a single command changes made while read-write mode can be discarded and quickly resynchronize the
The below procedure is applicable for 10g but also for higher versions.
Step 1 - In Standby database
A ) Set up a flash recovery area.
If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';
B ) Cancel Redo Apply and create a guaranteed restore point.
SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE;
To Confirim the details of restore point and its scn and time stamp run
NAME SCN TIME
-------------------------------------------------- ------------- ------------------------------
STANDBY_FLASHBACK_TESTING 22607810 12-APR-09 01.10.21.000000000 P
Step 2 - In Primary Database
A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database. When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.
B ) Defer log archive destinations pointing to the standby that will be activated.
Step 3 - In Standby database
A ) Activate the physical standby database:
Once its done you can check the controlfile status will be changed from Standby to Current
CONTROL
-------
CURRENT
B) Then open the database.
SQL> ALTER DATABASE OPEN;
Step 4 - In Standby database
Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database
For example :
Table created.
SQL> insert into testing values ( 'testing for flashback on standby database');
1 row created.
SQL> commit;
Commit complete.
Step 5 - In standby database
A ) Revert the active standby database back to Physical standby database
A1. Mount the database.
A2. Flashback the database to restore point.
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1290208 bytes
Variable Size 159383584 bytes
Database Buffers 125829120 bytes
Redo Buffers 2904064 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;
You can confirm the same by checking the controlfile status. It will be now backup controlfile
CONTROL
--------------
BACKUP
B ) Convert to Standby database
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select controlfile_type from v$database;
--------------
STANDBY
Step 6 - In standby database
A ) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap.
Step 7 - In Primary database
A ) Re-enable archiving to the physical standby database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
Step 8 - In Standby database
A ) Open the database in Read only mode and ensure that all the transaction done in active mode are no more
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> select * from testing;
select * from testing
*
ERROR at line 1:
ORA-00942: table or view does not exist
B ) Drop the restore point
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> DROP RESTORE POINT Standby_flashback_testing ;
Caution:
While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.
Oracle Metalink - Doc ID 805438.1
No comments:
Post a Comment