Techdata VINA 지식 공유

Oracle DB Scheduler 개념 및 사용법

■ 목적.

Database내 혹을 Database Server에서 정규, 비정규 작업을 수행 .

■ 사용 용도

Database 통계치 수집 작업 일 마감 작업

Backup 작업

Database내의 정주기 작업

Database Server내의 정주기 작업

■ 특징

9i이전의 job queue의 개선된 기능으로 Scheduler에서 job queue의 기능도 포함함. 다양한 프로그램을 등록하여 수행 가능.

PL/SQL stored procedure Anonymous PL/SQL block

External Executable program(shell, os command등)

EM의 UI 화면 제어 혹은 DBMS_SCHEDULER API를 이용한 제어. Job에 대한 Priority 지정 및 수행할수 있는 Time Window 지정 기능.

• Architecture

  Program : 수행될 프로그램 및 argument 정의

  Job : 어떠한 것을 언제 수행할지를 정의

  Schedule : 수행 시기 및 반복 주기 정의

  Job Class : job들의 카테고리의 구분 정의.

  Window : Job의 수행 가능 시간 구간을 정의

  Window group : Window들의 집합으로 여러 구간 정의 시 사용

Resource Consumer Group : job과 우선 순위 정의와의 연결정의

Resource Plan : 자원에 대한 우선순위 정의(cpu등)

■ 필요 권한

Create Job : job, schedule, program을 만들기 위한 권한.

Manager Scheduler : window, job class, window group을 만들기 위한 권한

EXECUTE ON <program or class> : 일반 User가 program에 대한 수행하기 위한 권한 주기.

■ SCHEDULER_ADMIN Role

Job Runtime Structure

■    정의된 Job에 대한 수행 방법.

지정된 Job에 대한 수행은 CJQ process가 check하여 수행될 job을 job slave process에게 지정 한다.

Create a Program

수행 할 Program이나 Shell, PL/SQL을 'Program'으로 등록한다.

BEGIN

   DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'TDDBA.TEST_PRG',

program_action => '/DBMS/TMASG/DBA/TDDBA/test.sh', program_type => 'EXECUTABLE',

enabled => TRUE, number_of_arguments => 0, comments => 'Test shell program');

END;

/

설명    : test.sh이라는 os shell을 수행하는 program등록.

Program_type : 등록하는 program의 type을 정의. 'EXECUTABLE', 'plsql_block',' stored_procedure ' 등이 있음.

Enabled : program에 대한 validate check를 하며 또한 바로 사용할 수 있는 enable상태로 만든다.

■ 작업에 대한 수행 주기, 반복 등의 정의한다.

BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name => 'min_job_schedule',

-- start_date => SYSTIMESTAMP,

-- end_date => SYSTIMESTAMP + 30,

repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',

comments => 'Every 30 min');

end;

/

설명 : 30분마다 수행하는 계획을 자긴 min_job_schedule 이라는 schedule 생성. Repeat_interval : Schedule의 반복 시간은 지정.

ex) repeat_interval=> 'FREQ=HOURLY; INTERVAL=4' 4시간 마다.

repeat_interval=> 'FREQ=DAILY'

하루마다

repeat_interval=> 'FREQ=MINUTELY; INTERVAL=15' 15분 마다

repeat_interval=> 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=15'

매년, 3,6,9,12월 15일에.

repeat_interval=> 'SYSDATE + 6/24' repeat_interval=> 'SYSDATE + 1' repeat_interval=> 'SYSDATE + 15/(24*60)'

Job Class를 등록 한다.

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS (

job_class_name    =>'TEST_JOB_CLASS',

--    resource_consumer_group    IN VARCHAR2 DEFAULT NULL,

--    service    IN VARCHAR2 DEFAULT NULL,

logging_level    => DBMS_SCHEDULER.LOGGING_FULL, log_history    =>1,

comments    =>'test');

end;

/

설명 : TesT_job_class라는 job class로이 class에 포함된 job은 logging level이 logging_full이고 1일 동안 log기 록이 보존된다.

Service : 이 job class에 속한 job이 포함되는 Service를 명시.

Logging Level : 이 Job Class에 속한 Job의 logging level을 정하는 것으로

LOGGING_FULL, LOGGING_RUNS,

LOGGING_OFF 3가지가 지원된다.

Log_history: 이 job class에 속한 job의 log를 보존하는 기간을 정의한다. Default value는 30일 이다.

begin

