Snapshot Standby:
-----------------
Which is new feature of oracle 11g dataguard for real time testing and which is very good feature for dataguard.
Sometimes we need to make some test on our database and then we want to revert what we have done on database.
Snapshot standby database where we can basically convert the physical standby database into a READ-WRITE real time production database which we can use that database
temporarily for our possible development testing and disaster scenario.
At the same time, it maintains protectioin by continuing to receive data from the production database, archiving it for later use.
Basically, Sanpshot standby database uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to. So
this future give us possibility on work on that database.
Steps:
1. Check db_recovery parameter on both databases (Primary & Standby)
On Primary:
show parameter db_recovery;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amer/amer_fbr/' sope=both;
alter database flashback on;
On Standby:
show parameter db_recover;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amerstd/amerstd_fbr/' scope=both;
Now check protection modes:
select protection_mode,protection_level from v$database;
Maximum availability..
By default standby is running MAXIMUM PERFORMANCE mode.
Changing protection mode: On Primary and standby:--
select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
alter system set log_archive_dest_2='SERVICE=amer SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=amer VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;
alter database set standby database to maximize availability;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
alter database flashback on;
Now stop MRP process:
alter database recover managed standby database cancel;
select flashback_on from v$database;
On primary:
Now for to can be sure before after state we are creating table on primary.
create table test_table_on_primary (status varchar2(20),timestamp date);
insert into test_table_on_primary values('before snapshot',sysdate);
commit;
set linesize 1000
select * from test_table_on_primary;
Now we can keep working on standby database;
On Standby:
alter database convert to snapshot standby;
shut immediate
startup
set linesize 1000select name,guarantee_flashback_database from v$restore_point;
select open_mode,database_role from v$database;
Db should be in READ-WRITE mode and db role is Snapshot Standby
Now we are adding one new record on table which we creaetd on primary.
We are doing this to can show primary keeping redolog until our standby db will have again physical standby db_role.
When we will finish our process on snapshot database and convert it to physical standby, primary start again to send redo to standby database for apply.
On Primary:
Insert into test_table_on_primary values('stdbywrkngs_snapsht',sysdate);
commit;
select * from test_table_on_primary;
so as you can see we have 2 records on table.
On Standby:
create table test_table_on_standby (status varchar2(20));
insert into test_table_on_standby values('workingin_snapshot_role');
commit;
select open_mode,database_role from v$database;
Db should be in READ-WRITE and database role is snapshot standby.
shut immeidate
startup mount
Now we turn back our database role from SNAPSHOT STANDBY to PHYSICAL STANDBY.
alter database convert to physical standby;
shut immediate;
startup mount
select open_mode,database_role from v$database;
DB must be in READ-WRITE and DB role is PHYSICAL STANDBY.
Now start MRP process from standby db..
alter database recover managed standby database disconnect from session;
Now our standby database up and running.. What we are expecting here?
Redolog has been started to apply on standby database and our table should bring us to correct data.
On Standby:
alter database recover managed standby database cancel;
alter database open read only;
select * from test_table_on_primary;
which should be show 2 records...
On standby we created test_table_on_standby..What we are expecting here? The table should not be exist after we stopped our process
select * from test_table_on_standby;
table or view does not exist
Now restart standby database with mount mode.
shut immediate;
startup mount;
alter database recover managed standby database diconnect from session;
As we can see we can make our test without any problem with new future of oracle 11g.
################# All the best #################
http://heliosguneserol.com/2011/12/08/11g-snapshot-standby-for-real-time-testing/
-----------------
Which is new feature of oracle 11g dataguard for real time testing and which is very good feature for dataguard.
Sometimes we need to make some test on our database and then we want to revert what we have done on database.
Snapshot standby database where we can basically convert the physical standby database into a READ-WRITE real time production database which we can use that database
temporarily for our possible development testing and disaster scenario.
At the same time, it maintains protectioin by continuing to receive data from the production database, archiving it for later use.
Basically, Sanpshot standby database uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to. So
this future give us possibility on work on that database.
Steps:
1. Check db_recovery parameter on both databases (Primary & Standby)
On Primary:
show parameter db_recovery;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amer/amer_fbr/' sope=both;
alter database flashback on;
On Standby:
show parameter db_recover;
alter system set db_recovery_file_dest_size=50m scope=both;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/amerstd/amerstd_fbr/' scope=both;
Now check protection modes:
select protection_mode,protection_level from v$database;
Maximum availability..
By default standby is running MAXIMUM PERFORMANCE mode.
Changing protection mode: On Primary and standby:--
select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
alter system set log_archive_dest_2='SERVICE=amer SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=amer VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;
alter database set standby database to maximize availability;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
alter database flashback on;
Now stop MRP process:
alter database recover managed standby database cancel;
select flashback_on from v$database;
On primary:
Now for to can be sure before after state we are creating table on primary.
create table test_table_on_primary (status varchar2(20),timestamp date);
insert into test_table_on_primary values('before snapshot',sysdate);
commit;
set linesize 1000
select * from test_table_on_primary;
Now we can keep working on standby database;
On Standby:
alter database convert to snapshot standby;
shut immediate
startup
set linesize 1000select name,guarantee_flashback_database from v$restore_point;
select open_mode,database_role from v$database;
Db should be in READ-WRITE mode and db role is Snapshot Standby
Now we are adding one new record on table which we creaetd on primary.
We are doing this to can show primary keeping redolog until our standby db will have again physical standby db_role.
When we will finish our process on snapshot database and convert it to physical standby, primary start again to send redo to standby database for apply.
On Primary:
Insert into test_table_on_primary values('stdbywrkngs_snapsht',sysdate);
commit;
select * from test_table_on_primary;
so as you can see we have 2 records on table.
On Standby:
create table test_table_on_standby (status varchar2(20));
insert into test_table_on_standby values('workingin_snapshot_role');
commit;
select open_mode,database_role from v$database;
Db should be in READ-WRITE and database role is snapshot standby.
shut immeidate
startup mount
Now we turn back our database role from SNAPSHOT STANDBY to PHYSICAL STANDBY.
alter database convert to physical standby;
shut immediate;
startup mount
select open_mode,database_role from v$database;
DB must be in READ-WRITE and DB role is PHYSICAL STANDBY.
Now start MRP process from standby db..
alter database recover managed standby database disconnect from session;
Now our standby database up and running.. What we are expecting here?
Redolog has been started to apply on standby database and our table should bring us to correct data.
On Standby:
alter database recover managed standby database cancel;
alter database open read only;
select * from test_table_on_primary;
which should be show 2 records...
On standby we created test_table_on_standby..What we are expecting here? The table should not be exist after we stopped our process
select * from test_table_on_standby;
table or view does not exist
Now restart standby database with mount mode.
shut immediate;
startup mount;
alter database recover managed standby database diconnect from session;
As we can see we can make our test without any problem with new future of oracle 11g.
################# All the best #################
http://heliosguneserol.com/2011/12/08/11g-snapshot-standby-for-real-time-testing/
