Convert Physical Standby To Snapshot Standby and Revert

Oracle Snapshot Database is a fully writable point-in-time clone of a physical standby, ideal for testing and reporting. Snapshot database uses flashback logs to revert to physical standby.

Please note that converting a physical standby to a snapshot standby can be performed with minimal manual effort by using DGMGRL.

Scenario 1: When DG broker is not setup.

-- Check database role , flashback status and cancel the MRP process

SQL> select name, open_mode, database_role from v$database;

SQL> select flashback_on from v$database;

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on;

-- Convert standby database to snapshot database.

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select name, open_mode, database_role from v$database;

SQL> alter pluggable database all open read write instances=all;

--Restart the database

srvctl stop database -d DRDBNAME

srvctl start database -d DRDBNAME

sqlplus "/as sysdba"

alter pluggable database all open read write instances=all;

-- how to revert snapshot standby database to physical standby

In Standby database ,bounce and mount the database.

( This is for RAC setup . In case, you have single instance database ignore below, just mount the database)

Open the database in Mount mode on only one instance and shutdown in all other nodes before executing convert to physical standby statement.

srvctl stop database -d DRDBNAME

srvctl start instance -d DRDBNAME -i DRDBNAME1 -o mount

SQL> alter database convert to physical standby;

--Bounce the database,

srvctl stop database -d DRDBNAME

srvctl start database -d DRDBNAME -o mount

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

--And don't forget to start the Media recovery process

SQL> alter database recover managed standby database disconnect from session;

====================================

Scenario 2: When DG broker is enabled.

SQL> select name, open_mode, database_role,flashback_on from v$database;
NAME           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
DRDBNAME         MOUNTED              PHYSICAL STANDBY YES

host>dgmgrl sys@DRDBNAME

DGMGRL> show configuration;
Configuration - prdcbrm
  Protection Mode: MaxPerformance
  Members:
  prdbname      - Primary database
  drdbname       - Physical standby database 
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 40 seconds ago)

DGMGRL> convert database drdbname to snapshot standby;

Converting database "drdbname " to a Snapshot Standby database, please wait...

Database "drdbname " converted successfully

--- verify from SQL prompt 

SQL> select name, open_mode, database_role,flashback_on from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
DRDBNAME   READ WRITE           SNAPSHOT STANDBY YES

SQL> alter pluggable database all open read write instances=all;

--Once activity completes in snapshot database ( e.g. application POC , expdp etc etc) , you can revert the database role to physical standby. Just connect to DGMGRL and fire -

DGMGRL> CONVERT DATABASE drdbname to PHYSICAL STANDBY;

Converting database "drdbname" to a Physical Standby database, please wait...

Operation requires a connection to database "prdbname"

Connecting ...

Connected to "prdbname"

Connected as SYSDBA.

Oracle Clusterware is restarting database "drdbname" ...

Connected to "drdbname"

Continuing to convert database "drdbname" ...

Database "drdbname" converted successfully

And its all done ! Enjoy !