오라클 관리 03 DBMS_JOB, DBMS

오라클 관리 03 DBMS_JOB, DBMS_SCHEDULER

2024. 10. 25. 17:14ㆍDB/ORACLE Admin

Oracle의 DBMS_JOB 패키지는 특정 시점에 반복적으로 실행해야 하는 작업을 자동으로 스케줄링할 수 있는 기능을 제공하여 데이터베이스 관리자가 특정 시간에 직접 작업을 수행할 필요 없이 자동으로 실행되도록 설정할 수 있어 편리하다.

1. DBMS_JOB 패키지 기본 설정

SET job_queue_processes = 1: 데이터베이스에서 동시에 실행할 수 있는 작업 대기열 프로세스의 수를 1로 설정하였다.

시스템에서 작업 대기열 프로세스가 1로 설정되어, 동시에 실행할 수 있는 작업 수가 제한되었다.

2. DBMS_JOB 프로시저 종류

submit	새로운 작업을 Job Queue에 등록한다.
remove	Job Queue에 등록된 작업을 제거한다.
change	등록된 작업의 속성을 변경한다.
next_date	작업의 다음 실행 시간을 변경한다.
interval	작업의 실행 주기를 변경한다.
what	실행할 PL/SQL 또는 프로시저 내용을 변경한다.
run	특정 작업을 수동으로 실행한다.

3. DBMS_JOB.submit ㄷ턋

job: 작업 번호로, 자동 생성된다.
what: 수행할 PL/SQL 구문 또는 프로시저 이름을 지정한다.
next_date: 작업의 다음 실행 시간을 지정한다.
interval: 작업의 실행 주기를 지정한다 (초 단위까지 가능).
no_parse: 파싱 여부 설정 (기본값은 FALSE).

DECLARE
   job_number BINARY_INTEGER;
BEGIN
   DBMS_JOB.submit(
      job        => job_number,
      what       => 'my_procedure;', -- 실행할 프로시저 또는 PL/SQL
      next_date  => SYSDATE,         -- 최초 실행 시간
      interval   => 'TRUNC(SYSDATE + 1)' -- 매일 자정 실행
   );
   COMMIT;
END;

4. DBMS_JOB.remove 예제

등록된 작업을 제거할 때 remove 프로시저를 사용한다.

-- 특정 작업 번호(job_number)의 작업을 제거
BEGIN
   DBMS_JOB.remove(job_number);
   COMMIT;
END;

5. Step 1: 테이블과 Sequence, Procedure 생성

테스트용 테이블(job_teste1)과 Sequence(seq_job_seq1), 그리고 데이터를 삽입하는 프로시저(insert_job_teste1)를 생성한다.

Step 2: Job 등록

이 스크립트를 실행하면 DBMS_JOB 패키지에 작업이 등록되고, 1분마다 insert_job_teste1 프로시저가 자동으로 실행된다.

참고: 시간 설정 방법

10분에 한 번 실행: SYSDATE + 1/24/6 또는 SYSDATE + 1/144
1분에 한 번 실행: SYSDATE + 1/24/60 또는 SYSDATE + 1/1440
5분에 한 번 실행: SYSDATE + 5/24/60
매일 새벽 2시 실행: TRUNC(SYSDATE) + 1 + 2/24
매일 밤 11시 실행: TRUNC(SYSDATE) + 23/24

각 주기 설정 시, 특정 시간에 정확히 실행되지 않고 몇 초 정도의 차이가 발생할 수 있다.

Step 3: Job 번호 확인 및 Commit

Job이 성공적으로 등록되면 job1.sql 스크립트를 통해 작업 번호를 확인한다.

RAC 환경에서 특정 노드에 Job 할당 (옵션)

RAC (Real Application Clusters) 환경에서 특정 노드에 Job을 할당해야 하는 경우, 다음과 같이 설정할 수 있다.

EXEC DBMS_JOB.INSTANCE(job => 25, instance => 1);

