Oracle 12c-Step by Step Configure Snapshot Standby Database

Oracle 12c-Step by Step Configure Snapshot Standby Database

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:-

Standby:-

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,

select flashback_on from v$database;

show parameter db_recovery_file_dest

Step:4 In standby side,stop the mrp process.

 alter database recover managed standby database cancel;

Step:5 Bounce the database and keep in mount stage,

shut immediate

startup mount

Step:4 Convert to snapshot standby database

alter database convert to snapshot standby

Step:5 Open the standby database in READ/WRITE mode

alter database open

Step:6 Check the DATABASE_ROLE and OPEN_MODE,

select status,instance_name,database_role,open_mode from v$database,v$Instance

Step:7 Testing on the snapshot standby database,

create user hari identified by oracle;
grant connect,resource to hari;
conn hari/oracle
create table sample(code number, name char(20));
insert into sample values (1‘sam’);
commit;

Step:8 Both side verify the archived sequence

Primary side
select thread#,max(sequence#) from v$archived_log group by thread#;

Standby side

select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;

Step:9 In Standby Side,bounce and mount the database

shut immediate
startup mount

Step:10 Convert the snapshot standby database to physical standby database,

alter database convert to physical standby;

Step:11 Bounce the database,

shut immediate
startup

Step:12 Check the standby database_role and mode.

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

Step:13 Enable the MRP process
alter database recover managed standby database disconnect from session;

Step:14 After convert to physical standby database,check the table status

select * from hari.sample;

Note:  After convert back to Physical standby database  once again restart the MRP process.

Reference:-

http://www.oracle.com/technetwork/testcontent/usingsnapshot-088081.html

 

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