Snapshot Standby Database (11g)
Introduction
One of the best new features of Oracle 11g is the introduction of Snapshot Standby Databases. This feature allows you to take a normal Physical Standby Database and convert it into a read/write database which can be used for reporting, testing of DR etc. Once the testing, or day's worth of reporting is concluded, you simply switch it back to a Physical Standby Database.
My Requirements
My brief was to produce a Snapshot Standby Database at 6AM each morning and revert back to a Physical Standby Database for a short duration only, to allow a day's worth of Production archive logs to be refreshed. Based on archive log generation patterns, I worked out that this meant placing the database into Physical Standby Database mode at 4AM.
Pre-Requisites
To convert a Physical Standby to Snapshot Standby, Flashback must be turned on. The result of this action is that whilst the database is in Snapshot mode, a number of flashback logfiles (.flb) will be created. According to Oracle documentation, these are supposed to be self-cleansed using flashback retention parameters etc. However, I'm not convinced that this works correctly. Therefore, to automatically cleanup the flashback logs, I turn flashback off once the database has been converted back to Physical Standby and this action cleans up the logs.
Solution
With the aide of a couple of cron-jobs, I execute the following scripts at 4AM and 6AM respectively.
# Convert Database to Physical Standby
00 04 * * * /oracle/bin/${ORACLE_SID}_physical_standby.ksh > /oracle/logs/${ORACLE_SID}_physical_standby.trc
############################################################################ #!/bin/ksh # # Description : This script converts the ${ORACLE_SID} Snapshot # Standby Database to a Physical Standby Database # # 20/04/2012 # nazim@nazimcricket.com ############################################################################ MAIL_LIST="nazim at nazimcricket.com" # Set your environment variables - we use a set script to do this . /usr/local/env/${ORACLE_SID} rm /oracle/logs/${ORACLE_SID}_physical_standby.log sqlplus sys<<DATA spool /oracle/logs/${ORACLE_SID}_physical_standby.log -- Shutdown Database shutdown immediate; -- Startup Database in Mount mode startup mount; -- Turn Flashback Off ALTER DATABASE FLASHBACK OFF; -- Convert database to Physical Standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY; -- Sleep for 30 seconds !sleep 30 -- Shutdown Database Again shutdown immediate; -- Startup Database in Nomount mode startup nomount; -- Mount the Database alter database mount standby database; -- Check Database Role select database_role from v\$database; -- Start Log Apply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE; spool off; exit; DATA #Note: A couple of specific ORA errors are ignored if grep 'ORA-' /oracle/logs/pcebnr_physical_standby.log | egrep -v 'ORA-32004|ORA-01507' then status='ERRORS: ' else status='SUCCESS: ' fi mail -s "$status ${ORACLE_SID} Physical Standby Conversion" ${MAIL_LIST} < /oracle/logs/${ORACLE_SID}_physical_standby.log
# Convert Database to Snapshot Standby
00 06 * * * /oracle/bin/${ORACLE_SID} > /oracle/logs/${ORACLE_SID}_snapshot_standby.trc
############################################################################ #!/bin/ksh # # Description : This script converts the ${ORACLE_SID} Physical # Standby Database to a Snapshot Standby Database # # It Unlocks a few core user accounts that are locked in source # # It takes note of the snapshot details # # 20/04/2012 # nazim@nazimcricket.com ############################################################################ MAIL_LIST="nazim@nazimcricket.com" # Set your environment variables - we use a set script to do this . /usr/local/env/${ORACLE_SID} rm /oracle/logs/${ORACLE_SID}_snapshot_standby.log sqlplus sys<<DATA spool /oracle/logs/${ORACLE_SID}_snapshot_standby.log -- Stop Redo Log Apply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -- Turn Flashback On ALTER DATABASE FLASHBACK ON; -- Convert Database to Snapshot Standby alter database convert to snapshot standby; -- Open Database alter database open; -- Open User Accounts alter user SCOTT account unlock; alter user TIGER account unlock; -- Record the Snapshot Time select NAME,SCN,TIME from v\$restore_point; -- Database Role select database_role from v\$database; spool off; exit; DATA if grep 'ORA-' /oracle/logs/${ORACLE_SID}_snapshot_standby.log then status='ERRORS: ' else status='SUCCESS: ' fi mail -s "$status ${ORACLE_SID} Snapshot Standby Conversion" ${MAIL_LIST} < /oracle/logs/${ORACLE_SID}_snapshot_standby.log