Step 4: 실행 중인 Job 내역 확인

현재 실행되고 있는 Job의 정보를 user_jobs 뷰를 통해 확인할 수 있다.

Step 5: 데이터 삽입 확인

Job이 정상적으로 실행되었는지 확인하기 위해 job_teste1 테이블을 조회한다.

6. 등록된 Job 삭제하기

등록된 Job 수정하기

Job 재등록 및 Commit

현재 Job 상태 확인

Job 간격 수정

Job의 수행 간격을 변경하려면 DBMS_JOB.INTERVAL 프로시저를 사용한다. , 5분마다 실행하도록 설정하려면 다음과 같이 한다.

▶JOB 삭제

7. DBMS_SCHEDULER

Oracle에서 DBMS_SCHEDULER는 DBMS_JOB보다 확장된 기능을 제공하는 강력한 스케줄링 도구이다. 특히 외부 스크립트를 실행할 수 있어 데이터베이스 작업 자동화에 매우 유용하며 Oracle 10g 이상 버전에서 사용할 수 있다.

주요 특징

DBMS_SCHEDULER는 기존 DBMS_JOB보다 다양한 기능을 제공한다:

외부 프로그램 실행: OS 스크립트 등을 직접 실행할 수 있다.
유연한 스케줄 설정: 다양한 시간 및 주기 설정이 가능하여 복잡한 작업 주기를 지정할 수 있다.
다양한 프로그램 유형 지원: SQL 작업, PL/SQL 프로그램, 스크립트 등을 실행할 수 있다.

구성 요소

DBMS_SCHEDULER는 다음 세 가지 주요 구성 요소로 이루어져 있다:

Program
DBMS_SCHEDULER가 수행할 작업(프로그램, 프로시저, 외부 스크립트 등)을 정의한다.
Schedule
작업의 실행 시간, 주기 등을 설정한다.
Job
프로그램과 스케줄을 결합하여 새로 생성할 작업(Job)을 정의한다.

Step-by-Step Guide to Creating a Job Using DBMS_SCHEDULER

사전 설정 scott 계정에 CREATE ANY JOB 권한을 부여해야 한다
테이블 및 시퀀스 생성 데이터가 삽입될 테이블과 이를 위한 시퀀스를 만든다.
프로시저 생성 insert_job_test라는 프로시저를 작성하여 테이블에 랜덤 데이터를 삽입한다.
Job 생성 및 스케줄 설정 DBMS_SCHEDULER 패키지를 이용해 3초마다 insert_job_test 프로시저를 실행하는 Job을 생성한다.
- job_name: 생성할 Job의 이름
- job_type: PLSQL_BLOCK 타입으로 지정
- job_action: 실행할 작업으로 insert_job_test 프로시저 호출
- start_date: Job 시작 시간 (현재 시간 설정)
- repeat_interval: 3초마다 실행되도록 설정
- enabled: Job을 활성화

5.JOB활성화

6. Job 수동 실행

7. Session 날짜 형식 설정

8. 데이터 삽입 확인

9. 현재 작동 중인 Job 확인하기

특정 Job 일시 중지 또는 재시작하기

특정 Job 삭제하기

반복 주기(repeat_interval) 표현 방법

일정한 시간 간격으로 수행

repeat_interval => 'FREQ=HOURLY; INTERVAL=1'  -- 1시간마다 수행
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30'  -- 30분마다 수행
repeat_interval => 'FREQ=SECONDLY; INTERVAL=5'  -- 5초마다 수행

복잡한 주기 설정 매년 8주차 금요일마다, 23시 5분에 수행하고자 하는 경우:

repeat_interval => 'FREQ=YEARLY; BYWEEKNO=8,16,24,32,40,48; BYDAY=FRI; BYHOUR=23; BYMINUTE=5;'

예시: PL/SQL 블록 사용

