dbms scheduler 를 이용한 backup 정책 구현

Author 서강혁,윤병길

Creation Date 2008-09-01

Last Updated

Version 1.0

Copyright(C) 2004 Goodus Inc.

All Rights Reserved

DBMS_SCHEDULER 를

이용한 Backup 정책 구현

Version 변경일자 변경자(작성자) 주요내용

1 2008-09-01 서강혁,윤병길 문서 최초 작성

DBMS_SCHEDULER 를 이용한 백업정책 구현

- 2 – 본 산출물의 내용은 굿어스㈜의 사전승인 없이

외부유출 및 공개를 금지합니다.

Contents

1. Overview ............................................................................................................................3

1.1. Backup정책 Architecture. ........................................................................................3

1.2. Backup 내용 .............................................................................................................4

2. 관련 oracle package ...........................................................................................................5

2.1. DBMS_SCHEDULER..................................................................................................5 2.1.1. dbms_scheduler 적용사례.......................................................................................... 5

2.1.2. dbms_scheduler.set_attribute 적용사례..................................................................... 6

2.1.3. dbms_scheduler package내 chain procedure 적용사례 ........................................... 7

2.2. DBMS_DATAPUMP ................................................................................................ 10 2.2.1. dbms_datapump 적용사례 ....................................................................................... 10 2.2.2. How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ............. 10

2.3. DBMS_FILE_TRANSFER........................................................................................ 12 2.3.1. asm 에서 asm diskgroup으로 file transfer 실행 예제 ............................................ 12 2.3.2. dbms_file_transfer sub procedure ............................................................................ 12

2.4. DBMS_AQ.............................................................................................................. 13 2.4.1. Advanced queue(AQ) 개요....................................................................................... 13

2.4.2. Dbms_aq 예제 .......................................................................................................... 14

3. Rman backup schedulering ..............................................................................................16

3.1. os shell script 작성 ................................................................................................ 16

3.2. Rman backup scheduler job 생성 ....................................................................... 17

3.3. Event based job scheduler 생성 ....................................................................... 18

3.4. external job 수행시 ORA-27369 처리 방안 .......................................................... 21

4. datapump backup schedulering .......................................................................................23

4.1. datapump data,log directory 생성 ........................................................................ 23

4.2. schema별 datapump scheduler program 생성 ..................................................... 23

4.2.1. SCMA1 Schema scheduler program 생성 ............................................................... 23

4.2.2. SCMA2 schema scheduler program 생성 ................................................................ 25

4.3. 보관주기가 지난 datapump backupfile 삭제 script ................................................ 26 4.3.1. os shell script............................................................................................................. 26 4.3.2. scheduler program .................................................................................................... 26

4.4. scheduler chain 생성............................................................................................. 27

4.5. scheduler class 생성.............................................................................................. 28

4.6. scheduler job 생성 ................................................................................................ 28

5. File_transfer backup schedulering...................................................................................31

5.1. stored procedure 생성 .......................................................................................... 31

5.2. job class 생성 ........................................................................................................ 32

5.3. job program 생성 .................................................................................................. 33

5.3.1. file_transfer 실행 job program 생성 ........................................................................ 33

5.3.2. os shell script 생성 ................................................................................................... 33

5.3.3. backup file remove job program 생성....................................................................... 33

5.4. job chain 생성 ....................................................................................................... 34

5.5. job scheduler 생성 ................................................................................................ 34

6. Remote Table Insert schedulering.................................................................................37




1. Overview Dbms_scheduler package(이하 스케줄러) 에서 os externaljob, pl-sql block, stored-procedure를 이용한

schedulering 사례를 살펴보고, event 기반 스케줄러(event based scheduler)를 이용하여 특정 event를 발

생시킨 후,그 결과값을 dbms_aqadm.dequeue로 받아 table에 저장하거나 또는 다른 이벤트의 연결고리

로 사용하는 다양한 스케줄러의 기능을 활용하여 전체 rdbms의 backup정책을 3rd part solution이 아닌

순수한 oracle package를 이용하여 구현해 보고자 한다

1.1. Backup정책 Architecture.




1.2. Backup 내용 백업단위 Job_name 비교

RMAN_BACKUP_ZERO 매주 월요일 am 2:00 Rman Backup

RMAN_BACKUP_ONE 월요일을 제외한 매일 am 2:00

DataPump Backup PUMP_BK_JOB_SCMA 주요 schema 별 table backup

FileTransfer Backup FTRANS_BACKUP asm diskgroup 내 rman backup

filesystem 으로 backup

백업기록 저장 INSERT_REMOTE_TAB Remote db 로 backup 내용 저장




2. 관련 oracle package

2.1. DBMS_SCHEDULER

2.1.1. dbms_scheduler 적용사례

■ PLSQL-BLOCK 삽입 program

BEGIN

DBMS_SCHEDULER.create_program (

program_name => 'test_plsql_block_prog',

program_type => 'PLSQL_BLOCK',

program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',

enabled => TRUE,

comments => 'Program to gather SCOTT''s statistics using a PL/SQL block.');

end;

/

■ external job(os shell script) 삽입 program

EXEC DBMS_SCHEDULER.create_program (

program_name => 'test_executable_prog',

program_type => 'EXECUTABLE',

program_action => '/u01/app/oracle/dba/gather_scott_stats.sh',

number_of_arguments => 0,

enabled => TRUE,

comments => 'Program to gather SCOTT''s statistics us a shell script.');

■ Stored Procedure 삽입 program.

EXEC DBMS_SCHEDULER.create_program (

program_name => 'test_stored_procedure_prog',

program_type => 'STORED_PROCEDURE',

program_action => 'DBMS_STATS.gather_schema_stats',

number_of_arguments => 1,

enabled => FALSE,

comments => 'Program to gather SCOTT''s statistics using a stored

procedure.');

■ program,schedule,job 을 모두 포함하는 job

EXEC DBMS_SCHEDULER.create_job (

job_name => 'test_full_job_definition',




job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'freq=hourly; byminute=0',

end_date => NULL,

enabled => TRUE,

comments => 'Job defined entirely by the CREATE JOB procedure.');

■ program,schedule 을 포함하는 job


job_name => 'test_prog_sched_job_definition',


schedule_name => 'test_hourly_schedule',

enabled => TRUE,

comments => 'Job defined by an existing program and schedule.');

■ program 을 포함하는 job


job_name => 'test_prog_job_definition',



repeat_interval => 'freq=hourly; byminute=0',

end_date => NULL,

enabled => TRUE,

