11g Testing with a Snapshot Standby Database Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

Before getting into the details with RAT,  the focus should be on what is needed for Real Application Testing besides the licenses. The most important part is a testing system which has already been exposed to the changes to be tested.

M Never use the production system for testing!

Oracle 11g offers a new possibility that is very convenient for real life testing. If there is a physical standby database, it can be converted to a snapshot standby database and use it as a testing environment, as shown in Figure 1.

NOTE:  A snapshot standby database cannot be used for real-time query or fast-start failover. 

Figure 1:  Testing Environment Diagram

Here is the syntax for activating a snapshot standby database:

Lutz as sysdba @ standby11 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; 

After testing, reconvert to the physical standby mode:

Lutz as sysdba @ standby11 SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 

It is also possible to use DGMGRL commands for activating or deactivating a snapshot standby database:

DGMGRL> CONVERT DATABASE standby11 TO PHYSICAL STANDBY; 

The role of a database can be retrieved from its control file:

Lutz as sysdba @ standby11 SQL> SELECT database_role FROM v$database; 

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

A database which is a fast-start failover target cannot be converted to a snapshot standby database. Switchover or failover to a snapshot standby database is not possible. If the database is the only physical standby in a maximum protection Data Guard Configuration, it cannot be turned into a snapshot standby database.

Converting a physical standby database into a snapshot standby database puts it into a state which allows opening it for read write. Now it can be used to run recorded workloads for testing. In the meantime, redo will still be received from the primary site but not applied yet.

Oracle creates an implicit guaranteed restore point to ensure that a flashback database operation can be performed in order to reset the database to the status before the activation of the snapshot standby state. After testing is finished, it is possible to discard the changes and re-enable redo apply  by converting the snapshot standby database into a physical standby database again.

M Since redo is received but not applied on the snapshot standby database, a   possibly corrupted shipped redo archive would only be discovered after re-enabling the redo apply!