DBMS_SCHEDULER을 통해 OS 레벨(EXTERNAL)의 shell 수행하기
Test Case 1(실패 케이스)
원인 : 선행 작업으로 CREDENTIAL을 생성해야 하는데 이 과정이 생략되어 발생한 실패케이스 이다.
목적은 tkprof를 수행하여 10046 트레이스 파일을 분석하는 것이 목적이다.
아래의 코드로 DBMS_SCHEDULER을 등록해서 사용하는 방법을 테스트 한다.
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'TKPR_JOB',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
number_of_arguments => 3,
start_date => SYSDATE,
repeat_interval => null,
end_date => null,
enabled => FALSE,
auto_drop => TRUE);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'TKPR_JOB',
argument_position => 1,
argument_value => '/home/oracle/DBA/script/SHELL/temp/temp.sh');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'TKPR_JOB',
argument_position => 2,
argument_value => '/home/oracle/DBA/script/SHELL/temp/testdb_ora_5627.trc');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'TKPR_JOB',
argument_position => 3,
argument_value => '/home/oracle/DBA/script/SHELL/temp/test_tkprof.txt');
DBMS_SCHEDULER.ENABLE(name => 'TKPR_JOB');
END;
/
temp.sh 의 내용은 아래와 같다.
#!/bin/bash export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/app/oracle/product/12.1.0/:/home/oracle/12.1.0/grid:/home/oracle/app/oracle/product/12.1.0//bin /home/oracle//app/oracle/product/12.1.0/bin/tkprof $1 $2 sys=no
Test Case 2 (성공 케이스)
1. 권한 부여
DB 사용 유저에게 권한을 부여한다.
GRANT CREATE JOB TO DB_MONITORING; GRANT CREATE EXTERNAL JOB TO DB_MONITORING; GRANT CREATE CREDENTIAL TO DB_MONITORING;
2 credential 생성
오라클 버전이 12.1 버전이라서 DBMS_CREDENTIAL 패키지를 사용한다.
BEGIN
DBMS_CREDENTIAL.create_credential(
credential_name => 'oracle_db_tkprof',
username => 'oracle',
password => 'oracle'
);
END;
/
만약 오라클 버전이 11. 버전인 경우 아래와 같이 credential을 생성한다.
BEGIN
DBMS_SCHEDULER.CREATE_CREDENTIAL(
credential_name => 'ORACLE_DB_TKPROF',
username => 'oracle',
password => 'oracle');
END;
/
3 External Shell Job 생성 및 실행
DECLARE
l_script VARCHAR2(32767);
BEGIN
l_script := '#!/bin/bash
/bin/bash /home/oracle/DBA/script/SHELL/temp/temp.sh /home/oracle/DBA/script/SHELL/temp/testdb_ora_5627.trc /home/oracle/DBA/script/SHELL/temp/test_tkprof.txt';
DBMS_SCHEDULER.create_job(
job_name => 'TKPR_JOB',
job_type => 'EXTERNAL_SCRIPT',
job_action => l_script,
start_date => SYSDATE,
repeat_interval => null,
end_date => null,
credential_name => 'oracle_db_tkprof',
enabled => TRUE,
auto_drop => TRUE);
END;
/
4 Job을 실행한 결과
COL JDATE FOR A20
COL OWNER FOR A15
COL JOB_CLASS FOR A10
COL CREDENTIAL_NAME FOR A20
COL OPERATION FOR A10
COL STATUS FOR A10
SELECT TO_CHAR(LOG_DATE,'YYYYMMDD HH24:MI:SS') JDATE
, OWNER
, OPERATION
, STATUS
, CREDENTIAL_NAME
FROM DBA_SCHEDULER_JOB_LOG
WHERE OWNER = 'DB_MONITORING'
AND JOB_NAME = 'TKPR_JOB';
JDATE OWNER OPERATION STATUS CREDENTIAL_NAME
-------------------- --------------- ---------- ---------- --------------------
20170405 09:45:32 DB_MONITORING RUN SUCCEEDED ORACLE_DB_TKPROF
1 row selected.
또 다른 결과
COL JDATE FOR A20
COL OWNER FOR A15
COL CREDENTIAL_NAME FOR A20
COL STATUS FOR A10
COL SESSION_ID FOR A10
COL REQ_START_DATE FOR A20
COL ACTUAL_START_DATE FOR A20
COL CPU_USED FOR A20
COL ADDITIONAL_INFO FOR A40
COL OUTPUT FOR A40
SELECT TO_CHAR(LOG_DATE,'YYYYMMDD HH24:MI:SS') JDATE
,INSTANCE_ID
,SESSION_ID
,OWNER
,STATUS
,ERROR#
,REQ_START_DATE
,ACTUAL_START_DATE
,CPU_USED
,CREDENTIAL_NAME
,ADDITIONAL_INFO
,OUTPUT
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE OWNER = 'DB_MONITORING'
AND JOB_NAME = 'TKPR_JOB';
JDATE INSTANCE_ID SESSION_ID OWNER STATUS ERROR# REQ_START_DATE ACTUAL_START_DATE CPU_USED CREDENTIAL_NAME ADDITIONAL_INFO OUTPUT
-------------------- ----------- ---------- --------------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- ----------------------------------------
20170405 09:45:32 1 260,41585 DB_MONITORING SUCCEEDED 0 05-APR-17 09.45.28.9 05-APR-17 09.45.28.9 +000 00:00:00.00 ORACLE_DB_TKPROF EXTERNAL_LOG_ID="job_94499_452594",
27105 AM +09:00 44428 AM +09:00 USERNAME="oracle" TKPROF: Release 12.1.0.2.0 - Development on 수 4월 5 09:45:31 2017
Copyright (c) 1982, 2014, Oracle and/or
its affiliates. All rights reserved.
1 row selected.
아래는 OS 레벨에서 생성된 파일을 확인한 내용이다.
/home/oracle/DBA/script/SHELL/temp> ls -al 합계 708 drwxr-xr-x. 2 oracle oracle 4096 2017-04-05 09:46 . drwxr-xr-x. 3 oracle oracle 4096 2017-04-04 16:38 .. -rwxr-xr-x. 1 oracle oracle 277 2017-04-04 19:15 temp.sh -rw-rw-rw-. 1 oracle oracle 89012 2017-04-05 09:45 test_tkprof.txt -rw-r-----. 1 oracle oracle 621526 2017-04-04 17:09 testdb_ora_5627.trc
5. credential 권한을 타 유저에게 부여하여 수행하기
GRANT EXECUTE ON ORACLE_DB_TKPROF TO KWAN;
DECLARE
l_script VARCHAR2(32767);
BEGIN
l_script := '#!/bin/bash
/bin/bash /home/oracle/DBA/script/SHELL/temp/temp.sh /home/oracle/DBA/script/SHELL/temp/testdb_ora_5627.trc /home/oracle/DBA/script/SHELL/temp/test_tkprof.txt';
DBMS_SCHEDULER.create_job(
job_name => 'TKPR_JOB',
job_type => 'EXTERNAL_SCRIPT',
job_action => l_script,
start_date => SYSDATE,
repeat_interval => null,
end_date => null,
credential_name => 'db_monitoring.oracle_db_tkprof',
enabled => TRUE,
auto_drop => TRUE);
END;
/
OS 레벨의 결과 내용
/home/oracle/DBA/script/SHELL/temp> ls -al 합계 708 drwxr-xr-x. 2 oracle oracle 4096 2017-04-05 11:09 . drwxr-xr-x. 3 oracle oracle 4096 2017-04-04 16:38 .. -rwxr-xr-x. 1 oracle oracle 277 2017-04-04 19:15 temp.sh -rw-rw-rw-. 1 oracle oracle 89012 2017-04-05 11:09 test_tkprof.txt -rw-r-----. 1 oracle oracle 621526 2017-04-04 17:09 testdb_ora_5627.trc