BEGIN
    dbms_scheduler.create_job (
        job_name     => 'gather_stats_job',
        job_type     => 'PLSQL_BLOCK',
        job_action   => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS("SCOTT"); END;',
        start_date   => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2',
        enabled      => TRUE
    );
END;
/

예시: 실행 프로그램 사용

외부 실행 파일을 호출하는 작업을 생성하려면 program_type을 EXECUTABLE로 지정하고 program_action에 스크립트 경로를 입력한다.

BEGIN
    dbms_scheduler.create_job (
        job_name     => 'run_external_script',
        job_type     => 'EXECUTABLE',
        job_action   => '/home/oracle/run.sh',
        start_date   => SYSTIMESTAMP,
        repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
        enabled      => TRUE
    );
END;
/

job_type이 'PLSQL_BLOCK'이면 program_action에 PL/SQL 코드 블록을 입력한다.
job_type이 'STORED_PROCEDURE'이면 program_action에 저장 프로시저 이름을 입력한다.
job_type이 'EXECUTABLE'이면 program_action에 실행할 외부 프로그램의 경로를 입력한다.

BY 절 옵션 설명

BYMONTH: 특정 월을 지정할 때 사용한다.
- 예: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
BYWEEKNO: 특정 주를 지정한다.
- 예: 1-53 (1주에서 53주)
BYYEARDAY: 연도 내 특정 일을 지정한다.
- 예: 1-366
BYMONTHDAY: 특정 달의 일을 지정한다.
- 예: 1-31
BYDATE: 특정 날짜를 지정한다. 1월 1일이라면 '0101'로 설정한다.
BYDAY: 특정 요일을 지정한다.
- 예: MON, TUE, WED, THU, FRI, SAT, SUN
BYHOUR: 특정 시간을 지정한다.
- 예: 0-23
BYMINUTE: 특정 분을 지정한다.
- 예: 0-59
BYSECOND: 특정 초를 지정한다.
- 예: 0-59

예시: 스케줄 생성

특정 요일과 시간에 작업 실행 예시

매주 금요일 오후 11시 50분에 실행되도록 설정한다.

BEGIN
    dbms_scheduler.create_job(
        job_name        => 'weekly_job',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN my_procedure(); END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=23; BYMINUTE=50;',
        enabled         => TRUE
    );
END;
/

INCLUDE를 사용하여 다른 스케줄 포함하기

새로운 스케줄에 기존 스케줄을 포함시켜 복합적인 스케줄을 구성할 수 있다.

BEGIN
    dbms_scheduler.create_schedule(
        schedule_name   => 'sub_sched_1',
        repeat_interval => 'FREQ=YEARLY; BYDATE=0101, 0201, 0301'
    );

    dbms_scheduler.create_schedule(
        schedule_name   => 'main_sched',
        repeat_interval => 'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15; BYHOUR=9,17; INCLUDE=sub_sched_1'
    );
END;
/

sub_sched_1: 매년 1월 1일, 2월 1일, 3월 1일에 실행된다.
main_sched: 두 달에 한 번, 매월 15일 오전 9시와 오후 5시에 실행되며, sub_sched_1을 포함하여 추가적으로 실행된다.

다음 작업 실행 간격 지정하기

DBMS_SCHEDULER에서 다음 작업까지의 간격을 지정할 수 있다. 아래와 같이 설정할 수 있다:

하루에 한 번 실행:
- 하루 간격으로 작업이 수행된다.
  repeat_interval => 'SYSDATE + 1'
특정 시간 간격 지정 (예: 36분마다):
- 여기서 30/1440은 36분을 의미하며, 해당 간격으로 작업이 수행된다.

epeat_interval => 'SYSDATE + (30/1440)'

외부 스크립트 실행하기

DBMS_SCHEDULER의 중요한 기능 중 하나는 외부에서 생성한 스크립트를 Oracle에서 실행할 수 있다는 것이다. 이를 설정하기 위해서는 권한과 그룹 설정이 필요한 externaljob.ora 파일을 수정해야 한다.

