Physical Standby To Snapshot Standby

Description:
In this Blog, we are going to see what is snapshot standby database and how to convert a physical standby database to a snapshot standby database with Demo.

Snapshot Standby Database:

Snapshot standby allows doing a read-write operation on the standby database.
We can convert the physical standby database to snapshot standby.
On that, we can do all types of testing or can be used as a development database.
Once the testing is over we can again convert the snapshot database to physical standby.
Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.

Data Guard Configuration Environment:

Overall Steps:

1. Check primary and standby database role and open mode.
2. Check the archived sequence value in standby.
3. Disable the MRP process.
4. Check the flashback status if it not enabled, enable the flashback.
5. Check the db_recovery_file_set location and size.
6. Bounce the database and start the db as mount stage.
7. Convert physical standby to snapshot standby database.
8. Open standby database and check open mode READ/WRITE.
9. Test the snapshot standby database now we can create user and tables 
for testing purpose.
10. verify the log sequence primary and standby.
11. Bounce database again open in mount stage.
12. Convert snapshot standby to physical standby.
13. Bounce the database.
14. Check the database role has been changed as physical standby and the 
mode as read only.
15. After bounce enable the MRP process and check table available or not.

Step 1- Check primary and standby database role and open mode.
Primary database:
[oracle@agent ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:16:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN agent PRIMARY READ WRITE
SQL>

Standby database:
[oracle@data ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:18:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN data PHYSICAL STANDBY READ ONLY WITH APPLY
SQL>

Step 2:- Check the archived sequence value in standby:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 14

Step 3:- Disable the MRP process in the standby database:
SQL> alter database recover managed standby database cancel;
Database altered.

Step 4:- Check the flashback status if it is not enabled enable the flashback.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Step 5:- Check the db_recovery_file_set location and size.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 8256M

Step 6:- Bounce the database and start the DB as mount stage:
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
Database mounted.

Step 7:- Convert physical standby to snapshot standby database:
SQL> alter database convert to snapshot standby;
Database altered.

Step 8:- Open standby database check read/write mode, and database role:
SQL> alter database open;
Database altered.
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
MOUNTED data SNAPSHOT STANDBY MOUNTED

Step 9:- Test the snapshot standby database now we can create user and tables for testing purposes.
SQL> create user agent identified by vbt default tablespace users quota unlimited on users;
User created
SQL> grant connect,resource to agent;
Grant succeeded.
SQL> conn agent/vbt
Connected.
SQL> create table test(id number,name varchar2(20));
Table created.
SQL> insert into test values(101,’ram’);
1 row created.
SQL> insert into test values(102,’raj’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
———- ——————–
101 ram
102 raj

Step 10:- verify the log sequence primary and standby.
Primary side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 14
Standby side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 14
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
LNS CONNECTED 0
DGRD ALLOCATED 0

Step 11:- Bounce database again open in mount stage.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.

Step 12:- convert snapshot standby to physical standby:
SQL> alter database convert to physical standby;
Database altered.

Step 13- Bounce the database
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

Step 14:- check the database role has been changed as physical standby and the mode as read-only
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN data PHYSICAL STANDBY READ ONLY

Step 15:- After bounce enable the MRP process and check table available or not.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select * from agent.test;
select * from agent.test
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Physical Standby To Snapshot Standby

Physical Standby To Snapshot Standby

Share this:

Like this: