4.7 스케줄 작업

• DBMS_JOB - 오라클의 거의 모든 버전에서 사용 가능
• DBMS_SCHEDULER - 오라클 10G 이상

DBMS_JOB 패키지 수행된 작업 분석시 고려 사항

● DBMS_JOB 패키지에 의해 수행된 SQL 텍스트를 눈으로 관찰 불가.( DBMS_SCHEDULER 도 동일 )

• 10gR1 - SQL 텍스트 정보가 JOB을 수행하는 PL/SQL 코드, 즉. DBMS_JOB.WHAT
• 10gR2 - 아예 SQL 텍스트 정보가 NULL 로 기록된다.
• 9I 까지는 실제로 수행된 SQL 텍스트가 관찰된다.

● DBMS_JOB 패키지에 수행되는 백그라운드 작업은 SQL trace 수행시 UDUMP 가 아닌 BDUMP 디렉토리에 트레이스 파일을 남긴다.

● 등록된 작업(JOB)은 [ALL/DBA/USERS]_JOBS 뷰와 DBMS_JOBS_RUNNING 뷰를 통해 관찰 가능하다.

DBMS_JOB 패키지 예제 )

-- 샘플 테이블 생성 
SQL> DROP TABLE T_DBMS_JOB ;
 SQL> CREATE TABLE T_DBMS_JOB
 ( INST_ID NUMBER, SID NUMBER, DESCR VARCHAR2(100), ACTION_ID NUMBER, ACTION_DT DATE DEFAULT SYSDATE ) ; 

-- 스케줄 수행할 프로시저 생성 
SQL> CREATE OR REPLACE PROCEDURE DO_SOMETHING
 IS
 V_SID NUMBER ;
 V_INST NUMBER ; 
BEGIN 

SELECT DISTINCT SID INTO V_SID FROM V$MYSTAT ; 
SELECT INSTANCE_NUMBER INTO V_INST FROM V$INSTANCE ; 

DBMS_APPLICATION_INFO.SET_MODULE( MODULE_NAME => 'DO_SOMETHING', ACTION_NAME => 'START'); 

FOR I IN 1 .. 2 LOOP
 INSERT INTO T_DBMS_JOB ( INST_ID, SID, DESCR, ACTION_ID ) VALUES ( V_INST, V_SID, 'DBMS_JOB TESTING', I ) ;
 END LOOP ;
 COMMIT ;

-- FOR MONTORING 
-- DBMS_LOCK.SLEEP(10) ; 

DBMS_APPLICATION_INFO.SET_MODULE( MODULE_NAME => 'DO_SOMETHING', ACTION_NAME => 'END'); 
END ; 

-- 테스트로 수행 
SQL> exec do_something ; 
PL/SQL procedure successfully completed. 

-- 결과 확인 
SQL> col descr for a20
SQL> select * from t_dbms_job ; 

INST_ID SID DESCR ACTION_ID ACTION_DT
 ---------- ---------- -------------------- ---------- ---------------
 2 1066 DBMS_JOB TESTING 1 06-APR-11
 2 1066 DBMS_JOB TESTING 2 06-APR-11 

CASE 1 : 지금 즉시 작업을 수행

SQL> truncate table t_dbms_job ; 
Table truncated. 

SQL> select * from t_dbms_job ; 
no rows selected 

SQL> col descr for a30
 SQL> var job_no number ;
 SQL> exec dbms_job.submit(:job_no, 'do_something;'); 
PL/SQL procedure successfully completed. 

SQL> select * from t_dbms_job ; 
no rows selected 

SQL> select * from t_dbms_job ; 
no rows selected 

==> 결과가 보여지지 않는다. 반드시 DBMS_JOB.SUBMIT 후에 COMMIT 을 수행해야 결과가 보여짐(스케줄이 작동한다)

SQL> commit ; 
Commit complete. 

SQL> select * from t_dbms_job ; 
INST_ID SID DESCR ACTION_ID ACTION_DT
 ---------- ---------- ------------------------------ ---------- ---------------
 2 1015 DBMS_JOB TESTING 1 06-APR-11
 2 1015 DBMS_JOB TESTING 2 06-APR-11 

CASE 2 ) 한시간 간격으로 수행 ( 매분으로 수정 )

SQL> var job_no number ;
SQL> exec dbms_job.submit(:job_no,'do_something;',sysdate,'sysdate+1/24/60'); 

PL/SQL procedure successfully completed. 

