Snapshot Standby Database on RAC

 

A snapshot standby database is a standby database which can be opened in read-write mode and again rolled back to physical standby mode. Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database but does not apply the redo data that it receives.

The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database. A snapshot standby database can be used for performing different test cases.

Steps to create a snapshot Standby Database

Check both primary and standby role:

select status, instance_name, database_role, open_mode from vdatabase, v$Instance;

select status, instance_name, database_role, open_mode from gv$database, gv$Instance;

Check the archived sequence in standby:

select thread#, max (sequence#) from v$archived_log group by thread#;

Check the Flashback status and db_recovery_file_set location:

select flashback on from v$database;

show parameter db_recovery_file_dest;

Ensure to have enough space in db_recovery_file_dest to store all the required flashback logs.

select * from v$flash_recovery_area_usage;

If flashback is off then turn on flashback.

Click the link below for the steps to turn on flashback

<ENABLE FLASHBACK>   

Status of MRP Process:

set lines 1234 pages 1234;

select

process, status, client_process, thread#, sequence#, block#, blocks, delay_mins

from v$managed_standby;

OR 

ps -eaf | grep mrp

Cancel the MRP IF RUNNING:

alter database recover managed standby database cancel;

Bounce the database and keep in mount stage:

You need to stop all the standby nodes and start one instance in mount mode

srvctl status database -d stby

srvctl stop database -d stby 

srvctl start instance -i stbyl -d stby -o mount

srvctl status database -d stby

After mounting DB if recovery starts automatically then from dmgrl/sqlplus stop recovery:

dgmgrl: 

EDIT DATABASE 'STBY' SET STATE-'APPLY-OFF';

OR

sqlplus:

alter database recover managed standby database cancel;

Convert to snapshot standby database

alter database convert to snapshot standby;

Open the alert log to check if any errors are reported during the creation of the snashot standby

on creating a snapshot standby a gurantee restore points gets created which can be checked using the following query

select name, gurantee_flashback_database from v$restore_point;

to convert the databse back to the same physical standy state this gurantee restore points will be used.

Open the Database in read write mode(as per your use case):

shut immediate; (shut down the instance to start al the Standby instances together)

srvctl start database -d stdby -o open

Both side verify the archived sequence:

Primary side

select thread#, max (sequence#) from v$archived_log group by thread#;

Standby side

select thread#, max (sequence#) from v$archived_log group by thread#;

select process, status, sequence# from v$managed_satandby;

Steps to convert back to Physical Standby:

Stop the database:

srvctl stop database -d stby 

Start one database instance in mount state:

sqlplus / as sysdba

startup mount;

Convert to pysical standby database

alter daabase convert to physical standby;

Stop the database instance and start the database all instances:

srvctl stop database -d stby 

srvctl start database -d stby

Via DGMGRL or manually start recovery:

dgmgrl:

EDIT DATABASE 'STBY' SET STATE='APPLY-ON';

sqlplus:

alter database recover standby database disconnect from session;