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
Gavin Soorma