SQL> select job, what, last_date, next_date, broken, failures, instance from dba_jobs where what='do_something;';
 JOB WHAT LAST_DATE NEXT_DATE B FAILURES INSTANCE
 ---------- ---------------------------------------- --------------- --------------- - ---------- ----------
 394 do_something; 06-APR-11 N 0
 ==> LAST_DATE 값이 NULL 아직 한번도 수행 된적 없음 !!! 임으로 한번 수행하는게 필요하다.!!! 
 
SQL> select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') from dual ; 
TO_CHAR(SYSDATE,'YY
 -------------------
 2011/04/06 17:54:33 

SQL> select * from T_DBMS_JOB order by action_dt desc ; 
no rows selected 

--임으로 한번 수행 
SQL> exec dbms_job.run(398) ; 
PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance 
from dba_jobs where what='do_something;';

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 398 do_something; 2011/04/06 17:57:08 2011/04/06 17:58:08 N 0 0 

SQL> select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') from dual ; 

TO_CHAR(SYSDATE,'YY
 -------------------
 2011/04/06 17:57:26 

SQL> select * from T_DBMS_JOB order by action_dt desc ; 

INST_ID SID DESCR ACTION_ID ACTION_DT
 ---------- ---------- ------------------------------ ---------- ---------------
 2 1066 DBMS_JOB TESTING 2 06-APR-11
 2 1066 DBMS_JOB TESTING 1 06-APR-11 

-- 시간 경과 후 매분 수행되는 결과 확인 
SQL> select * from T_DBMS_JOB order by action_dt desc ; 

INST_ID SID DESCR ACTION_ID ACTION_DT
 ---------- ---------- ------------------------------ ---------- ---------------
 1 1072 DBMS_JOB TESTING 1 06-APR-11
 1 1072 DBMS_JOB TESTING 2 06-APR-11
 1 1072 DBMS_JOB TESTING 2 06-APR-11
 1 1072 DBMS_JOB TESTING 1 06-APR-11
 2 1066 DBMS_JOB TESTING 2 06-APR-11
 2 1066 DBMS_JOB TESTING 1 06-APR-11 

6 rows selected. 
==> 인스턴스를 넘나들면서 수행 된다. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance 
from dba_jobs where what='do_something;'; < /FONT> 

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 398 do_something; 2011/04/06 17:59:08 2011/04/06 18:00:08 N 0 0

- JOB 삭제 
SQL> exec dbms_job.remove(398); 

PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance 
from dba_jobs where what='do_something;'; 

no rows selected 

SQL> truncate table T_DBMS_JOB ; 
Table truncated. 
SQL> select * from T_DBMS_JOB order by action_dt desc ; 
no rows selected 

CASE 3 ) 인스턴스 1번에서만 작업을 수행하되, 인스턴스 1번이 작업을 수행할 수 없을 경우에는 다른 인스턴스가 작업을 수행

SQL> select instance_number from v$instance ; 

INSTANCE_NUMBER
 ---------------
 2

SQL> var job_no number ;
SQL> exec dbms_job.submit(job=>:job_no, what= >'do_something;', instance=>1, force=>true, interval= >'sysdate+1/24/60');

PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance
 from dba_jobs where what='do_something;';

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 400 do_something; 2011/04/06 18:02:31 N 1 

SQL> select * from T_DBMS_JOB order by action_dt desc ; 
no rows selected 

SQL> commit ; 
Commit complete. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance
 from dba_jobs where what='do_something;';

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 400 do_something; 2011/04/06 18:02:54 2011/04/06 18:03:54 N 0 1 

SQL> select * from T_DBMS_JOB order by action_dt desc ; 

INST_ID SID DESCR ACTION_ID ACTION_DT
 ---------- ---------- ------------------------------ ---------- ---------------
 1 1072 DBMS_JOB TESTING 2 06-APR-11
 1 1072 DBMS_JOB TESTING 1 06-APR-11 

--시간 경과 후 

SQL> select inst_id, sid, to_char(action_dt,'YYYY/MM/DD HH24:MI:SS') from T_DBMS_JOB order by action_dt desc ; 

INST_ID SID TO_CHAR(ACTION_DT,'
 ---------- ---------- -------------------
 1 1072 2011/04/06 18:04:55
 1 1072 2011/04/06 18:04:55
 1 1072 2011/04/06 18:03:54
 1 1072 2011/04/06 18:03:54
 1 1072 2011/04/06 18:02:54
 1 1072 2011/04/06 18:02:54 

6 rows selected. 

==> 항상 1에서만 수행 되고, 1에서 수행 불가시에만, 2번 노드에서 수행

CASE 4 ) 인스턴스 1번에서만 작업을 수행하되, 인스턴스 1번이 작업을 수행할 수 없을 경우에만 에러가 나도록 한다.

SQL> exec dbms_job.remove(400); 
PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance 
 from dba_jobs where what='do_something;'; 
no rows selected 

SQL> truncate table T_DBMS_JOB ; 
Table truncated. 

SQL> select * from T_DBMS_JOB order by action_dt desc ; 
no rows selected 

SQL> var job_no number ;
SQL> exec dbms_job.submit(job=>:job_no, what= >'do_something;', instance=>1, force=>false, interval= >'sysdate+1/24/60');
PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance
 from dba_jobs where what='do_something;';

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 401 do_something; 2011/04/06 18:08:07 N 1 

SQL> commit ; 
Commit complete. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance
 from dba_jobs where what='do_something;';< /FONT>

JOB WHAT LAST_D NEXT_D B FAILURES INSTANCE
 ---------- ---------------------------------------- ------------------- ------------------- - ---------- ----------
 401 do_something; 2011/04/06 18:08:26 2011/04/06 18:09:26 N 0 1

SQL> select inst_id, sid, to_char(action_dt,'YYYY/MM/DD HH24:MI:SS') from T_DBMS_JOB order by action_dt desc ; 

INST_ID SID TO_CHAR(ACTION_DT,'
 ---------- ---------- -------------------
 1 1072 2011/04/06 18:08:26
 1 1072 2011/04/06 18:08:26 

....

SQL> select count from t_dbms_job ; 

COUNT
 ----------
 48 

SQL> select distinct inst_id from t_dbms_job ; 

INST_ID
 ----------
 1 

SQL> exec dbms_job.remove(401); 
PL/SQL procedure successfully completed. 

SQL> select job, what, to_char(last_date,'YYYY/MM/DD HH24:MI:SS') AS LAST_D,
 to_char(next_date,'YYYY/MM/DD HH24:MI:SS') AS NEXT_D, broken, failures, instance
 from dba_jobs where what='do_something;';< /FONT>

no rows selected 

CASE 5 : 작업(Job)의 분배를 오라클에 맡긴다. 오라클은 각 노드의 부하를 고려해서 적절하게 작업을 분배한다.

SQL> var job_no number ;
SQL> exec dbms_job.submit(:job_no,'do_something;',sysdate,'sysdate+1/24/60'); 

DBMS_SCHEDULER 패키지

• 개선판이라기보다는 완전히 새로 추가된 패키지
• DBMS_JOB - 특정 시간 및 간격으로 특정 작업을 수행
• DBMS_SCHEDULER - 매우 복잡하고 정교한 수준의 스케줄 작업 가능

DBMS_SCHEDULER 의 장점

1. GUI(EM)을 통해서 손쉬운 관리
2. 모듈화된 스케줄 오보젝트를 통해서 쉽게 공유할수 있다. ( 프로그램, 스케줄, 잡 )
3. 모든 스케줄 활동이 기록된다.
4. TimeZone 이 지원된다.
5. 디비간 이동시 손쉽다(By DataPump)

● 프로그램(Program)

• 실행 가능한 프로그램(Program)을 말함
• DBMS_JOB 은 PL/SQL 블록, 저장 프로시저(Stored Procedure)만 가능
• DBMS_SCHEDULER는 외부 프로그램까지 사용 가능
  ( PL/SQL 블록, 저장 프로시저(Stored Procedure), 실행 파일(Executable, Unix Shell, Windows 실행 파일) )
• DBMS_SCHEDULER.CREATE_PROGRAM 프로시져를 통해 등록 가능하며
• [ALL/DBA/USER]_SCHEDULER_PROGRAMS 뷰를 통해서 확인 가능
• 스케줄렁에 의해서 실행 되는 무엇( 프로그램 이름, 타입, Argument 갯수 등 )

● 스케줄(Schedule)

• 작업을 수행할 스케줄을 말함.
• 작업 시작 시간, 종료 시간, 간격 등을 지정할 수 있다.
• DBMS_JOB에 비해 유연함
  EX ) "FREQ=YEARLY; BYMONTH=4; BYMONTHDAY=15; BYHOUR=9; BYMINUTE=0; BYSECOND=0"
  매년 4월 15일 9시에 작업 수행
• DBMS_SCHEDULER.CREATE_SCHEDULE 프로시져를 통해 등록 가능하며
• [ALL/DBA/USER]_SCHEDULER_SCHEDULES 뷰를 통해서 확인 가능

★WHEN / HOW MANY 에 대한 정의

#. USING PL/SQL Expression
: repeat_interval =>'sysdate+36/24'
#. Using Calendar Expression
: i) repeat_interval => 'FREQ=HOURLY;INTERVAL=4' ( Indiates a repeat interval of every four hours )
ii) repeat_interval => 'FREQ=YEARLY;BYMONTH=MAR,JUN,SEP,DEC;BYMONTH=15'
( indicate as repeat interval of every year on Mar 15th, Jun 15th, Sep 15th and Dec 15th )

