この記事は約29分0秒で読むことができます。

DBMS_SCHEDULER.create_jobとかの話

シェルスクリプトつくる

スクリプト

コード表示

echo 'hello world!'
/mnt/18cr3/share/sh/t.sh

実行例

コード表示

[oracle@f285aba0589a sh]$ ll
total 0
[oracle@f285aba0589a sh]$ vi t.sh
[oracle@f285aba0589a sh]$ ll
total 4
-rw-r--r--. 1 oracle oinstall 20 Apr  7 18:34 t.sh
[oracle@f285aba0589a sh]$ sudo chmod +x t.sh
[sudo] password for oracle:
[oracle@f285aba0589a sh]$ ll
total 4
-rwxr-xr-x. 1 oracle oinstall 20 Apr  7 18:34 t.sh
[oracle@f285aba0589a sh]$ pwd
/mnt/18cr3/share/sh
[oracle@f285aba0589a sh]$ ./t.sh
hello world!

プログラム作る

資格証明とかわすれずに。資格証明の概要。ローカル外部ジョブの概要。リモート外部ジョブの概要。とかみればいい。ちなみにデフォルトのままいくとORAERRORになる模様。参考までのcfgファイル。externaljob.oraにnobodyと書かれているが、資格証明作るとうごく。

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ find / -name *job* -type f 2>/dev/null
/opt/oracle/product/18c/dbhome_1/rdbms/admin/catjobq.sql
/opt/oracle/product/18c/dbhome_1/rdbms/admin/dbmsjob.sql
/opt/oracle/product/18c/dbhome_1/rdbms/admin/externaljob.ora
/opt/oracle/product/18c/dbhome_1/rdbms/admin/prvthjob.plb
/opt/oracle/product/18c/dbhome_1/rdbms/admin/prvtjob.plb
/opt/oracle/product/18c/dbhome_1/rdbms/xml/xsl/kujob.xsl
/opt/oracle/product/18c/dbhome_1/bin/extjob
/opt/oracle/product/18c/dbhome_1/bin/extjobo
/opt/oracle/product/18c/dbhome_1/jdk/lib/missioncontrol/plugins/org.eclipse.core.jobs_3.6.0.v20140424-0053.jar
/usr/bin/jobs
[oracle@f285aba0589a ~]$ vim /opt/oracle/product/18c/dbhome_1/rdbms/admin/externaljob.ora

# $Header: externaljob.ora 16-dec-2005.20:47:13 rramkiss Exp $
#
# Copyright (c) 2005, Oracle. All rights reserved.
# NAME
#   externaljob.ora
# FUNCTION
#   This configuration file is used by dbms_scheduler when executing external
#   (operating system) jobs. It contains the user and group to run external
#   jobs as. It must only be writable by the owner and must be owned by root.
#   If extjob is not setuid then the only allowable run_user
#   is the user Oracle runs as and the only allowable run_group is the group
#   Oracle runs as.
#
# NOTES
#   For Porters: The user and group specified here should be a lowly privileged
#                user and group for your platform. For Linux this is nobody
#                and nobody.
# MODIFIED
#     rramkiss   12/09/05 -  Creation
#
##############################################################################
# External job execution configuration file externaljob.ora
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site.  Important system parameters
# are discussed, and default settings given.
#
# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.

run_user = nobody
run_group = nobody

 
Oracle Schedulerの概要  
DBMS_SCHEDULER  
Oracle Schedulerを使用したジョブのスケジューリング  
コード表示

begin
	dbms_scheduler.drop_job('exe_t_sh');
exception
	when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
end; 
/
begin
	dbms_scheduler.drop_credential('crd');
exception
	when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
end;
/

declare
	prg_nm varchar2(100):='';
begin
	dbms_scheduler.create_credential('crd','oracle','pwd');
	dbms_scheduler.create_job (
		job_name        => 'exe_t_sh'
		,job_type        => 'executable'
		,job_action      => '/mnt/18cr3/share/sh/t.sh'
		,number_of_arguments => 0
		,enabled             => false
		,comments            => 'test'
		,credential_name     => 'crd'
		);
exception
	when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
end;
/

オブジェクト確認

スクリプト

コード表示

col job_name for a10
col job_creator for a10
col job_type for a10
col job_action for a10
col number_of_arguments for 99
col schedule_type for a10
col job_class for a10
col enabled for a10
col state for a10
col run_count for 99
col logging_level for a10
col credential_owner for a10
col credential_name for a10
col comments for a10

select
	job_name
	, job_creator
	, job_type
	, job_action
	, number_of_arguments
	, schedule_type
	, job_class
	, enabled
	, state
	, run_count
	, logging_level
	, credential_owner
	, credential_name
	, comments
from
	user_scheduler_jobs;

col credential_name for a15
col username for a15
col database_role for a15
col windows_domain for a15
col comments for a15

select
	credential_name
	, username
	, database_role
	, windows_domain
	, comments
from
	user_scheduler_credentials;

実行例

コード表示

AINE@pdb1> col job_name for a10
AINE@pdb1> col job_creator for a10
AINE@pdb1> col job_type for a10
AINE@pdb1> col job_action for a10
AINE@pdb1> col number_of_arguments for 99
AINE@pdb1> col schedule_type for a10
AINE@pdb1> col job_class for a10
AINE@pdb1> col enabled for a10
AINE@pdb1> col state for a10
AINE@pdb1> col run_count for 99
AINE@pdb1> col logging_level for a10
AINE@pdb1> col credential_owner for a10
AINE@pdb1> col credential_name for a10
AINE@pdb1> col comments for a10
AINE@pdb1> select job_name ,job_creator ,job_type ,job_action ,number_of_arguments ,schedule_type ,job_class ,enabled ,state ,run_count ,logging_level ,credential_owner ,credential_name ,comments from user_scheduler_jobs;

