监控DG脚本

#监控DG脚本

##源端脚本

**monitor_doic.sh **

/home/oracle/scripts/monitor.sh /home/oracle/scripts/doic_profile doic stddoic

monitor.sh

PF=$1
SID=$2
CS=$3
export STATE=0
export RSTATE=0
LOGFILE=monitor_${SID}.log
ET=`date +"%y-%m-%d %T"`
PDBSTAT=`/home/oracle/scripts/ora_database_stat.sh $SID`
if [ $PDBSTAT -eq 1 ]
then
     STATE=64
     echo ${ET}"||"${SID}"||"DATAGUARD"||"${STATE} >/home/oracle/scripts/log/${LOGFILE}
     exit
fi

MAX_SEQ=`/home/oracle/scripts/query_max_sequence.sh $PF`
PRE_SEQ=`expr $MAX_SEQ - 1`
AC=`/home/oracle/scripts/query_remote_archivedlog.sh $PF $CS $PRE_SEQ`
if [ $AC -eq 1 ]
then
        STATE=`expr $STATE + 0`
else
        STATE=`expr $STATE + 1`
fi
. /home/oracle/scripts/tnsping.sh $PF $CS
RSTATE=`rsh BKdoicPRD01 /home/oracle/scripts/std_monitor.sh $PF $SID $CS $MAX_SEQ`
STATE=`expr $STATE + $RSTATE`
if [ $STATE -eq 0 ]
then
        echo ${ET}"||"${SID}"||"DATAGUARD"||"S >/home/oracle/scripts/log/${LOGFILE}
else
        echo ${ET}"||"${SID}"||"DATAGUARD"||"${STATE} >/home/oracle/scripts/log/${LOGFILE}
fi

doic_profile

JAVA_HOME=/usr/java5

export JAVA_HOME

PATH=$JAVA_HOME/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/home/oracle/software/apach
e-ant-1.7.1/bin:.

export PATH

ORACLE_SID=doic
ORACLE_HOME=/oracle/product/10.2.0
PATH=$PATH:$ORACLE_HOME/bin:.:/user/local/bin
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export NLS_LANG
umask=022
export PATH
export RACLE_BASE

export ORACLE_SID
export ORACLE_HOME

ora_database_stat.sh

SID=$1
DBSTAT=0
for p in `cat /home/oracle/scripts/monitor_db_processes.txt`
do
        PR=${p}_${SID}
        ps -ef |grep ${PR}|grep -v grep >/dev/null
        if [ $? -ne 0 ]
        then
                DBSTAT=1
                break
        fi

done
echo $DBSTAT

query_max_sequence.sh

PF=$1
. ${PF}
sqlplus -S "/ as sysdba" <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
  select max(sequence#) from v\$archived_log;
EOF

query_remote_archivedlog.sh

PF=$1
CS=$2
SEQ=$3
. ${PF}
sqlplus -S "/ as sysdba" <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
  select count(sequence#) from v\$archived_log
        where sequence#=$SEQ and name='$CS';
EOF

##目标端脚本

std_monitor.sh

PF=$1
SID=$2
CS=$3
MAX_SEQ=$4
RSTATE=0
DBSTAT=`/home/oracle/scripts/ora_database_stat.sh $SID`
if [ $DBSTAT -eq 1 ]
then
     RSTATE=`expr $RSTATE + 32`
     echo $RSTATE
     exit
else 
     RSTATE=`expr $RSTATE + 0`
fi

SEQ=`expr ${MAX_SEQ} - 1`
APL=`/home/oracle/scripts/std_archivedlog_applied.sh $PF $SEQ`
if [ $APL -eq 1 ]
then
     RSTATE=`expr $RSTATE + 0`
else
     RSTATE=`expr $RSTATE + 4`
fi
GAP=`/home/oracle/scripts/std_archivelog_gap.sh $PF`
if [ $GAP -eq 0 ]
then
     RSTATE=`expr $RSTATE + 0`
else
     RSTATE=`expr $RSTATE + 8`
fi
MRP=`/home/oracle/scripts/std_mrp_process.sh $PF`
if [ $MRP -eq 0 ]
then
     RSTATE=`expr $RSTATE + 16`
else
     RSTATE=`expr $RSTATE + 0`
fi

echo $RSTATE

ora_database_stat.sh

SID=$1
DBSTAT=0
for p in `cat /home/oracle/scripts/monitor_db_processes.txt`
do 
        PR=${p}_${SID}
        ps -ef |grep ${PR}|grep -v grep >/dev/null
        if [ $? -ne 0 ]
        then
                DBSTAT=1
                break
        fi
        
done
echo $DBSTAT

std_archivedlog_applied.sh

PF=$1
. ${PF}
SEQ=$2
sqlplus -S "/ as sysdba" <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
  select count(sequence#) from v\$archived_log
        where sequence#=${SEQ} and applied='YES';
EOF

std_archivelog_gap.sh

PF=$1
. ${PF}
sqlplus -S "/ as sysdba" <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
  select count(*) from v\$archive_gap;
EOF

std_mrp_process.sh

PF=$1
. ${PF}
sqlplus -S "/ as sysdba" <<EOF
set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
select count(program) from v\$process 
        where program like '%(MRP0)%';
EOF

##删除备库归档日志脚本

archived_log.sh

cd /home/oracle/scripts/doic/temp
. /home/oracle/scripts/doic/doic_profile
sqlplus "/ as sysdba" <<EOF
@/home/oracle/scripts/doic/archived_log.sql
EOF
grep "ORACLE not available" /home/oracle/scripts/doic/archived_log.txt >>/dev/null
if [ $? = 0 ]
then
        exit
fi

grep "database not mounted" /home/oracle/scripts/doic/archived_log.txt >>/dev/null
if [ $? = 0 ]
then
        exit
fi

grep "select" /home/oracle/scripts/doic/archived_log.txt >>/dev/null
if [ $? = 0 ]
then
        exit
fi

grep "*" /home/oracle/scripts/doic/archived_log.txt >>/dev/null
if [ $? = 0 ]
then
        exit
fi

grep "ERROR" /home/oracle/scripts/doic/archived_log.txt >>/dev/null
if [ $? = 0 ]
then
        exit
fi

for logname in `cat /home/oracle/scripts/doic/archived_log.txt`
do
if [ -f $logname ]
then
        echo "rm $logname" 
        rm $logname
fi
done

doic_profile

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.

export PATH

if [ -s "$MAIL" ]           # This is at Shell startup.  In normal
then echo "$MAILMSG"        # operation, the Shell checks
fi                          # periodically.
ORACLE_SID=doic
export ORACLE_SID
ORACLE_HOME=/oracle/product/10.2.0
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:.:/user/local/bin
export PATH
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export NLS_LANG
PS1=`hostname`$
export PS1
umask=022

archived_log.sql

set echo off
set feedback off
set pagesize 0
set linesize 80
set verify off
set term off
set trims off
set heading off
set timing off
column name format a80
spool /home/oracle/scripts/doic/archived_log.txt
select name from v$archived_log 
        where  exists (select database_role from v$database where  trim(database_role)='PHYSICAL STANDBY') and applied='YES' and trim(name)<>'doic' and completion_time<=sysdate-1/24 and completion_time>=sysdate-60 order by sequence#;
spool off