● 작업(Job)

• 주어진 프로그램과 스케줄에 따라 수행할 작업을 말함 ( 언제 무엇을 )
• 명시적으로 생성된 프로그램과 스케줄을 이용할 수도 있고,
• 작업을 생성하면 암묵적인 프로그램과 스케줄을 생성할 수도 있다.
• DBMS_SCHEDULER.CREATE_JOB 프로시져를 통해 등록 가능하며
• [ALL/DBA/USER]_SCHEDULER_JOBS 뷰를 통해 확인 가능
• 작업이 수행되면서 남는 로그 데이터는 [ALL/DBA/USER]_SCHEDULER_JOB_LOG 뷰나
• [ALL/DBA/USER]_SCHEDULER_JOB_RUN_DETAILS 뷰를 통해 확인 가능

● 작업 클래스(Job Class)

• 작업의 공통 속성을 묶어서 만든 분류를 말한다.
• Resouce Consumer Group, Service, Logging Level, Log History 의 속성을 조합해서 하나의 클래스 생성 한다.
• Resource Consumer Group 은 DBMS_RESOURCE_MANGER 패키지를 통해서 생성,
• 말 그대로 자원을 얼마나 사용 가능하게 할지를 지정
• Service는 작업이 특정 서비스에 대한 리소스 친화도(Resource Affinity)를 가지도록 지정
  Service는 RAC 에서 클러스터 내의 여러 노드를 묶은 논리적인 그룹
