#!/usr/bin/bash
# Script: doflashback.sh
# Author: Theodore Zacharia
# Version: 1.0 20/04/2011 - C-NOMIS DB scramble release
# Version: 1.1 07/11/2011 - Added backtime
# Version: 1.2 15/11/2011 - Pushpa Godhania added listtime capabilty
# Version: 1.3 16/11/2011 - Added -s to set the SID
#
# Utility script manage flashback for a clustered Oracle DB
# The main modes of operation are:
# * list     - which lists all the markers
# * set      - which sets a marker with the given name
# * back     - which flashbacks to a given marker
# * drop     - which drops a given marker
#
# Addtionally
#
# * listtime - list the oldest time we can flashback to
# * backtime - allows the database to be flashed back to a point in time
#              you MUST supply the time in the format: DD/MM/YY HH24:MI
# * backimm  - allows for flashing back when the srvctl utility or clustering
#              is not working, be aware you need to run the stop and start
#              on each node for this to work
#

# ***** Mainline

while getopts s: AOPT
do
case $AOPT in
        s) export ORAENV_ASK=NO;export ORACLE_SID=$OPTARG;. oraenv ;;
esac
done

shift $((OPTIND-1))

echo "***** IMPORTANT *****"
echo "Ensure you have run . oraenv and set your DB correctly"
echo " "
if [[ $ORACLE_SID == "" || $ORACLE_SID == "oracle" ]]
then
        echo "**** WARNING, it looks like you have not set the SID"
        echo "     continuing, but it probably will not work :( "
fi


if (( $# < 1 ))
then
	echo "usage: $0 [-s SID] list|set|<back|backtime>|drop <markername>"
	echo "-s SID   - allows you to set the ORACLE SID"
	echo "The full set of options are:"
	echo "set      - set a restore point and flashback marker"
	echo "back     - flashback to a marker, using srvctl"
	echo "backimm  - flashback to a marker, using DB Immediate shutdown"
	echo "backtime - flashback to a point in time, format DD/MM/YY HH24:MI"
	echo "drop     - drop a restore point (and marker)"
	echo "list     - list the flashback markers available"
	echo "listtime - list the oldest time we can flashback to"
	exit
fi

MNAME="$2 $3 $4"

if [[ $1 == "set" ]]
then
	echo "setting a restore point / marker $MNAME"
sqlplus -s / as sysdba << EOF
create restore point $MNAME guarantee flashback database;
EOF
elif [[ $1 == "back" || $1 == "backtime" ]]
then
	echo "flashing back to marker/time $MNAME"
	echo "stopping database $ORACLE_SID"
	echo "using srvctl cluster services"
# Method 1: using srvctl ensures all nodes on cluster go down
ORACLE_CLUSTER=${ORACLE_SID%1}
echo "Stopping the DB cluster $ORACLE_CLUSTER"
echo
srvctl stop database -d $ORACLE_CLUSTER
echo
echo 
srvctl status database -d $ORACLE_CLUSTER
echo
# check result code here, if non-zero do shutdown immediate path
echo "<ENTER> when ${ORACLE_CLUSTER}1 and ${ORACLE_CLUSTER}2  are down and we can continue..."
read ANS
# NOTE: Not ALL of the commands in the following sequence are always
# necessary, but I find that some combination of them are depending on
# any problem encountered
if [[ $1 == "backtime" ]]
then
echo "Flashing back to time $MNAME"
sqlplus -s / as sysdba << EOFT
!echo Mounting DB
startup mount exclusive
!echo Flashing back DB
flashback database to timestamp to_timestamp('$MNAME', 'DD/MM/YY HH24:MI');
!echo Opening logs
alter database open resetlogs;
!echo Done
EOFT
else
sqlplus -s / as sysdba << EOF
!echo Mounting DB
startup mount exclusive
-- !echo Suspending Flashback
-- alter database flashback off;
!echo Flashing back DB
flashback database to restore point $MNAME;
!echo Opening logs
alter database open resetlogs;
-- !echo Starting Flashback service
-- alter database flashback on;
-- !echo Opening database
-- alter database open;
!echo Done
EOF
fi

MYORACLE_SID2=${ORACLE_SID%1}2
echo "Starting DB cluster again, if this fails ... "
echo "Remember to start the instance on the other node, at $MYORACLE_SID2"
#echo "using . oraenv and then srvctl start instance -d $ORACLE_CLUSTER -i $MYORACLE_SID2"
#rsh ssedb2 "srvctl start instance -d ps1e -i $MYORACLE_SID2"
echo "using . oraenv and then srvctl start cluster -d $ORACLE_CLUSTER"
srvctl start database -d $ORACLE_CLUSTER
srvctl status database -d $ORACLE_CLUSTER

echo "starting the orcon service"
./doconservice.sh start or
./doconservice.sh list
#srvctl start service -d $ORACLE_CLUSTER -s l2orcon
#srvctl status service -d $ORACLE_CLUSTER -s l2orcon

elif [[ $1 == "backimm" ]]
then
	echo "flashing back to marker $MNAME"
	echo "stopping database $ORACLE_SID"
	echo "using shutdown immediate route"
	echo "ARE YOU SURE you want to use this option, Y to continue"
	read ANS
	if [[ $ANS != "Y" ]]
	then
		exit 1
	fi
# Method 2: use shutdown immediate, bu this only does node you are on
ORACLE_CLUSTER=${ORACLE_SID%1}
sqlplus -s / as sysdba << EOF
!echo Shutting down DB
shutdown immediate
!echo Mounting DB
startup mount exclusive
-- !echo Suspending Flashback
-- alter database flashback off;
!echo Flashing back DB
flashback database to restore point $MNAME;
!echo Opening logs
alter database open resetlogs;
-- !echo Starting Flashback service
-- alter database flashback on;
!echo Opening database
alter database open;
!echo Done
EOF
echo "NOTE: you need to start up the other cluster nodes manually"
echo "and probably the orcon service too"


elif [[ $1 == "drop" ]]
then
	echo "dropping the restore point $MNAME"
sqlplus -s / as sysdba << EOF
drop restore point $MNAME;
EOF
elif [[ $1 == "listtime" ]]
then
       echo "listing oldest flashback time"
sqlplus -s / as sysdba << EOF
set head off
set trim on
set trimspool on
set colsep ,
set linesize 999
set pagesize 999
set wrap off
select to_char(OLDEST_FLASHBACK_TIME, 'DD/MM/YY HH24:MI')
    from v\$flashback_database_log;
EOF
elif [[ $1 == "list" ]]
then
	echo "listing markers"
sqlplus -s / as sysdba << EOF
set head off
set trim on
set trimspool on
set colsep ,
set linesize 999
set pagesize 999
set wrap off
select rtrim(name)||', '||rtrim(scn)||', '||rtrim(time)||', '||
rtrim(database_incarnation#)||', '||guarantee_flashback_database
from v\$restore_point
order by time asc;
EOF
else
	echo "unknown command"
fi