Convert physical standby to snapshot standby database in dataguard Oracle

Snapshot standby database which allows to use of the physical standby database in read-write mode for a shorter time. Snapshot standby database receives and archives, but does not apply the redo data. 

Note: All local updates will be discarded when the snapshot database is converted back to the physical standby database. 

Note: A restore point is created when a physical standby database is converted into a snapshot standby database and that restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

Steps to convert the Physical Standby Database to the Snapshot Standby Database

1. Configure the flash recovery area if not configured in the Primary (Primary)

-- Set the size for recovery area.
Alter system set db_recovery_file_dest_size=<size>

-- Set Flash recovery area.
Alter system set db_recovery_file_dest=<path>

2. Stop the managed service and bring the physical standby database to the mount stage. (Standby)

-- Stop the media recovery service
 SQL> alter database recover managed standby database cancel; 

--Down the database
Shutdown immediate;
Startup mount;

3. Convert the physical standby database to a snapshot standby database. 

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 

-- Database is dismounted on completion, restart the database again
Startup

4. Check the status of Oracle Database

SQL> select open_mode,database_role from v$database;

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

Steps to convert the Snapshot Standby Database to the Physical Standby Database

1. Shut down the snapshot standby database. (Standby)

Shutdown immediate;

2. Bring the database into the mount stage:

startup mount;

3. Convert the database into a physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

4. Shutdown the database and mount it

Shutdown immediate
Startup mount

5. Check the status of database

SQL> select open_mode,database_role from v$database;

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

6. Start the media recovery process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;