How to perform the Snapshot Standby database in Oracle Database 11gR2(11.2.0.4)
Posted by Mir Sayeed Hassan on June 25th, 2020
How to perform the Snapshot Standby database in Oracle Database 11gR2(11.2.0.4)
The below scenario is to test the snapshot standby database in Oracle database 11gR2(11.2.0.4), Its an feature which allow a read-write operation on standby DB.
We are performing the convert of physical standby db to the snapshot standby, after its conversion is completed successfully we can do the DML operation for testing purpose.
Once the testing is completed we can revert back standby db to the physical standby db & sync it to primary database.
Check the status of database
SQL> select status, version, open_mode from V$instance, v$database; STATUS VERSION OPEN_MODE ------------ ----------------- -------------------- MOUNTED 11.2.0.4.0 MOUNTED
Stop the recovery process of standby
SQL> alter database recover managed standby database cancel; Database altered.
Shutdown the database SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Startup database with mount
SQL> startup mount ORACLE instance started. Total System Global Area 1.8547E+11 bytes Fixed Size 2263656 bytes Variable Size 3.4897E+10 bytes Database Buffers 1.5032E+11 bytes Redo Buffers 244588544 bytes Database mounted.
Check the recovery process
SQL> @/home/oracle/script/stbytest.sql; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 THREAD# MAX(SEQUENCE#) ---------- -------------- 1 653083 2 329147
Note:Flashback database should not be enabled to the conversion operation.
Check the flashback database
SQL> select flashback_on FROM v$database; FLASHBACK_ON ------------------ NO
Convert the standby to a snapshot standby
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered.
SQL> alter database open; Database altered.
Check the restore point created.
SQL> select name,guarantee_flashback_database from V$restore_point; NAME GUA ------------------------------------------------------ SNAPSHOT_STANDBY_REQUIRED_06/25/2020 14:42:38 YES
SQL> select flashback_on FROM v$database; FLASHBACK_ON ------------------ RESTORE POINT ONLY
Check the status of database
SQL> select status, open_mode from V$instance, V$database; STATUS OPEN_MODE ------------ -------------------- OPEN READ WRITE
Now Database is in read-write mode & you can perform any testing changes.
—————————————————————————————-
Convert back from snapshot database to the physical standby database
Note:
All the changes done for testing purpose will be lossing it when its convert back to physical standby database.
Stutdown the database
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Startup the database in mount mode
SQL> startup mount ORACLE instance started. Total System Global Area 1.8547E+11 bytes Fixed Size 2263656 bytes Variable Size 3.4897E+10 bytes Database Buffers 1.5032E+11 bytes Redo Buffers 244588544 bytes Database mounted.
Convert the database from snapshot standby database to physical standby database
SQL> alter database convert to physical standby; Database altered.
Shutdown the database
SQL> shu immediate ORA-01507: database not mounted ORACLE instance shut down.
Startup in NOMOUNT Mode
SQL> startup nomount; ORACLE instance started. Total System Global Area 1.8547E+11 bytes Fixed Size 2263656 bytes Variable Size 3.4897E+10 bytes Database Buffers 1.5032E+11 bytes Redo Buffers 244588544 bytes
Mount database as standby database
SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Database altered.
Check the status of recovery process
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SQL> select thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 653198 489472 1121 RFS IDLE 0 0 0 0 THREAD# MAX(SEQUENCE#) ---------------------- 1 653083 2 329147
Check the status of standby database
SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------------------- MOUNTED PHYSICAL STANDBY
Start the recovery process of standby database
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
Note: Issue few “alter system switch” on primary database.
Check the status of current standby database
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby ; SQL> select thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ARCH CLOSING 1 653200 1232896 1840 ARCH CLOSING 1 653201 106496 130 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS RECEIVING 1 653202 13243267 16 MRP0 APPLYING_LOG 2 329150 7939055 18415190 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ----------------------------------------------- RFS IDLE 0 0 0 0 RFS IDLE 2 329235 7844055 52 13 rows selected. THREAD# MAX(SEQUENCE#) ----------------------- 1 653087 2 329148