Converting physical standby database to snapshot standby

Converting physical standby database to snapshot standby

All the following steps to be executed on standby database only..


Set the size for recovery area.

Alter system set db_recovery_file_dest_size=100G;

Set Flash recovery area.

Alter system set db_recovery_file_dest='+RECO_DG'

Stop managed recovery if it is active.
 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Bring the physical standby database to mount stage.

Startup mount;

Convert physical standby database to snapshot standby database.

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Shutdown Immediate;

Startup;

select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE SNAPSHOT STANDBY

Converting Snapshot standby database to physical standby

Shutdown Immediate;

Startup mount;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Shutdown immediate;

Startup mount;

select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
MOUNTED    PHYSICAL STANDBY

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Note: During this time archivelogs are getting shipped to standby from primary