Oracle 11g provides customers the “Snapshot Standby” database feature, so that customers can leverage their investments in standby database systems. When leveraging the “Snapshot Standby” feature, customers can temporarily open up the standby database and run their tests on this system. For example, customer might want to test an application upgrade that alters tables or insert/update/deletes data.
When you convert a physical standby database to a snapshot standby in 11g, the database automatically creates a guaranteed restore point (Obviously “Flashback Database” has to be enabled in order to do this). Once you are done with your testing, the database gets automatically flashed back to this guaranteed restore point and then the log apply starts again.
Point to note with the snapshot standby database is that, in 11g the log transport continues to work. So all the logs are being received on the standby database and gap resolution continues to work (So in case of a primary failure, the logs just need to get applied on the standby). In 10g you could open up the standby database and test on it, but the log transport used to stop.
Let us look at some of the steps involved in setting up and using a “Snapshot standby” database in 11g. For similar functionality with 10g refer to the following documents.
Using 10g Physical standby database for Read/Write Testing and Reporting
10g Snapshot Standby – Dell Case Study
First follow all the normal steps that you would in setting up a physical standby database.
Make sure that you enable “Flashback Database” on both the primary and the standby.
Make sure that log shipping and apply are working properly.
In our example RK01 is the primary database and RK01DG is the standby database.
Check the salary for empno 7934
 |
Update the emp table on the primary database
Switch the logfile to ensure that the change gets propagated and applied on the standby database.
SQL> Update scott.emp set sal=1310 where empno=7934;
SQL> Commit;
SQL> connect / as sysdba
SQL> alter system switch logfile;
 |
Use the command “alter database convert to snapshot standby” to try and switch the database to a “Snapshot Standby”
 |
Since the database is in managed recovery mode you get an error.
Cancel the managed recovery
Then convert the database using the “alter database convert to snapshot standby”
This command leaves the standby database in nomount mode.
Shutdown the database and start it back up (In my screen shot i am starting up in mount mode, you can start it all the way up)
 |
Check the role of the standby database, it should say “Snapshot Standby”
Query v$restore_point to see the details of the guaranteed restore point that it created
 |
List the columns in the employee table on the primary database (RK01). This is just for reference, because as part of our testing we are going to add columns to this table.
 |
Login as the scott user to the standby database and run some transactions
 |
 |
Now that testing is over, we can convert this back to the physical standby database.
Issue the “alter database to convert to physical standby” command to convert this from a “Snapshot Standby” to a “Physical standby” database.
 |
Since the database is in open mode, it complains.
Restart the database in mount mode and then issue the same convert command again
 |
Once the command succeeds the database has to be restarted.
Startup the database in mount mode and put the database in managed recovery mode.
Check the role of the standby database. It should now say “physical standby”.
 |
Run transactions on primary to make sure that log transport and apply are working
 |
Put the standby database in read only mode and check if the transactions got applied on the standby
 |
Also make sure that the changes made during testing have been rolled back.
 |
Those are the basic steps to setup and test a “Snapshot Standby” database.
