Convert Physical Standby To Snapshot Standby

How to Convert Physical Standby To Snapshot Standby?

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 standby database allows us to use the available data on the physical standby database. suppose you are going to change in your application and before implementing on production environment you want to test it, so we can test it on the snapshot standby database. when we convert physical standby into a snapshot standby database a guaranteed restore point is created automatically. once our testing is completed, we convert back snapshot standby database to the physical standby database using flashback guaranteed restore point. After revert back, all the changes mean that transactions made when the standby database was opened in READ WRITE mode will be flushed out.

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.

Check Details with primary Database

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 database Details

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 is configured on the standby database or not.

 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. Cancel the MRP process, shutdown the physical standby database and startup mount.

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. 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 in read write mode.

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 

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 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?

Step 1. Shutdown the snapshot standby database and startup mount.

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

SQL> startup mount
.
Database mounted. 

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 finished 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.

Oracle 19c Data Guard Configuration step by step

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

Share this
Share

1 thought on “How to Convert Physical Standby To Snapshot Standby?”

Leave a Comment

Share