bash and sqlplus by Example: Kill Long Running Jobs

===============================
General
===============================
Known jobs should be running for only few minutes.
However, since they are connected via DB_LINKS to several remote databases, due to networks issues connection can be stuck, and job is hanged.
The workaround - it to kill this job, and start execution again.

Following code is executed from crontab every 15 minutes, and checks for jobs which run longer than 30 minutes.

===============================
Code
===============================

less kill_long_running_jobs.sh

#!/bin/bash

. /etc/profile

. /etc/sh/orash/oracle_login.sh igt

handle_return_code() {

  ret_code=$1

  step_name=$2

  if [[ $ret_code -ne 0 ]];then

    echo $step_name Finished With Error!! Return Code $ret_code

    exit $ret_code

  fi

}

WORK_DIR=/software/oracle/oracle/scripts

LOG_DIR=${WORK_DIR}

RUN_TIME=`date +"%Y%m%d_%H%M%S"`

LOG_FILE=killed_sessions.log

TEMP_FILE=long_running_sessions.tmp

cd ${WORK_DIR}

touch ${LOG_DIR}/${LOG_FILE}

rm -f ${TEMP_FILE}

sqlplus -s /nolog <<EOF

whenever sqlerror exit SQL.SQLCODE

@./kill_long_running_jobs.sql

EOF

handle_return_code $? ./kill_long_running_jobs.sql

if [[ ! -f ${TEMP_FILE} || ! -s ${TEMP_FILE} ]]; then

  echo "$RUN_TIME   No Long Running Sessions Found" >> ${LOG_DIR}/${LOG_FILE}

  exit 0

fi

echo reading file ${TEMP_FILE}

while read line

do

  KILL_CMD=`echo $line | awk 'BEGIN {FS="~"}{print $1}'`

  WHAT=`echo $line | awk 'BEGIN {FS="~"}{print $2}'`

  echo "$RUN_TIME   ${KILL_CMD} ${WHAT}" >> ${LOG_DIR}/${LOG_FILE}

  ${KILL_CMD}

done < ${TEMP_FILE}

less kill_long_running_jobs.sql

@./set_user.sql

connect &&user/&&pass@&&conn_str

PROMPT CONNECTED TO &&user/&&pass@&&conn_str

SET HEADING OFF

SET PAGESIZE 0

SET LINESIZE 200

SET NEWPAGE NONE

SET FEEDBACK OFF

SET VERIFY OFF

SPOOL long_running_sessions.tmp

SELECT 'kill -9 '||V_PROCESS.spid||'~'||DBA_JOBS.what AS LINUX_KILL

FROM   V$SESSION V_SESSION,

       V$PROCESS V_PROCESS,

       DBA_JOBS_RUNNING STUCK_JOBS,

       DBA_JOBS

WHERE  V_PROCESS.addr = V_SESSION.paddr

  AND  V_SESSION.type != 'BACKGROUND'

  AND  V_SESSION.sid = STUCK_JOBS.sid

  AND  DBA_JOBS.job = STUCK_JOBS.job

  AND  logon_time < (SYSDATE - 30/1440)

  AND  UPPER(DBA_JOBS.what) IN ('EXT_MOCO.SCHEDULE;','EXT_OVMD.SCHEDULE;','EXT_IPN.SCHEDULE;','EXT_GLR.SCHEDULE;','EXT_SPARX.SCH

EDULE;')

UNION ALL

SELECT 'kill -9 4545~EXT_SPARX.SCHEDULE;' AS LINUX_KILL

FROM DUAL

WHERE 1=2;

SPOOL OFF

less set_user.sql

DEFINE user=user_name

DEFINE pass=user_pass

DEFINE conn_str=orainst

Code Example. PL/SQL Kill and re-create Stuck Jobs

=====================================
General
=====================================
For a job connecting to a remote DB via a db_link, sometimes due to network issues, the job is in "stuck" mode
as a workaround, there is a job running every 2 hours, and killing jobs that run over 1 hour.


=====================================
PL/SQL CODE

=====================================

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

-----------------------------------------------
-- Known Exceptions
-----------------------------------------------
  EXP_ORA_SESS_MARK_FOR_KILL            EXCEPTION;
  PRAGMA EXCEPTION_INIT(EXP_ORA_SESS_MARK_FOR_KILL,-31);
  
  -------------------------------------------------
  PROCEDURE write_sga_w_log(p_procedure_name IN VARCHAR2,
                            p_data           IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO SGA_W_LOG (module_name, msg_text, msg_date)
    VALUES (p_procedure_name, p_data, SYSDATE);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       NULL;
  END write_sga_w_log;
  
  
  -------------------------------------------------
  PROCEDURE KILL_LONG_RUNNING_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sql_cmd          VARCHAR2(1000);
    
    CURSOR long_running_jobs_cur IS
    SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||V$SESSION.sid||','||V$SESSION.serial#||'''', 1,15)||' IMMEDIATE' AS kill_cmd,
           V$SESSION.sid, 
           V$SESSION.serial#,
           DBA_JOBS.what
      FROM DBA_JOBS ,
           DBA_JOBS_RUNNING,
           V$SESSION
     WHERE DBA_JOBS.this_date < SYSDATE - 1/24
       AND DBA_JOBS_RUNNING.job = DBA_JOBS.job
       AND DBA_JOBS_RUNNING.sid = V$SESSION.sid
       AND V$SESSION.status <> 'KILLED';       
           
  BEGIN  
    v_module_name := 'KILL_LONG_RUNNING_JOB';
    
    FOR long_running_jobs_rec IN long_running_jobs_cur LOOP
      BEGIN
        v_sql_cmd := long_running_jobs_rec.kill_cmd;
        v_msg_text := 'Killing Stuck Job :'||long_running_jobs_rec.what;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        v_msg_text := 'Runing: '||v_sql_cmd;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);
        
        EXECUTE IMMEDIATE v_sql_cmd;
    
        
      EXCEPTION
        WHEN EXP_ORA_SESS_MARK_FOR_KILL THEN
          v_msg_text := 'Done! Session Was Marked For Kill';
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);          
        
        WHEN OTHERS THEN
          v_msg_text := 'Unexpected Error: '||SQLERRM;
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
      END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END KILL_LONG_RUNNING_JOB;    

  -------------------------------------------------
  PROCEDURE RECREATE_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sparx_what       VARCHAR2(1000);
    CURSOR get_job_list_cur(cp_job_what IN VARCHAR2) IS
    SELECT *
      FROM DBA_JOBS 
     WHERE UPPER(WHAT) = cp_job_what;
           
  BEGIN  
    v_module_name := 'RECREATE_JOB';
    v_sparx_what := UPPER('EXT_SPARX.schedule;');
        
    
    FOR get_job_list_rec IN get_job_list_cur(v_sparx_what) LOOP

        v_msg_text := 'Deleting Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.remove(get_job_list_rec.job);
          COMMIT;
        END;
        
        v_msg_text := 'Creating Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.ISUBMIT
          ( job       => get_job_list_rec.job 
           ,what      => get_job_list_rec.what
           ,next_date => get_job_list_rec.next_date
           ,interval  => get_job_list_rec.interval
          );
          COMMIT;
        END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END RECREATE_JOB;    
  -------------------------------------------------
END;

=====================================
Job

=====================================

DECLARE

   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_UTIL.KILL_LONG_RUNNING_JOB;', 
                  NEXT_DATE => TRUNC(SYSDATE,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/1)+121)*1)/(1440), 
                  INTERVAL => 'TRUNC(SYSDATE,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,''MI''))/1)+121)*1)/(1440)'
 );
 COMMIT;
END;
/