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.
Dataguard Configuration:-
Step:1 Check both primary and standby role.
Primary:-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN class PRIMARY READ WRITE
Standby:-
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- MOUNTED class 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 37
Step 3:- Check the flashback status and db_recovery_file_set location,
select flashback_on from v$database;
show parameter db_recovery_file_dest
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE -------------------------- ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/recovery_dest/ db_recovery_file_dest_size big integer 2G SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
Step:4 In standby side,stop the mrp process.
alter database recover managed standby database cancel;
SQL> recover managed standby database cancel; Media recovery complete.
Step:5 Bounce the database and keep in mount stage,
shut immediate
startup mount
SQL> shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 943716968 bytes Fixed Size 8903272 bytes Variable Size 583008256 bytes Database Buffers 348127232 bytes Redo Buffers 3678208 bytes Database mounted.
Step:6 Convert to snapshot standby database
alter database convert to snapshot standby
SQL> alter database convert to snapshot standby; Database altered.
Step:7 Open the standby database in READ/WRITE mode
alter database open
SQL> alter database open; Database altered.
Step:8 Check the DATABASE_ROLE and OPEN_MODE,
select status,instance_name,database_role,open_mode from v$database,v$Instance
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN class SNAPSHOT STANDBY READ WRITE
Step:9 Testing on the snapshot standby database,
create user test identified by test;
grant connect,resource to test;
conn test/test
create table t1(sno number, name varchar2(20));
insert into t1 values (1,’example’);
commit;
SQL> alter user test quota unlimited on USERS; User altered. SQL> conn test/test; Connected. SQL> create table t1(sno number, name varchar2(20)); Table created. SQL> insert into t1 values (1,'example'); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; SNO NAME ---------- -------------------- 1 example SQL>
Step:10 Both side verify the archived sequence
Primary side
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 47
Standby side
select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;
SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 47
Step 11: In Standby Side,bounce and mount the database
shut immediate
startup mount
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 943716968 bytes Fixed Size 8903272 bytes Variable Size 759169024 bytes Database Buffers 171966464 bytes Redo Buffers 3678208 bytes Database mounted.
Step:12 Convert the snapshot standby database to physical standby database,
alter database convert to physical standby;
SQL> alter database convert to physical standby; Database altered.
Step:13 Bounce the database,
shut immediate
startup
SQL> shu immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 943716968 bytes Fixed Size 8903272 bytes Variable Size 759169024 bytes Database Buffers 171966464 bytes Redo Buffers 3678208 bytes Database mounted. Database opened.
Step:14 Check the standby database_role and mode.
select status,instance_name,database_role,open_mode from v$database,v$Instance;
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN class PHYSICAL STANDBY READ ONLY
Step:15 Enable the MRP process
alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database disconnect from session; Database altered.
Step:16 After convert to physical standby database,check the table status
Note: After convert back to Physical standby database once again restart the MRP process.
SQL> select * from test.t1; select * from test.t1 * ERROR at line 1: ORA-00942: table or view does not exist
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
