Snapshot Standby Database Non 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 for Creating Snapshot Standby Database

Check DB Status

select status, instance_name, database_role, open mode from v$database, v$Instance;

Check Flashback Status:

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;

Cancel the MRP IF RUNNING:

alter database recover managed standby database cancel;

Convert to Snapshot Standby:

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):

alter database open;

Check DB Status

select status, instance_name, database_role, open mode from v$database, v$Instance;

Steps to convert a snapshot standby back to physical standby

Verify the status of the DB:

set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select * from V$FLASH_RECOVERY_AREA_USAGE;

Ensure the DB is in mount state:

shut immediate;

startup mount;

set lines 300

set pages 3000

select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

select process,status,client_process,sequence# from gv$managed_standby;

Convert the db back to physical standby 

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

Start the DB back again to mount/read only based on your need

shut immediate;

startup mount;

set lines 300

set pages 3000

select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

select process,status,client_process,sequence# from gv$managed_standby;

Put the db in recovery mode

alter database recover automatic managed standby database disconnect from session;

set lines 300

set pages 3000

select process,status,client_process,sequence# from gv$managed_standby;

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

Perform a log switch in primary and check in standby if the recovery is all good

--primary:

alter system switch logfile;

--standby:

set lines 300

set pages 3000

select process,status,client_process,sequence# from gv$managed_standby;