What is snapshot standby:
Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.
i. e 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
(which is an exact replication of production ). 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.
Step:1 Check both primary and standby role.
primary: SQL> select name, open_mode, database_role from v$database;
standby: SQL> select name, open_mode, database_role from v$database;
Step 2:- Check the archived sequence in standby,
select thread#,max(sequence#) from v$archived_log group by thread#;
Step 3:- Check the flashback status and db_recovery_file_set location,
SQL> select flashback_on from v$database;
FLASHBACK_ON
--------------
YES
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 8016M
Step 4: In standby side,stop the mrp process.
SQL> alter database recover managed standby database cancel;
Database altered.
Step:5 Bounce the database and keep in 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 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 645926848 bytes
Database Buffers 180355072 bytes
Redo Buffers 3952640 bytes
Database mounted.
Step:5 Convert to snapshot standby database
SQL> alter database convert to snapshot standby;
Database altered.
Step:6 Open the standby database in READ/WRITE mode
SQL> alter database open;
Database altered.
Step:7 Check the DATABASE_ROLE and OPEN_MODE,
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance;
Step:8 Testing on the snapshot standby database,
----Testing on the snapshot standby database--------------
Step:9 Both side verify the archived sequence
Standby:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
SQL> select process,status,sequence# from v$managed_standby;
Step:10 In Standby Side,bounce and mount the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 645926848 bytes
Database Buffers 180355072 bytes
Redo Buffers 3952640 bytes
Database mounted.
Step:11 Convert the snapshot standby database to physical standby database,
SQL> alter database convert to physical standby;
Database altered.
Step:12 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 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 645926848 bytes
Database Buffers 180355072 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.
Step:13 Check the standby database_role and mode.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
Step:14 Enable the MRP process
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Note: After convert back to Physical standby database once again restart the MRP process
***************************************
Convert Physical Standby To Snapshot Standby Using Dgmgrl
***************************************
oracsdb01:
oracle@hostname1:/home/oracle>srvctl status database -d oracsdb01
Instance oracsdb01 is running on node hostname1
Instance oracsdb02 is running on node hostname2
SQL> select name,open_mode,database_role,log_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
oracpdb01 MOUNTED PHYSICAL STANDBY ARCHIVELOG
SQL> set pagesize 300 linesize 300
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
SQL> 2 3 (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
4 5 WHERE
ARCH.THREAD# = APPL.THREAD#
6 7 ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 93980 93980 0
2 118561 118561 0
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 2500G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on;
Database altered.
SQL>select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
Step2: connect to the
DGMGRL> show configuration;
Configuration - oracpdb01_DG_CONFIG
Protection Mode: MaxPerformance
Members:
oracpdb01- Primary database
oracsdb01- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> validate database oracsdb01
Database Role: Physical standby database
Primary Database: oracpdb01
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
oracpdb01: Off
oracsdb01: Off
Managed by Clusterware:
oracpdb01: YES
oracsdb01: YES
Temporary Tablespace File Information:
oracpdb01 TEMP Files: 10
oracsdb01 TEMP Files: 5
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(oracpdb01 ) (oracsdb01 )
2 5 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(oracsdb01 ) (oracpdb01 )
1 5 5 Insufficient SRLs
2 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 2 on oracpdb01
Step3) Convert physical to snapshot standby.
DGMGRL> convert database oracsdb01 to snapshot standby;
Converting database "oracsdb01 " to a Snapshot Standby database,
please wait...
Database "oracsdb01 " converted successfully
DGMGRL> show configuration
SQL> select open_mode,database_role from v$database;
Now the database is open in read write mode.
So you can do any changes or testing on the database.
Once your activity is done, you can revert it back to physical standby using below command.
step4)
DGMGRL> convert database oracsdb01 to physical standby;
DGMGRL> show configuration;
Enjoy the work and do like me if you like this article!!!