comments => 'Job defined by existing program and inline schedule.');

2.1.2. dbms_scheduler.set_attribute 적용사례

BEGIN

DBMS_SCHEDULER.set_attribute (

name => 'hourly_schedule',

attribute => 'repeat_interval',

value => 'freq=hourly; byminute=30');

END;

/




2.1.3. dbms_scheduler package내 chain procedure 적용사례

연결된 여러 job 을 순차적으로 실행하고자 할 때 예를 들어 job1 이 성공한 후에 job2 가 실행되어야 하

는 상황이 발생할 때 스케줄러 내부의 chin proceduer 를 사용하여 순차적인 job 이 실행되도록 할 수

있다.

■ chain 생성

BEGIN

DBMS_SCHEDULER.create_chain (

chain_name => 'cafebackupchain1',

rule_set_name => NULL,

evaluation_interval => NULL,

comments => 'cafe3backupchain');

END;

/

■ chaining 할 program 생성

서로 연결할 program 을 생성한다.

BEGIN

DBMS_SCHEDULER.CREATE_PROGRAM (

program_name => 'Backup_Incr_zero_Chain1',

program_action => '/oracle/Testbackup/rman/backup_zero.sh',


enabled => TRUE,

comments => 'full backup on monday am two');


program_name => 'Backup_Incr_one_Chain1',

program_action => '/oracle/Testbackup/rman/backup_one.sh',


enabled => TRUE,

comments => 'incremental backup except monday on week am two');

END;

/

■ chain step 정의

Chain 을 구성할 프로그램을 진행순서에 따라 chain step 을 정의한다.

BEGIN




DBMS_SCHEDULER.define_chain_step (


step_name => 'chain_step_1',

program_name => 'Backup_Incr_zero_Chain1');




program_name => 'Backup_Incr_one_Chain1');




program_name => 'FTP_BACKUP_FILESYSTEM_Chain2');

END;

/

■ 실제적으로 각 step 이 어떻게 수행될 것인지 chain rule 에 대해 정의한다. condition 의 경우는 논

리 연산으로 조건을 부여하고, action 의 경우는 시작 interval 지정이 가능하다.

또한, 동시에 두 개의 step 을 수행하도록 설정이 가능하다.

Chain 진행 순서

1. chain_step_1 이 매주 월요일 START 한다.

2. chain_step_2 가 매주 월요일을 제외한 요일에 START 한다.

3. chain_step_1 또는 chain_step_2 가 성공하면 10 분 후에

4. chain_step_3 이 실행된다.

5. Chain_step_3 이 completed 되면 종료한다.

BEGIN

DBMS_SCHEDULER.define_chain_rule (


condition => 'TRUE',

action => 'START chain_step_1',

rule_name => 'chain_rule_1',

comments => '1st step in this chain on monday');



condition => 'TRUE',




action => 'START chain_step_2',


comments => '2nd step in this chain on except monday');



condition => 'chain_step_1 SUCCEEDED OR chain_step_2 SUCCEEDED',

action => 'AFTER 00:10:00 START chain_step_3',


comments => '3rd step in this chain');


Chain_name=> 'cafebackupchain1',

Condition=>,'chain_step_3 completed'

Action=>'end');

END;

/

■ chain 이 정의된 job 생성

매일 am 2 시 10 분에 수행될 chain type 의 스케줄러 job 을 생성한다.

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'cafebackupchain1_job',

job_type => 'CHAIN',

job_action => 'cafebackupchain1',

repeat_interval => 'freq=daily;byhour=2;byminute=10',

enabled => TRUE);

END;

/




2.2. DBMS_DATAPUMP

이 package 는 대량의 data/meta data 를 전송하기 위한 expdp/impdp 유틸리티 API 를 구현하

고 있다. user 가 expdp scott/tiger, impdp scott/tiger 로 시작하는 command 는 실제 이 dbms_datapu

mp 를 통해 작동하게 됩는 것이다.

그러므로 우리가 expdp 나 impdp 를 통해 할 수 있는 모든 작업은 dbms_datapupm 를 통해서

도 구현 가능하다.

2.2.1. dbms_datapump 적용사례

declare

handle number;

begin

handle := dbms_datapump.open ('EXPORT', 'SCHEMA'); ->datapump 를 scmema type 으로 open

dbms_datapump.add_file(handle, 'avail.dmp', 'BTU'); ->BTU pump directory 밑에 avail.dmp file 로

expdp 수행

