Oracle DgFailOver

#!/bin/bash
#
# Creater : ygzhou (Zhou)
# Date : 2016-05-11
# Description : Auto Failover PHYSICAL STANDBY Database
###########################################################
## failover.sh ##
###########################################################


# Some important Environment variables
echo "****************************************************************************************************"
echo "***【Step 1】: Environment variables                                    "`date`
echo "****************************************************************************************************"


echo -n "Please Enter 【ORACLE_SID】:"
read SID;
if [ "$SID" = "" ]; then
    echo "You havn't Enter your db name!";
    unset ORACLE_SID
else if awk -F: '{print $1}' /etc/sf/shell/db_info.txt|grep -w $SID
then
export ORACLE_SID=$SID;
export ORACLE_HOME=`awk -F: '{if ($1 == "'$ORACLE_SID'") {print $2; exit}}' /etc/sf/shell/db_info.txt`
export NLS_LANG=`awk -F: '{if ($1 == "'$ORACLE_SID'") {print $3; exit}}' /etc/sf/shell/db_info.txt`
else
  echo "The database name not exist in /etc/sf/shell/db_info.txt";
  unset ORACLE_SID;
fi
fi

#======== PATH ========
umask 022
export TERM=vt100
export EDITOR=vi
export ORACLE_BASE=/dba/oracle
export PATH=$ORACLE_HOME/bin:.:$HOME/sh:/dba/app/product/11.2.0/grid/bin:/sbin:/usr/sbin:/opt/VRTS/bin:/opt/VRTSvcs/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TMP=/tmp
export ORA_ENCRYPT_LOGIN=true
export SHELL_PATH=/etc/paic/shell
LD_LIBRARY_PATH=$LD_LIBRARY_PATH ;export LD_LIBRARY_PATH
PATH=$PATH ; export PATH
alias crsctl=/dba/app/product/11.2.0/grid/bin/crsctl


#========= check os users ===============
if id oracle &> /dev/null ; then
    oralce_home_dir=`awk -F: '{if ($1=="oracle") print $6}' /etc/passwd`
    [ -f "${oralce_home_dir}/.bash_profile" ] && . "${oralce_home_dir}/.bash_profile"
    oracle_user_id=`id -u oracle 2> /dev/null`
fi
/
#======== functions ========
run_sql() {
    sqlplus -silent / as sysdba << EOF
set heading off feedback off pagesize 0 verify off echo off
$*
exit;
EOF
}


#======== main =========
cur_dir=`pwd`
scripts_dir=${cur_dir}
plsqls_dir=${cur_dir}/sql
backups_dir=${cur_dir}/${ORACLE_SID}
mkdir -p ${backups_dir}

echo "****************************************************************************************************"
echo "***  Step 2: Create backup directory                                    "`date`
echo "****************************************************************************************************"

#---- verify run user ----
if [[ "$EUID" -ne "${oracle_user_id}" ]] ; then
    echo "please run this script with oracle."
    exit
fi


dbrole=`run_sql 'SELECT DATABASE_ROLE FROM V$DATABASE;'`
echo "****************************************************************************************************"
echo "***  Step 3: Verify database role                                       "`date`
echo "****************************************************************************************************"
if [ "${dbrole}" != "PHYSICAL STANDBY" ] ; then
    echo "Current database role is ${dbrole} , please verify failover information ! "
    exit
fi
sleep 5;

dbjobs=`run_sql "@${plsqls_dir}/query_jobs.sql"`
echo "****************************************************************************************************"
echo "***  Step 4: Backup job parameter                                       "`date`
echo "****************************************************************************************************"
sleep 5;

setjobs=`run_sql 'alter system set job_queue_processes=0;'`
echo "****************************************************************************************************"
echo "***  Step 5: Disable jobs                                               "`date`
echo "****************************************************************************************************"
if echo "${setjobs}" | egrep -i -q "ORA-" ; then
    echo "Set job_queue_processes=0 failed."
    echo "${setjobs}"
    exit
fi
sleep 5;

stop_redo_apply=`run_sql 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;'`
echo "****************************************************************************************************"
echo "***  Step 6: Stop Redo Apply                                            "`date`
echo "****************************************************************************************************"
if echo "${stop_redo_apply}" | egrep -i -q "ORA-" ; then
    echo "Stop Redo Apply failed."
    echo "${stop_redo_apply}"
    exit
fi
sleep 5

create_point=`run_sql 'create restore point zhouxin_1 guarantee flashback database;'`
echo "****************************************************************************************************"
echo "***  Step 7: Create restore point                                       "`date`
echo "****************************************************************************************************"
if echo "${create_point}" | egrep -i -q "ORA-" ; then
    echo "create restore point failed."
    echo "${create_point}"
    exit
fi
sleep 5

read_only=`run_sql 'ALTER DATABASE OPEN READ ONLY;'`
echo "****************************************************************************************************"
echo "***  Step 8: Startup db readonly                                        "`date`
echo "****************************************************************************************************"
sleep 5
db_open_mode=`run_sql 'select OPEN_MODE from v$database; '`
echo "****************************************************************************************************"
echo "***  Step 9: Check database open mode & status                          "`date`
echo "****************************************************************************************************"
if ! echo "${db_open_mode}" | egrep -i -q "READ ONLY" ; then
    echo "DB open mode is not read only."
    echo "${db_open_mode}"
    exit
