Oracle 11gR2 Active Data Guard 运维脚本

这几天搭建Oracle 11gR2 的ADG环境，wait4friend 顺手写了几个用于日常运维的脚本，记录下来。

#######################################################################

dg_start_db.sh

启动ADG环境中的Primary和Standby脚本，自动判断db role，然后启动到相应状态

#!/bin/bash
# ****************************************************
#   dg_start_db.sh
#   Written by McDull Zeng 2012-10-28  
#  
#   This script is used to startup Oracle 11gR2 in a Active Data Guard cluser.
#   It does determines the database role, and
#       1. open the PRIMARY db
#       or
#       2. open STANDBY db read only and realtime apply redo logs
#
#   Usage: just add item in /etc/init.d/rc.local ==> su - oracle -c "/home/oracle/script/dg_start_db.sh"
# ****************************************************

# User specific environment and startup programs
if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi

# startup listener
lsnrctl start

# determine the database role (Primary/Standby)
sqlplus -s / as sysdba <<EOF
    spool /tmp/oracle_role.log
    startup mount;
    set pages 0
    set head off
    set feed off
    select database_role from v\$database;
    spool off
    exit;
EOF

DB_ROLE=`cat /tmp/oracle_role.log | tail -1`
#echo "ROLE = ${DB_ROLE}"

if [ `echo $DB_ROLE | grep 'PRIMARY' | wc -l` -eq 1 ];then
    sqlplus -s / as sysdba <<EOF
        alter database open;
        exit;
EOF
elif [ `echo $DB_ROLE | grep 'PHYSICAL STANDBY' | wc -l` -eq 1 ];then
    sqlplus -s / as sysdba <<EOF
        alter database open read only;
        alter database recover managed standby database disconnect from session using current logfile;
        exit;
EOF
fi

#######################################################################

dg_switchover.sh

用于主备切换的脚本，支持参数 P2S和S2P ， 顾名思义啦

#!/bin/bash
# ****************************************************
#   Written by McDull Zeng 2012-11-01  
#  
#   This script is used to switch Data Guard role.
#
# ****************************************************

# User specific environment and startup programs
if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi

