Once a day, at 12:15 execute a script that would identify and kill Oracle Job processes, that are running longer than 24 hours.
This script is launched on Oracle server, as oracle user.
steps
A. crontab task - that runs ksh script once a day, at 12:15.
B. ksh script that launches perl script
C. perl script that connects to DB to find zombie Linux processes that were launched by Oracle job, and then kills these tasks.
crontab -l
15 12 * * * /backup/ora_exp/kill_zombie_jobs.sh
kill_long_zombie_jobs.sh
#!/bin/bash
. /etc/profile
. /etc/sh/orash/oracle_login.sh igt
perl /backup/ora_exp/kill_zombie_jobs.pl
kill_long_zombie_jobs.pl
#! /usr/bin/perl
use DBI;
use Time::gmtime;
use File::Copy;
####################################################################
##### Subroutine to get the current date #########################
####################################################################
sub getDate
{
use Time::gmtime;
$tm=gmtime;
($second,$minute,$hour,$day,$month,$year) = (gmtime) [0,1,2,3,4,5];
my $locDate=sprintf("%04d%02d%02d_%02d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec );
return $locDate;
}
########################
# main start here ######
########################
my $RetCode;
my $myDate=getDate;
my $logLocation="/backup/ora_exp/Logs/kill_zombie_jobs/";
my $logFile=$logLocation."kill_zombie_log_".$myDate.".log";
my $sql;
my $db="cgw_new";
my $db_driver="dbi:Oracle:".$db;
my $dbh=DBI->connect($db_driver,'collector','cgw154igt',{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";
print $DBI::errstr;
print "Log file:".$logFile."\n";
my $sql="SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL, ";
$sql=$sql."PROCESSES.spid, PROCESSES.program, ";
$sql=$sql."SESSIONS.username, SESSIONS.sql_id, SESSIONS.logon_time, SESSIONS.event ";
$sql=$sql."FROM v\$process PROCESSES, V\$SESSION SESSIONS ";
$sql=$sql."WHERE PROCESSES.program like '%J0%' ";
$sql=$sql." AND PROCESSES.addr=SESSIONS.paddr ";
$sql=$sql." AND SESSIONS.schemaname <> 'SYS' ";
$sql=$sql." AND SESSIONS.type = 'USER' ";
$sql=$sql." AND PROCESSES.background IS NULL ";
$sql=$sql." AND SESSIONS.sid NOT IN (SELECT sid FROM DBA_JOBS_RUNNING) ";
$sql=$sql." AND logon_time < SYSDATE-1/4";
$sql=$sql." AND logon_time < SYSDATE-1";
open (MyLog,">>".$logFile);
print MyLog "----I---- Startting kill zombie process on ".$myDate."\n";
print MyLog "Running SQL: "."\n".$sql."\n";
print MyLog "\n\n";
print MyLog "These are the details of the job that were killed : \n";
print MyLog ".........................."."\n";
my $FNsth=$dbh->prepare($sql);
$FNsth->execute();
my @row;
my $kill_cmd;
while (@row=$FNsth->fetchrow())
{
open (MyLog,">>".$logFile);
print MyLog "V$SEESION.sid: ".$row[1]." \n";
print MyLog "PROCESSES.program: ".$row[2]." \n";
print MyLog "SESSIONS.username: ".$row[3]." \n";
print MyLog "SESSIONS.sql_id: ".$row[4]." \n";
print MyLog "SESSIONS.logon_time: ".$row[5]." \n";
print MyLog "SESSIONS.event: ".$row[6]." \n";
print MyLog ".........................."."\n";
$kill_cmd = $row[0];
print MyLog "Running command ".$kill_cmd."..........";
system($kill_cmd);
print MyLog "Done"."\n";
}
$FNsth->finish();
$dbh->disconnect;
print MyLog "\n=========================\n";
print MyLog "Script Finished Successfuly";
print MyLog "\n=========================\n";
close MyLog;
SQL to identify zombie jobs
=================================
SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL,
PROCESSES.spid,
PROCESSES.program,
SESSIONS.username,
SESSIONS.sql_id,
SESSIONS.logon_time,
SESSIONS.event
FROM V$PROCESS PROCESSES,
V$SESSION SESSIONS,
DBA_JOBS_RUNNING RUNNING_JOBS
WHERE PROCESSES.program like '%J0%'
AND PROCESSES.addr=SESSIONS.paddr
AND SESSIONS.sid = RUNNING_JOBS.sid(+)
AND SESSIONS.schemaname <> 'SYS'
AND SESSIONS.type = 'USER'
AND RUNNING_JOBS.job IS NULL
-- AND SESSIONS.logon_time < SYSDATE-1;
=======================================
profile files, not really important for this example...
=======================================
. /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
pathmunge () {
if ! echo $PATH | /bin/egrep -q "(^|:)$1($|:)" ; then
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
fi
}
# Path manipulation
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
pathmunge /usr/local/bin
pathmunge /usr/X11R6/bin after
# No core files by default
ulimit -S -c 0 > /dev/null 2>&1
USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
HOSTNAME=`/bin/hostname`
HISTSIZE=1000
if [ -z "$INPUTRC" -a ! -f "$HOME/.inputrc" ]; then
INPUTRC=/etc/inputrc
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
. $i
fi
done
unset i
#!/bin/bash
#
# NAME:
# oracle_login.sh
# !!!
#
# ABSTRACT:
# Defining oracle's enironment variables. - Unix version
#
#
# ARGUMENTS:
# [p1] - oracle sid
#
# Note - 1) the parameter can be ommitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
# PROJECT_SID -
# should be defined in the following way:
# globaly - the node run only one oracle instance
# group login - there is more than one instance on the current node
# as a parameter - there is more than one instance per project,
# or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
# 1) PROJECT_SID is not defined
# 2) the database PROJECT_SID does not exist
# 3) PROJECT_SID missing from $ORASH/sid.hosts
# 4) ORA_VER missing from $ORASH/sid.hosts
# 5) charcter missing for current sid from $ORASH/charset.dat
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+-------------------+-----------------------------------------+
# | Date | Name | Description |
# +-------------+-------------------+-----------------------------------------+
# + + + +
# +-------------+-------------------+-----------------------------------------+
if [ "$ORASH" = "" ]; then
ORASH=$SH_ETC/etc/orash
fi
FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1` #=> Linux
if [ "$#" -ge 1 ]; then
PROJECT_SID=$1
else
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
fi
ORACLE_BASE=/software/oracle
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$ORACLE_HOME" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
return 2
fi
ORA_VER=`grep "$PROJECT_SID" $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
if [ "$ORA_VER" = "" ]; then
echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
return 4
fi
DEFAULT_SID=$PROJECT_SID
ORACLE_SID=$DEFAULT_SID
TNS_ADMIN=${ORACLE_HOME}/network/admin
#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$HOST" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
return 3
fi
#-------------------------------------#
# TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
# NODE=`hostname`
# if [ "$NODE" != "$HOST" ]; then
# TWO_TASK=${DEFAULT_SID}_remote
# fi
#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$CHARSET" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
return 5
fi
NLS_DATE_FORMAT="DD-MON-RR"
ORACLE_LPPROG=lpr
ORACLE_LPARGS="-P la424"
NLS_LANG="AMERICAN_AMERICA."${CHARSET}
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8
cleaned_path=$PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
else
PATH=$cleaned_path$ORACLE_HOME/bin
fi
shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
if [ "$shlib_def" -eq 0 ]; then
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
else
cleaned_shlib_path=$LD_LIBRARY_PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
else
LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
fi
fi
export PROJECT_SID
export ORACLE_TERM
export ORACLE_HOME
export ORACLE_BASE
export ORACLE_SID
export TNS_ADMIN
export ORACLE_SERVER
export PATH
export LD_LIBRARY_PATH
export NLS_LANG
export ORA_NLS33
unset ORA_NLS32
ORACLE_ENV_DEFINED="yes"
export ORACLE_ENV_DEFINED
export ORA_VER
No comments:
Post a Comment