How to convert physical standby database to snapshot standby database

Snapshot Standby database feature in dataguard environment gives us opportunity to open standby database in read write mode and then return to back as physical standby in read only.

We can use dgmgrl or sqlplus to convert physical standby database to snapsho standby database, here I'll write both method.

To open physical standby database in read write mode as snapshot standby database, flashback must be on.

Using SQLPLUS to convert phyical standby database to snapshot standby database:

SQL> alter system set db_recovery_file_dest='+DG';
System altered.

SQL> alter system set db_recovery_file_dest_size=500G;
System altered.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     MOUNTED              NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> startup

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     READ WRITE           NONE    SNAPSHOT STANDBY

Convert snapshot standby database back to physical standby database:

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     READ WRITE           NONE    SNAPSHOT STANDBY

SQL> shut immediate

SQL> Startup nomount

SQL> alter database mount;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.

SQL> shut immediate

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME      OPEN_MODE            GUARD_S DATABASE_ROLE
--------- -------------------- ------- ----------------
TESTDB     MOUNTED              NONE    PHYSICAL STANDBY

Using DGMGRL to convert snapshot standby database to physical standby and return back:

-- convert physical standby to snapshot standby
DGMGRL> CONVERT DATABASE TESTDB TO SNAPSHOT STANDBY;

-- convert snapshot satndby to physical standby
DGMGRL> CONVERT DATABASE TESTDB to PHYSICAL STANDBY;