Document ID: 38347.1
Subject: Oracle Database SHUTDOWN script
Last Modified: 29 May 96
Author: DHAYTER
The following command file will attempt to SHUTDOWN IMMEDIATE an Oracle7
database on either VAX or ALPHA OpenVMS platforms. If the SHUTDOWN IMMEDIATE
does not complete within a reasonable timeframe (coded below for 50 seconds)
a SHUTDOWN ABORT is issued, followed by a STARTUP RESTRICT and a SHUTDOWN
NORMAL.
NOTE: The script does *NOT* perform any backups.
Keywords:
SHUTDOWN;NORMAL;IMMEDIATE;ABORT;STARTUP;RESTRICT;PARALLEL;BACKUP;VMS;
$! SHUTDOWN_FOR_BACKUP.COM
$! =======================
$!
$! Copyright (c) Oracle Corporation 1979, 1996
$!
$! Author: Dave Hayter
$!
$! Date: 29-MAY-1996
$!
$! Status: *UNSUPPORTED*
$!
$! Function: To ensure that an Oracle7 database instance on OpenVMS
$! (VAX or ALPHA) is cleanly shut down for taking a cold backup.
$!
$! Restrictions: This procedure CANNOT shutdown other instances in an
$! Oracle Parallel Server environment but it SHOULD inform
$! us if other instances of this database are running.
$!
$ SET NOON
$ ECHO := WRITE SYS$OUTPUT
$ SID = F$TRNLNM("ORA_SID") ! Pick up the Oracle SID
$ PID = F$GETJPI("","PID") ! Pick up our process id
$ IF SID .NES. "" THEN GOTO SID_OK
$ ECHO "The logical ORA_SID is not defined - cannot connect to a database"
$ EXIT 44
$!
$!
$!
$SID_OK:
$!
$! Establish whether the background process for this SID are present
$!
$ GOSUB LOOK_FOR_SMON
$ IF SMON_UP THEN GOTO GET_RIGHTS_ID
$ ECHO ""
$ ECHO "INFORMATION: This instance appears to be down:"
$ ECHO " Unable to identify process ORA_''SID'_SMON"
$ EXIT 1
$!
$!
$GET_RIGHTS_ID:
$!
$! Enable the required VMS rights identifier to allow us to CONNECT INTERNAL
$!
$ ident_reqd = "ORA_''sid'_DBA"
$ ident_num = f$ident(ident_reqd,"NAME_TO_NUMBER")
$ if ident_num .eq. 0
$ then
$ ident_reqd = "ORA_DBA"
$ ident_num = f$ident(ident_reqd,"NAME_TO_NUMBER")
$ if ident_num .eq. 0 then goto skip_rights_id
$ endif
$ tmp = ident_num/%x10000
$ tmp[16,16]=0
$ if tmp .lt. %X8000 then goto skip_rights_id
$ set right/ena 'ident_reqd'
$ sts = '$status'
$ if sts then goto SKIP_RIGHTS_ID
$ ECHO ""
$ ECHO "ERROR: Cannot acquire the required rights identifier ''IDENT_REQD'"
$ exit sts
$!
$!
$!
$SKIP_RIGHTS_ID:
$!
$! Pre-delete any temporary files before processing begins
$!
$!
$ TMP = F$SEARCH("TMP_DBFILE_''pid'.TMP;*")
$ IF TMP .NES. "" THEN DELETE/NOLOG/NOCONFIRM TMP_DBFILE_'pid'.TMP;*
$ TMP = F$SEARCH("TMP_SQLDBA_INIT_''pid'.SQL;*")
$ IF TMP .NES. "" THEN DELETE/NOLOG/NOCONFIRM TMP_SQLDBA_INIT_'pid'.SQL;*
$!
$!
$! Create a SQLDBA_INIT_'pid'.SQL file which
$! automatically does a CONNECT INTERNAL.
$!
$ CREATE TMP_SQLDBA_INIT_'pid'.SQL
CONNECT INTERNAL
SET ECHO OFF
$!
$ TMP = F$SEARCH("TMP_SQLDBA_INIT_''pid'.SQL")
$ OLD_SQLDBA_INIT = F$TRNLNM("ORA_SQLDBA_INIT","LNM$PROCESS_TABLE")
$ IF OLD_SQLDBA_INIT .NES. "" THEN DEASSIGN ORA_SQLDBA_INIT
$ ECHO "Initiating shutdown -- please wait..."
$ DEFINE/JOB/NOLOG ORA_SQLDBA_INIT 'TMP'
$!
$!
$! Create files which contain the number of open redolog threads (instances)
$! and the process id's of any connected sessions.
$!
$ DEFINE/USER SPOOL1 'f$environment("default")'TMP_DBFILE_'pid'.TMP
$ DEFINE/USER SPOOL2 'f$environment("default")'TMP_PROCS_'pid'.TMP
$ DEFINE/USER SYS$OUTPUT NLA0:
$ SQLDBA MODE=LINE
SPOOL SPOOL1
SELECT COUNT(*) THREADS FROM V$THREAD WHERE STATUS = 'OPEN';
SPOOL OFF
SPOOL SPOOL2
SELECT SPID FROM V$PROCESS WHERE ADDR IN
(SELECT PADDR
FROM V$SESSION
WHERE TYPE = 'USER'
AND AUDSID != USERENV('SESSIONID'))
/
SPOOL OFF
$!
$!
$! Pick up the number of open redo log threads (and therefore OPS instances)
$!
$ OPEN/READ/ERR=TMP_DBFILE_ERROR TMP TMP_DBFILE_'pid'.TMP
$ READ/END=TMP_DBFILE_ERROR/ERR=TMP_DBFILE_ERROR TMP LINE
$ READ/END=TMP_DBFILE_ERROR/ERR=TMP_DBFILE_ERROR TMP LINE
$ READ/END=TMP_DBFILE_ERROR/ERR=TMP_DBFILE_ERROR TMP LINE
$ READ/END=TMP_DBFILE_ERROR/ERR=TMP_DBFILE_ERROR TMP LINE
$ LINE_C = F$EDIT(LINE,"COLLAPSE")
$ IF F$TYPE(LINE_C) .NES. "INTEGER" THEN GOTO TMP_DBFILE_THREAD_ERROR
$ THREADS = 'LINE_C'
$ IF THREADS .GT. 1
$ THEN
$ OT$$ = THREADS - 1
$ MSG = "instance has"
$ IF OT$$ .GT. 1 THEN MSG = "instances have"
$ ECHO ""
$ ECHO "******************************************************"
$ ECHO "WARNING: ''OT$$' other ''MSG' this database open."
$ ECHO " *ALL* instances must be cleanly shutdown to"
$ ECHO " ensure a safe offline backup of the database."
$ ECHO "******************************************************"
$ ECHO ""
$ WAIT 00:00:02
$ ENDIF
$!
$!
$ CLOSE TMP
$!
$! Construct a list of processes which are still connected to this instance
$!
$ OPEN/READ/ERR=TMP_PROCS_ERROR TMP TMP_PROCS_'pid'.TMP
$ PCT = 0
$!
$PROC_LOOP:
$ READ/END=PROC_LOOP_END/ERR=TMP_PROCS_ERROR TMP LINE
$ PROC = F$EDIT(LINE,"COLLAPSE")
$ IF PROC .EQS. "" THEN GOTO PROC_LOOP
$ IF F$EXTRACT(0,1,PROC) .EQS. "-" THEN GOTO PROC_LOOP
$ XPID = "%X''PROC'"
$ IF F$TYPE(XPID) .NES. "INTEGER" THEN GOTO PROC_LOOP
$ PCT = PCT + 1
$ IF PCT .EQ. 1
$ THEN
$ ECHO "The following processes are still connected to this instance..."
$ ECHO ""
$ ENDIF
$ PID_'PCT' = PROC
$ ECHO "Process #''PCT'
$ ECHO "VMS Process id: ''PROC'"
$ ECHO "VMS username: ''f$getjpi(proc,"USERNAME")'"
$ ECHO "Logged in to VMS: ''f$getjpi(proc,"LOGINTIM")'"
$ ECHO "Current image: ''f$getjpi(proc,"IMAGNAME")'"
$ ECHO ""
$ GOTO PROC_LOOP
$!
$PROC_LOOP_END:
$ CLOSE TMP
$ DELETE/NOLOG/NOCONFIRM TMP_PROCS_'pid'.TMP;*
$ GOTO DONE_PROCS
$!
$!
$TMP_PROCS_ERROR:
$ STS = '$STATUS'
$ IF F$TRNLNM("TMP") .NES. "" THEN CLOSE TMP
$ ECHO ""
$ ECHO "ERROR: Unable to process TMP_PROCS_'pid'.TMP"
$ ECHO F$MESSAGE(STS)
$ ECHO ""
$ ECHO ""
$ ECHO "... connected processes will not be stopped."
$ ECHO ""
$ ECHO ""
$ ECHO ""
$!
$!
$!
$!
$DONE_PROCS:
$!
$! Establish whether the database is open.
$!
$ DELETE/NOLOG/NOCONFIRM TMP_DBFILE_'pid'.TMP;*
$ GOSUB LOOK_FOR_SMON
$ IF SMON_UP THEN GOTO INITIATE_SHUTDOWN
$ ECHO ""
$ ECHO "INFORMATION: The database appears to be down now."
$ ECHO ""
$ GOSUB TIDY
$ EXIT 1
$!
$!
$!
$!
$INITIATE_SHUTDOWN:
$!
$! Construct a command procedure which initiates a SHUTDOWN IMMEDIATE
$!
$!
$ OPEN/WRITE COM SHUTDOWN_IMMEDIATE_'pid'.COM
$ WRITE COM "$ DEFINE/JOB/NOLOG SHUTDOWN_PROC ''''F$GETJPI("""",""PID"")"
$ WRITE COM "$ DEFINE/USER SYS$OUTPUT NLA0:"
$ WRITE COM "$ SQLDBA MODE=LINE"
$ WRITE COM "SHUTDOWN IMMEDIATE"
$ WRITE COM "$ STS = ''''$STATUS"
$ WRITE COM "$ IF STS THEN DEFINE/JOB/NOLOG SHUTDOWN TRUE"
$ WRITE COM "$ IF .NOT. STS THEN DEFINE/JOB/NOLOG SHUTDOWN FAILED"
$ WRITE COM "$ EXIT
$ CLOSE COM
$ DEFINE/JOB/NOLOG SHUTDOWN FALSE ! Initially set this job logical to FALSE
$!
$!
$! Spawn a subprocess which runs the above command file
$!
$ COUNT = 0
$ ECHO "Attempting to SHUTDOWN IMMEDIATE..."
$ SET MESSAGE/NOID/NOTEXT/NOFAC/NOSEV
$ SPAWN/NOWAIT @SHUTDOWN_IMMEDIATE_'pid'
$ SET MESSAGE/ID/TEXT/FAC/SEV
$!
$!
$!
$WAIT_FOR_SHUTDOWN:
$!
$! Wait for the 'SHUTDOWN IMMEDIATE' subprocess to complete.
$! This is signalled when the value of the SHUTDOWN logical
$! changes from FALSE to TRUE or FAILED.
$!
$ IF COUNT .GE. 5 THEN GOTO HAVE_TO_ABORT
$ WAIT 00:00:10
$ IF COUNT .GT. 2 THEN -
ECHO "Still waiting for SHUTDOWN IMMEDIATE to complete..."
$ COUNT = COUNT + 1
$ SL = F$TRNLNM("SHUTDOWN","LNM$JOB")
$ IF SL .EQS. "" THEN GOTO BAD_SL
$ IF SL .EQS. "FAILED" THEN GOTO SHUTDOWN_IMMEDIATE_FAILED
$ IF .NOT. SL THEN GOTO WAIT_FOR_SHUTDOWN
$ GOSUB LOOK_FOR_SMON
$ IF SMON_UP THEN GOTO INITIATE_SHUTDOWN
$ ECHO ""
$ ECHO "Instance ""''sid'"" has been successfully shut down"
$ GOSUB STOP_PROCS
$ GOSUB TIDY
$ IF THREADS .GT. 1
$ THEN
$ ECHO ""
$ ECHO "******************************************************"
$ ECHO "WARNING: ''OT$$' other ''MSG' this database open"
$ ECHO " *ALL* instances must be cleanly shutdown to"
$ ECHO " ensure a safe offline backup of the database."
$ ECHO "******************************************************"
$ ECHO ""
$ WAIT 00:00:02
$ ENDIF
$ EXIT
$!
$!
$!
$SHUTDOWN_IMMEDIATE_FAILED:
$!
$! We should only arrive here if the SQLDBA session which issues the
$! SHUTDOWN ABORT exits back to DCL with a failure status.
$!
$ ECHO ""
$ ECHO "ERROR: SHUTDOWN IMMEDIATE has FAILED!"
$ GOSUB TIDY
$ EXIT 44
$!
$!
$!
$BAD_SL:
$!
$! We should never arrive here. If we do - I want to know about it!
$!
$ ECHO ""
$ ECHO "PROGRAM ERROR: Symbol SL has a null value"
$ ECHO ""
$ DELETE/NOLOG/NOCONFIRM SHUTDOWN_IMMEDIATE_'pid'.COM;*
$ GOSUB TIDY
$ EXIT 44
$!
$!
$!
$HAVE_TO_ABORT:
$!
$! If we arrive here, SHUTDOWN IMMEDIATE has not completed within the
$! the timeframe we have specified. So we have to SHUTDOWN ABORT
$!
$ xpid = f$trnlnm("SHUTDOWN_PROC","LNM$JOB") ! pid of SHUTDOWN IMMEDIATE
$! ECHO "XPID = ""''XPID'"""
$ IF XPID .NES. "" THEN STOP/ID='XPID' ! Stop the SHUTDOWN IMMEDIATE
$ DELETE/NOLOG/NOCONFIRM SHUTDOWN_IMMEDIATE_'pid'.COM;*
$ ECHO ""
$ ECHO ""
$ ECHO "WARNING: Database cannot be SHUTDOWN IMMEDIATE - aborting..."
$ ECHO ""
$ DEFINE/USER SYS$OUTPUT NLA0:
$ SQLDBA MODE=LINE ! Have to SHUTDOWN ABORT
SHUTDOWN ABORT
EXIT
$ STS = '$STATUS' ! Pick up SQLDBA status
$ IF .NOT. STS THEN GOTO ABORT_FAILED ! If that fails there's trouble!
$ GOSUB LOOK_FOR_SMON ! Can we find the SMON process ?
$ IF .NOT. SMON_UP THEN GOTO ABORT_SUCCEEDED ! If we can't the DB is now down
$!
$!
$!
$ABORT_FAILED:
$!
$! Arrive here if SQLDBA (SHUTDOWN ABORT) falls over
$!
$ ECHO "ERROR: SHUTDOWN ABORT has FAILED!"
$ DELETE/NOLOG/NOCONFIRM SHUTDOWN_IMMEDIATE_'pid'.COM;*
$ GOSUB TIDY
$ EXIT 44
$!
$!
$!
$ABORT_SUCCEEDED:
$!
$! SHUTDOWN ABORT has succeeded.
$! We now have to restart the database (RESTRICT) and issue
$! a SHUTDOWN NORMAL to ensure safety of the cold backup.
$!
$ ECHO ""
$ ECHO ""
$ ECHO "Shutdown abort completed successfully"
$ ECHO "Now restarting for a SHUTDOWN NORMAL..."
$ ECHO ""
$ IF THREADS .GT. 1 THEN GOTO STARTUP_PARALLEL
$ DEFINE/USER SYS$OUTPUT NLA0:
$ SQLDBA MODE=LINE
STARTUP RESTRICT
EXIT
$ STS = '$STATUS'
$ GOTO AFTER_RESTART
$!
$!
$!
$STARTUP_PARALLEL:
$ DEFINE/USER SYS$OUTPUT NLA0:
$ SQLDBA MODE=LINE
STARTUP RESTRICT PARALLEL
EXIT
$ STS = '$STATUS'
$!
$!
$!
$AFTER_RESTART:
$ IF .NOT. STS THEN GOTO STARTUP_FAILED
$ GOSUB LOOK_FOR_SMON
$ IF SMON_UP THEN GOTO SHUTDOWN_NORMAL
$!
$!
$!
$STARTUP_FAILED:
$!
$! This could be a problem. Database is NOT safe to back up.
$!
$ ECHO "ERROR: Failed to restart database after SHUTDOWN ABORT"
$ GOSUB TIDY
$ EXIT 44
$!
$!
$!
$SHUTDOWN_NORMAL:
$!
$! We have successfully restarted the database after the SHUTDOWN ABORT.
$! Now we have to SHUTDOWN NORMAL in order to take a clean backup.
$!
$ ECHO ""
$ ECHO ""
$ ECHO "Now issuing a SHUTDOWN NORMAL..."
$ ECHO ""
$ DEFINE/USER SYS$OUTPUT NLA0:
$ SQLDBA MODE=LINE
SHUTDOWN NORMAL
EXIT
$ STS = '$STATUS'
$ IF .NOT. STS THEN GOTO SHUTDOWN_NORMAL_FAILED
$ GOSUB LOOK_FOR_SMON
$ IF .NOT. SMON_UP THEN GOTO DONE
$!
$!
$!
$SHUTDOWN_NORMAL_FAILED:
$!
$! This could be a problem. Database is NOT safe to back up.
$!
$ ECHO ""
$ ECHO "ERROR: Failed to shutdown instance ''sid' after STARTUP RESTRICT"
$ GOSUB TIDY
$ EXIT 44
$!
$!
$!
$DONE:
$!
$! That's it - we've successfully shutdown (normal) this instance
$!
$ ECHO ""
$ ECHO "Instance ''sid' successfully shutdown (normal)"
$ GOSUB STOP_PROCS
$ GOSUB TIDY
$ IF THREADS .GT. 1
$ THEN
$ ECHO ""
$ ECHO "******************************************************"
$ ECHO "WARNING: ''OT$$' other ''MSG' this database open."
$ ECHO " *ALL* instances must be cleanly shutdown to"
$ ECHO " ensure a safe offline backup of the database."
$ ECHO "******************************************************"
$ ECHO ""
$ ENDIF
$ EXIT
$!
$!
$!
$TMP_DBFILE_ERROR:
$!
$! Handle tmp file errors...
$!
$ STS = '$STATUS'
$ ECHO ""
$ ECHO "ERROR: Cannot read file TMP_DBFILE_''pid'.TMP"
$ GOSUB TIDY
$ EXIT STS
$!
$TMP_DBFILE_THREAD_ERROR:
$ DELETE/NOLOG/NOCONFIRM TMP_DBFILE_'pid'.TMP;*
$ ECHO ""
$ ECHO "PROGRAM ERROR: Expected a number of threads. This is what I received:"
$ ECHO LINE
$ GOSUB TIDY
$ EXIT 40
$!
$!
$!
$!
$STOP_PROCS:
$ X = 0
$ IF PCT .GT. 0
$ THEN
$ ECHO "Any processes which are still connected will be stopped..."
$ ECHO ""
$ WAIT 00:00:05
$ ENDIF
$!
$STOP_LOOP:
$ X = X + 1
$ IF X .GT. PCT THEN GOTO STOP_PROCS_DONE
$ PROC = PID_'X'
$ IMG = F$EDIT(F$GETJPI(PROC,"IMAGNAME"),"COLLAPSE")
$ IF IMG .NES. ""
$ THEN
$ ECHO ""
$ ECHO "Stopping process ''PROC' ..."
$ STOP/ID='PROC'
$ ECHO ""
$ ENDIF
$ GOTO STOP_LOOP
$!
$STOP_PROCS_DONE:
$ RETURN
$!
$!
$!
$TIDY:
$ TMP = F$SEARCH("TMP_DBFILE_''pid'.TMP;*")
$ IF TMP .NES. "" THEN DELETE/NOLOG/NOCONFIRM TMP_DBFILE_'pid'.TMP;*
$ TMP = F$SEARCH("TMP_SQLDBA_INIT_''pid'.SQL;*")
$ IF TMP .NES. "" THEN DELETE/NOLOG/NOCONFIRM TMP_SQLDBA_INIT_'pid'.SQL;*
$ IF "''OLD_SQLDBA_INIT'" .NES. "" THEN -
DEFINE/NOLOG ORA_SQLDBA_INIT 'OLD_SQLDBA_INIT'
$ IF F$TRNLNM("ORA_SQLDBA_INIT","LNM$JOB") .NES. "" THEN -
DEASSIGN/JOB ORA_SQLDBA_INIT
$!
$ RETURN
$!
$!
$!
$!
$LOOK_FOR_SMON:
$ SMON_UP = "FALSE"
$ SHOW SYSTEM/OUT=SHUTDOWN_SHOW_SYS_'pid'.TMP
$ DEFINE/USER SYS$OUTPUT NLA0:
$ DEFINE/USER SYS$ERROR NLA0:
$ SEARCH SHUTDOWN_SHOW_SYS_'pid'.TMP "ORA_''SID'_SMON"
$ STS = '$STATUS
$ DELETE/NOLOG/NOCONFIRM SHUTDOWN_SHOW_SYS_'pid'.TMP;*
$ IF STS .EQ. 1 THEN SMON_UP = "TRUE"
$ RETURN