• Logging Level 은 작업 실행에 대한 로그 데이터의 레벨을 지정
• Log History 는 로그 데이터를 얼마나 저장할 지를 지정한다
• 같은 작업 클래스에 속하는 작업은 같은 속성을 공유하기 때문에 관리상의 편의점을 제공
• 하나의 JOB 은 하나의 JOB CLASS 에만 속함

● 윈도우(Window)

• 특정 리소스 플랜(Resoure Plan)을 적용하는 시간 단위를 의미
• 리소스 플랜은 오라클이 자원을 관리하는 단위로, 작업의 종류에 따라 CPU 등의 자원을 얼마나 부여할 지를 지정하는 역할
  ( 윈도우를 지정하면 해당 윈도우 안에서 실행되는 작업은 윈도우 생성시 지정한 리소스 플랜을 사용하게 된다

● 체인(Chain )

• 프로그램의 집합을 의미 한다. 일련의 프로그램들을 순서대로 수행하고자 할 경우에 사용한다.
• 체인은 오라클 10g R2 에서 추가된 기능

스케줄작업과 RAC Instance

• DBMS_JOB ==> 작업을 실행할 인스턴스 번호를 지정 // 한노드로 지정 시에 좀 더 편리
• DBMS_SCHEDULER => Instance_Stickness 라는 개념을 통해 좀 더 지능적으로 인스턴스를 할당

INSTANCE_STICKSNESS = TRUE ( DBMS_SCHEDULER.SET_ATTRIBUTE ) 의미

1. 작업 수행 시 현재 가장 부하가 적은 인스턴스에 작업을 분배 한다.
2. 이후 작업 수행 시에는 가능한 최초에 지정된 인스턴스에서 작업을 수행하도록 한다.
   이 매커니즘을 일컬어 인스턴스 접착도라고 하며 리소스 친화도를 구현하는 방법
3. 최초에 지정된 인스턴스가 다운되거나, 부하가 심해서 작업을 수행할 수 없을 경우 다른 인스턴스에서 작업을 수행
4. 만일 INSTANCE_STICKNESS 속성 값이 FALSE 이면 오라클은 인스턴스 순서대로 작업을 수행한다.
   즉, DBMS_JOB 패키지를 이용해 작업을 수행하되 인스턴스 번호를 지정하지 않은 경우와 거의 같은 방식으로 작동한다.

스케즐 등록 해보기

-- 샘플 테이블 생성
CREATE TABLE CHECK_SCHEDULE 
( INST_ID NUMBER, ACT_DATE DATE DEFAULT SYSDATE, ACT_DESC VARCHAR2(100)) ;

-- 샘플 프로시져 생성
CREATE OR REPLACE PROCEDURE OPS$ORACLE.DO_SCHEDULE_PROG
IS
  V_SID NUMBER ;
  V_INST NUMBER ; 
BEGIN 
     SELECT INSTANCE_NUMBER INTO V_INST FROM V$INSTANCE  ; 
     INSERT INTO CHECK_SCHEDULE ( INST_ID, ACT_DATE,  ACT_DESC ) VALUES ( V_INST, SYSDATE, 'DO SCHEDULEING') ;
END ;     
      

-- 스케줄 등록
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(schedule_name=>'CHK_SCHE',repeat_interval=>'FREQ=MINUTELY');

-- 스케줄 확인
SELECT SCHEDULE_NAME, REPEAT_INTERVAL  
FROM DBA_SCHEDULER_SCHEDULES 
WHERE OWNER ='OPS$ORACLE' AND SCHEDULE_NAME='CHK_SCHE';


SCHEDULE_NAME	REPEAT_INTERVAL
CHK_SCHE	FREQ=MINUTELY


-- 프로그램 등록
EXEC DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME=>'DO_SCHEDULE_PROGRAM',PROGRAM_TYPE=>'STORED_PROCEDURE',PROGRAM_ACTION=>'DO_SCHEDULE_PROG');

