这几天搭建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;