How to Convert Physical Standby To Snapshot Standby Database?

Hello friends, In this article, we are going to discuss how to convert physical standby to snapshot standby database manually.

What is snapshot Standby?

As you know the standby database is a copy of the primary database. so Oracle snapshot Oracle provides the snapshot standby function, in which the read-write operation is possible without worry. This means that we have a tested and exact replication of the production environment for development purposes only once testing is complete. When ready, we can revert any changes done to the standby environment with a quick conversion back to its original state as a physical standby database.

Note:- Before converting physical standby into the snapshot standby database the FRA (Flash Recovery Area) must be configured on the physical standby database. It is not necessary to have flashback enabled.

Production Environment

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

 STATUS   INSTANCE_NAME   DATABASE_ROLE   OPEN_MODE
  -------    -------------------   --------------          --------------
  OPEN        ocp                      PRIMARY               READ WRITE 


SQL> select thread#,max(sequence#) from v$archived_log group by thread#; 

 THREAD#     MAX(SEQUENCE#)
  -------         --------------
  1                   99 

Standby Environment

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

 STATUS   INSTANCE_NAME   DATABASE_ROLE           OPEN_MODE
  -------    -------------------   -----------------------    --------------
  OPEN        ocpdr                  PHYSICAL STANDBY   READ ONLY WITH APPLY   



SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD#     MAX(SEQUENCE#)
---------- --------------
1                       99



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

As you can see in the above outputs, the standby database is in sync with the Primary database.

Check FRA Configuration on standby

In my case, the FRA is configured on the standby database.

SQL> show parameter db_recovery_file_dest
 
NAME                         TYPE                VALUE
---------------------------  -----------  -------------
db_recovery_file_dest        string       +FRA_OCP
db_recovery_file_dest_size   big integer  2122M 

Step 1: Stop MRP process

Cancel the MRP process, shut down the physical standby database and startup mount normally.

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

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

SQL> startup mount 
.
 Database mounted. 

Step 2: Convert into snapshot standby

After mounting the database, convert it into the snapshot standby database.

SQL> alter database convert to snapshot standby;
Database altered. 

After the above command, your physical standby database becomes a snapshot standby database, now you can open it in read-write mode.

Step 3. Open snapshot standby database.

SQL> alter database open;
Database altered. 

Check mode

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
 
STATUS     INSTANCE_NAME   DATABASE_ROLE     OPEN_MODE
-------- --------------- ----------------      -----------
OPEN            ocpdr          SNAPSHOT STANDBY    READ WRITE 

Step 4: Test snapshot standby environment

Let’s check the snapshot standby database to create a user and one table. Create a user in a snapshot standby database.

SQL> create user ocptest identified by ocptest ; 
 User created. 

After creating a user, grant privileges.

SQL> grant connect,resource to ocptest;
 Grant succeeded.

Create a table and insert a few records.

SQL> conn ocptest/ocptest@ocpdr
 Connected.

SQL> create table test(id number, name char(15));
 Table created.

SQL> insert into test values (10,'AARAV');
 1 row created.

SQL> insert into test values(20,'AATHARVA');
 1 row created.

SQL> commit;
 Commit complete.

 SQL> select * from test;
CODE       NAME
---------- --------------
10                  AARAV
20                  AATHARVA  

SQL> update test set ID=50 where name='AARAV';
 1 row updated.

SQL> commit;
 Commit complete.

SQL> select * from test;
CODE       NAME
---------- --------------
50                  AARAV
20                  AATHARVA 

Yes, our snapshot standby works properly. Let’s check archive RFS status on standby. The archive sequence number on the primary:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
 
THREAD#     MAX(SEQUENCE#)
---------- --------------
1                     102 

On Snapshot Standby

SQL> select process,status,sequence# from v$managed_standby;

 PROCESS   STATUS             SEQUENCE#
 ----------   ----------           --------------
ARCH         CLOSING                   1
ARCH         CONNECTED             0
ARCH         CONNECTED             0
ARCH         CONNECTED             0
RFS            IDLE                           0
RFS            IDLE                           103
RFS            IDLE                           0

 7 rows selected.

As you can see, on the production database the latest sequence generated is 102 and on the snapshot standby database, the RFS process is idle for sequence 103.

How to convert snapshot standby to physical standby?

On Standby

Step 1. Shut down the snapshot standby database and startup mount.

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

SQL> startup mount
.
Database mounted.

Convert snapshot standby into physical standby

Using the following command, we can convert the snapshot standby database into the physical standby database.

SQL> alter database convert to physical standby;
Database altered. 

After finishing the above command, take the restart standby database and start the MRP process.

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

SQL> startup mount
.
Database mounted. 


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

 STATUS   INSTANCE_NAME   DATABASE_ROLE             OPEN_MODE
  -------    -------------------   -----------------------      --------------
  OPEN        ocpdr                  PHYSICAL STANDBY    READ ONLY WITH APPLY 

Start the MRP process

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

Check MRP process is running or not.

 SQL> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      WAIT_FOR_LOG         105  

Check snapshot standby username and table which we created.

SQL> select username,account_status from dba_users where username='OCPTEST';
no rows selected 

The snapshot standby data which we created is flushed out after converting it in a physical standby database.

I hope you found this article very informative, if yes please share your comment.

ReadResolve huge archive gap between primary and standby

Connect with me on:
Instagram: https://www.instagram.com/shripaldba
Linkedin: 
https://www.linkedin.com/in/shripal-singh
Twitter: 
https://twitter.com/ocptechnology
Facebook: 
https://www.facebook.com/ocptechnology
YouTube:
https://www.youtube.com/ocptechnology

3 thoughts on “How to Convert Physical Standby To Snapshot Standby Database?”

Leave a Comment