Just finished an Oracle Database 11g Data Guard course where we implemented a Snapshot Standby Database – which is a new feature of 11g. Particularly the possibility to receive redo from the Primary still while the Standby is used for testing purpose is new in 11g.

DGMGRL> show configuration

Configuration

 Name:                myconfig

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

 logst - Logical standby database

Fast-Start Failover: DISABLED

Current status for "myconfig":

SUCCESS

This is my configuration with the Physical Standby physt in place. You may create that configuration following the PDF 11g Data Guard on the command line from my Downloads page yourself. Now I create the Snapshot Standby:

DGMGRL> convert database physt to snapshot standby;

Converting database "physt" to a Snapshot Standby database, please wait...

Database "physt" converted successfully

DGMGRL> show configuration;

Configuration

 Name:                myconfig

 Enabled:             YES

 Protection Mode:     MaxAvailability

 Databases:

 prima - Primary database

 physt - Snapshot standby database

 logst - Logical standby database

Fast-Start Failover: DISABLED

Current status for "myconfig":

SUCCESS

Prerequisite for that action is that physt is in Flashback Mode. The Data Guard Broker silently created a guaranteed restore point to ensure that it can flashback the Snapshot Standby back to a Physical Standby after testing is done:

$ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 24 15:30:01 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

SNAPSHOT STANDBY

I am going to test here while my Primary is still transmitting Redo Protocol to the Standby Site. It gets written into Standby Logs there and archived as usual. Only the Redo Apply is stopped while in Snapshot Standby Role:

SQL> drop user scott cascade;

User dropped.

SQL>  create tablespace nu datafile '/home/oracle/physt/nu01.dbf' size 10m;

Tablespace created.

SQL> alter database datafile '/home/oracle/physt/users01.dbf' resize 500m;

Database altered.

SQL> connect sys/oracle@prima as sysdba

Connected.

SQL> select bytes/1024/1024 as mb from v$datafile

 where name='/home/oracle/prima/users01.dbf';

 MB

----------

 10

During testing, I dropped my „Application User“ scott, increased the size of one datafile and even created a new tablespace. I will now do some „productive changes“ on the Primary:

SQL> connect scott/tiger@prima
Connected.
SQL> create table test as select * from dept;

Table created.

Now I want my Physical Standby back:

DGMGRL> convert database physt to physical standby;

Converting database "physt" to a Physical Standby database, please wait...

Operation requires shutdown of instance "physt" on database "physt"

Shutting down instance "physt"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "physt" on database "physt"

Starting instance "physt"...

ORACLE instance started.

Database mounted.

Continuing to convert database "physt" ...

Operation requires shutdown of instance "physt" on database "physt"

Shutting down instance "physt"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "physt" on database "physt"

Starting instance "physt"...

ORACLE instance started.

Database mounted.

Database "physt" converted successfully

Is everything on the Standby again as on the Primary?

$ sqlplus sys/oracle@physt as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 24 15:44:39 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

SQL> select bytes/1024/1024 as mb from v$datafile

  where name='/home/oracle/physt/users01.dbf';

 MB

----------

 10

SQL> select * from scott.test;

select * from scott.test

 *

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> recover managed standby database cancel

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from scott.test;

 DEPTNO DNAME          LOC

---------- -------------- -------------

 10 ACCOUNTING     NEW YORK

 20 RESEARCH       DALLAS

 30 SALES          CHICAGO

 40 OPERATIONS     BOSTON

The datafile got resized back (!) to 10m, the user scott is back and the change done on the Primary during the testing time is also present on the Standby now. Even the new tablespace nu, created on the Snapshot Standby disappeared:

SQL> select * from v$tablespace;

 TS# NAME                           INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

 0 SYSTEM                         YES NO  YES

 1 SYSAUX                         YES NO  YES

 2 UNDOTBS1                       YES NO  YES

 3 TEMP                           NO  NO  YES

 4 USERS                          YES NO  YES

It also removed the datafile from the OS Filesystem. One thing (at least) that should not be done during testing: A drop tablespace is not tolerated. You can do it on the Snapshot Standby, but afterwards it cannot get reconverted into Physical Standby again.

Conclusion: We now have a very comfortable and fast way to use our Physical Standby as a testing system  – maybe together with the 11g New Feature Database Replay – without losing our Disaster Protection. Only the Failover Time will increase in case, because the Snapshot Standby has first to be reconverted and collected Redo has to be applied before the Failover can succeed.