DBMS_SCHEDULER.CREATE_JOB (

job_name    =>'TDDBA.TEST_JOB', program_name    => 'TDDBA.TEST_PRG', schedule_name    => 'min_job',

job_class    =>'TEST_JOB_CLASS',

enabled    =>TRUE);

end;

/

Program_name : job에서 사용할 program Schedule_name : job을 수행할 주기 schedule Job_class : job의 속성을 정의한 class

참고 : dbms_scheduler.set_attribute를 이용하여 job에 대해 지정할 수 있는 attribute들

job_class job_priority auto_drop restartable max_runs max_failures schedule_limit logging_level

BEGIN

DBMS_SCHEDULER.CREATE_JOB(

job_name    =>'TDDBA.TEST_JOB',

job_type    => 'EXECUTABLE',

job_action    =>'/DBMS/TMASG/DBA/TDDBA/test.sh', start_date    => sysdate+1/24,

repeat_interval    => 'SYSDATE + 1800/86400', job_class    => 'TEST_JOB_CLASS',

enabled    => TRUE,

comments    => 'test job');

END;

/

설명 : test.sh shell을 하루 후부터 30분 마다 수행하는 job 등록.

1. os의 shell program은 extjob에 의해 수행되는데 이를 nobody user가 수행할수 있도록 지정.

• $ORACLE_HOME/bin/extjob 이 수행하게 되는데, 이는 nobody owner로 지정하게 된다.

   

  ls –l extjob* (IBM)

  -rws--s--x    1 nobody    nobody    624382 Apr 6 15:12 extjob

• HP 에서는 nobody로 owner가 지정되지 않으므로 일반 user( unprivileged user)가 owner가 되도록 한다.

   

  ls –l extjob* (hp) : oranbody user를 생성하고 ownership 바꾼후 mode를 4711로 변경.

  -rws--x--x    1 oranbody    dbusers    33936 Jun 11 18:13 extjob

   

  Oracle install 문서 참고 :

  http://download-west.oracle.com/docs/html/B10811_02/ch4.htm#sthref1606

2. Install 작업시 추가 사항. root.sh 이후에..

# cd oracle_home/bin

# mv extjob.nobody extjob

# chown nobody extjob

# chmod 4711 extjob

3. Shell 내용.

shell의 첫 라인은 shell을 지정하는 #!/usr/bin/ksh 를 지정해야 함. shell내의 모든 command는 path를 알 수 있도록 지정.

ex) test.sh

#!/usr/bin/ksh

/usr/bin/ls -ltr > /dbms/tkmasg/dba/TDDBA/a.out

1 Scheduler관련 View

[DBA | ALL | USER]_SCHEDULER_JOBS    : 등록된 job

[DBA | ALL | USER]_SCHEDULER_JOB_ARGS    : job의 arguments

[DBA | ALL | USER]_SCHEDULER_RUNNING_JOBS    : 현재 running중인 job들의 정보

[DBA | ALL | USER]_SCHEDULER_JOB_LOG    : job의 log

[DBA | ALL | USER]_SCHEDULER_JOB_RUN_DETAILS    : job의 수행된 정보 및 Error 정보

[DBA | ALL | USER]_SCHEDULER_PROGRAMS [DBA | ALL | USER]_SCHEDULER_PROGRAM_ARGS [DBA | ALL | USER]_SCHEDULER_SCHEDULES [DBA | ALL]_SCHEDULER_JOB_CLASSES

[DBA | ALL ]_SCHEDULER_WINDOWS

[DBA | ALL ]_SCHEDULER_WINDOW_DETAILS [DBA | ALL ]_SCHEDULER_WINDOW_LOG

2. ETC command

1 Scheduler 관련 View

[DBA | ALL | USER]_SCHEDULER_JOBS : 등록된 job

[DBA | ALL | USER]_SCHEDULER_JOB_ARGS : job의 의 arguments

[DBA | ALL | USER]_SCHEDULER_RUNNING_JOBS : 현재 running 중인 job 들의 정 보

[DBA | ALL | USER]_SCHEDULER_JOB_LOG : job의 의 log

[DBA | ALL | USER]_SCHEDULER_JOB_RUN_DETAILS : job 의 수행된 정보 보 및 및 Error 정 보

[DBA | ALL | USER]_SCHEDULER_PROGRAMS

[DBA | ALL | USER]_SCHEDULER_PROGRAM_ARGS

[DBA | ALL | USER]_SCHEDULER_SCHEDULES

[DBA | ALL]_SCHEDULER_JOB_CLASSES

[DBA | ALL ]_SCHEDULER_WINDOWS

