Description:-
- Oracle Database 11g introduced the Snapshot Standby feature in Data Guard
- A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
- A snapshot standby database receives and archives, but does not apply,
redo data from a primary database. - The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
Let start the Snapshot Standby database Demo:-
Dataguard Configuration:-
Step:1 Check both primary and standby role.
select status,instance_name,database_role,open_mode from v$database,v$Instance;
Primary:-
SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DOYEN READ WRITE PRIMARY
Standby:-
SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DOYEN MOUNTED PHYSICAL STANDBY
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- ---------------
MOUNTED doyen PHYSICAL STANDBY MOUNTED
Step 2:- Check the archived sequence in standby,
select thread#,max(sequence#) from v$archived_log group by thread#;
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 26
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:4 Convert to snapshot standby database
SQL> alter database convert to snapshot standby; Database altered.
Step:5 Open the standby database in READ/WRITE mode
SQL> alter database open; Database altered.
Step:6 Check the DATABASE_ROLE and OPEN_MODE,
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN doyen SNAPSHOT STANDBY READ WRITE
Step:7 Testing on the snapshot standby database,
create user hari identified by oracle; grant connect,resource to hari; SQL> conn hari/oracle Connected. SQL> create table sample1(code number, name char(20)); Table created. SQL> insert into sample values(1,'sam'); 1 row created. commit; SQL> select * from sample1; CODE NAME --------- ------------- 1 sam
Step:8 Both side verify the archived sequence
Primary side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ———- ————– 1 25
Standby side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 26 SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- DGRD ALLOCATED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 DGRD ALLOCATED 0 DGRD ALLOCATED 0 7 rows selected.
Step:9 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:10 Convert the snapshot standby database to physical standby database,
SQL> alter database convert to physical standby; Database altered.
Step:11 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:12 Check the standby database_role and mode.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- ----------------
OPEN doyen PHYSICAL STANDBY READ ONLY
Step:13 Enable the MRP process
SQL> alter database recover managed standby database disconnect from session; Database altered.
Step:14 After convert to physical standby database,check the table status
select * from hari.sample1 * ERROR at line 1: ORA-00942: table or view does not exist
Note: After convert back to Physical standby database once again restart the MRP process.
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