dbms_datapump.metadata_filter(handle, 'SCHEMA_EXPR','=''SCOTT''); ->’SCOTT’ schema

data&meta data

dbms_datapump.set_parallel(handle, 2); ->parallel process 2

dbms_datapump.start_job(handle); ->expdp start

dbms_datapump.detach(handle); -> 수행완료 후 detach

end;

/

2.2.2. How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS

Datapump 수행중 예기치 못한 장애로 pump 가 중단되는 경우 pump job table이 detach되지 못한 상

황이 발생할 수 있다. 이를 clear하기 위해서 아래과정을 수행한다.

■ ‘NOT RUNNING’ 상태의 data pump job 확인

CONNECT / as sysdba

SET lines 200

COL owner_name FORMAT a10;

COL job_name FORMAT a20

COL state FORMAT a11

COL operation LIKE state

COL job_mode LIKE state

-- locate Data Pump jobs:




SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

WHERE job_name NOT LIKE 'BIN$%'

ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED

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

SCOTT EXPDP_20051121 EXPORT SCHEMA EXECUTING 1

SCOTT SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0

SCOTT SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0

SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

■ Job Master Table 확인

SELECT o.status, o.object_id, o.object_type,

o.owner||'.'||object_name "OWNER.OBJECT"

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT

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

VALID 85283 TABLE SCOTT.EXPDP_20051121

VALID 85215 TABLE SCOTT.SYS_EXPORT_TABLE_02

VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

■ 삭제하고자 하는 master table 삭제

DROP TABLE scott.sys_export_table_02;

■ datapump 재실행 후 해당 pump job clear

CONNECT scott/tiger

SET serveroutput on

SET lines 100

DECLARE

h1 NUMBER;

BEGIN

h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT');

DBMS_DATAPUMP.STOP_JOB (h1);

END;

/




2.3. DBMS_FILE_TRANSFER 이기종 platform, asm diskgroup 에서 다른 asm diskgroup 이나 filesystem 으로,혹은 filesystem 에서 asm

diskgroup 으로 data 를 이전하고자 할 때 사용할 수 있는 package 이다.

2.3.1. asm 에서 asm diskgroup으로 file transfer 실행 예제

■ target ,source db directory 생성

TargetDB

CREATE OR REPLACE DIRECTORY df AS '+DGBACKUP2/cafe2db/controlfile ';

GRANT WRITE ON DIRECTORY df TO system;

SourceDB

CREATE OR REPLACE DIRECTORY ft1 AS '+DGBACKUP2/cafe2db/backupset/2008_06_20';

GRANT READ,WRITE ON DIRECTORY ft1 TO system;

■ db link 생성

Remote target db 에 access 하기 위한 db link 생성

SourceDB

CREATE DATABASE LINK DL_CAFE3 CONNECT TO system IDENTIFIED BY zkvp_10g

USING 'CAFE3DB1';

■ file_transfer 수행

exec

DBMS_FILE_TRANSFER.PUT_FILE('ft1','nnndn0_tag20080619t020016_0.294.657770421','df','backup1.dbf','DL

_CAFE3'); ->(Sourcedb_dir,Sourcedb_file,Targetdb_dir,db_link,Targetdb_file)

exec

DBMS_FILE_TRANSFER.PUT_FILE('ft1','nnndn0_tag20080619t020016_0.366.657770421','df','backup2.dbf','DL

_CAFE3');

2.3.2. dbms_file_transfer sub procedure

Procedure Name Destination

COPY_FILE Source,target db 모두 local filesystem 이거나 asm diskgroup

GET_FILE Remote -> Local

PUT_FILE Local -> Remote




2.4. DBMS_AQ

2.4.1. Advanced queue(AQ) 개요

(1) Message

message 란 queue 에 들어가고 꺼내지는 정보의 가장 작은 단위이다. 이것은 raw type 이나 object type

인 user data (payload)와 priority 등의 추가적인 제어 정보를 담고 있는 meta data 로 구성되어 있다. 하

나의 메시지는 오직 하나의 queue 에만 위치할 수 있으면 ENQUEUE procedure 에 의해 queue 에 들어

가고 DEQUEUE procedure 에 의해 queue 에서 읽혀진다.

(2) Queue

queue 는 메시지들의 저장 장소라고 할 수 있다. User queue 와 exception queue 라는 두가지 type 이 존

재하는데 이 중 user queue 가 정상적인 message 처리를 위한 것이고 오류로 인해 제대로 처리되지 못

한 메시지는 exception queue 로 전달된다.

데이타베이스내에 생성될 수 있는 queue 의 숫자에는 제한이 없으며, DBMS_AQADM package 에 의해서

생성/변경/시작/멈춤/삭제되어질 수 있다.

(3) Queue Tables

queue 는 queue table 에 저장되어진다. 각각의 queue table 은 하나의 데이타베이스 테이블로 구성되며

하나 이상의 queue 들을 포함한다. 각각의 queue table 에는 하나의 default exception queue 이 존재하

며 DBMS_AQADM package 를 이용하여 생성할 수 있다.

(4) Agents

agent 는 queue user 로 producer 와 consumer 두가지 type 이 있다. producer 는 message 를 queue 에

전달하는데 이것을 enqueuing 이라고 하고, consumer 가 queue 에서 message 를 읽는 것을 dequeuing




이라고 한다. 여러명의 producer 와 consumer 가 하나의 queue 를 사용할 수 있으며 하나의 agent 는

name, address, protocol 에 의해 구분된다. 그런데 이 agent 는 실제 database 상의 user 는 아니며 실제

로는 application 혹은 program 이라고 생각할 수 있다.

2.4.2. Dbms_aq 예제

SQL> CREATE TYPE message_type AS OBJECT ( dg_name VARCHAR2(50),dg_pct number );

2 /

Type created.

SQL> BEGIN

2 dbms_aqadm.create_queue_table(queue_table => 'dg_qtab',

3 queue_payload_type => 'message_type');

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 dbms_aqadm.create_queue( queue_name => 'dg_q',

3 queue_table => 'dg_qtab' );

4 END;

5 /


SQL> BEGIN

2 dbms_aqadm.start_queue(queue_name=>'dg_q');

3 END;

4 /


SQL> CREATE OR REPLACE PROCEDURE p_enqueue(msg IN VARCHAR2)

2 AS

3 PRAGMA AUTONOMOUS_TRANSACTION;

4 enqueue_options dbms_aq.enqueue_options_t;

5 message_properties dbms_aq.message_properties_t;

6 message_handle RAW(16);




7 BEGIN

8 dbms_aq.enqueue( queue_name => 'example_queue',

9 enqueue_options => enqueue_options,

10 message_properties => message_properties,

11 payload => message_type(msg),

12 msgid => message_handle);

13 COMMIT;

14 END;

15 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p_dequeue

2 AS

3 PRAGMA AUTONOMOUS_TRANSACTION;

4 dequeue_options dbms_aq.dequeue_options_t;

5 message_properties dbms_aq.message_properties_t;

6 message_handle RAW(16);

7 message message_type;

8 BEGIN

9 dbms_aq.dequeue( queue_name => 'example_queue',

10 dequeue_options => dequeue_options,

11 message_properties => message_properties,

12 payload => message,

13 msgid => message_handle);

14 dbms_output.put_line('Message : ' || message.message_body);

15 COMMIT;

16 END p_dequeue;

17 /

Procedure created.

SQL> set serverout on

SQL> EXEC p_enqueue('THIS IS A TEST MESSAGE');

SQL> EXEC p_dequeue

Message : THIS IS A TEST MESSAGE




3. Rman backup schedulering

3.1. os shell script 작성 rman backup 을 수행하기 위한 shell script 를 작성한다.

Backupset 당 5 개의 datafile 을 asm disk group 인 DGBACKUP1 에 압축(compressed) 백업을

수행하며,백업 후 관련 archive log file 은 모두 삭제한다.

[/oracle/Testbackup/rman> more backup_zero.sh

#!/usr/bin/ksh

export O_SID=`ps -ef | grep pmon | grep CAFE |awk '{print$8}'|cut -d"_" -f3`

export ORACLE_SID=$O_SID

export ORACLE_HOME=/oracle/product/10.2.0

/oracle/product/10.2.0/bin/rman target system/oracle append log /oracle/Testbackup/rman/zero.log

<<EOF

crosscheck copy of archivelog all;

delete noprompt expired copy;

run{

allocate channel ch1 type disk;




backup as compressed backupset incremental level 0 filesperset 5

format='+DGBACKUP1' database plus archivelog DELETE ALL INPUT;

release channel ch1;




}

DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 14 DAYS;

list backup of database;

list backup of controlfile;

list backup of archivelog all;

list backup of spfile;

crosscheck backup;

EOF




3.2. Rman backup scheduler job 생성 위에서 생성한 shell을 호출하는 스케줄러를 생성하고 그 스케줄러가 매주 월요일 am 2:10 에

수행되도록 한다.

이 job의 attribute를 해당 job이 start,stop,,succeed,fail,broken 상태가 되었을 때 이에 대한 event가

발생하도록 설정한다.

REM############################################

REM# incremental 0 Level Backup Scheduler »ý¼º

REM# Run Every Monday AM 2:00

REM############################################

exec DBMS_SCHEDULER.drop_job('RMAN_BACKUP_ZERO');

begin


job_name=>'RMAN_BACKUP_ZERO',

job_type=>'EXECUTABLE',

job_action=>'/oracle/Testbackup/rman/backup_zero.sh',

repeat_interval=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=10',

enabled=>true,

comments=>'INCREMENTAL LEVEL 0 ON MONDAY');

end;

/

REM############################################

REM# Event 속성 설정

REM############################################

begin

DBMS_SCHEDULER.SET_ATTRIBUTE (

name =>'RMAN_BACKUP_ZERO',

attribute=>'raise_events',

value=>DBMS_SCHEDULER.job_started

+DBMS_SCHEDULER.job_succeeded

+DBMS_SCHEDULER.job_failed

+DBMS_SCHEDULER.job_broken

+DBMS_SCHEDULER.job_stopped);

end;

/




3.3. Event based job scheduler 생성 rman backup 의 결과 event 를 받아서 backupresult table 에 backup 결과를 저장하는 event based

scheduler job 을 생성한다. 1>oracle에서는 기본적으로 “SCHEDULER$_EVENT_QTAB” queue table이 존재한다. 이 queue table을

기반으로 “SCHEDULER$_EVENT_QUEUE” queue가 있다. 따라서 별도의 queue table이나 queue를

생성하지 않는 이상 oracle 내부에 발생하는 모든 event message는 모두 SCHEDULER$_EVENT_QUEUE에

쌓이게 된다.

SQL>select name,queue_table,queue_type from DBA_QUEUES;

NAME QUEUE_TABLE QUEUE_TYPE

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

………………………………………………………………………………………………………………………………………………………….

SCHEDULER$_EVENT_QUEUE SCHEDULER$_EVENT_QTAB NORMAL_QUEUE

AQ$_SCHEDULER$_EVENT_QTAB_E SCHEDULER$_EVENT_QTAB EXCEPTION_QUEUE

……………………………………………………………………………………………………………………………………………………………

2>Dbms_scheduler package 에는 이 SCHEDULER$_EVENT_QUEUE 에 subscriber 를 추가할 수 있는

add_event_queue_subscriber procedure 가 있다. 이 procdeure 에 subscriber 를 추가하고 access 가능한

user 를 등록하면 스케줄러 package 에서 발생하는 event 의 dequeue 정보를 확인할 수 있다. 이 부분이

① 이다.

3>앞에서 스케줄러 job이 작동하고 나면 그 결과를 event로 처리되도록 하는 set_attribute()

procedure를 설정했다. 이렇게 되면 스케줄러 job에서 발생하는 이벤트는 SCHEDULER$_EVENT_QTAB에

아래와 같은 형식으로 저장된다.

SQL>select q_name,msgid,user_data from SCHEDULER$_EVENT_QTAB;

Q_NAME MSGID

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

USER_DATA(EVENT_TYPE, OBJECT_OWNER, OBJECT_NAME, EVENT_TIMESTAMP, ERROR_CODE, ER

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

SCHEDULER$_EVENT_INFO('JOB_STARTED', 'SYSTEM', 'FTRANS_BACKUP', '10-A

UG-08 07.10.26.097527 PM +09:00', 0, NULL, 1, NULL, 2, 0, 0, NULL, NULL, NULL, N

ULL, NULL, NULL, NULL, NULL)

AQ$_SCHEDULER$_EVENT_QTAB_E 53F040B65D8281D7E0401E0A2D7C63D3

SCHEDULER$_EVENT_INFO('JOB_SUCCEEDED', 'SYSTEM', 'FTRANS_BACKUP', '10

-AUG-08 07.10.26.923493 PM +09:00', 0, NULL, 0, 2042, 3, 0, 0, NULL, NULL, NULL,

NULL, NULL, NULL, NULL, NULL)




그리고 SCHEDULER$_EVENT_QTAB의 description을 살펴보면

SQL> desc SCHEDULER$_EVENT_QTAB

Name Null? Type

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

Q_NAME VARCHAR2(30)

MSGID NOT NULL RAW(16)

………………………………………………………………………………………………………………………

ENQ_TIME TIMESTAMP(6)

ENQ_UID VARCHAR2(30)

ENQ_TID VARCHAR2(30)

DEQ_TIME TIMESTAMP(6)

DEQ_UID VARCHAR2(30)

DEQ_TID VARCHAR2(30)

………………………………………………………………………………………………………………………

DEQUEUE_MSGID RAW(16)

………………………………………………………………………………………………………………………….

USER_DATA SCHEDULER$_EVENT_INFO

USER_PROP ANYDATA

dequeue정보를 가져오는 데 필요한 Q_NAME과 USER_DATA정보를 확인 할 수 있다.

이를 기반으로 발생한 이벤트의 정보를 DEQUEUE하는 EVENT BASED JOB SCHEDULER를 생성한다.

1. Dequeue 정보를 가져오는 agent는 앞에서 생성한 AQAGENT이다. 이 부분이 ② 이다.

2. 발생한 이벤트 DEQUEUE정보는 DBMS_AQ.DEQUEUE() package를 이용해서 가져온다.

Queue 이름은 별도의 queue table과 queue를 생성하지 않았으므로 SCHEDULER$ _EVENT_QTA

3. B table의 SCHEDULER$_EVENT_QUEUE queue를 이용한다. 이 부분이 ③ 이다.

4. job에서 dequeue 정보를 가져올 event_condition은 SCHEDULER$_EVENT_QTAB의 USER_DATA

TYPE 컬럼중에 OBJECT_NAME이 “RMAN_BACKUP_ZERO”이고 OBJECT_OWNER가 “SYSTEM”인

경우이다. 이 부분이 ④이다.

5. This argument specifies the queue into which events that start this particular job will be

enqueued (the source queue). If the source queue is a secure queue, the queue_spec argument

is a string containing a pair of values of the form queue_name, agent name. ⑤

6. EVENT DEQUEUE 정보를 BackupResult Table 에 insert 한다. ⑥




REM############################################

REM# create Event Based Scheduler (EVENT_BASED_JOB1)

REM############################################

exec dbms_scheduler.remove_event_queue_subscriber('aqagent');

exec dbms_scheduler.add_event_queue_subscriber('aqagent');

BEGIN

DBMS_AQADM.ENABLE_DB_ACCESS(

agent_name => 'AQAGENT',

db_username => 'SYSTEM');

END;

/

exec DBMS_SCHEDULER.drop_job ('event_based_job1');

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'event_based_job1',


job_action => 'DECLARE

l_dequeue_options DBMS_AQ.dequeue_options_t;

l_message_properties DBMS_AQ.message_properties_t;

l_message_handle RAW(16);

l_queue_msg sys.scheduler$_event_info;

v_inst varchar2(20);

BEGIN

l_dequeue_options.consumer_name :=''AQAGENT''; ②

DBMS_AQ.dequeue(queue_name => ''SYS.SCHEDULER$_EVENT_QUEUE'',

dequeue_options => l_dequeue_options,

message_properties => l_message_properties,

payload => l_queue_msg,

msgid => l_message_handle);③

commit;

select instance_name into v_inst from v$instance;

insert into BackupResult values(v_inst

, l_queue_msg.event_type

, l_queue_msg.object_owner

, l_queue_msg.object_name

, l_queue_msg.event_timestamp ⑥

①




, l_queue_msg.error_code

, l_queue_msg.event_status

, l_queue_msg.log_id

, l_queue_msg.run_count

, l_queue_msg.failure_count

, l_queue_msg.retry_count);

Commit;

END;',


event_condition => 'tab.user_data.object_name = ''RMAN_BACKUP_ZERO''

and tab.user_data.object_owner=''SYSTEM''', ④

queue_spec =>'sys.scheduler$_event_queue,AQAGENT',⑤

enabled => TRUE);

END;

/

3.4. external job 수행시 ORA-27369 처리 방안 Applies

Oracle Server – EE version 10.1.0.2 to 10.2.0.4

Solaris Operating System(SPARC 64-bit)

Linux x86

Symptoms

ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted

Or

ORA-27369: job of type EXECUTABLE failed with exit code: 274662

In dba_scheduler_job_run_details.additional_info, the following details are recorded:

ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted

STANDARD_ERROR="touch: cannot touch `<file_name>': Permission denied"

Or




ORA-27369: job of type EXECUTABLE failed with exit code: 274662

STANDARD_ERROR="Oracle Scheduler error: Config file is not owned by root or is

writable by group or other or extjob is not setuid and owned by root"

Solution

1. $ORACLE_HOME/rdbms/admin/externaljob.ora 의 owner 를 root 로 지정하고 permission 을 640 으

로 지정

# cd $ORACLE_HOME/rdbms/admin/

# chown root externaljob.ora

# chmod 640 externaljob.ora

# ls -la externaljob.ora

-rw-r----- 1 root oinstall 1537 Sep 13 09:24 externaljob.ora

2. $ORACLE_HOME/rdbms/admin/externaljob.ora 을 열어서 run_user,run_group 을 “nobody”에서

해당 oracle 계정과 group 을 변경

#run_user = nobody

run_user = oracle

#run_group = nobody

run_group = dba

3. $ORACLE_HOME/bin/extjob permission 처리

# cd $ORACLE_HOME/bin

# chmod 4750 extjob

# ls -la extjob

-rwsr-x--- 1 root oinstall 64988 Mar 29 18:22 extjob

4. $ORACLE_HOME/bin/extjobo permission 처리

#ls -al extjob*

-rwsr-x--- 1 root dba 59353 Jul 28 00:50 extjob

-rwxr-xr-x 1 oracle dba 59988 Mar 11 23:52 extjobO

-rwx------ 1 oracle dba 59353 Jul 28 00:50 extjobo

-rwxr-xr-x 1 oracle dba 59988 Mar 11 23:52 extjoboO

#chmod 755 extjobo

-rwxr-xr-x 1 oracle dba 59353 Jul 28 00:50 extjobo




4. datapump backup schedulering dbms_datapump 를 이용하여 schema 별(scma1,scma2)로 특정 table 에 대한 pump 를 수행하고 백업보

관주기(1 주일)가 지난 pump file 은 삭제하는 job 을 스케줄러 chain 을 이용하여 생성한다.

4.1. datapump data,log directory 생성 dbms_datapump 수행을 위한 dump,log directory 를 생성한다.

REM############################################

REM# Create dataPump DATA,LOG Directory

REM# DATA dir =>ASM Disk group

REM# LOG dir =>Filesystem

REM############################################

create or replace DIRECTORY PUMP_BACKUP as '+dgbackup1/SRVDB/PUMP';

CREATE or REPLACE DIRECTORY PUMP_LOG as '/oracle/Testbackup/pump/log';

grant read,write on directory PUMP_BACKUP to system;

grant read,write on directory PUMP_LOG to system;

4.2. schema별 datapump scheduler program 생성 4.2.1. SCMA1 Schema scheduler program 생성

Asm disk group 으로 dbms_datapump backup 을 수행한다.

PLSQL-BLOCK 을 이용하여 스케줄러 job 을 생성한다.

아래 Dbms_datapump 의 procedure 를 살펴보면

1> table 단위로 expdp 가 실행되도록 ‘NEW1’ jobname 으로 datapump 를 오픈한다.

2> expdp 로 생성되는 logfile 은 PUMP_LOG directory 밑에 “scma1.2008_09_01.log” 형식으로 생성

된다. File_type=>3 은 logfile 을 지칭한다.

3> Metadata_filter procedure 로 Schema IN (‘SCMA1’) , Table in (‘TAB1’,’TAB2’,’TAB3’)을 expdp 하

도록 지정한다.

4> Dump file 은 PUMP_DIR directory 밑에 “scma1.2008_09_01.dmp” 형식으로 생성된다.

File_type=>1 은 dumpfile 을 지칭하는 값이다.

KU$_FILE_TYPE_DUMP_FILE (dump file for a job)

KU$_FILE_TYPE_LOG_FILE (log file for a job)

KU$_FILE_TYPE_SQL_FILE (output for SQL_FILE job)

5> set_parameter value

keep_master – A Master Table is created at the beginning of an export operation. At




successful completion of the export job, this table is written to the dump file set, and then dropped. If you set keep_master=y, the Master Table will not be dropped at the end of the job

Begin

DBMS_SCHEDULER.CREATE_program (

program_name=>'P_PUMP_BK_JOB_SCMA1',

program_type=>'PLSQL_BLOCK',

program_action=>'declare

v_date varchar2(20);

h1 number;

BEGIN

select to_char(sysdate,''MM'')||to_char(sysdate,''DD'')||to_char(sysdate,''HH24'')||to_char(sysdate,''MI'')

into v_date from dual;

h1 := dbms_datapump.open (operation => ''EXPORT'', job_mode =>

''TABLE'',job_name=>''NEW1'',version => ''COMPATIBLE'');

dbms_datapump.set_parallel(handle => h1, degree => 1);

DBMS_DATAPUMP.ADD_FILE(handle =>h1

,filename =>''scma1.''||v_date||''.log''

,directory =>''PUMP_LOG''

,filetype =>3);

dbms_datapump.metadata_filter(handle => h1

,name => ''SCHEMA_EXPR''

,value => ''IN(''''SCMA1'''')'');

dbms_datapump.metadata_filter(handle=>h1

,name=>''NAME_EXPR''

,value=>''IN (''''TAB_1'''',''''TAB_2''',''''TAB_3'''')'');

dbms_datapump.add_file(handle=>h1

,filename=>''scma1.''||v_date||''.dmp''

,directory=>''PUMP_BACKUP''

,filetype=>1);

dbms_datapump.set_parameter(handle => h1, name => ''KEEP_MASTER'', value => 0);

dbms_datapump.set_parameter(handle => h1, name => ''DATA_ACCESS_METHOD'', value =>

''AUTOMATIC'');

dbms_datapump.set_parameter(handle => h1, name => ''ESTIMATE'', value => ''BLOCKS'');

dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

dbms_datapump.detach(h1);

END;',

enabled=>true);




end;

/

4.2.2. SCMA2 schema scheduler program 생성

REM############################################

REM# SCMA2

REM############################################

exec DBMS_SCHEDULER.drop_program ('P_PUMP_BK_JOB_SCMA2',force=>TRUE);

BEGIN


program_name => 'P_PUMP_BK_JOB_SCMA2',

program_type => 'PLSQL_BLOCK',

program_action=>'declare


h2 number;

BEGIN

select to_char(sysdate,''MM'')||to_char(sysdate,''DD'')||to_char(sysdate,''HH24'')||to_char(sysdate,''MI'')


h2 := dbms_datapump.open (operation => ''EXPORT'', job_mode =>

''TABLE'',job_name=>''NEW2'', version => ''COMPATIBLE'');

dbms_datapump.set_parallel(handle => h2, degree => 1);

DBMS_DATAPUMP.ADD_FILE(handle =>h2

,filename =>''scma2.''||v_date||''.log''

,directory =>''PUMP_LOG''

,filetype =>3);

dbms_datapump.metadata_filter(handle => h2

,name => ''SCHEMA_EXPR''

,value => ''IN(''''SCMA2'''')'');

dbms_datapump.metadata_filter(handle=>h2

,name=>''NAME_EXPR''

,value=>''IN (''''TAB4'''',''''TAB5'''',''''TAB6'''')'');

dbms_datapump.add_file(handle=>h2

,filename=>''scma2.''||v_date||''.dmp''

,directory=>''PUMP_BACKUP''

,filetype=>1);

dbms_datapump.set_parameter(handle => h2, name => ''KEEP_MASTER'', value => 0);

dbms_datapump.set_parameter(handle => h2, name => ''DATA_ACCESS_METHOD'', value =>

''AUTOMATIC'');




dbms_datapump.set_parameter(handle => h2, name => ''ESTIMATE'', value => ''BLOCKS'');

dbms_datapump.start_job(handle => h2, skip_current => 0, abort_step => 0);

dbms_datapump.detach(h2);

END;',

enabled=>true);

end;

/

4.3. 보관주기가 지난 datapump backupfile 삭제 script

4.3.1. os shell script 2 일이 지난 dump file 을 remove 하는 shell script 이다.

[oracle/backup/pump> more rmpump.sh

#!/usr/bin/ksh

export O_SID=`ps -ef | grep pmon | grep ASM |awk '{print$8}'|cut -d"_" -f3`

export ORACLE_SID=$O_SID

export LOGFILE=/oracle/Testbackup/pump/log/rm_pump.`date "+%m%d%H"`

export DB_NAME=`grep CAFE /etc/oratab | awk -F: '{ print $1}'`

export B2DATE=`date --date "2 days ago" +%m%d`

export ORACLE_HOME=/oracle/product/10.2.0

/oracle/product/10.2.0/bin/asmcmd <<EOF

cd dgbackup1/$DB_NAME/PUMP

rm -rf SCMA*$B2DATE*.dmp

EOF

echo "rm -rf SCMA*"$B2DATE"*.dmp" >> $LOGFILE

echo "pump file deleted" >> $LOGFILE

echo "date: `date "+%Y-%m-%d"` `date "+%H:%M"`" >> $LOGFILE

4.3.2. scheduler program 스케줄러 chain 의 마지막 부분에 추가하기 위해 dumpfile 삭제 program 을 생성한다.




REM############################################

REM# Remove dmpfile Program

REM############################################

EXEC DBMS_SCHEDULER.DROP_program(PROGRAM_name=>'P_RMPUMP');

begin


program_name=>'P_RMPUMP',

program_type=>'EXECUTABLE',

program_action=>'/oracle/Testbackup/pump/rmpump.sh',

enabled=>true,

comments=>'REMOVE DUMP over 2 DAYS');

end;

/

4.4. scheduler chain 생성 chain rule

1. scma1 schema datapump start

2. scma1 datapump 가 성공(succeed)하면 scma2 datapump start

3. scma2 datapump 가 성공하면 2 일 dump file 삭제하는 job 실행

4. file 삭제가 완료(completed)하면 종료(end)

REM############################################

REM# create Chain

REM############################################

BEGIN

dbms_scheduler.create_chain('chain_PUMP');

dbms_scheduler.define_chain_step('chain_PUMP','step1','P_PUMP_BK_JOB_SCMA1');

dbms_scheduler.define_chain_step('chain_PUMP','step2','P_PUMP_BK_JOB_SCMA2');

dbms_scheduler.define_chain_step('chain_PUMP','step3','P_RMPUMP');

dbms_scheduler.define_chain_rule('chain_PUMP','true','START step1');

dbms_scheduler.define_chain_rule('chain_PUMP','step1 succeeded','start step2');

dbms_scheduler.define_chain_rule('chain_PUMP','step2 succeeded','start step3');

dbms_scheduler.define_chain_rule('chain_PUMP','step3 completed','end');

dbms_scheduler.enable('chain_PUMP');

END;

/




4.5. scheduler class 생성 chain 을 스케줄러 job 에 등록하기 위한 job class 와 스케줄러 job 생성

rac 환경에서는 서로 다른 job 이 동일한 시간대에 다른 node 에서 같이 수행될 수 없다. 따라

서 같이 수행되어야 할 job 은 한쪽 노드에서만 수행되도록 job class 를 생성하면서

service_name 을 등록해 준다.

Note 454639.1 : ORA-31626 ORA-31650 While Using DataPump Export in RAC Environment

There is one datapump limitation in 10gR2 :

summarized as:

It is not possible to start or restart Data Pump jobs on one instance of a RAC if there are Data

Pump jobs currently

running on other instances of the RAC. It is possible to start jobs on the insta

nce where the other jobs are running or on any instance of the RAC if no jobs are currently

running.

REM############################################

REM# create job class

REM############################################

--EXEC DBMS_SCHEDULER.DROP_JOB_CLASS(job_class_name=>'PUMP_JOB_CLS');

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS(

job_class_name=>'PUMP_JOB_CLS',

service=>'SRVDB1',

comments=>'EXECUTE ONLY Node1');

end;

/

4.6. scheduler job 생성

REM############################################

REM# create Job

REM# Execute Everyday am 5:10

REM############################################

begin


job_name=>'PUMP_BK_JOB_SCMA',

job_type=>'CHAIN',




job_action=>'chain_PUMP',

job_class=>'PUMP_JOB_CLS',

repeat_interval=> 'FREQ=DAILY;BYHOUR=5;BYMINUTE=10',

enabled=>true);

end;

/

begin


name =>'PUMP_BK_JOB_SCMA',






+DBMS_SCHEDULER.job_stopped

+DBMS_SCHEDULER.job_completed

+DBMS_SCHEDULER.job_chain_stalled);

end;

/

exec dbms_scheduler.drop_job('event_based_job3');

BEGIN










BEGIN

l_dequeue_options.consumer_name :=''AQAGENT'';








msgid => l_message_handle);

commit;






, l_queue_msg.event_timestamp







Commit;

END;',


event_condition => 'tab.user_data.object_name = ''PUMP_BK_JOB_SCMA''

and tab.user_data.object_owner=''SYSTEM''',

queue_spec =>'sys.scheduler$_event_queue,AQAGENT',

enabled => TRUE);

END;

/




5. File_transfer backup schedulering

5.1. stored procedure 생성 dbms_file_transfer 를 이용해서 위에서 asm diskgroup 으로 rman 을 이용해서 backup 한 file 들

을 filesystem 으로 다시 백업하도록 해보자

우선 dbms_scheduler job 에 등록할 procedure 를 생성한다.

이 procedure 에 대해 간단히 설명하면

1> rman backup command 중 format 구문에 변수를 사용하지 않고 단순히 asm diskgroup 만을 쓰

게되면(예 format=’+DGBACKUP’) 실제 rman backup file 은 ‘2008_08_01’이라는 해당 일자 directory

가 만들어지면서 그 아래에 모든 backup file 들이 저장된다.

file_transfer 를 사용할때는 target dir 은 고정이지만 source dir 쪽이 매일 날짜단위로 dir 이 변경된

다.

그러므로 첫번째로 source dir 쪽의 directory 생성을 매일 반복해 주어야 한다. 이 부분이 ① 이다.

생성된 source dir 은 마지막에 삭제한다.

2> 다음 부분은 rman 으로 매일 백업되는 backup_piece 를 v$backup_piece 에서 확인한

다음 그 결과를 cursor 에 담아 Loop 구문으로 cursor 에 담겨 있는 row 수 만큼 file_transfer 를 수

행하는 구문이다. 이 부분이 ② 이다.

REM############################################

REM# Create Procedure for dbms_filetransfer backup

REM############################################

DROP PROCEDURE FTRANS_PROC;

CREATE OR REPLACE PROCEDURE FTRANS_PROC IS

TYPE TraCurTyp IS REF CURSOR;

Tra_cv TraCurTyp;

Tra_rec varchar2(2000);

sql_stmt VARCHAR2(200);


BEGIN

select to_char(sysdate,'YYYY')||'_'||to_char(sysdate,'MM')||'_'||to_char(sysdate,'DD')


EXECUTE IMMEDIATE

'CREATE OR REPLACE DIRECTORY SOURCEDIR'

|| ' AS ''+DGBACKUP1/SRVDB/backupset/'||v_date||'''';

EXECUTE IMMEDIATE

'GRANT READ ON DIRECTORY SOURCEDIR TO SYSTEM';

①




EXECUTE IMMEDIATE

'CREATE OR REPLACE DIRECTORY TARGETDIR'

|| ' AS ''/DGBACKUP/''';

EXECUTE IMMEDIATE

'GRANT WRITE ON DIRECTORY TARGETDIR TO SYSTEM';

sql_stmt :='select substr(handle,instr(handle,''/'',-1,1)+1) from v$backup_piece '||

'where handle like ''%'||v_date||'%'' and handle not like ''%autobackup%''' ;

OPEN Tra_cv FOR sql_stmt;

LOOP

FETCH Tra_cv INTO Tra_rec;

EXIT WHEN Tra_cv%NOTFOUND;

dbms_file_Transfer.copy_file('SOURCEDIR',''||Tra_rec ||'','TARGETDIR',''||Tra_rec||'');

END LOOP;

CLOSE Tra_cv;

EXECUTE IMMEDIATE

'DROP DIRECTORY SOURCEDIR';

END FTRANS_PROC;

/

②

5.2. job class 생성 RAC 에서 스케줄러 작업은 instance 별로 random 하게 발생한다.

만약 한쪽 node 로 스케줄러를 고정시키려면 job class 를 생성하고 service 속성에 한쪽노드로

preconnect 된 service_name 을 입력하면 된다.

REM############################################

REM# Node1 에서 수행되도록 job class 에 service 등록

REM############################################

EXEC DBMS_SCHEDULER.DROP_JOB_CLASS(job_class_name=>'FILE_TRANSFER_JOB_CLS');

begin

DBMS_SCHEDULER.CREATE_JOB_CLASS(

job_class_name=>'FILE_TRANSFER_JOB_CLS',

service=>'SRVDB1',

comments=>'EXECUTE ONLY Node1');

end;

/




5.3. job program 생성

5.3.1. file_transfer 실행 job program 생성

REM############################################

REM# Create Pump Scheduler Program,Chain,Job

REM############################################

exec DBMS_SCHEDULER.drop_program ('P_FTRANS_BACKUP');

begin


program_name=>'P_FTRANS_BACKUP',

program_type=>'STORED_PROCEDURE',

program_action=>'sys.FTRANS_PROC',

enabled=>true);

end;

/

5.3.2. os shell script 생성

[oracle/backup/file_transfer> more rmtransfiles.sh

#!/usr/bin/ksh

# delete rman backup in filesystem over 7 days

cd /DGBACKUP;

find /DGBACKUP -mtime +7 -exec rm {} \;

5.3.3. backup file remove job program 생성

REM############################################

REM# After 7 days Remove filetransfer backup

REM############################################

exec DBMS_SCHEDULER.drop_program ('P_FTRANS_BACKUP_REMOVE');

BEGIN


program_name => 'P_FTRANS_BACKUP_REMOVE',





program_action=>'/oracle/Testbackup/file_transfer/rmtransfiles.sh',

enabled=>true);

end;

/

5.4. job chain 생성 BEGIN

dbms_scheduler.create_chain('chain_FT');

dbms_scheduler.define_chain_step('chain_FT','step1','P_FTRANS_BACKUP');

dbms_scheduler.define_chain_step('chain_FT','step2','P_FTRANS_BACKUP_REMOVE');

dbms_scheduler.define_chain_rule('chain_FT','true','START step1');

dbms_scheduler.define_chain_rule('chain_FT','step1 SUCCEEDED','start step2');

dbms_scheduler.define_chain_rule('chain_FT','step2 completed','end');

dbms_scheduler.enable('chain_FT');

END;

/

5.5. job scheduler 생성 REM############################################

REM# create Job

REM# Execute Everyday am 6:00

REM############################################

begin


job_name=>'FTRANS_BACKUP',

job_type=>'CHAIN',

job_action=>'CHAIN_FT',

job_class=>'FILE_TRANSFER_JOB_CLS',

repeat_interval=> 'FREQ=DAILY;BYHOUR=7;BYMINUTE=10',

enabled=>true);

end;

/

begin


name =>'FTRANS_BACKUP',









+DBMS_SCHEDULER.job_stopped

+DBMS_SCHEDULER.job_completed

+DBMS_SCHEDULER.job_chain_stalled);