fi
instance_stat=`run_sql 'SELECT STATUS FROM V$INSTANCE;'`
if ! echo "${instance_stat}" | egrep -i -q "OPEN" ; then
    echo "Instance status is not open."
    echo "${instance_stat}"
    exit
fi

shutdown=`run_sql 'SHUTDOWN IMMEDIATE;'`
echo "****************************************************************************************************"
echo "***  Step 10: Shutdown database                                         "`date`
echo "****************************************************************************************************"

mount=`run_sql 'startup mount;'`
echo "****************************************************************************************************"
echo "***  Step 11: Startup mount                                             "`date`
echo "****************************************************************************************************"


activate=`run_sql 'ALTER DATABASE ACTIVATE STANDBY DATABASE;'`
echo "****************************************************************************************************"
echo "***  Step 12: Failover standby database                                 "`date`
echo "****************************************************************************************************"
sleep 5

open=`run_sql 'ALTER DATABASE OPEN;'`
shutdown=`run_sql 'shutdown immediate;'`
restrict=`run_sql 'startup restrict;'`
echo "****************************************************************************************************"
echo "***  Step 13: Restrict database                                         "`date`
echo "****************************************************************************************************"

all_dblinks=`run_sql "@${plsqls_dir}/query_open_links.sql"`
sleep 5;
run_sql 'alter system set open_links=0 scope=spfile;'
echo "****************************************************************************************************"
echo "***  Step 14: Disable all dblinks                                       "`date`
echo "****************************************************************************************************"
sleep 5;


create_user=`run_sql 'create user zhouxin835088 identified by SFDBA#pwd123 profile default;'`
if echo "${create_user}" | egrep -i -q "ORA-" ; then
   echo "Oracle proxy user zhouxin835088 exist !."
   echo "${create_user}"
   exit
fi
grant_user=`run_sql 'grant dba to zhouxin835088;'`
sleep 5;
echo "****************************************************************************************************"
echo "***  Step 15: create Proxy user                                         "`date`
echo "****************************************************************************************************"
sleep 5;


all_users=`run_sql 'select distinct owner from dba_db_links order by owner;'`
for users in ${all_users}
do
if [[ "$users" = "SYS" ]]; then
   query_dblinks=`run_sql "@${plsqls_dir}/query_all_links.sql;"`
   sleep 5;
   drop_dblinks=`run_sql "@${backups_dir}/drop_dblinks.sql"`
   echo "Current drop user ${users} dblinks !"

elif [[ "$users" = "PUBLIC" ]]; then
   query_dblinks=`run_sql "@${plsqls_dir}/query_pub_links.sql;"`
   sleep 5;
   drop_dblinks=`run_sql "@${backups_dir}/drop_dblinks.sql"`
   echo "Current drop user ${users} dblinks !"

else
   alter_user=`run_sql "alter user ${users} grant connect through zhouxin835088;"`
   sleep 3;
   query_dblinks=`run_sql "conn ZHOUXIN835088[${users}]/SFDBA#pwd123;
                          @${plsqls_dir}/query_all_links.sql;"`
   sleep 5;
   drop_dblinks=`run_sql "conn ZHOUXIN835088[${users}]/SFDBA#pwd123;
                         @${backups_dir}/drop_dblinks.sql"`
   echo "Current drop user ${users} dblinks !"
   sleep 5;
fi
done

echo "****************************************************************************************************"
echo "***  Step 16: drop all dblinks                                          "`date`
echo "****************************************************************************************************"
sleep 5;

shutdown=`run_sql 'shutdown immediate;'`
open=`run_sql 'startup;'`
echo "****************************************************************************************************"
echo "***  Step 17: Restart database normal mode                              "`date`
echo "****************************************************************************************************"
sleep 5;

db_mode=`run_sql 'select OPEN_MODE from v$database; '`
echo "****************************************************************************************************"
echo "***  Step 18: Check database open mode                                  "`date`
echo "****************************************************************************************************"
if ! echo "${db_mode}" | egrep -i -q "READ WRITE" ; then
    echo "DB open mode is not READ WRITE."
    echo "${db_mode}"
    exit
fi
sleep 5;


#---- step 16 Temp file ----
tmp_files=`run_sql 'select file_name from dba_temp_files;'`
if echo "${tmp_files}" | egrep -q 'temp[0-9]+\.dbf' ; then
    if ! [ -f "${tmp_files}" ] ; then
        echo "tmp file enpty exists, but not find on disk."
        echo "${tmp_files}"
        exit
    fi
else
    systmp_files=`run_sql 'SELECT NAME FROM V$DATAFILE WHERE ROWNUM<=1;'`
    tmp_dir=`dirname "${systmp_files}"`
    run_sql "ALTER TABLESPACE TEMP ADD TEMPFILE '${tmp_dir}/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;"
fi
echo "****************************************************************************************************"
echo "***【Step 18】: Check temp tablespace                                   "`date`
echo "****************************************************************************************************"
sleep 5;
#---- finish ----

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867586/viewspace-2116782/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867586/viewspace-2116782/