JOB_NAME   JOB_CREATO JOB_TYPE   JOB_ACTION NUMBER_OF_ARGUMENTS SCHEDULE_T JOB_CLASS  ENABLED    STATE      RUN_COUNT LOGGING_LE CREDENTIAL CREDENTIAL COMMENTS
---------- ---------- ---------- ---------- ------------------- ---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
EXE_T_SH   AINE       EXECUTABLE /mnt/18cr3                   0 IMMEDIATE  DEFAULT_JO FALSE      DISABLED           0 OFF        AINE       CRD        test
                                 /share/sh/                                B_CLASS
                                 t.sh


1 row selected.

Elapsed: 00:00:00.01


AINE@pdb1> col credential_name for a15
AINE@pdb1> col username for a15
AINE@pdb1> col database_role for a15
AINE@pdb1> col windows_domain for a15
AINE@pdb1> col comments for a15
AINE@pdb1> select credential_name ,username ,database_role ,windows_domain ,comments from user_scheduler_credentials;

CREDENTIAL_NAME USERNAME        DATABASE_ROLE   WINDOWS_DOMAIN  COMMENTS
--------------- --------------- --------------- --------------- ---------------
CRD             oracle

1 row selected.

Elapsed: 00:00:00.00

資格証明発行しないとこんなかんじでエラー。うまくいくとアウトプット列にでる

コード表示

ORA-27370: ジョブ・スレーブは、タイプEXECUTABLEのジョブの起動に失敗しました
ORA-27300: OSシステム依存操作:Waiting for extjob to send childがステータス:62で失敗しました。
ORA-27301: OS障害メッセージ: Timer expired
ORA-27302: sjsec 6dで障害が発生しました

----------------------------------------------------------------------
AINE@pdb1> col job_name for a30
AINE@pdb1> col status for a30
AINE@pdb1> col output for a30
AINE@pdb1> select s1.status ,s1.output ,s1.job_name from user_scheduler_job_run_details s1 where to_char(s1.log_date,'yyyymmddhh24mi') between to_char(sysdate - interval '7' hour,'yyyymmddhh24mi') and to_char(sysdate - interval '0' hour,'yyyymmddhh24mi') ;

STATUS                         OUTPUT                         JOB_NAME
------------------------------ ------------------------------ ------------------------------
FAILED                                                        EXE_T_SH
SUCCEEDED                      hello world!                   EXE_T_SH

2 rows selected.

Elapsed: 00:00:00.00

有効化

ちなみに有効化すると即時実行されて、外部ローカルJOBはuser_scheduler_jobsから消される。

コード表示

begin
	dbms_scheduler.enable('EXE_T_SH');
end; 
/
begin
	dbms_scheduler.disable('EXE_T_SH');
end;
/

実行例

コード表示

AINE@pdb1> select job_name ,job_creator ,job_type ,job_action ,number_of_arguments ,schedule_type ,job_class ,enabled ,state ,run_count ,logging_level ,credential_owner ,credential_name ,comments from user_scheduler_jobs;

no rows selected

Elapsed: 00:00:00.13

ログ確認

うまくいった

コード表示

INE@pdb1> select to_char(s1.log_date,'yyyymmddhh24mi') as tp ,to_char(sysdate - interval '7' hour,'yyyymmddhh24mi') as from_tp ,to_char(sysdate - interval '0' hour,'yyyymmddhh24mi') as to_tp ,s1.job_name ,s1.job_class ,s1.operation ,s1.status from user_scheduler_job_log s1 where to_char(s1.log_date,'yyyymmddhh24mi') between to_char(sysdate - interval '7' hour,'yyyymmddhh24mi') and to_char(sysdate - interval '0' hour,'yyyymmddhh24mi') ;

TP           FROM_TP      TO_TP        JOB_NAME   JOB_CLASS  OPERATION                      STATUS
------------ ------------ ------------ ---------- ---------- ------------------------------ ------------------------------
201904071953 201904071636 201904072336 EXE_T_SH   DEFAULT_JO RUN                            FAILED
                                                  B_CLASS

201904072334 201904071636 201904072336 EXE_T_SH   DEFAULT_JO RUN                            SUCCEEDED
                                                  B_CLASS

201904072002 201904071636 201904072336 EXE_T_SH   DEFAULT_JO RUN                            SUCCEEDED
                                                  B_CLASS


3 rows selected.

Elapsed: 00:00:00.02


AINE@pdb1> select s1.status ,to_char(s1.log_date,'yyyymmddhh24mi') as tp ,s1.output ,s1.job_name from user_scheduler_job_run_details s1 where to_char(s1.log_date,'yyyymmddhh24mi') between to_char(sysdate - interval '7' hour,'yyyymmddhh24mi') and to_char(sysdate - interval '0' hour,'yyyymmddhh24mi') ;

STATUS                         TP           OUTPUT                         JOB_NAME
------------------------------ ------------ ------------------------------ ----------
FAILED                         201904071953                                EXE_T_SH
SUCCEEDED                      201904072002 hello world!                   EXE_T_SH
SUCCEEDED                      201904072334 hello world!                   EXE_T_SH

3 rows selected.

Elapsed: 00:00:00.00

いったんここまで。

よるも遅いし、創作意欲が全快になりそうなので、楽しみをとっておく。いろいろできそうだ。DBLINK先の外部リモートジョブのコールとかdocker内でできそうだし。。

Leave a Reply

Your email address will not be published. Required fields are marked *