end;

/

exec dbms_scheduler.drop_job('event_based_job4');

BEGIN










BEGIN

l_dequeue_options.consumer_name :=''AQAGENT'';





msgid => l_message_handle);

commit;






, l_queue_msg.event_timestamp










Commit;

END;',


event_condition => 'tab.user_data.object_name = ''FTRANS_BACKUP''

and tab.user_data.object_owner=''SYSTEM''',

queue_spec =>'sys.scheduler$_event_queue,AQAGENT',

enabled => TRUE);

END;




6. Remote Table Insert schedulering

REM############################################

REM# Create DB Link for Remote table Access

REM############################################

drop database link DL_REMOTE;

create database link DL_REMOTE connect to DBDEV01 identified by "xxxxxx" USING 'BTAB';

REM############################################

REM# Create Select ~Insert Procedure

REM############################################

DBA_SCHEDULER_JOB_RUN_DETAILS, BACKUPRESULT 를 조인하여 오늘 발생한 백업정보를

CURSOR 로 정의하여 수행된 백업 횟수만큼 FOR ~ LOOP 처리하는 PROCEDURE 생성

CREATE OR REPLACE PROCEDURE RTAB_INS_PROC IS

CURSOR cursor_rmt IS

SELECT substr(B.INST_NAME,1,7) DB_NAME

,B.EVENT_TYPE E_EVENT_TYPE

,B.OBJECT_OWNER

,B.OBJECT_NAME

,A.ACTUAL_START_DATE START_time

,A.ACTUAL_START_DATE+RUN_DURATION END_time

,Decode(substr(B.object_name,1,1),'B','R',substr(B.object_name,1,1)) backup_type

,A.ERROR# ERROR_CODE

,B.EVENT_STATUS

,A.LOG_ID

,B.RUN_COUNT

,B.FAILURE_COUNT

,B.RETRY_COUNT

,A.ADDITIONAL_INFO ERROR_INFO

FROM DBA_SCHEDULER_JOB_RUN_DETAILS A,BACKUPRESULT B

WHERE A.LOG_ID=B.LOG_ID

AND A.JOB_SUBNAME IS NULL

AND to_char(A.ACTUAL_START_DATE,'YYYY-MM-DD')=TO_CHAR(sysdate,'YYYY-MM-DD')

ORDER BY START_TIME;

record_cursor_rmt cursor_rmt%ROWTYPE;

Begin




FOR record_cursor_rmt IN cursor_rmt LOOP

INSERT INTO BACKUP_RESULT_ORACLE@DL_REMOTE values

(Record_cursor_rmt.DB_NAME

,Record_cursor_rmt.E_EVENT_TYPE

,Record_cursor_rmt.OBJECT_OWNER

,Record_cursor_rmt.OBJECT_NAME

,Record_cursor_rmt.START_TIME

,Record_cursor_rmt.END_TIME

,Record_cursor_rmt.BACKUP_TYPE

,Record_cursor_rmt.ERROR_CODE

,Record_cursor_rmt.EVENT_STATUS

,Record_cursor_rmt.LOG_ID

,Record_cursor_rmt.RUN_COUNT

,Record_cursor_rmt.FAILURE_COUNT

,Record_cursor_rmt.RETRY_COUNT

,Record_cursor_rmt.ERROR_INFO);

END LOOP;

END RTAB_INS_PROC;

/

REM############################################

REM# Create Job

REM############################################

매일 AM 7:1O 에 수행되도록 job 생성

exec DBMS_SCHEDULER.DROP_JOB('INSERT_REMOTE_TAB');

BEGIN


job_name => 'INSERT_REMOTE_TAB',

job_type => 'STORED_PROCEDURE',

job_action => 'RTAB_INS_PROC',

repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0',

enabled => TRUE,

comments => 'AFTER BACKUP INSERT REMOTE TABLE');

END;

/

참고 사이트 :www.okjsp.co.kr Oracle 10g Reference Library Meta Link DocuID:336014.1 /454639.1

dbms scheduler 를 이용한 backup 정책 구현

Documents