-- 프로그램 Enable 
EXEC DBMS_SCHEDULER.ENABLE('DO_SCHEDULE_PROGRAM');

-- 프로그램 등록 확인

SELECT PROGRAM_NAME, PROGRAM_ACTION, ENABLED 
FROM DBA_SCHEDULER_PROGRAMS 
WHERE OWNER ='OPS$ORACLE' AND PROGRAM_NAME='DO_SCHEDULE_PROGRAM';

PROGRAM_NAME		PROGRAM_ACTION		ENABLED
DO_SCHEDULE_PROGRAM	DO_SCHEDULE_PROG	TRUE

-- 잡 등록
EXEC DBMS_SCHEDULER.CREATE_JOB(JOB_NAME=>'CHK_SCHE_JOB' ,PROGRAM_NAME=>'DO_SCHEDULE_PROGRAM' ,SCHEDULE_NAME=>'CHK_SCHE' ,AUTO_DROP=>FALSE);

-- 잡 Enable 
EXEC DBMS_SCHEDULER.ENABLE('CHK_SCHE_JOB'); 

-- 잡 등록 확인
SELECT JOB_NAME, PROGRAM_NAME, SCHEDULE_NAME, ENABLED 
FROM DBA_SCHEDULER_JOBS 
WHERE OWNER ='OPS$ORACLE' AND JOB_NAME='CHK_SCHE_JOB';

JOB_NAME	PROGRAM_NAME		SCHEDULE_NAME	ENABLED
CHK_SCHE_JOB	DO_SCHEDULE_PROGRAM	CHK_SCHE	TRUE


-- 작업 결과 확인
SELECT LOG_DATE, ACTUAL_START_DATE, JOB_NAME, STATUS  
FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME ='CHK_SCHE_JOB';

LOG_DATE				ACTUAL_START_DATE			JOB_NAME	STATUS
2011/04/20 13:36:02.127937 +09:00	2011/04/20 13:36:02.087344 +09:00	CHK_SCHE_JOB	SUCCEEDED
2011/04/20 13:37:01.223407 +09:00	2011/04/20 13:37:01.209592 +09:00	CHK_SCHE_JOB	SUCCEEDED
2011/04/20 13:38:01.127528 +09:00	2011/04/20 13:38:01.110444 +09:00	CHK_SCHE_JOB	SUCCEEDED

-- 실제 결과 확인
SELECT * FROM CHECK_SCHEDULE ;
INST_ID	ACT_DATE	ACT_DESC
1	2011/04/20 13:36:02	DO SCHEDULEING
1	2011/04/20 13:37:01	DO SCHEDULEING
1	2011/04/20 13:38:01	DO SCHEDULEING
1	2011/04/20 13:39:01	DO SCHEDULEING
1	2011/04/20 13:40:01	DO SCHEDULEING