Snapshot standby in Oracle 10g

Snapshot standby allow the standby database to open for testing, reporting prupose. You can revert back the transactions to continue in standby mode. You can also use snapshot standby in Oracle 10g.  11g reduces these steps making this a feature they call the “SNAPSHOT DATABASE”.
Steps:1.Set Flash Recovery Area parameters
2.Create a guranteed restore point
3.Activate standby database
4.Perform read/write testing
5.Revert to original standby
1.Set Flash Recovery Area parameters

SQL> alter system set db_recovery_file_dest_size=2G; SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\fra';

2.Create a guranteed restore pointto revert to when you need to return to the original state.

SQL> create restore point pre_activ_snap guarantee flashback database; SQL> select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point; NAME TIME STORAGE_SIZE GUA -------------------------------------------------- -------------------------------- ------------ --- PRE_ACTIV_SNAP 20-MAR-10 07.59.35.000000000 PM 8192000 YES

3.Activate the standby database

SQL> alter database activate standby database; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL MOUNTED CURRENT SQL> alter database open; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL READ WRITE CURRENT

Now the standby database is in READ/WRITE mode after the activation.

PRMY> ALTER SYSTEM ARCHIVE LOG CURRENT; PRMY> ALTER SYSTEM SET DG_BROKER_START=FALSE; PRMY> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

NOW you can use the physical standby safely for testing purposes, it is a fully functional database with data the same as production as of a certain point in time, no new data will be added…remember your Recovery Time Objective.

When testing is over, Revert….back into a physical standby.

STBY> STARTUP MOUNT FORCE; STBY> FLASHBACK DATABASE TO RESTORE POINT HOTFIX1; STBY> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; STBY> STARTUP MOUNT FORCE; STBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

(*using REAL-TIME APPLY)

Some useful SQL commands

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; select * from v$flash_recovery_area_usage;