Sometimes We need to make some test on our database and then We want to revert what We have done on database.

I want to make demo for 11g Snapshot Standby for real time testing. Now let us talk about what I meant deeply
Oracle 11g have one one very good feature which is calling 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

Basically, Snapshot 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.

Now let use see how we can use this future, here is the my scenario:

I have 11gR2(11.2.0.1) database on AIX 6.1 server which is working as primary database at İstanbul.This database also have physical standby database which run on our disaster center at İzmir(PS: İstabul and İzmir are cities in  Turkey(The distance is almost 600 Km.)

What is our purpose? We want to make some test on standby database,thatswhy we want to open that database,working on it than we want to turn normal state as working as before.

Here is the our steps:

1. Check db_recovery parameter on both database(standby&primary)

On primary

SQL> show parameters db_recovery;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                     string
db_recovery_file_dest_size           big integer 0

SQL> alter system set db_recovery_file_dest_size=50g scope=both;

Database altered.

SQL> Alter system set db_recovery_file_dest=’+ORAFRA’ scope=both;

Database altered.

SQL> alter database flashback on;

Database altered.

On Standby

SQL> show parameters db_recovery;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest                     string
db_recovery_file_dest_size           big integer 0

SQL> alter system set db_recovery_file_dest_size=50g scope=both;

Database altered.

SQL> Alter system set db_recovery_file_dest=’+ORAFRA’ scope=both;

Database altered.

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
——————– ——————–
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> alter database flashback on;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

On Primary

Now for to can be sure before after state We are creating table on primary.

SQL> create table test_table_on_primary (status varchar2(20),date timestamp);

Table created.

SQL> insert into test_table_on_primary values(‘before snapshot’,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 1000
SQL> select * from test_table_on_primary

STATUS               TARIH
——————– —————————————————————————
before snapshot      23-NOV-11 01.54.48.000000 PM

Now we can keep working on standby database

On Standby

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2211064 bytes
Variable Size            2717909768 bytes
Database Buffers          469762048 bytes
Redo Buffers               16953344 bytes
Database mounted.
Database opened.

SQL> set linesize 1000
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;

NAME                                                                                                                             GUA
——————————————————————————————————————————– —
SNAPSHOT_STANDBY_REQUIRED_11/23/2011 13:35:41                                                                                    YES

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
READ WRITE           SNAPSHOT STANDBY

Now we are adding one new record on table which we created on primary. We are doing this to can show primary keeping redolog until our standby db will have againg physical standby database_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

SQL> insert into test_table_on_primary values(‘standbyworkingas_snapshot’,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_table_on_primary;

STATUS               TARIH
——————– —————————————————————————
before snapshot                      23-NOV-11 01.54.48.000000 PM
standbyworkingas_snapshot           23-NOV-11 01.59.47.000000 PM

So as you can see we have 2 records on table.

On standby

select * from test_table_on_primary

STATUS               TARIH
——————– —————————————————————————
before snapshot      23-NOV-11 01.54.48.000000 PM

As you can see it brings only one record.

So now we can start to make our test on standby database

On standby

SQL> create table test_table_on_standby(status varchar2(20));

Table created.

SQL> insert into test_table_on_standby values(‘workingin_snapshot_role’);

1 row created.

SQL> commit;

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
READ WRITE           SNAPSHOT STANDBY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2211064 bytes
Variable Size            2717909768 bytes
Database Buffers          469762048 bytes
Redo Buffers               16953344 bytes
Database mounted.

Now we turn back our database role from SNAPSHOT STANDBY to PHYSICAL STANDBY

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2211064 bytes
Variable Size            2717909768 bytes
Database Buffers          469762048 bytes
Redo Buffers               16953344 bytes
Database mounted.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
——————– —————-
MOUNTED              PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

SQL> alter database open read only;

Database altered.

SQL> select * from test_table_on_primary;

STATUS               TARIH
——————– —————————————————————————
before snapshot      23-NOV-11 01.54.48.000000 PM
snapshotta           23-NOV-11 01.59.47.000000 PM

On standby we created test_table_on_standby…What we are expecting here? The table should not be exist after we stopped our process

SQL> select * from test_table_on_standby;
select * from test_table_on_standby
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now we restart standby database wiht mount mode

SQL>alter database recover managed standby database cancel;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

QL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2211064 bytes
Variable Size            2717909768 bytes
Database Buffers          469762048 bytes
Redo Buffers               16953344 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

As you can see we can make our test wihtout any problem wiht new future of  Oracle11g

Reference:
11g Using Snapshot Standby Database. [ID 443720.1]