Oracle JOB 중지, 비활성화 작업

Oracle 작업으로 인해 JOB을 중지해야 할 경우 JOB을 조회해서 현황을 파악하고, JOB을 중지하거나 비활성화하는 방안에 대해 설명합니다.

 

JOB 조회

-- JOB 조회, dba_jobs 테이블에서 정보 확인
-- Broken=Y는 비활성화 된 JOB
col job for 9999
col schema_user for a11
col what for a40
col failures for 9999
col total_time for 9999
col last_date for a10
col last_sec for a10
col next_date for a10
col next_sec for a10
col  interval for a30
select job, broken, schema_user, --what
failures, total_time, last_date, last_sec, next_date, next_sec, interval
from dba_jobs order by Broken, next_date;

 

활성화된 JOB만 조회

-- broken = N인것만 조회
select job, broken, schema_user, --what
failures, total_time, last_date, last_sec, next_date, next_sec, interval
from dba_jobs where broken = 'N' order by job, next_date;

 

JOB 중지

활성화된 JOB 중지 스크립트 생성

-- JOB 중지 스크립트 생성(broken = N인것만)
SELECT 'EXECUTE dbms_job.broken(' || job || ', TRUE);'
from dba_jobs where broken = 'N' order by job, next_date;

-- 아래와 같은 쿼리가 생성됨

-- JOB 정지(정지스크립트로 생성된 구문으로 변경하여 실행)
BEGIN
EXECUTE dbms_job.broken(925, TRUE);
EXECUTE dbms_job.broken(5, TRUE);
EXECUTE dbms_job.broken(805, TRUE);
EXECUTE dbms_job.broken(725, TRUE);
END;
/

 

활성화된 JOB 다시 활성화 스크립트 미리 생성해 둠

-- JOB 정지 푸는  스크립트 미리 생성(broken =Y로 변경)
SELECT 'EXECUTE dbms_job.broken(' || job || ', FALSE);'
from dba_jobs where broken = 'N' order by job, next_date;

 

JOB Queue 조회 후 0으로 변경, JOB이 실행할 수 없음

show parameter JOB_QUEUE_PROCESSES;
NAME                                 TYPE        VALUE
job_queue_processes                  integer     50

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0

 

JOB 확인

-- 정지되면 Broken 컬럼이 Y로 표시됨
select job, broken, schema_user, --what
failures, total_time, last_date, last_sec, next_date, next_sec, interval
from dba_jobs order by Broken, next_date;

모든 작업 완료 후 JOB원복

-- JOB 중지 해제(미리 생성된 JOB 정지 푸는  스크립트 구문으로)
BEGIN
EXECUTE dbms_job.broken(925, FALSE);
EXECUTE dbms_job.broken(5, FALSE);
EXECUTE dbms_job.broken(805, FALSE);
EXECUTE dbms_job.broken(725, FALSE);
END;
/

JOB 실행

EXECUTE DBMS_JOB.RUN(JOB번호);
commit;

BEGIN
EXECUTE dbms_job.broken(925, TRUE);
END;
/

 

JOB중지, 중지되지 않는 경우

1. KILL_SQL 생성하여 수행

col sid for 99999
col status for a7
col what for a30
col running for a10
col kill_sql for a50
SELECT /*+ RULE */ D.JOB, V.SID, V.SERIAL#, STATUS, LOG_USER USERNAME, WHAT, DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') RUNNING, D.FAILURES, 'alter system kill session ' || '''' || V.SID || ', ' || V.SERIAL# || '''' || ' immediate;' KILL_SQL 
FROM DBA_JOBS_RUNNING D, V$SESSION V, DBA_JOBS J 
WHERE V.SID = D.SID AND D.JOB = J.JOB;

 

JOB SID SERIAL# STATUS USERNAME WHAT RUNNING FAILURES KILL_SQL

88 2271 49077 ACTIVE TOSADM PRO_CODECO_ONECALL(); 00:01:55 0 alter system kill session ‘2271, 49077’ immediate;

 

위와 같이 실행 중인 JOB에 대해 KILL_SQL구문을 생성해 줌, 해당 SQL문을 실행하여 KILL 수행

위의 작업으로 SESSION이 완전히 삭제되지 않고 KILLED Marking이 되면 해당 SESSION이 삭제될 때까지 기다리거나 OS 레벨에서 강제 KILL 하기 위해 SPID를 확인

SELECT A.SPID FROM V$PROCESS A INNER JOIN V$SESSION B ON B.PADDR = A.ADDR WHERE B.SID = 3796;

SPID

19923316

 

-- On-Premise & EC2
alter system kill session '2271, 49077' immediate;

-- AWS RDS
begin
rdsadmin.rdsadmin_util.kill(sid => 2271, serial => 49077, method => 'IMMEDIATE');
end;
/

 

KILL 되지 않으면 OS레벨에서 KILL OS레벨 => !ps -ef | grep ora_j

oracle 10813466 12255550 1 15:35:47 pts/2 0:00 grep ora_j oracle 16973858 1 6 15:34:55 – 0:00 ora_j001_HJITTOS2 oracle 22937890 1 0 15:35:46 – 0:00 ora_j000_HJITTOS2

 

kill -9 [PID]

4) JOB제거 DBMS_JOB.REMOVE(100); COMMIT;