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