Flashback Scripts

The following flashback scripts can be used to flashback a training environment automatically. Before use, please test thoroughly for your environment. It is also recommended that the following flashback article should be read.

The code and a description of each script follows.

SQL Script cr_flashback_database_flag_table.sql

The script cr_flashback_database_flag_table.sql is used to create a database table that holds information about which environment should be flashed back. The table is accessed by flashback_db.ksh and update_flashback_status.ksh

It should be run once on the database being flashed back prior to creating any restore points using the following commands

  • sqlplus '/ as sysdba'
  • SQL> @cr_flashback_database_flag_table.sql

Code

--
-- cr_flashback_database_flag_table.sql
--
 
DROP TABLE flashback_database_flag
/
 
CREATE TABLE flashback_database_flag(
environment_name CHAR(10), 
flashback_flag char(1)
)
/
 
GRANT SELECT ON flashback_database_flag TO PUBLIC
/
 
INSERT INTO flashback_database_flag VALUES('TRAINING','N')
/
 
COMMIT
/

Crontab File crontab_myhostname_test

The crontab file crontab_myhostname_test is a crontab formatted file that can be used to automate the scheduling of flashback_db.ksh every 15 minutes. The script flashback_db.ksh will check the table created by cr_flashback_database_flag_table.sql to see if the database needs to be flashed back. If it does, then the database will be flashed back. If not, then the script will exit.

This crontab file also schedules a weekly forced flashback of the database so the database is ready for training on Monday morning.

Load the crontab file into the database using the following command

  • crontab <crontab_myhostname_test

Code

#This is the crontab file for myhostname
#
#      The file can be located in
#
#               /u01/app/oracle/dba/crontab
#
#
# Run the flashback script to check for User initiated training flashback
#
* 15,30,45,00 * * * /u01/app/oracle/dba/scripts/flashback_db.ksh training > /var/tmp/flashback_db.log 2>&1 
#
#
# Force a flashback of the training database
#
18 15 * * 5 /u01/app/oracle/dba/scripts/flashback_db.ksh training force > /var/tmp/flashback_db.log 2>&1

Shell Script update_flashback_status.ksh

The shell script update_flashback_status.ksh is an interactive script used to update the table created by SQL script cr_flashback_database_flag_table.sql

It will ask for the environment name being updated and whether it should be flashed back and will need a number of variables setting in the code before use.

This script can be given to the training department to set the flashback flag in the above table to either Y or N. If set to Y, then the script flashback_db.ksh will flashback the database within 15 minutes as determined by the crontab job in the crontab file crontab_myhostname_test. Use the following commands to run the script.

  • ./update_flashback_status.ksh

Code

#!/bin/ksh 
############################################################################
#
#       Script :        update_flashback_status.ksh
#
#       Date :          03rd December 2009
#
#       Author :        Mark Ramsay
#
#       Description :   Updates the flashback_database_flag table
#                       with either a Y or an N.
#                       This table is used in conjunction with
#                       flashback_db.ksh
#
#       Parameters :    None.
#
#
#       Variables  :    The following variables need to be set in the script:-
#
#                       ORACLE_SID
#                       CONNECTION_USER
#                       CONNECTION_PASSWD
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  Initials  Description
#
#############################################################################
#
#Set-up Variables
#
clear

export ORACLE_SID=
export CONNECTION_USER=
export CONNECTION_PASSWD=

echo "What environment are you updating? (Default=Training)  "
read flashback_env

if [[ $flashback_env = '' ]]
then flashback_env='TRAINING'
     export flashback_env
else export flashback_env
fi

echo "Do you want to flashback this environment? (Default=N) "
read flashback_yn

if [[ $flashback_yn = '' ]]
then flashback_yn='N'
     export flashback_yn
fi

if [[ $flashback_yn != 'Y' ]] | [[ $flashback_yn != 'y' ]]
then flashback_yn='N'
else flashback_yn='Y'
export $flashback_yn
fi

echo "You replied $flashback_env"
echo "You replied $flashback_yn"

#
#Update flag
#

export ORAENV_ASK=NO

. oraenv $ORACLE_SID

sqlplus -S $CONNECTION_USER/$CONNECTION_PASSWD <<EOF 2>&1
SET TRIMSPOOL ON
SET PAGES 0
SET HEA OFF
SET FEEDBACK OFF
UPDATE flashback_database_flag 
SET flashback_flag='$flashback_yn'
WHERE environment_name='$flashback_env'
/
COMMIT
/
EOF

#
#Report status to user
#

status_flag=`sqlplus -S $CONNECTION_USER/$CONNECTION_PASSWD <<EOF 2>&1
SET TRIMSPOOL ON
SET PAGES 0
SET HEA OFF
SET FEEDBACK OFF
SELECT flashback_flag 
FROM flashback_database_flag
WHERE environment_name='$flashback_env'
/
EOF`

if [[ $status_flag = Y ]]
then
echo ""
echo ""
echo "**************************************************************************"
echo "* WARNING - The flashback flag is set to $status_flag.                              *"
echo "* WARNING - The $ORACLE_SID $flashback_env database will be flashed back!!!!!!!!! *"
echo "**************************************************************************"
echo ""
echo ""
else
echo ""
echo ""
echo "The flashback flag is set to N"
echo "The $ORACLE_SID $flashback_env database will NOT be flashed back."
echo ""
echo ""
fi

#
#Exit Script
#

echo "JOB COMPLETE"

Shell Script flashback_db.ksh

