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.
Read - Resolve 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?”