Monday, June 17, 2013

Create snapshot database from physical standby Oracle 11gr2

One of the very nice new features is that you can create a snapshot database for testing purpose from a  physical standby database. This while the snapshot standby database receives and archives, but does not apply, redo data from a primary database. The redo received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures

here is how it's done in a rac cluster

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

stop all instance's but one. as this is a 2 node rac we stop 1 instance.

$ srvctl stop instance -d gsxq9_02 -i gsxq94 -o immediate

make sure that the other running instance is in mount fase

SQL>select open_mode,database_role from gv$database;
OPEN_MODE                                                    DATABASE_ROLE
------------------------------------------------------------ ------------------------------------------------
MOUNTED                                                      PHYSICAL STANDBY

check if database is in flashback mode so we can rewind the database back.

SQL> select flashback_on from v$database;
FLASHBACK
---------
YES

now convert physical standby database in to snapshot database.

SQL>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

as the database is dismounted after conversion start the database.

$srvctl start database -d gsxq9_02

SQL> select name,open_mode from gv$database;
NAME                        OPEN_MODE
--------------------------- ------------------------------------------------------------
GSXQ9                       READ WRITE
GSXQ9                       READ WRITE

when a snapshot database is created there all so is created automatically a restore point.

SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from  v$restore_point;
NAME                                               GUARANTEE
-------------------------------------------------- ---------
SNAPSHOT_STANDBY_REQUIRED_05/30/2013 10:44:43      YES

let look what is writen in the trace file.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Thu May 30 10:44:07 2013
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
ORA-38784 signalled during: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY...
Thu May 30 10:44:36 2013
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu May 30 10:44:37 2013
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/gsxq9_02/GSXQ93/trace/GSXQ93_pr00_31103.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Thu May 30 10:44:37 2013
MRP0: Background Media Recovery process shutdown (GSXQ93)
Managed Standby Recovery Canceled (GSXQ93)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_05/30/2013 10:44:43
Thu May 30 10:44:44 2013
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Thu May 30 10:44:47 2013
RESETLOGS after incomplete recovery UNTIL CHANGE 101569395
Resetting resetlogs activation ID 3374798723 (0xc9275783)
Online log +DGG1/gsxq9_02/onlinelog/group_1.291.816776291: Thread 1 Group 1 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_1.292.816776291: Thread 1 Group 1 was previously cleared
Online log +DGG1/gsxq9_02/onlinelog/group_2.289.816776293: Thread 1 Group 2 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_2.271.816776295: Thread 1 Group 2 was previously cleared
Online log +DGG1/gsxq9_02/onlinelog/group_3.313.816776297: Thread 1 Group 3 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_3.300.816776299: Thread 1 Group 3 was previously cleared
Online log +DGG1/gsxq9_02/onlinelog/group_4.314.816776299: Thread 2 Group 4 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_4.295.816776301: Thread 2 Group 4 was previously cleared
Online log +DGG1/gsxq9_02/onlinelog/group_5.315.816776303: Thread 2 Group 5 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_5.290.816776303: Thread 2 Group 5 was previously cleared
Online log +DGG1/gsxq9_02/onlinelog/group_6.316.816776305: Thread 2 Group 6 was previously cleared
Online log +DGGFRA1/gsxq9_02/onlinelog/group_6.256.816776307: Thread 2 Group 6 was previously cleared
Standby became primary SCN: 101569393
Thu May 30 10:44:47 2013
Setting recovery target incarnation to 3
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Thu May 30 10:45:31 2013
alter database open
Data Guard Broker initializing...
Data Guard Broker initialization complete
This instance was first to open
Using SCN growth rate of 16384 per second
Picked broadcast on commit scheme to generate SCNs
Thu May 30 10:45:31 2013
Assigning activation ID 3379285420 (0xc96bcdac)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DGG1/gsxq9_02/onlinelog/group_1.291.816776291
  Current log# 1 seq# 1 mem# 1: +DGGFRA1/gsxq9_02/onlinelog/group_1.292.816776291
Successful open of redo thread 1

now we can do some testing.

CREATE USER test_user IDENTIFIED BY test_user
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO test_user

CONN test_user/test_user

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT INTO test_tab (id, description) VALUES (1, 'Description for 1');
COMMIT;

INSERT INTO test_tab (id, description) VALUES (2, 'Description for 2');
COMMIT;

SQL> select * from test_tab;
        ID DESCRIPTION
---------- --------------------
         1 Description for 1
         2 Description for 2

SQL>create tablespace snaptest;
select name from v$tablespace;
NAME
--------------------------------------------------
SYSTEM
TEMP
SYSAUX
UNDOTBS1
TOOLS
UNDOTBS2
USERS
IDSD
IDSX
IDDD
IDDX
INDD
INDX
COND
CONX
CALD
CALX
VERD
VERX
SNAPTEST
20 rows selected.

After testing we set the database back as standby database.

$ srvctl stop database -d gsxq9_02

SQL> conn / as sysdba
Connected.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.
SQL> select open_mode,database_role from gv$database;
OPEN_MODE                                                    DATABASE_ROLE
------------------------------------------------------------ ------------------------------------------------
MOUNTED                                                      PHYSICAL STANDBY
MOUNTED                                                      PHYSICAL STANDBY

and the database is back as standby database.

So is the test_tab  table still  there and the tablespace snaptest as the user test
let's look

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> select username from dba_users where username = 'TEST';
no rows selected

And that means also no table test_tab

SQL> select name from v$tablespace;
NAME
------------------------------------------------------------------------------------------
SYSTEM
TEMP
SYSAUX
UNDOTBS1
TOOLS
UNDOTBS2
USERS
IDSD
IDSX
IDDD
IDDX
INDD
INDX
COND
CONX
CALD
CALX
VERD
VERX

and as you see the tablespace SNAPTEST is also gone.

In the trace file you can see what has been done by Oracle.

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (GSXQ93)
Thu May 30 11:04:46 2013
krsv_proc_kill: Killing 7 processes (all RFS)
Flashback Restore Start
Deleted Oracle managed file +DGG1/gsxq9_02/datafile/snaptest.331.816778407
Flashback: deleted datafile #19 in tablespace #19 from control file.
Flashback: dropped tablespace #19: 'SNAPTEST' from the control file.
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 3379285420 (0xc96bcdac)
The primary database controlfile was created using the
'MAXLOGFILES 288' clause.
There is space for up to 282 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 268435456;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 268435456;

Clearing online redo logfile 1 +DGG1/gsxq9_02/onlinelog/group_1.291.816776291
Clearing online log 1 of thread 1 sequence number 46
Thu May 30 11:06:48 2013
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Clearing online redo logfile 1 complete
Clearing online redo logfile 4 +DGG1/gsxq9_02/onlinelog/group_4.314.816776299
Clearing online log 4 of thread 2 sequence number 1
Thu May 30 11:06:52 2013
Clearing online redo logfile 4 complete
Thu May 30 11:06:52 2013
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_40.322.816778833
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_31.353.816776571
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_32.352.816778251
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_33.309.816778833
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_41.280.816778835
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_34.281.816778835
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_42.328.816778985
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_35.270.816778985
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_43.327.816778991
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_36.359.816778991
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_44.364.816779199
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_2_seq_37.365.816779199
Thu May 30 11:07:05 2013
Media Recovery Log +DGGFRA1/gsxq9_02/archivelog/2013_05_30/thread_1_seq_45.367.816779201

As you see after the database is a stanbdby database again redo data received from the primary will then be applied

That's it.