Snapshot standby database allows to open the database in read/write mode and continues to ship the redo from primary which can later be applied after reverting the database to Physical standby.
Application has a test case which needs to be tested in production, so converted the database to snapshot standby database and reverted back to physical standby after they finished their testing.
Check if Flash back enabled for the database
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO
Set the DB_RECOVERY_FILE_DEST and FLASH recovery area. Default retention(DB_FLASHBACK_RETENTION_TARGET) time is 1 day, so set the retention period and size as required.
SQL> alter system set db_recovery_file_dest=’/u03/ofaroot/PROD_FLASHBACK/’ scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=50G scope=both;
System altered.
Mount the Instance:
SQL> startup nomount
ORACLE instance started.Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
SQL> alter database mount standby database;Database altered.
Check if parameters are modified:
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u03/ofaroot/PROD_FLAS
HBACK
db_recovery_file_dest_size big integer 50G
Enable Flashback:
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
YES
Stop the MRP Process and start the DB in mount mode
SQL> alter databAse recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.
SQL> select name from v$database;NAME
———
PROD
Convert the database to Snapshot standby:
SQL> alter database convert to snapshot standby;
Database altered.
Alertlog Entries:
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED 12:41:17
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 7195028071
Resetting resetlogs activation ID 3952627246 (0xeb984e2e)
Snapshot database creates a guaranteed restore point , so make sure sufficient space is maintained in flash recovery area.
Open the database:
SQL> alter database open;
Database altered.
SQL> select instance_name,database_role,open_mode from v$database,v$Instance;
INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————- —————- ——————–
PROD SNAPSHOT STANDBY READ WRITE
Reverting the database to physical standby:
SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.SQL> alter database convert to physical standby;
Database altered.
Alertlog Entries:
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (PROD)
krsv_proc_kill: Killing 3 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped
Convert to Physical standby statement will flashback to the guaranteed restore point and drops the guaranteed restore point as they do not age out.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.Total System Global Area 1.0189E+10 bytes
Fixed Size 2247376 bytes
Variable Size 6023021872 bytes
Database Buffers 4143972352 bytes
Redo Buffers 19460096 bytes
Database mounted.
Start the MRP:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select instance_name,database_role,open_mode from v$database,v$Instance;
INSTANCE_NAME DATABASE_ROLE OPEN_MODE
—————- —————- ——————–
PROD PHYSICAL STANDBY MOUNTED
LEARNING DBA 