• Category Archives Oracle Administration
  • Monitoring Oracle Standby with Solaris Scripting

    Validating Archives Between Production and Standby Databases

    I wrote these Solaris Unix scripts for a book project about 10 years ago. They are dated but I have used them myself recently to construct useful monitoring scripting in Linux.

    On the Production Database

    #!/bin/ksh
    
    ORACLE_HOME=/<mount-point>/oracle/product/8.1.7
    LD_LIBRARY_PATH=/<mount-point>/oracle/product/8.1.7/lib:/usr/ucblib:/usr/openwin/lib
    JAVA_HOME=/<mount-point>/oracle/product/jre/1.1.8
    ORACLE_SID=<SID>
    ORACLE_SBIN=/<mount-point>/oracle/product/8.1.7/sbin
    ORACLE_ALERT=/<mount-point>/oracle/admin/sudbp/bdump
    
    PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin
    
    host=`hostname`
    
    if [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]
    || [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$ORACLE_SBIN" ]
    || [ -z "$PATH" ] || [ -z "$host" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    USAGE="$0: Incorrect arguments, Usage: $0 <"email1 email2 ... emailn">"
    
    if [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
    exit 1
    elif [ -z "$1" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" "emergency@xyz.com" < /dev/null
    exit 1
    fi
    
    email=$1
    
    echo "Executing $0 on $host"
    
    $ORACLE_HOME/bin/sqlplus internal<<!
    set head off term off echo off feedback off show off trim off trims off verify off linesize 132 escape on;
    spool /u01/app/oracle/product/8.1.5/sbin/logs/archives_e4501.log;
    select 'SEQUENCE '||to_char(sequence#)||' DATE '||to_char(first_time,'yyyy/mm/dd hh:mi:ss')
    from v$log_history where sequence# = (select max(sequence#)-1 from
    v$log_history);
    spool off;
    exit
    !
    
    cat $ORACLE_SBIN/logs/archives_production.log | grep "SEQUENCE" | grep -v grep | grep -v SQL> | awk '{ print $1,$2,$3,$4,$5 }' > $ORACLE_SBIN/logs/archives_production.log
    
    if [ -s $ORACLE_SBIN/logs/archives_production.log ]; then
    /usr/bin/rcp -p $ORACLE_SBIN/logs/archives_production.log &ltStandby Database Hostname>:/<mount-point>/oracle/product/8.1.7/sbin/logs/archives_production.log
    else
    sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs"
    echo $sub
    mailx -s "$sub" $email < "Error"
    exit 1
    fi
    
    exit 0
    

    On the Standby Database

    #!/bin/ksh
    
    ORACLE_HOME=/<mount-point>/oracle/product/8.1.7
    LD_LIBRARY_PATH=/<mount-point>/oracle/product/8.1.7/lib:/usr/ucblib:/usr/openwin/lib
    JAVA_HOME=/<mount-point>/oracle/product/jre/1.1.8
    ORACLE_SID=<SID>
    ORACLE_SBIN=/<mount-point>/oracle/product/8.1.7/sbin
    ORACLE_ALERT=/<mount-point>/oracle/admin/sudbp/bdump
    
    PATH=/bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/ucb:/usr/local/bin:/usr/sbin:/usr/X/bin:$JAVA_HOME
    PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/sbin
    
    host=`hostname`
    
    if [ -z "$ORACLE_HOME" ] || [ -z "$LD_LIBRARY_PATH" ] || [ -z "$JAVA_HOME" ]
    || [ -z "$ORACLE_SID" ] || [ -z "$ORACLE_ALERT" ] || [ -z "$ORACLE_SBIN" ]
    || [ -z "$PATH" ] || [ -z "$host" ]; then
    echo "$0 aborted - variable not defined"
    mailx -s "$0 aborted - variable not defined" $email < /dev/null
    exit 1
    fi
    
    USAGE="$0: Incorrect arguments, Usage: $0 <"email1 email2 ... emailn">"
    
    if [ `whoami` != 'oracle' ]; then
    echo "$0 aborted - user `whoami` is incorrect, must be user oracle"
    mailx -s "$0 aborted - user `whoami` is incorrect, must be user oracle" $email < /dev/null
    exit 1
    elif [ -z "$1" ]; then
    echo "$USAGE"
    mailx -s "$USAGE" "emergency@xyz.com" < /dev/null
    exit 1
    fi
    
    email=$1
    
    echo "Executing $0 on $host"
    
    $ORACLE_HOME/bin/sqlplus internal<<!
    set head off term off echo off feedback off show off trim off trims off verify off linesize 132 escape on;
    spool $ORACLE_SBIN/logs/archives_standby.log;
    select 'SEQUENCE '||to_char(sequence#)||' DATE '||to_char(first_time,'yyyy/mm/dd hh:mi:ss')
    from v$log_history where sequence# = (select max(sequence#) from v$log_history);
    spool off;
    exit
    !
    
    cat $ORACLE_SBIN/logs/archives_standby.log | grep "SEQUENCE" | grep -v grep | grep -v SQL> | awk '{ print $2 }' > $ORACLE_SBIN/logs/archives_standby.log
    
    if [ -s $ORACLE_SBIN/logs/archives_production.log ] -a [ -s $ORACLE_SBIN/logs/archives_standby.log ]; then
    export prod=`/bin/cat $ORACLE_SBIN/logs/archives_production.log`
    export stby=`/bin/cat $ORACLE_SBIN/logs/archives_standby.log`
    export diffs=$(($prod-$stby))
    if [ $diffs -gt 2 ]; then
    sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs ($diffs)"
    echo $sub
    mailx -s "$sub" $email < /dev/null
    exit 1
    fi
    else
    sub="Script $0 on $host in database $ORACLE_SID detected missing archive logs"
    echo $sub
    mailx -s "$sub" $email < /dev/null
    exit 1
    fi
    
    exit 0
    

    Recovery Script on Standby Database

    #!/bin/ksh
    
    export ORACLE_SID=<SID>
    export ORACLE_HOME=/<mount-point-1>/oracle/product/8.1.7
    PATH=$PATH:$ORACLE_HOME/bin
    export LD_LIBRARY_PATH=lib:/usr/ucblib
    
    svrmgrl << EOF
    connect internal
    recover standby database
    auto
    exit
    

    Changing Strings in Files Using the sed Editor

    Be extremely careful using a script such as shown below where global changes can be made.

    #!/bin/ksh
    for file in $* do
    	cp $file $file.old
    	sed -e 's/find/replace/g' < $file > $file
    done
    

    Related Postings:
    Unix for Oracle
    Monitoring Oracle with Solaris Scripting