测试过程如下
1、创建test shell test_job.sh
[oracle@db1 ~]$ pwd
/home/oracle
[oracle@db1 ~]$ vi test_job.sh
#!/bin/sh
echo "zzz ***"$(date)
env
pwd
id
cd /home/oracle/
. ~/.bash_profile
echo "=====new env"
env
2、创建JOB
调度/home/oracle/test_job.sh >> /home/oracle/test_job.log
exec dbms_scheduler.create_job(JOB_NAME=>'test_shell', job_type => 'EXECUTABLE',job_action=>'/home/oracle/test_job.sh >> /home/oracle/test_job.log',start_date => sysdate,repeat_interval=> 'FREQ=MINUTELY;BYSECOND=10', comments => 'test shell');
exec dbms_scheduler.enable(NAME=>'test_shell');
3、手工调度测试
SQL> exec dbms_scheduler.run_job(job_name => ‘test_shell’);
BEGIN dbms_scheduler.run_job(job_name => ‘test_shell’); END;
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: No such file or
directory
ORA-06512: at “SYS.DBMS_ISCHED”, line 238
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 568
ORA-06512: at line 1
通过MOS搜到以下文档
Clarification on Externaljob.ora and Permissions (Doc ID 979186.1)
主要描述当oracle job中调度外部命令时，以什么用户身份运行,详细请参考MOS文档
a) SYS SCHEMA 的外部作业以oracle用户身份执行
b) 非SYS SCHEMA 的外部作业以$ORACLE_HOME/rdbms/admin/externaljob.ora中配置的用户执行，默认为run_user=nobody and run_owner=nobody
但经过分析当前调度不是因为用户不对导致，默认SYS的JOB就是以oracle用户执行。
job_action参数执行的值就是一个可执行的程序，当作一个整体来判断，显然
“/home/oracle/test_job.sh >> /home/oracle/test_job.log” 是不存在的
job_action应指定’/bin/sh’ or ‘/bin/bash’
shell角本以参数传入
4、因为没办法直接以>重定向符号输出日志了，所以重新编辑角本，在shell中输出日志
$vi test_job.sh
#!/bin/sh
echo "zzz ***"$(date) >>/home/oracle/job.log
env >>/home/oracle/job.log
pwd >>/home/oracle/job.log
id >>/home/oracle/job.log
cd /home/oracle/
. ~/.bash_profile
echo "=====new env" >>/home/oracle/job.log
env >>/home/oracle/job.log
5、以正确的方法创建JOB
exec dbms_scheduler.drop_job('test_shell');
---创建job
exec dbms_scheduler.create_job(JOB_NAME=>'test_shell', job_type => 'EXECUTABLE',job_action=>'/bin/bash',number_of_arguments => 1,start_date => sysdate,repeat_interval=> 'FREQ=MINUTELY;BYSECOND=10', comments => 'test shell');
---设置参数
exec dbms_scheduler.set_job_argument_value(job_name=>'test_shell', argument_position=>1, argument_value=>'/home/oracle/test_job.sh') ;
--enable
exec dbms_scheduler.enable(NAME=>'test_shell');
6、执行job
exec dbms_scheduler.run_job(job_name => 'test_shell');
----日志示例
zzz ***Wed Mar 25 17:56:10 CST 2020
PWD=/
SHLVL=1
_=/bin/env
/
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
=====new env
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
QTDIR=/usr/lib64/qt-3.3
OLDPWD=/
QTINC=/usr/lib64/qt-3.3/include
LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/dbhome_1/lib:
ORACLE_SID=orcl11g
ORACLE_BASE=/oracle/app/oracle
PATH=/oracle/app/oracle/product/11.2.0/dbhome_1/bin:/bin:/sbin:/usr/bin:/usr/sbin:/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch:/opt/oracle.SupportTools:/home/oracle/ggs:/oracle/app/oracle/product/11.2.0/dbhome_1/OPatch
PWD=/home/oracle
LANG=en_us.zhs16gbk
GG_HOME=/home/oracle/ggs
SHLVL=1
QTLIB=/usr/lib64/qt-3.3/lib
CVS_RSH=ssh
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=192.168.56.2:0.0
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1
G_BROKEN_FILENAMES=1
_=/bin/env
总结
1、SYS SCHEMA 的外部作业以oracle用户身份执行
2、非SYS SCHEMA 的外部作业以$ORACLE_HOME/rdbms/admin/externaljob.ora中配置的用户执行，默认为run_user=nobody and run_owner=nobody
3、创建时要以/bin/bash or /bin/sh作为actiion,shell角本以参数创建
4、shell执行时当前工作目录是/，需要shell中改变工作目录
5、不能写重定向，需要shell中记录日志文件
6、没有自动调用.profile，与crontab一致，需要角本中调用