The script flashback_db.ksh is used to flashback the database.

It will need a number of variables setting in the code before use and expects the environment name to be passed as $1 and an optional $2 parameter in order to force the flashback regardless of the flag in the above table.

This script is called every 15 minutes by the crontab and once a week using a forced flashback. It can be run using the following command.

  • ./flashback_db.ksh

Code

#!/bin/ksh 
############################################################################
#
#       Script :        flashback_db.ksh
#
#       Date :          03rd December 2009
#
#       Author :        Mark Ramsay
#
#       Description :   Checks to see if a table exits with a flag set 
#                       to Y.  If set, then it flashes back the DB to
#                       a previously created guarenteed restore point.
#
#       Parameters :    \$1 ENVIRONMENT
#                       \$2 FORCE_FLASH
#
#       Variables  :    The following variables need to be set in the script:-
#
#                       ORACLE_SID
#                       CONNECTION_USER
#                       CONNECTION_PASSWD
#                       RESTORE_NAME
#
#
# History       Date      Name      Reason
#               ----      ----      ------
#               dd/mm/yy  Initials  Description
#
#############################################################################
#
#Set-up Variables
#

export ORACLE_SID=
export CONNECTION_USER=
export CONNECTION_PASSWD=
export RESTORE_NAME=
export ENVIRONMENT=$1
export FORCE_FLASH=$2
export PATH=$PATH:/u01/app/oracle/product/10.2.0/bin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/ccs/bin:/usr/bin::/usr/sbin:/usr/local/bin

if [[ $ENVIRONMENT = '' ]]
then echo "\$1 - Environment value not set, script exiting..."
exit
fi

#
#Check to see if Flag is set
#

export ORAENV_ASK=NO

. oraenv $ORACLE_SID

isFlag=`sqlplus -S $CONNECTION_USER/$CONNECTION_PASSWD <<EOF 2>&1
SET TRIMSPOOL ON
SET PAGES 0
SET HEA OFF
SET FEEDBACK OFF
SELECT flashback_flag 
FROM FLASHBACK_DATABASE_FLAG
WHERE environment_name=UPPER('$ENVIRONMENT')
/
EOF`

#
#If FORCE_FLASH is set to force then force a flashback.
#

if [[ $FORCE_FLASH = 'force' ]]
then export isFlag='Y'
echo "****************************************************"
echo "* Force Flash has been set.                        *"
echo "* Ignoring value in table flashback_database_flag. *"
echo "****************************************************"
fi

#
#If flag is not set to Y exit
#

if [[ $isFlag != Y ]]
then echo "No Flashback Database Required"
     echo "Script exiting...."
     exit
fi

#
#Flag must be set to Y so flash back
#

echo ""
echo "*************************************"
echo "* Flashback flag is set to: $isFlag       *"
echo "*************************************"
echo ""

sqlplus '/ as sysdba' <<EOF 2>&1
!echo "Dropping old end_of_$ENVIRONMENT restore point.."
DROP RESTORE POINT end_of_$ENVIRONMENT
/
!echo "Creating new end_of_$ENVIRONMENT restore point.."
CREATE RESTORE POINT end_of_$ENVIRONMENT
/
SHUTDOWN IMMEDIATE
/
STARTUP MOUNT
/
!echo "Flashing back database.."
FLASHBACK DATABASE TO RESTORE POINT $RESTORE_NAME
/
!echo "Reseting database logs.."
ALTER DATABASE OPEN RESETLOGS
/
!echo "Dropping old start_of_${ENVIRONMENT}_temporary_restore_point.."
DROP RESTORE POINT start_of_${ENVIRONMENT}_temporary_restore_point
/
!echo "Creating new start_of_${ENVIRONMENT}_temporary_restore_point.."
CREATE RESTORE POINT start_of_${ENVIRONMENT}_temporary_restore_point
/
!echo "Dropping guarenteed restore point: $RESTORE_NAME.."
DROP RESTORE POINT $RESTORE_NAME
/
!echo "Creating new  guarenteed restore point: $RESTORE_NAME.."
CREATE RESTORE POINT $RESTORE_NAME GUARANTEE FLASHBACK DATABASE
/
!echo "Bouncing database.."
SHUTDOWN IMMEDIATE
/
STARTUP
/
EOF

#
#Reset flashback flag
#

sqlplus -S $CONNECTION_USER/$CONNECTION_PASSWD <<EOF 2>&1
set trimspool on
set pages 0
set hea off
set feedback off
UPDATE flashback_database_flag 
SET flashback_flag='N'
WHERE environment_name=UPPER('$ENVIRONMENT')
/
COMMIT
/
EOF

#
#Check flashback flag to ensure reset.
#

isFlag=`sqlplus -S $CONNECTION_USER/$CONNECTION_PASSWD <<EOF 2>&1
SET TRIMSPOOL ON
SET PAGES 0
SET HEA OFF
SET FEEDBACK OFF
SELECT flashback_flag 
FROM flashback_database_flag
WHERE environment_name=UPPER('$ENVIRONMENT')
/
EOF`

echo ""
echo "*********************************************"
echo "* Flashback flag has been reset to: $isFlag       *"
echo "*********************************************"
echo ""

#
#Mail log to Training Team
#

cat /var/tmp/flashback_db.log | /bin/mailx -s "`hostname`: Training Flashback Report" firstname.lastname@mydomain.com

#
#Exit Script
#

echo "JOB COMPLETE"
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License