standby delay

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Tue, 01 Mar 2005 14:56:07 GMT
Message-ID: <Pine.BSO.4.58.0502281506520.4962@bart.rhadmin.org>

Under 8i, I've implemented a simple script to hold the standby at a 4-hour window in the past (measured from the arrival of a compressed archived log), so an "ALTER DATABASE OPEN READ ONLY" lets me fish out accidentally deleted objects (and the like).

Who needs 9i flashback? :)

This is done with the GNU "stat" and "date" utilities which can report in "epoch" time (# of seconds since 1/1/1970) which makes for easy time arithmetic (unfortunately, this stuff will blow up in 2038 when UNIX time dies). These utilities are part of the GNU "coreutils" collection.

Below is the script that I am using under 8i, posted in the hope that someone finds it useful (I have hard-coded a few things that you will have to fix). I use cron to run this once a minute like so:

• * * * * ORACLE_SID=x ORACLE_HOME=/y ~oracle/delay.sh

  ---

   / Charles J. Fisher    | "Fascism should more properly be called          /
  /  cfisher_at_rhadmin.org  |  corporatism, since it is the merger of state   /
 /   http://rhadmin.org   |  and corporate power." -- Mussolini            /
---------------------------------------------------------------------------

#!/bin/ksh

if [ -f ~/.RECOVERING-$ORACLE_SID ]
then

        exit
else

        touch ~/.RECOVERING-$ORACLE_SID
fi

cd /crm3/arch

for x in *.bz2
do

        cur=$(/usr/local/bin/gdate +%s)

        this=$(/usr/local/bin/gstat -c %Z $x);

        ((diff=cur-this))

	# 4 hours is 14400 seconds
	if [[ $diff -gt 14400 ]]
	then
		/usr/contrib/bin/bzip2 -dq $x
	fi

done

a=*.ARC

if [[ -z "$a" ]]
then

	rm ~/.RECOVERING-$ORACLE_SID
	exit

fi

$ORACLE_HOME/bin/svrmgrl > /dev/null 2>&1 <<-EOF

	connect internal
	recover automatic standby database;
	cancel
	quit

EOF rm -f *.ARC

rm ~/.RECOVERING-$ORACLE_SID Received on Tue Mar 01 2005 - 08:56:07 CST