11g Snapshot Standby Database

A snapshot standby database is a temporarily opened real time production database for testing or reporting. It is a fully updateable standby database after conversion from physical standby. It receives redo data from primary, but does not apply. The redo data is applied after conversion back to physical standby to sync up with primary. It implicitly creates a guaranteed restore point during the conversion to snapshot standby and removes this guaranteed restore point during the conversion back to physical standby.

CONVERT TO SNAPSHOT STANDBY
1. Stop Redo Apply
SQL> recover managed standby database cancel;
2. Ensure the database is mounted, but not open.
3. Convert to Snapshot Standby. This creates the GRP and leaves the database in nomount mode.
SQL> alter database convert to snapshot standby.
4. Restart the database.
SQL> shutdown immediate
SQL> startup

CONVERT BACK TO PHYSICAL STANDBY
1.  Ensure the database is mounted, but not open.
SQL> shutdown immediate
SQL> startup mount
2. Convert to Physical Standby. This discards the changes made to the database, flashback the database to GRP, deletes the flashback logs in recovery area, drops GRP and leaves the database in nomount mode.
SQL> alter database convert to physical standby;
3. Restart the database in mount mode.
SQL> shutdown immediate
SQL> startup mount
4. Start Redo Apply.
SQL> recover managed standby database disconnect from session;

Data Guard - Failed Primary

Prior to Oracle 10g, after a failover occurs, it was impossible to bring the original primary database back into Data Guard configuration as a new physical standby without restoring it. To avoid this restore, the flashback logging must have been enabled or the guaranteed restore point must have been created in the original primary database to allow flashback database to a state prior to its failure. 

Setup a job to create GRP daily and drop GRP of the previous day. See my blog at http://anandbitra.blogspot.com/2011/05/guaranteed-restore-point.html for more info on GRP.

FAILOVER TO PHYSICAL STANDBY

1. Identify any gaps on standby.

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

2. If primary host is accessible, copy missing logs and register on standby.

SQL> alter database register physical logfile 'path';

3. Initiate failover on standby. Force terminates active RFS process.

SQL> recover managed standby database finish force;

4. Convert the standby to primary.

SQL> alter database commit to switchover to primary;

5. Open this new primary database.

SQL> shutdown immediate

SQL> startup

6. Stop redo transport to failed primary.

SQL> alter system set log_archive_dest_state_2='defer';

CONVERT FAILED PRIMARY TO PHYSICAL STANDBY

1. Determine SCN at which old standby became primary.

SQL> select to_char(standby_became_primary_scn) from v$database;

2. On failed primary, flashback to GRP.

SQL> startup mount

SQL> flashback database to restore point <before_failure>;

3. Recover database until SCN.

SQL> recover database until change <standby_became_primary_scn>;

4. Convert to physical standby.

SQL> alter database convert to physical standby;

5. Mount the database.

SQL> shutdown immediate

SQL> startup mount

6. Start redo apply on this new standby. 

SQL> recover managed standby database disconnect from session;

7. Start redo transport on primary.

SQL> alter system set log_archive_dest_state_2='enable'; 

SWITCHOVER TO ORIGINAL PRIMARY (CURRENTLY PHYSICAL STANDBY)

1.  On primary, switchover to physical standby.

SQL> alter database commit to switchover to physical standby with session shutdown;

2. Mount the database.

SQL> shutdown immediate

SQL> startup mount

3. Start Redo apply.

SQL> recover managed standby database disconnect from session;

4. On standby, switchover to primary.

SQL> alter database commit to switchover to primary;

5. Open the database.

SQL> shutdown immediate

SQL> startup