使用dbms_scheduler调用shell脚本

来源：互联网发布：ecshop微商城源码下载编辑：程序博客网时间：2024/06/10 04:57

--1,创建programbegin  dbms_scheduler.create_program(program_name   => 'lcy_test_prog',                                program_type   => 'EXECUTABLE',                                program_action => '/lcytest/File_test.sh',                                enabled        => true,                                comments       => 'oracle database rman level 0 backup');end;

--2,创建schedule 每天凌晨运行一次begin  dbms_scheduler.create_schedule(schedule_name   => 'lcy_test_sche',                                 repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',                                 comments        => 'oracle database rman level 0 backup schedule 1');end;

--3,创建job,此处若提示权限问题，则需要grant --grant CREATE EXTERNAL JOB to db-user;begin  dbms_scheduler.create_job(job_name      => 'lcy_test_job',                            program_name  => 'lcy_test_prog',                            schedule_name => 'lcy_test_sche',                            enabled       => true);end;

--4,执行job,一般来说此处必会提示错误：--ora-27369 job of type excutalble failed with exit code...--请参考文档：https://community.oracle.com/thread/557102--通俗来说，需要修改$ORACLE_HOME/rdbms/admin/externaljob.ora文件--将其中的run_user/run_group改为自己DB服务器的用户，并有权限可以执行externaljob.ora文件begin  --  --DBMS_SCHEDULER.ENABLE('lcy_test_job');  --  dbms_scheduler.run_job(job_name => 'lcy_test_job');  --  --DBMS_SCHEDULER.DISABLE('lcy_test_job');  end;

-- All jobsselect * from user_scheduler_jobs;-- Get information to jobselect * from user_scheduler_job_log order by log_date desc;-- Show details on job runselect * from user_scheduler_job_run_details;

以下为报错列表：

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

参考文档：

http://blog.itpub.net/23135684/viewspace-777177/

Clarification on Externaljob.ora and Permissions (文档 ID 979186.1)

有问题基本上都是权限问题

NOTE:

1. Users using 11g should use the new method of specifying a credential which eliminates many of the issues mentioned in this note. External jobs which have a credential (available in 11g) run as the user specified in the credential.

2. Relinking the Binary (Using "relink all") might also change the permissions and ownership for files jssu,extjob,externaljob.ora.You need to manually change the permissions and ownership.As root user,please perform

# chown root $ORACLE_HOME/bin/extjob
# chmod 4750 $ORACLE_HOME/bin/extjob

# chown root $ORACLE_HOME/rdbms/admin/externaljob.ora
# chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora

# chown root $ORACLE_HOME/bin/jssu
# chmod 4750 $ORACLE_HOME/bin/jssu

0 0