이전 포스트: [Oracle] 사용자 관리
다음 포스트: [Oracle] Network와 Oracle Net Service
1. DBMS_JOB 패키지
유닉스의 좋은 기능 중 정해진 시간에 반복되는 작업을 수행할 수 있도록 해주는 cron이라는 기능이 있습니다. 관리자는 작업 내용을 스크립트 파일로 생성하고 crond 데몬에게 작업을 지시하면 데몬은 특정 시간에 해당 작업을 수행하게 됩니다.
oracle에서도 이와 같은 기능을 하는 DBMS_JOB이라는 패키지가 있습니다.
(DBMS_SCHEDULER는 좀 더 확장된 기능을 가진 패키지입니다)
DBMS_JOB 패키지를 설정하면 SNP 백그라운드 프로세스가 패키지에 설정된 작업을 수행합니다. 초기화 파라미터 파일에 아래 항목을 설정한 후 재시작하면 프로세스가 동작하며 작업을 수행합니다.
job_queue_processes = 1: SNP 프로세스의 개수job_queue_interval = 60: SNP 프로세스가 Sleep 상태에서 깨어나는 간격(초 단위)
DBMS_JOB 패키지는 다양한 프로시저를 가지고 있습니다.
submit: 새로운 작업을 Job Queue 목록에 등록remove: Job Queue에 등록된 job 제거change: Job Queue에 등록된 job 변경next_date: Job Queue에 등록된 job의 작동 시간 변경interval: Job Queue에 등록된 job의 수행 주기 변경what: 수행할 procedure or package 변경run: 등록되어 있는 특정 job을 수동으로 실행
job 관리하기
각 프로시저는 다양한 파라미터를 가지고 있습니다. 아래의 job, what, next_date, interval, no_parse가 그것입니다.
dbms_job.submit(
job out binary_integer,
what in varchar2,
next_date in date default sysdate,
interval in varchar2 default 'null',
no_parse in boolean default false
)주요 파라미터의 의미
job: job 번호. 다른 프로시저에서 호출될 수도 있습니다.what: 수행할 pl/sql or procedure or package 이름. 이곳에 직접 수행하기를 원하는 SQL 문장을 써도 됩니다.next_date: 다음에 수행될 시간.interval: 수행되는 주기. 초 단위까지 지정 가능합니다.no_parse: parse 여부. 기본값은 false입니다.
Job 관리는 아주 많은 배치성 작업들에 대해서 편하고 유용하게 관리할 수 있는 방법이므로 중요합니다.
새로운 job 등록 테스트
Step 1) 테스트용 table, sequence, procedure 생성
-- sequence 생성
SQL> create sequence seq_job_seq1;
-- table 생성
SQL> create table job_test01
2 (no number,
3 name varchar2(5));
-- 프로시저 생성
SQL> create or replace procedure insert_job_test01
2 is
3 begin
4 insert into job_test01
5 values(seq_job_seq1.nextval, dbms_random.string('a',3));
6 end;
7 /Step 2) Job에 등록할 프로시저 생성
SQL> !vi job1.sql
begin
dbms_job.submit(:jno,
'insert_job_test01;',
sysdate,
'sysdate + 1/24/60',
false);
end;
/
:wq!interval 설정 방법
- 10에 한 번씩 실행:
sysdate + 1/24/6또는sysdate + 1/144
1은1일을 의미. 1/24는 1일을 24시간으로 나눈다는 의미. 즉 1시간. 1/24/6은 1일을 24시간으로 나누고 6으로 나눈단 의미. 즉 10분.- 1분에 한 번 실행:
sysdate + 1/24/60또는sysdate + 1/1440- 5분에 한 번 실행:
sysdate + 5/24/60- 매일 새벽 2시 실행:
trunc(sysdate) + 1 + 2/24
다음날 새벽 2시를 의미.trunc를 사용한 이유는- 매일 밤 11시 실행:
trunc(sysdate) + 23/24
오늘 밤 11시를 의미.
Step 3) 프로시저를 Job에 등록
SQL> variable jno number;
SQL> @job1
SQL> commit;
만약 RAC 환경에서 특정 노드에 job을 할당해야 할 경우 아래와 같이 입력합니다.
SQL> EXEC dbms_job.instance(job=>25, instance=>1);
Step 4) 수행되고 있는 job 내역 확인
SQL> select what, job, next_date, next_sec, failures, broken
from user_jobs
where what='insert_job_test01;';
Step 5) 실제 테이블에 데이터가 입력되는지 확인
SQL> select * from job_test01;
등록되어 있는 job 삭제하기
SQL> exec dbms_job.remove(<job 번호>);
SQL> select what, job, next_date, next_sec, failures, broken
from user_jobs
where job=<job 번호>;
등록되어 있는 job 수정하기
SQL> variable jno number;
SQL> @job1
-- job number(jno) 확인
SQL> print jno;
JNO
----------
2
SQL> commit;
SQL> select what, job, next_date, next_sec, interval
from user_jobs
where job=2;
-- job 파라미터 변경
SQL> exec dbms_job.change(:jno, 'insert_job_test01;', sysdate, 'sysdate+5/24/60');
SQL> set line 200
SQL> col interval for a30
SQL> select what, job, next_date, next_sec, interval
from user_jobs
where job=2;
2. DBMS_SCHEDULER
DBMS_SCHEDULER는 DBMS_JOB 패키지보다 확장된 기능들을 제공하는 유용한 스케줄링 도구입니다. 가장 큰 차이점은 외부 스크립트를 실행할 수 있다는 것입니다.
관리자는 DBMS_SCHEDULER를 사용해서 정해진 시간에 튜닝에 필요한 테이블, 인덱스, 클러스터 등의 각종 통계값을 계산해서 저장하거나, 정해진 시간에 백업을 수행하거나, Mview나 Standby 서버와 원본과의 동기화 작업 등 주기적으로 반복되어야 하는 작업들을 자동화시켜서 훨씬 편하게 업무를 진행할 수 있습니다.
구성
-
Program
이 부분에 dbms_scheduler가 수행할 프로그램이나 프로시저, 스크립트 등에 대한 정보를 저장해서 별도로 수행할 수 있습니다. -
Schedule
dbms_scheduler가 수행할 job이 실제 수행할 시간이나 실행 주기 등을 별도로 생성할 수 있습니다. -
job
새로 생성할 job 부분을 정의합니다.
DBMS_SCHEDULER 사용하기
신규 job 생성하기
SQL> grant create any job to khyup;
SQL> conn khyup/tiger;
KHYUP> create table job_test1
(no number,
name varchar2(5),
rdate date default sysdate);
KHYUP> create sequence seq_job_test1_no;
KHYUP> create or replace procedure insert_job_test
is
begin
insert into khyup.job_test1 (no, name)
values(seq_job_test1_no.nextval, dbms_random.string('a',2));
commit;
end;
/
KHYUP> begin
dbms_scheduler.create_job(
job_name => 'insert_job_test1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert_job_test; END;',
start_date => systimestamp,
repeat_interval => 'freq=secondly; interval=3');
end;
/
KHYUP> exec dbms_scheduler.enable('insert_job_test1');
KHYUP> exec dbms_scheduler.run_job('insert_job_test1');
KHYUP> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
KHYUP> select * from job_test1 order by 3;
현재 작동 중인 job 확인하기
SQL> select job_name, job_creator from user_scheduler_jobs;
JOB_NAME JOB_CREATOR
-------------------- --------------------
INSERT_JOB_TEST1 KHYUP
특정 job 일시 중지 또는 재시작
(등록된 job이 사라지는 것은 아닙니다)
-- 일시 중지
KHYUP> exec dbms_scheduler.disable('insert_job_test1');
-- 다시 시작
KHYUP> exec dbms_scheduler.enable('insert_job_test1');특정 job 삭제하기
KHYUP> begin
dbms_scheduler.drop_job('insert_job_test1');
end;
/
KHYUP> select job_name from user_scheduler_jobs;
no rows selected
SQL> program_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT'); END;',dbms_scheduler.create_job 자세히 살펴보기
dbms_scheduler.create_job(
job_name => 'insert_job_test1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert_job_test; END;',
start_date => systimestamp,
repeat_interval => 'freq=secondly; interval=3');job_type: job_action 값에 따라 달라집니다.
BEGIN ~ END와 같이 PL/SQL 블록으로 적으면PLSQL_BLOCK
프로시저 이름을 적었다면STORED_PROCEDURE
실행 프로그램을 적었다면EXECUTABLE
prgram_action => '/home/oracle/run.sh',
program_type => 'EXECUTABLE'job_action:
1) 실제 pl/sql 블록
2) 프로시저 이름
3) OS에 있는 실행 파일 이름
4) program_name으로 미리 생성해 둔 프로그램 이름 지정
5) Chained 값으로 생성된 체인 활용start_date: job 시작 시간 지정
1)systimestamp: 지금 즉시 시작.
2) 특정 시간에 시작
start_date => to_timestamp_tz('2022-11-15 02:00:00 ROK', 'YYYY-MM-DD HH24:MI:SS TZR')end_date: job 종료 시간 지정
end_date => systimestamp + interval '30' day
end_date => to_timestamp_tz('2022-11-15 24:00:00 ROK', 'YYYY-MM-DD HH24:MI:SS TZR')repeat_interval: 반복 주기 지정
repeat_interval => 'FREQ=HOURLY; INTERVAL=1' -- 1시간 간격으로 수행
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30' -- 30분 간격으로 수행
repeat_interval => 'FREQ=SECONDLY; INTERVAL=5' -- 5초 간격으로 수행
repeat_interval => 'FREQ=WEEKLY; INTERVAL=2' -- 2주 간격으로 수행
repeat_interval => 'FREQ=MONTHLY' -- 매달 수행
repeat_interval => 'FREQ=YEARLY' -- 매년 수행BY 절을 사용하면 보다 구체적으로 시간을 지정할 수 있습니다.
repeat_interval => 'FREQ=YEARLY; BYWEEKNO=8,16,24,32,40,48; BYDAY=FRI;'
repeat_interval => 'FREQ=YEARLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=50;'INCLUDE: 다른 일정을 현재 일정에 포함. sub_sched_1이라는 스케줄을 현재 스케줄에 포함시킨다면,
repeat_interval =>
'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15; BYHOUR=9,17; INCLUDE=sub_sched_1');다음 작업 시간까지의 간격 지정
repeat_interval => 'SYSDATE + 1'; -- 1일 뒤에 수행
repeat_interval => 'SYSDATE + 30/1440' -- 30분 마다 수행외부 스크립트 생성 후 실행
이제 소개해드릴 내용은 OS에서 생성한 스크립트를 실행하는 방법입니다. 외부에서 생성된 스크립트를 oracle에서 실행하기 위해서는 먼저 해당 스크립트를 실행할 권한과 그룹이 지정되어 있는 externaljob.ora 파일을 수정해야 합니다.
Step 1) externaljob.ora 파일 내용 수정
-- oracle 계정의 소속 그룹 확인
# id oracle
-- externaljob.ora 파일 내용 수정
# vi /ORA19/app/oracle/product/19.0.0/db_1/rdbms/admin/externaljob.ora
#run_user = nobody -- 기존 내용 주석 처리
#run_group = nobody -- 기존 내용 주석 처리
run_user = oracle
run_group = oinstall
:wq!Step 2) 파일의 권한을 640으로 변경
$ sudo chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora
oracle is not in the sudoers file. This incident will be reported.에러 발생 시 권한 문제이므로/etc/sudoers/에 내용 추가가 필요합니다.
- 아래 사이트 참고 : https://starseeker711.tistory.com/176
Step 3) 외부 스크립트 실행 명령어인 extjob 파일의 소유자 변경
$ sudo chown oracle $ORACLE_HOME/bin/extjob
$ ls -al $ORACLE_HOME/bin/extjob
Step 4) sys 계정으로 oracle 접속 후 external job을 수행할 계정에 적절한 권한 부여
SYS> grant execute on sys.dbms_scheduler to khyup;
SYS> grant create job to khyup;
SYS> grant create external job to khyup;
Step 5) 수행할 외부 스크립트 생성
SYS> !
$ vi mkdir.sh
#!/bin/bash
date=`date +%Y-%m-%d-%H-%M-%S`
mkdir /ORA19/app/oracle/$date
:wq!
$ chmod 755 mkdir.sh
$ sh mkdir.sh
$ sh mkdir.sh
$ ls /ORA19/app/oracle/2022*
2022-11-18-16-55-48 2022-11-18-16-56-52
Step 6) 외부 스크립트를 수행하는 schedule을 생성합니다.
$ exit
SYS> begin
dbms_scheduler.create_schedule (
schedule_name => 'test_dir',
start_date => systimestamp,
end_date => null,
repeat_interval => 'freq=secondly;interval=10');
end;
/
Step 7) 스크립트를 실행하는 job을 생성합니다.
begin
dbms_scheduler.create_job (
job_name => 'test_dir_1',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/mkdir.sh',
schedule_name => 'test_dir');
end;
/
Step 8) 해당 job을 enable합니다.
SQL> begin
dbms_scheduler.enable('test_dir_1');
end;
/
Step 9) 해당 job을 실행합니다.
SQL> exec dbms_scheduler.run_job('test_dir_1');
Step 10) 잠시 후에 디렉토리가 10초 간격으로 생성되는지 확인합니다.
$ ls /ORA19/app/oracle/2022*
또는
KHYUP> select log_date, owner, job_name, status
from user_scheduler_job_run_details
where owner='KHYUP'
order by log_date desc;
위 job은 테스트 용도이고, 가만히 놔두면 계속 디렉토리를 10초 마다 생성하기 때문에 job을 삭제해야 합니다.
KHYUP> begin
dbms_scheduler.drop_job('TEST_DIR_1');
end;
/
JOB 속성 변경하기
job과 관련된 속성들(job_name, job_type, job_action, start_date 등)이 변경되어야 할 경우 해당 속성들을 변경하는 방법들을 살펴보겠습니다.
KHYUP>begin
dbms_scheduler.create_job(
job_name => 'insert_job_test2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert_job_test; END;',
start_date => systimestamp,
repeat_interval => 'freq=secondly; interval=5');
end;
/
이 job의 수행 주기를 5초 -> 10초로 변경해보겠습니다.
KHYUP> begin
2 dbms_scheduler.set_attribute(
3 name => 'insert_job_test2',
4 attribute => 'repeat_interval',
5 value => 'freq=secondly; interval=10');
6 end;
7 /
정상적으로 변경되었는지 확인합니다.
SYS> select job_name, start_date, repeat_interval, end_date
from user_scheduler_jobs
where job_creator='KHYUP';
DBMS_SCHEDULER 관리하기
1) 생성된 job을 즉시 실행하기
SQL> exec dbms_scheduler.run_job('insert_job_test1');2) job 중단하기
SQL> exec dbms_scheduler.stop_job(job_name => 'insert_job_test1', force => true);
만약 force => true로 지정하게 되면 현재 job이 실행 중이더라도 해당 job을 중단합니다.
3) job 삭제하기
SQL> exec dbms_scheduler.drop_job(job_name => 'insert_job_test1', force => true);
마찬가지로 force => true로 지정되어 있기 때문에 해당 job이 실행 중이더라도 강제로 삭제합니다.
참고
- <오라클 관리 실무> - 서진수