externaljob.ora 설정 방법

파일 위치: 일반적으로 $ORACLE_HOME/rdbms/admin/에 위치한 externaljob.ora 파일을 연다.
사용자와 그룹 설정:
- 사용자: oracle로 변경한다.
- 그룹: oracle 계정의 소속 그룹으로 변경한다.
- id조회

외부 작업 실행을 위한 설정

외부 작업 설정

externaljob.ora 파일 설정은 기본적으로 되어 있지만, 추가 확인이 필요하다.

파일 위치 확인:

extjob 파일의 권한 설정:

외부 작업 실행 파일인 extjob에 SetUID를 설정한다.
기본적으로 oracle 사용자가 설정되어 있어야 한다.

주의: 권한이 제대로 설정되지 않으면 Permission denied 에러가 발생할 수 있다.

외부 작업 실행 권한 부여

외부 작업을 실행할 계정에 권한을 부여하기 위해 SYS 계정으로 Oracle에 접속하여 다음 권한을 부여한다.

DBMS_SCHEDULER 실행 권한:

 GRANT EXECUTE ON sys.dbms_scheduler TO scott;

작업 생성 권한:
```
GRANT CREATE JOB TO scott;
```
외부 작업 생성 권한:

 GRANT CREATE EXTERNAL JOB TO scott;

외부 스크립트 생성 및 테스트

스크립트 생성: 외부 스크립트 mkdir.sh를 생성하여 매번 실행 시 날짜와 시간을 기준으로 디렉터리를 자동으로 생성하도록 한다.
- date 명령어는 현재 시간을 YYYY-MM-DD-HH-MM-SS 형식으로 가져오고, 이 값을 사용해 /app/oracle/ 하위에 디렉터리를 생성한다.

실행 권한 부여:

[oracle@localhost ~]$ chmod 755 mkdir.sh

스크립트 실행 테스트:
디렉터리 생성 확인:

Step 6: 스케줄 생성

DBMS_SCHEDULER 스케줄 생성: 10초 간격으로 mkdir.sh 스크립트를 실행하는 스케줄을 생성한다.
결과:
- 위 스케줄이 성공적으로 생성되면 매 10초마다 mkdir.sh가 실행되며, /app/oracle/ 디렉터리에 날짜 기반 폴더가 자동으로 생성된다.

외부 스크립트를 실행하는 Job 생성

Job 생성:
- mkdir.sh 스크립트를 주기적으로 실행하기 위한 Job을 생성한다.

Job 활성화 (Enable)

Job 활성화:
- 생성한 Job을 활성화하여 스케줄에 따라 실행될 수 있도록 설정한다.

Job 즉시 실행

Job 즉시 실행:
- 설정된 스케줄과 관계없이 즉시 Job을 실행하여 동작을 확인한다.

디렉터리 생성 확인

생성된 디렉터리 확인:
Oracle에서 확인:

이상으로 DBMS_SCHEDULER를 활용하여 외부 스크립트를 주기적으로 실행하는 Job을 생성, 활성화, 테스트하는 과정이 완료되었다.

Job 실행 로그 확인ob 실행 로그 조회:

SCOTT 유저의 Job 실행 기록을 확인하여 Job이 정상적으로 수행되었는지 확인한다.

 SELECT log_date, owner, job_name, status
FROM user_scheduler_job_run_details
WHERE owner = 'SYS'
ORDER BY log_date DESC;

'DBMS > ORACLE Admin' 카테고리의 다른 글

오라클 아키텍쳐 01 (0)	2024.11.02
오라클 관리 04 ORACLE Net Service (0)	2024.10.27
오라클 관리 02 사용자 (0)	2024.10.24
오라클 관리 01 메모리 관리 (0)	2024.10.23
오라클 인프라 (6)	2024.10.06

JJ의메모장

태그

최근글

댓글

공지사항

아카이브