Snapshot Standby Database in Action

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.

About these ads

, , ,

  1. #1 by emre baransel on September 25, 2010 - 09:53

    I didn’t know that drop tablespace is not tolerated, thanks for the info.

    As an addition, to open a standby database read-write for test puposes in 10gR2:

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIABDH

    Snapshot standby makes things much easier.

  2. #2 by Uwe Hesse on October 7, 2010 - 07:28

    Emre, thank you for pointing out the “Snapshot Standby in 10g”! The term and the Database Role Snapshot Standby was not invented there, but the most important difference is: In 10g, the “Snapshot Standby” cannot receive Redo Protocol from the Primary while being used for testing.

  3. #3 by sunilbhola on February 9, 2011 - 17:25

    I think in snapshot standby – standby will RECEIVE the redo but NOT apply it.

  4. #4 by Uwe Hesse on February 9, 2011 - 17:26

    exactly

  5. #5 by sunilbhola on February 10, 2011 - 05:44

    @emre baransel,

    The steps you sent is for ACTIVATING the standby database. After activate it will NOT even receive the “redo” from the production database. However on Snapshot standby it will RECEIVE the redo but not apply it.

    Regards,
    Sunil Bhola

  6. #6 by Uwe Hesse on February 10, 2011 - 09:03

    @Sunil,
    Emre gave the steps to open a Physical Standby Read-Write (for testing prupose, for example) and flashback afterwards for 10g. The term Snapshot Standby was invented in 11g, together with the possibility that it can receive redo protocol from the Primary while being Snapshot Standby. In 10g, that was just not possible yet. If you look at my comment #2 – I have said that already.

  7. #7 by sunilbhola on February 10, 2011 - 09:09

    Thanks Uew. I did not notice it that you have replied already on this. :)

  8. #8 by Uwe Hesse on February 10, 2011 - 09:13

    You’re welcome :-) And thank YOU for sharing information with your comments!

  9. #9 by sunilbhola on February 10, 2011 - 13:16

    Thanks to you for sharing your knowledge on oracle. I will however, will be regular on your blog and will try to share my views.

    Actually I am more into Data Guard/RMAN/Upgrades-Install/ and will surely share with you, as and when required. I really appreciate your knowledge. Thanks for sharing..

  1. Clone Database from Snapshot Standby Database « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,747 other followers

%d bloggers like this: