Transcript Automating JobSubmission Shutdown and Startup

Automating JobSubmission
Shutdown and Startup
April Sims
OCP 8i 9i DBA
Southern Utah University
Topics
Unix account for JOBSUB
Unix Environment Variables
File Permissions
Modifying End User File Locations
Writing Scripts
Submitting Cron Entries
Integrating with DB backups/shutdowns
Maintaining JOBSUB Output
Unix account for JOBSUB
Separate unix account needed to
keep these files separate from SCT
BANNER code.
May have to allow a few endusers the
ability to access their jobsubmission
output. Consider using SAMBA.
Financial Aid has uploads/downloads
to different agencies.
Unix Environmental Variables
Remove all ORACLE environmental
variables from profile calling them inside
each script.
This allows you to complete upgrades,
change databases, etc. without having to
modify each and every script.
Use a SID.ini file and “source” it inside the
script.
Also good to keep from hardcoding
passwords is to start a password file as
well.
Mods to oraenv
# Install any "custom" code here
#
if [ "$ORACLE_SID" = “PPRD6" ] ; then
. /u01/app/sct/banner01/admin/banenv
. /u01/app/oracle/PPRD6.ini
fi
if [ "$ORACLE_SID" = "PPRD" ] ; then
. /u01/app/sct/banner03/admin/banenv
. /u01/app/oracle/PPRD.ini
fi
PPRD6.ini
ORACLE_HOME=/u01/app/oracle/product/oracle9i;
export ORACLE_HOME
SUU_EXE_HOME=/u01/app/suu/banner02/general/e
xe; export SUU_EXE_HOME
SUU_BANNER_LINKS=/u01/app/suu/banner02/links
; export SUU_BANNER_LINKS
COBMODE=32
PATH=$SUU_EXE_HOME:$SUU_BANNER_LINKS:/
u01/app/oracle/product/oracle9i/bin:$PATH
ORACLE_SID=PPRD6
export ORACLE_SID PATH COBMODE
Password file
.jobsub.pprd6 (named so that the leading .
hides it from ls commands)
general/password
fimsmgr/password
export PASS=`grep general
$HOME/.jobsub.pprd6 | cut -f 2`
sqlplus $PASS
@$BANNER_LINKS/gurstop.sql
File Permissions
JOBSUB directory and below is
owned by JOBSUB and group
permissions is nobody…..
To grant endusers an UNIX logon
account then permissions can be
changed to limit access. Requires at
least read permission on the directory
above.
Demo
Modifying End User File Location
Some job submission output is not
uniquely identified.
So..one person’s output overwrites
someone else’s…SCT is rewriting
code to prevent this in the future.
SQL> insert guruprf
Writing Scripts
#!/bin/ksh
# SUU_BANNER_LINKS contains any modified banner code and is
first in the path to pick the mods up instead of the vanilla code.
# gurstart_prod.shl
export ORAENV_ASK=NO
export ORACLE_SID=PPRD6
. /usr/local/bin/oraenv
LOGFILE=$HOME; export LOGFILE
HOME=$HOME/$ORACLE_SID; export HOME
cd $HOME
sh $SUU_BANNER_LINKS/gurjobs_pprd6.shl $ORACLE_SID
GURJOBS > $HOME/gurjobs_start_$ORACLE_SID.log 2>&1 &
HOME=$LOGFILE; export HOME
Submitting Cron Entries
JOBSUB unix account has to be
enabled to allow CRON
To add crontab entries as banjobs
crontab –e
Crontab cont’d
# Fields are:
# MM HH DD MM WW /cat/file/string/to/execute
#
minute (0-59),
#
hour (0-23),
#
day of the month (1-31),
#
month of the year (1-12),
#
day of the week (0-6 with 0=Sunday).
#!/bin/ksh
3 15 * * 0-6 /u03/banjobs/clean_all.sh >
/u03/banjobs/clean_all.log 2>&1
15,30,45,0 7-18 * * 0-6
/u03/banjobs/scripts/gurstart_PPRD6.ksh >gurjobs.log 2>&1
Integrating with DB backups or
other tasks
#!/usr/bin/ksh
#nightly.ksh RUN as ROOT , no passwords needed.
# Written by April Sims, DBA at SUU
# This stops/starts all BANNER processes nightly for backups.
# First I export the databases while they are still up.
/bin/su - oracle -c "/u01/app/oracle/scripts/export.sh" <<EOF
EOF
# this stops all the jobsub processes, I run 2 different PROD ones so that in case one of them hangs.
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstop_prod.shl" <<EOF
EOF
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstop_prod2.shl" <<EOF
EOF
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstop_pprd.shl" <<EOF
EOF
# this stops sleep/wake processing
/bin/rm /u03/banjobs/sleepwake/PROD/PROD.fgractg
/bin/rm /u03/banjobs/sleepwake/PROD/PROD.forappl
/bin/sleep 180
# This is a cold backup of certain databases
/bin/su - oracle -c "/u01/app/oracle/scripts/cold.sh" <<EOF
EOF
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstart_prod.shl" <<EOF
EOF
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstart_prod2.shl" <<EOF
EOF
/bin/su - banjobs -c "/u03/banjobs/scripts/gurstart_pprd.shl" <<EOF
EOF
# Starts sleep/wake as a background process
/bin/su - banjobs -c "/u03/banjobs/sleepwake/PROD/start_sleep_wake.shl &" <<EOF
EOF
exit
Maintaining JOBSUB Output
Jobsub collector tables needed to be
cleaned out on a regular
basis….recommendation 2 weeks
Script that cleans out the collector table
and corresponding *.lis and *.log files.
(you will also see *.DAT, *.rpf, *.clg,
*.prt, *.out, *.err, *.srt, *.ext, *.htm)
Thanks to Dave Iler
#!/usr/bin/bash –x
cd /u03/banjobs/PPRD
HOME=/u03/banjobs; export HOME
ORACLE_SID=PPRD6
export ORACLE_SID
ORAENV_ASK=NO ; export ORAENV_ASK
PATH=/usr/local/bin:$PATH; export PATH
. /usr/local/bin/oraenv
export PASS=`grep general $HOME/.banjobs.pprd6 | cut -f 2`
$ORACLE_HOME/bin/sqlplus $PASS @clean_PPRD6.sql
<<EOF
EOF
exit
Clean_PPRD6.sql
set pagesize 0
set feed off
set term off
spool /u03/banjobs/PPRD6/rmfiles_PPRD6.bash
select '#!/usr/bin/bash -x' from dual;
select 'oldir=`pwd`' from dual;
select 'cd /u03/banjobs/PPRD6' from dual;
select 'rm ' || guboutp_file_name
from guboutp
where sysdate-guboutp_date_saved > 30;
select 'cd $oldir' from dual;
spool off
set feed on
set term on
delete from guroutp
where (guroutp_user_id, guroutp_one_up_no, guroutp_file_number) in
(select guboutp_user_id, guboutp_one_up_no, guboutp_file_number
from guboutp
where sysdate - guboutp_date_saved > 30);
delete from guboutp
where sysdate - guboutp_date_saved > 30;
host chmod 500 /u03/banjobs/PPRD6/rmfiles_PPRD6.bash
host /u03/banjobs/PPRD6/rmfiles_PPRD6.bash
/
exit
Result from 1st script
#!/usr/bin/bash -x
oldir=`pwd`
cd /u03/banjobs/PPRD6
rm gurpded_5750.lis
rm gurpded_5750.log
rm gurpded_5751.lis
rm gurpded_5752.lis
rm gurpded_5752.log
rm gurpded_5751.log
rm fgrfndh_5761.lis
rm gurpded_5753.log
rm fgrfndh_5760.lis
rm fgrfndh_5760.log
rm fgrfndh_5761.log
rm farvalp_5765.lis
rm farvalp_5765.log
cd $oldir