[DBA | ALL ]_SCHEDULER_WINDOW_DETAILS

[DBA | ALL ]_SCHEDULER_WINDOW_LOG

2. ETC command

exec DBMS_SCHEDULER.enable('TDDBA.TEST_JOB') : job enable 시 키 기.

exec DBMS_SCHEDULER.DISABLE('TDDBA.TEST_JOB') : job disable 시 키 기.

exec DBMS_SCHEDULER.DROP_JOB('TDDBA.TEST_JOB'); : job drop

exec dbms_scheduler.drop_schedule('min_job'); : schedule drop

exec dbms_Scheduler.drop_program('TDDBA.TEST_PRG');: program drop

exec DBMS_SCHEDULER.RUN_JOB('TDDBA.TEST_JOB') : manual run a job

Exec DBMS_SCHEDULER.PURGE_LOG(log_history => 1, job_name => 'DEV_TEST_JOB1'); : log

Sample job with anonymous PL/SQL

1. Job Class 생성

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS (

job_class_name =>'PLSQL_BLOCK_JOB_CLASS',

logging_level =>DBMS_SCHEDULER.LOGGING_FULL,

log_history =>1,

comments =>'plsql block job class');

end;

/

2. Program 생성

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

program_name => 'INSERT_DEPT_PRG',

program_type => 'PLSQL_BLOCK',

program_action =>

'DECLARE

v_cnt number;

BEGIN

v_cnt := 1;

while v_cnt < 11 loop

insert into TDDBA.dept values (v_cnt, ''aaa'',''ggg'');

commit;

v_cnt := v_cnt + 1;

end loop;

END;',

enabled => TRUE);

END;

/

3. Schedule 생성

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name => 'min_30_job',

repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',

comments => 'Every 30 min');

end;

/

4. Job 생성

begin

DBMS_SCHEDULER.CREATE_JOB (

job_name =>'TDDBA.INSERT_DEPT_JOB',

program_name =>'TDDBA.INSERT_DEPT_PRG',

schedule_name =>'min_30_job',

job_class =>'PLSQL_BLOCK_JOB_CLASS',

enabled =>TRUE);

end;

/

5. Testing.

exec DBMS_SCHEDULER.RUN_JOB('TDDBA.INSERT_DEPT_JOB');

Sample job with O/S Shell program

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS (

job_class_name =>'TEST_JOB_CLASS',

logging_level =>DBMS_SCHEDULER.LOGGING_FULL,

log_history =>1,

comments =>'test');

end;

/

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'TDDBA.TEST_PRG',

program_action => '/usr/bin/sh /DBMS/TMASG/DBA/TDDBA/test.sh',

program_type => 'EXECUTABLE',

enabled => TRUE,

comments => 'Test shell program');

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name => 'min_job',

repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',

comments => 'Every 10 min');

end;

/

begin

DBMS_SCHEDULER.CREATE_JOB (

job_name =>'TDDBA.TEST_JOB',

program_name => 'TDDBA.TEST_PRG',

schedule_name => 'min_job',

job_class =>'TEST_JOB_CLASS',

enabled =>TRUE);

end;

/

Test.sh

------------

#!/usr/bin/ksh

/usr/bin/ls -ltr > /dbms/tkmasg/a.out

------------

chmod +x test.sh

Sample job with anonymous PL/SQL

CREATE OR REPLACE PROCEDURE TDDBA.TEST_STORED_PLSQL

IS

BEGIN

UPDATE TDDBA.DEPT SET LOC = 'SEOUL'

WHERE ROWNUM =1;

commit;

END;

/

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS (

job_class_name =>'PLSQL_JOB_CLASS',

logging_level =>DBMS_SCHEDULER.LOGGING_FULL,

log_history =>1,

comments =>'procedure job class');

end;

/

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM(

program_name => 'TEST_STORED_PRG',

program_action => 'TDDBA.TEST_STORED_PLSQL',

program_type => 'STORED_PROCEDURE',

enabled => TRUE);

END;

/

BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name => 'min_15_job',

repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',

comments => 'Every 15 min');

end;

/

begin

DBMS_SCHEDULER.CREATE_JOB (

job_name =>'TDDBA.UPDATE_DEPT_JOB',

program_name =>'TDDBA.TEST_STORED_PRG',

schedule_name =>'min_15_job',

job_class =>'PLSQL_JOB_CLASS',

enabled =>TRUE);

end;

/

exec DBMS_SCHEDULER.RUN_JOB('TDDBA.UPDATE_DEPT_JOB');