#!/bin/ksh #============================================================================= # Name: stdby_ship.sh # Type: korn-shell script # Author: Tim Gorman (Evergreen Database Technologies, Inc) # Date: 29Mar01 # # Description: # Korn-shell script intended to be executed from the UNIX "cron" utility # against the primary database. This script should be executed as # frequently as necessary to ship any recently-generated archived redo # log files generated by the primary database over to the standby database. # # Assumptions: # This script expects to be executed under the UNIX "cron" utility as the # UNIX account owning the Oracle software, so that it can connect # "/ as SYSDBA". # # This script relies on the standard "oraenv" shell script supplied with # a standard Oracle RDBMS distribution. If you use some other method for # setting up the necessary Oracle environment variables such as ORACLE_SID # and ORACLE_HOME, then this script will need to be modified accordingly. # # Also, this script expects an environment variable named ORACLE_BASE to # be set up in the "oraenv" script, and it also expects a directory # structure like the following: # # $ORACLE_BASE/ # admin/ # $ORACLE_SID/ # adhoc/ # adump/ # bdump/ # cdump/ # pfile/ # udump/ # # This script uses the "adhoc" subdirectory in which to create some # files used for locking. All other directory locations are extracted # from the database. # # Notifications and alerts: # This script will send all messages to "standard output", which is # typically emailed to the calling user by the UNIX "cron" utility. # # Alternatively, this script is also designed to expect the presence of # one or two text files, each containing a list of email addresses, one # per line. Both text files are expected in the $HOME directory of the # Oracle software owner, and they are named: # # .dbamail List of email addresses for DBAs to receive # longer, more complete error messages, warnings, # and informational messages # .dbapage List of email addresses (intended for text msgs) # for short messages only in the event of serious # failure # # Modifications: # TGorman 29Mar01 #============================================================================= _Pgm=stdby_ship export PATH=/opt/bin:/usr/bin:/usr/local/bin:${PATH} # #---------------------------------------------------------------------------- # Define a korn-shell function to handle error messages... #---------------------------------------------------------------------------- _Echo() ### ...define shell-function "_Echo()"... { if [[ "$1" = "failure" ]] then if [ -r ${HOME}/.dbapage ] then sed '/^#/d' ${HOME}/.dbapage | while read _PageRcpt do date | mailx -s "`hostname`: ${_Pgm} $1" ${_PageRcpt} done fi fi if [ -r ${HOME}/.dbamail ] then sed '/^#/d' ${HOME}/.dbamail | while read _EmailRcpt do echo "$2" | mailx -s "`hostname`: ${_Pgm} $1" ${_EmailRcpt} done else echo "`hostname`: ${_Pgm} $1\n\n$2\n" fi } ### ...end of definition of shell-function "_Echo()"... # #----------------------------------------------------------------------------- # Validate the number of command-line parameters... #----------------------------------------------------------------------------- if (( $# != 2 )) then echo "Usage: \"$0 \"; aborting..." exit 1 fi # _PriTnsString=$1 _StdbyOraSID=$2 # #----------------------------------------------------------------------------- # Set up connection information to both PRIMARY and STANDBY databases... #----------------------------------------------------------------------------- if [ ! -r ~/.orapwd ] # ...validate that ".orapwd" file exists... then _Echo warning "Cannot read \"~/.orapwd\" file; aborting..." exit 1 fi # _PriUnPwd=`grep "@${_PriTnsString}$" ~/.orapwd | grep -i "^system/"` if [ -z ${_PriUnPwd} ] # ...validate that password for SYSTEM was found... then _Echo warning "Cannot find password for SYSTEM on ${_PriTnsString}; aborting..." exit 1 fi # #----------------------------------------------------------------------------- # Check to see if the STANDBY database is local. Since the STANDBY instance # is running in mount mode, we must use CONNECT INTERNAL... #----------------------------------------------------------------------------- dbhome ${_StdbyOraSID} > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "\"${_StdbyOraSID}\" is not local to this host; aborting..." exit 1 fi # #----------------------------------------------------------------------------- # Set Oracle environment variables... #----------------------------------------------------------------------------- export ORACLE_SID=${_StdbyOraSID} export ORAENV_ASK=NO . oraenv > /dev/null 2>&1 unset ORAENV_ASK # #----------------------------------------------------------------------------- # Validate that important environment variables exist... #----------------------------------------------------------------------------- if [[ "${ORACLE_SID}" = "" ]] then _Echo warning "ORACLE_SID not set; aborting..." exit 1 fi if [[ "${ORACLE_HOME}" = "" ]] then _Echo warning "ORACLE_HOME not set; aborting..." exit 1 fi if [[ "${LD_LIBRARY_PATH}" = "" ]] then _Echo warning "LD_LIBRARY_PATH not set; aborting..." exit 1 fi if [[ "${ORACLE_BASE}" = "" ]] then _Echo warning "ORACLE_BASE not set; aborting..." exit 1 fi # #----------------------------------------------------------------------------- # Validate that important directories exist... #----------------------------------------------------------------------------- if [ ! -d ${ORACLE_BASE} ] then _Echo warning "Directory \"${ORACLE_BASE}\" not found; aborting..." exit 1 fi if [ ! -d ${ORACLE_BASE}/admin ] then _Echo warning "Directory \"${ORACLE_BASE}/admin\" not found; aborting..." exit 1 fi if [ ! -d ${ORACLE_BASE}/admin/${ORACLE_SID} ] then _Echo warning "Directory \"${ORACLE_BASE}/admin/${ORACLE_SID}\" not found; aborting..." exit 1 fi if [ ! -d ${ORACLE_BASE}/admin/${ORACLE_SID}/adhoc ] then _Echo warning "Directory \"${ORACLE_BASE}/admin/${ORACLE_SID}/adhoc\" not found; aborting..." exit 1 fi # #----------------------------------------------------------------------------- # Check to see if the STANDBY is being refreshed at the present time. If so, # then just exit quietly... #----------------------------------------------------------------------------- _LockFile=${ORACLE_BASE}/admin/${ORACLE_SID}/adhoc/stdby_init.lock if [ -f ${_LockFile} ] then exit 0 fi # #----------------------------------------------------------------------------- # Create a temporary environment variable to refer to a temp shell script... #----------------------------------------------------------------------------- _TmpFile=/tmp/.stdby_ship_${$}.tmp _OutFile=/tmp/.stdby_ship_${$}.out # #----------------------------------------------------------------------------- # Use SQL*Plus to query the PRIMARY database to determine which archived redo # log files need to be shipped up to the STANDBY database... #----------------------------------------------------------------------------- sqlplus -s /nolog << __EOF__ > ${_OutFile} 2>&1 whenever oserror exit 1 whenever sqlerror exit 1 connect ${_PriUnPwd} variable v_new_lastseq number set echo off feedback off pagesize 0 linesize 500 trimspool on trimout on /* if the table already exists, then let this command just fail with no harm */ whenever sqlerror continue create table standbydb_loghistory ( last_seq#_shipped number not null ); set serveroutput on size 1000000 echo off feedback off trimspool on whenever oserror exit 2 whenever sqlerror exit 2 spool ${_TmpFile} whenever sqlerror exit 95 alter system switch logfile; whenever sqlerror continue alter system archive log all; whenever oserror exit 3 whenever sqlerror exit 3 declare -- cursor get_logseqnos(in_lastseq in number) is select sequence# seqno, name from v\$archived_log where archived = 'YES' and sequence# > in_lastseq and resetlogs_change# >= (select resetlogs_change# from v\$database) order by sequence#; -- v_hostname varchar2(100); v_lastseq number; -- already_locked exception; pragma exception_init(already_locked, -54); -- begin -- /* retrieve the hostname of the database server */ select decode('${_HostName}', host_name, '', host_name || ':') into v_hostname from v\$instance; -- /* Retrieve the SEQUENCE# of the last archived redo log file * shipped to the standby database. If this record is locked, then * it means that another similar process is still trying to ship the * files, so return a value of "-1"... */ begin -- select last_seq#_shipped into v_lastseq from standbydb_loghistory for update of last_seq#_shipped nowait; -- :v_new_lastseq := v_lastseq; -- exception -- when no_data_found then -- insert into standbydb_loghistory values (0); -- select last_seq#_shipped into v_lastseq from standbydb_loghistory for update of last_seq#_shipped nowait; -- :v_new_lastseq := v_lastseq; -- when already_locked then -- v_lastseq := -1; :v_new_lastseq := -1; -- -- end; -- /* If the row in the STANDBYDB_LOGHISTORY table is already locked, * then do nothing. Otherwise, query the V$ARCHIVED_LOG view to find * the list of archived redo log files which need to be shipped... */ if v_lastseq > -1 then -- for s in get_logseqnos(v_lastseq) loop -- dbms_output.put_line('scp ' || v_hostname || s.name || ' ' || s.name); -- :v_new_lastseq := s.seqno; -- end loop; -- end if; -- end; / spool off whenever oserror exit 4 whenever sqlerror exit 4 ${_SqlTESTING}host chmod 755 ${_TmpFile} whenever oserror exit 5 whenever sqlerror exit 5 ${_SqlTESTING}host ${_TmpFile} whenever oserror exit 6 rollback whenever sqlerror exit 6 rollback update standbydb_loghistory set last_seq#_shipped = :v_new_lastseq; whenever oserror exit 7 rollback whenever sqlerror exit 7 rollback commit; whenever oserror exit 8 whenever sqlerror exit 8 ${_SqlTESTING}host rm -f ${_TmpFile} exit success __EOF__ # #----------------------------------------------------------------------------- # Based on error code from SQL*Plus, compose an error message... #----------------------------------------------------------------------------- integer _Rtn=$? case ${_Rtn} in 0) rm -f ${_TmpFile} ${_OutFile} ;; # ...everything is OK! 1) _Echo failure "Cannot connect to PRIMARY" ;; 2) _Echo failure "SQL*Plus unable to create SPOOL file" ;; 3) _Echo failure "Error occurred during PL/SQL block" ;; 4) _Echo failure "chmod 755 of SPOOL file failed" ;; 5) _Echo failure "Executing SPOOL file failed" ;; 6) _Echo failure "UPDATE STANDBYDB_LOGHISTORY failed" ;; 7) _Echo failure "COMMIT failed" ;; 8) _Echo failure "Remove of SPOOL file failed" ;; 95) _Echo failure "ALTER SYSTEM SWITCH LOGFILE failed" ;; *) _Echo failure "SQL*Plus failed" ;; esac # #----------------------------------------------------------------------------- # Done! #----------------------------------------------------------------------------- exit 0