Loading....

Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.

We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to it’s earlier state as a physical standby database.

While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.

After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.

Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.

Initially we have a Data Guard broker configuration in place where APEX is the Primary database
and APEXDG is the Standby database.


DGMGRL> show configuration

Configuration
  Name:                gavin
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    apex   - Primary database
    apexdg - Physical standby database

Fast-Start Failover: DISABLED

Current status for "gavin":
SUCCESS

We now convert the physical standby database to a snapshot standby


DGMGRL> convert database 'apexdg' to snapshot standby;
Converting database "apexdg" to a Snapshot Standby database, please wait...
Database "apexdg" converted successfully

DGMGRL> show configuration

Configuration
  Name:                gavin
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    apex   - Primary database
    apexdg - Snapshot standby database

Fast-Start Failover: DISABLED

Current status for "gavin":
SUCCESS


We now connect to the snapshot standby database which is open in read-write mode and we create a table 


apex:/u01/oracle/scripts> export ORACLE_SID=apexdg
apexdg:/u01/oracle/scripts> sqlplus sh/SH

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:33:29 2008

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test_snapshot
  2  as select * from all_objects;

Table created.

SQL> select count(*) from test_snapshot;

  COUNT(*)
----------
     56467

At this point in time, we revert back to the original state

DGMGRL> convert database 'apexdg' to physical standby;

Converting database "apexdg" to a Physical Standby database, please wait...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Continuing to convert database "apexdg" ...
Operation requires shutdown of instance "apexdg" on database "apexdg"
Shutting down instance "apexdg"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "apexdg" on database "apexdg"
Starting instance "apexdg"...
ORACLE instance started.
Database mounted.
Database "apexdg" converted successfully
DGMGRL>


We now connect to the physical standby database and see that all the changes we made while
the standby database was functioning as a snapshot standby have been rolled back. The table
which we created in the snapshot standby database is not physically present in the physical
standby database.

apexdg:/u01/oracle/scripts> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 19 09:48:38 2008

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;

Database altered.

SQL> conn sh/SH@apexdg
Connected.


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

Please follow and like us:

Last Update: July 7, 2009  

July 7, 2009 189 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?