# action
if [ $# == 1 ];then
    ACTION=${1}
else
    echo "Usage: ./dg_switch.sh P2S or ./dg_switch.sh S2P"
exit 0
fi

# determine the database role (Primary/Standby)
sqlplus -s / as sysdba <<EOF
    spool /tmp/oracle_role.log
    set pages 0
    set head off
    set feed off
    select database_role||'|'||switchover_status||'|' from v\$database;
    spool off
    exit;
EOF

DB_ROLE=`cat /tmp/oracle_role.log | tail -1 | awk -F'|' '{print $1}' `
SWITCH_STATUS=`cat /tmp/oracle_role.log | tail -1 | awk -F'|' '{print $2}' `
#echo "ROLE = ${DB_ROLE}"
#echo "SWITCH_STATUS = ${SWITCH_STATUS}"

# Switch Primary to Standby
if [ "${ACTION}" = "P2S" -a "${DB_ROLE}" = "PRIMARY" -a "${SWITCH_STATUS}" = "TO STANDBY" ];then
lsnrctl stop
sqlplus -s / as sysdba <<EOF
        alter system switch logfile;
        alter system checkpoint;
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
conn / as sysdba
startup mount;
alter database open read only;
alter database recover managed standby database disconnect using current logfile ;
    exit;
EOF
lsnrctl start
    echo "******************************************"
    echo "******** Primary to Standby Done! ********"   
# Switch Standby to Primary
elif [ "${ACTION}" = "S2P" -a "${DB_ROLE}" = "PHYSICAL STANDBY" -a "${SWITCH_STATUS}" = "TO PRIMARY" ];then
sqlplus -s / as sysdba <<EOF
alter database commit to switchover to primary;
shutdown immediate;
conn / as sysdba
startup;
    exit;
EOF
    echo "******************************************"
    echo "******** Standby to Primary Done! ********"
else
    echo "******************************************"
    echo "********* Pre Status Check ERROR! ********"
    echo "DATABASE_ROLE = ${DB_ROLE}"
    echo "SWITCHOVER_STATUS = ${SWITCH_STATUS}"   
fi

#######################################################################

dg_clean_arc.sh

清除归档日志的脚本。同时适用于Primary和Standby

    Primary采用RMAN备份，所以清除的时候要判断 1. 是否已经备份，2. 是否在Standby已经应用

    Standby也采用RMAN备份，不过只需判断已经应用过，并备份

#!/bin/bash
# ****************************************************
#   dg_clean_arc.sh
#   Written by McDull Zeng 2012-10-29  
#  
#   This script is used to delete archived logs in a Data Guard cluser.
#   It needs a RETENTION which means the least number of days a arc log exists.
#
##   crontab under oracle user:
##      0 1 * * *           /home/oracle/script/dg_clean_arc.sh 1
# ****************************************************

# User specific environment and startup programs
if [ -f ~/.bash_profile ];
then
  . ~/.bash_profile
fi

# default retetion
if [ $# == 1 ];then
    RETENTION=${1}
else
    RETENTION=3
fi

#====================================================================== 
# query db role and whether it is a Data Guard enviroment
DB_INFO=`${ORACLE_HOME}/bin/sqlplus -s / as sysdba<<EOF
  set pages 0
  set head off
  set feed off
    select d.database_role || '|' || l.standby_count || '|' as db_info
      from (select d.database_role from v\\$database d) d,
           (select count(1) as standby_count
              from v\\$archived_log l
             where l.standby_dest = upper('YES')
               and rownum = 1) l
     where rownum = 1;
EOF`

DB_ROLE=`echo ${DB_INFO} | awk -F'|' '{print $1}'`
HAS_STANDBY=`echo ${DB_INFO} | awk -F'|' '{print $2}'`
# echo "DB ROLE = ${DB_ROLE}"
# echo "HAS Stamdby = ${HAS_STANDBY}"

if [ "${DB_ROLE}" = "PRIMARY" ];then
    sqlplus -s / as sysdba <<EOF
        set pages 0
        set head off
        set feed off
        spool /tmp/arc_need_delete.log
        -- run on Primary
        -- log which is backuped and applied to Standby
        select 'delete noprompt archivelog until sequence ' || max(l.sequence#) ||
               ' thread ' || l.thread# || ';'
          from v\$archived_log l
         where l.standby_dest = upper('no') -- local log
           and l.backup_count > 0 -- for RMANed primary
           and l.deleted = upper('no')
           and l.sequence# in
              -- log which is applied on standby
               (select l.sequence#
                  from v\$archived_log l
                 where l.completion_time < sysdate - ${RETENTION}
                   and l.standby_dest = upper('yes') -- standby log
                   and l.applied = upper('yes') -- for standby applied
                   and l.resetlogs_change# =
                       (select d.resetlogs_change# from v\$database d))
         group by l.thread#;       
        spool off
        exit;
EOF
elif  [ "${DB_ROLE}" = "PHYSICAL STANDBY" ];then
    sqlplus -s / as sysdba <<EOF
        set pages 0
        set head off
        set feed off
        spool /tmp/arc_need_delete.log
        -- run on Standby
        -- log which is applied to Standby
        select 'delete noprompt archivelog until sequence ' || max(l.sequence#) ||
               ' thread ' || l.thread# || ';'
          from v\$archived_log l
         where l.completion_time < sysdate - ${RETENTION}
           and l.standby_dest = upper('no') -- local redo
           and l.applied = upper('yes') -- for standby
           and l.deleted = upper('no')
           and l.backup_count > 0 -- for RMANed standby
           and l.resetlogs_change# = (select d.resetlogs_change# from v\$database d)
         group by l.thread#;
        spool off
        exit;
EOF
fi

DEL_CMD=`cat /tmp/arc_need_delete.log`
#echo $DEL_CMD

#Startup rman to delete
rman target=/ <<EOF
${DEL_CMD}
exit;
EOF  

exit;