因业务需要,有时候要使用数据库里面的job调用服务器上的脚本完成特定功能。这里演示一下如果利用dbms_scheduler包完成工作。以及可能会遇到的各种错误及解决方法。

1.       首先在服务器上的/home/oracle/scripts目录里创建一个示例脚本demo.sh

[oracle@11gR2_64 scripts]$ more demo.sh

#!/bin/bash

 

echo `date` >> /home/oracle/scripts/demo.log

[oracle@11gR2_64 scripts]$

 

注意输出日志部分,这里最好使用绝对路径,原因后面会再讲到。

 

2.       dbms_scheduler.create_job 创建一个job。注意job类型为EXECUTABLE

                SQL> begin

                  2    dbms_scheduler.create_job(job_name => 'job_test1', job_type => 'EXECUTABLE', job_action => '/home/oracle/scripts/demo.sh');

                  3  end;

                  4  /

                PL/SQL procedure successfully completed.

                SQL>

 

3.       dbms_scheduler.run_job函数执行job

        SQL> begin

          2    dbms_scheduler.run_job(job_name => 'job_test1');

          3  end;

          4  /

        begin

        *

        ERROR at line 1:

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

        ORA-06512: at "SYS.DBMS_ISCHED", line 196

        ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

        ORA-06512: at line 2

 

        SQL>

可以看到,这里报了个没有执行权限的错误。这是因为在10.2.0.2以后,在$ORACLE_HOME/rdbms/admin 里面有一个externaljob.ora的配置文件。该文件里面配置的是执行job时的用户。里面默认的配置用户如下2行:

run_user = nobody

run_group = nobody

 

我们这里需要将其改为oracle用户及用户组。改完后,如下:

run_user = oracle

run_group = oinstall

 

4.       externaljob.ora文件修改完后,还有几个文件需要配置:

$ORACLE_HOME/bin/extjob 文件的用户及用户组必须是 root:oraclegroup, 并且需要setuid,如:chmod 4750(-rwsr-x—)

$ORACLE_HOME/bin/extjob 文件的用户及用户组可设为 oracle:oraclegroup, 给于普通的755权限即可。

 

 [root@11gR2_64 bin]# ll | grep job

-rwsr-x— 1 root   oinstall   1254458 May  5 13:20 extjob

-rwxr-xr-x 1 oracle oinstall   1254458 May  5 13:20 extjobo

-rwxr-xr-x 1 oracle oinstall   1255589 Aug 24  2013 extjobO

-rwxr-xr-x 1 oracle oinstall   1255589 Aug 24  2013 extjoboO

 

11g后在bin下还有一个jssu的可执行程序,需要有rootsetuid权限。

[root@11gR2_64 bin]# ll | grep jssu

-rwsr-x— 1 root   oinstall     43720 May  5 13:20 jssu

[root@11gR2_64 bin]#

 

5.       一般安装上述设置完成后,就可以job就可以正常工作了。

SQL> begin

  2    dbms_scheduler.run_job(job_name => 'job_test1');

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

SQL>

 

检查一下主机上的demo.log日志文件

[oracle@11gR2_64 scripts]$ more demo.log

Sun Aug 3 10:54:46 HKT 2014

可以看到,日志有正确记录。

 

6.       最后再回过头说下最开始提到的,为什么最好要用绝对路径。如果我们改为相对路径,如下:

[oracle@11gR2_64 scripts]$ vi demo.sh

#!/bin/bash

 

echo `date` >> ./demo.log

 

再执行一遍job

SQL> exec dbms_scheduler.run_job(job_name => 'job_test1');

BEGIN dbms_scheduler.run_job(job_name => 'job_test1'); END;

 

*

ERROR at line 1:

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

permitted

ORA-06512: at "SYS.DBMS_ISCHED", line 196

ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

ORA-06512: at line 1

 

SQL>

 

可以看到,又报了一个Operation not permitted的错误。我们到dba_scheduler_job_run_details视图里面查看一下详细信息。

SQL> col additional_info for a120

SQL> select *

  2    from (select t.additional_info

  3            from dba_scheduler_job_run_details t

  4           where owner = 'KEVIN'

  5             and job_name = 'JOB_TEST1'

  6           order by log_id desc)

  7   where rownum = 1;

 

ADDITIONAL_INFO

————————————————————————————————————————

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

STANDARD_ERROR="/home/oracle/scripts/demo.sh: line 3: demo.log: Permission denied"

 

SQL>

 

日志里面可以看到,demo.sh文件里面的第3行报执行权限错误。也就是echo `date` >> ./demo.log 写日志这行出错了。因为oracle调用job执行demo.sh这个脚本的时候,并不是cd /home/oracle/scripts 这个目录下执行的,因此对当前执行目录可能根本就没有写入权限。这里修改很简单,只需要将路径写全就可以了。

 

附几个常见的错误列表:

 

274661 – can't get owner of or permissions of externaljob.ora file
274662 – not running as root or externaljob.ora file is writable by group or other or externaljob.ora file not owned by root (can't switch user)
274663 – setting the group or effective group failed
274664 – setting the user or effective user failed
274665 – a user or group id was not changed successfully
274666 – cannot access or open externaljob.ora file
274667 – invalid run_user specified in externaljob.ora file
274668 – invalid run_group specified in externaljob.ora file
274669 – error parsing externaljob.ora file
274670 – extjobo is running as root user or group




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件