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