Convert Physical Standby To Snapshot Standby Database

In this article, we are going to learn how to convert physical standby to snapshot standby database step by step on linux platform.

What is snapshot standby database?

Snapshots allow for a read-write operation on the standby database. We can use snapshots for testing or for developing databases, which are exact replicas of production databases. Once testing or development is complete, we can convert the snapshot standby to physical standby database to undo any changes made during the snapshot phase.

Pre-check on primary

Before changing anything, you must check the production and primary are in sync, if not make them in sync then start the activity. The below command will show you about the sync status.

SQL>col STBY_BEHIND_BY for a20
SQL>select (select sysdate from dual)"TIMESTAMP",s.thread#,s.MAX_SEQ#, a.MAX_APP_SEQ#, (s.MAX_SEQ#-a.MAX_APP_SEQ#) "Difference" from
( select thread#,max(sequence#) "MAX_SEQ#" from v$archived_log group by thread# ) s,
(select thread#,max(sequence#) "MAX_APP_SEQ#" from v$archived_log where applied='YES' group by thread#) a where a.thread#=s.thread# order by s.thread#;

Pre-check on standby

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE          OPEN_MODE
----------------------------------    -----------------------------------------
PHYSICAL STANDBY    READ ONLY WITH APPLY
1 row selected.

Step 1: Stop the MRP process

SQL> alter database recover managed standby database cancel;
Database altered.

Step 2: Check FRA

Check FRA (Flash Recovery Area) if it is not configured, then configure it using the below steps.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
----------------------------------- ------------ ------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0

In my case the FRA is not configured, let me configure it.

How to configure FRA

SQL> alter system set db_recovery_file_dest_size=5g;

System altered.

SQL> alter system set db_recovery_file_dest='/u02/ocpdb/';

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- 
db_recovery_file_dest string /u02/ocpdb/
db_recovery_file_dest_size big integer 5G

Step 3: Convert physical standby to snapshot standby database

Using the below commands you can convert your physical standby to snapshot standby.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open;

Database altered.

Now check the physical standby role and open mode.

SQL> select database_role, open_mode from v$database;

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

1 row selected. 

We successfully convert physical standby to snapshot standby database, start your application testing now, once your testing is completed and you want to revert back to the standby as physical standby database, please follow the below steps.

Step 4: Convert snapshot standby to the physical standby database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 435644608 bytes
Fixed Size 2161272 bytes
Variable Size 141847128 bytes
Database Buffers 2173741234 bytes
Redo Buffers 148703264 bytes
Database mounted.

SQL> alter database convert to physical standby;

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 435644608 bytes
Fixed Size 2161272 bytes
Variable Size 141847128 bytes
Database Buffers 2173741234 bytes
Redo Buffers 148703264 bytes
Database mounted.

Check physical standby database role and open mode.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE          OPEN_MODE
----------------------------------    -----------------------------------------
PHYSICAL STANDBY    READ ONLY WITH APPLY

1 row selected.

Step 5: Start the MRP process

After completing the activity we successfully rollback the standby into the physical standby database, and now start the MRP process.

SQL> alter database recover managed standby database disconnectfrom session;
Database altered. 

• 
  How to Add New Disk in ASM DiskGroup
• 
  Ways of generating EXECUTION PLAN in Oracle Database
• 
  Convert Physical Standby To Snapshot Standby Database
• 
  ORA-00020: maximum number of processes exceeded
• 
  Drop Database Manually in Oracle
• 
  Solve ORA-12528