How to submit an external script with Oracle scheduler

Preamble

While working on OCM certification I had to investigate more in details the Oracle scheduler that I do not know very well. I did not plan initially to write a post on this simple thing. My initial (simple) goal was to create a dummy Korn shell script and try to execute it from Oracle scheduler. Simple isn’t it ? This is what I initially thought and at the end I have spent a bit of time on it so decided to share and write this post…

All the other posts I have seen on Internet are not mentioning any issues while My Oracle Support (MOS) web site is full of documents on all the potential errors you might have…

I have done my testing using Oracle Enterprise Edition 11.2.0.4 running on two boxes one using Linux Oracle Linux Server release 7.1 and one using HPUX 11.31 on Itanium.

Oracle scheduler external job

Let’s first create a test table:

SQL> CREATE TABLE yjaquier.test01(val DATE);
 
TABLE created.

And a simple Korn Shell program:

#!/usr/bin/ksh
 
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
 
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
insert into yjaquier.test01 values(sysdate);
commit;
EOF

Remark:
Never acknowledge your environment is set when launching external script because it is not the case !!

Do not forget to make your script executable unless the run will fail and the job will be deleted by Oracle (!!):

*
ERROR AT line 1:
ORA-27370: job slave failed TO launch a job OF TYPE EXECUTABLE
ORA-27300: OS SYSTEM dependent operation:timed OUT getting external job p failed WITH status: 2
ORA-27301: OS failure message: No such FILE OR DIRECTORY
ORA-27302: failure occurred AT: sjsec 6a
ORA-06512: AT "SYS.DBMS_ISCHED", line 196
ORA-06512: AT "SYS.DBMS_SCHEDULER", line 486
ORA-06512: AT line 1

I start by creating a program with same name as executable:

SQL> EXEC DBMS_SCHEDULER.CREATE_PROGRAM(program_name=>'yannick01',program_type=>'EXECUTABLE',program_action=>'/home/oracle/yannick/yannick01',enabled=>TRUE,comments=>'Test progam yannick01');
 
PL/SQL PROCEDURE successfully completed.

Then wanted to create a job with same name and realized the name across program/job must be unique:

SQL> EXEC DBMS_SCHEDULER.CREATE_JOB(job_name=>'yannick01',program_name=>'yannick01',enabled=>FALSE,comments=>'Test job yannick01');
BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name=>'yannick01',program_name=>'yannick01',enabled=>FALSE,comments=>'Test job yannick01'); END;
 
*
ERROR AT line 1:
ORA-27477: "YJAQUIER.YANNICK01" already EXISTS
ORA-06512: AT "SYS.DBMS_ISCHED", line 135
ORA-06512: AT "SYS.DBMS_SCHEDULER", line 419
ORA-06512: AT line 1

So reverted and changed names:

SQL> EXEC DBMS_SCHEDULER.DROP_PROGRAM('yannick01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_SCHEDULER.CREATE_PROGRAM(program_name=>'yannick01_prog',program_type=>'EXECUTABLE',program_action=>'/home/oracle/yannick/yannick01',enabled=>TRUE,comments=>'Test progam yannick01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 200
SQL> col PROGRAM_ACTION FOR a30
SQL> col comments FOR a30
SQL> SELECT program_name,program_type,program_action,comments FROM DBA_SCHEDULER_PROGRAMS WHERE owner='YJAQUIER';
 
PROGRAM_NAME                   PROGRAM_TYPE     PROGRAM_ACTION                 COMMENTS
------------------------------ ---------------- ------------------------------ ------------------------------
YANNICK01_PROG                 EXECUTABLE       /home/oracle/yannick/yannick01 Test progam yannick01
 
SQL> EXEC DBMS_SCHEDULER.CREATE_JOB(job_name=>'yannick01_job',program_name=>'yannick01_prog',enabled=>FALSE,comments=>'Test job yannick01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col PROGRAM_NAME FOR a30
SQL> SELECT job_name,program_name,comments FROM DBA_SCHEDULER_JOBS WHERE owner='YJAQUIER';
 
JOB_NAME                       PROGRAM_NAME                   COMMENTS
------------------------------ ------------------------------ ------------------------------
YANNICK01_JOB                  YANNICK01_PROG                 Test job yannick01

First run:

SQL> EXEC DBMS_SCHEDULER.RUN_JOB('yannick01_job');
BEGIN DBMS_SCHEDULER.RUN_JOB('yannick01_job'); END;
 
*
ERROR AT line 1:
ORA-27370: job slave failed TO launch a job OF TYPE EXECUTABLE
ORA-27300: OS SYSTEM dependent operation:timed OUT getting external job p failed WITH status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred AT: sjsec 6a
ORA-06512: AT "SYS.DBMS_ISCHED", line 196
ORA-06512: AT "SYS.DBMS_SCHEDULER", line 486
ORA-06512: AT line 1

Not really helpful to solve, going in DBA_SCHEDULER_JOB_RUN_DETAILS I have been able to find more information. I also realized that what is displayed on screen when having running issue is of no help most of the time:

ORA-27370: job slave failed TO launch a job OF TYPE EXECUTABLE
ORA-27300: OS SYSTEM dependent operation:timed OUT getting external job p failed WITH status: 2
ORA-27301: OS failure message: No such FILE OR DIRECTORY
ORA-27302: failure occurred AT: sjsec 6a

Then I found in Oracle official documentation below table:

Job in SYS Schema? Platform Default Credentials
Yes All User who installed Oracle Database.
No UNIX and Linux Values of the run-user and run-group attributes specified in the file ORACLE_HOME/rdbms/admin/externaljob.ora
No Windows User that the OracleJobSchedulerSID Windows service runs as (either the Local System account or a named local or domain user).

Note: You must manually enable and start this service. For improved security, Oracle recommends using a named user instead of the Local System account.

I am using my own account and so using to run the script the account and group you can see in externaljob.ora file:

server1{oracle}# cat $ORACLE_HOME/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

Which is the default and obviously this account has no right to launch anything on my server. I’m even asking myself the added value to set a so low privileges default account… Moreover on HPUX the nobody group does not exist and is instead called nogroup.

So decided to create a credential and recreate my job (that has been deleted after the unsuccessful run, so the auto_drop property to false):

SQL> EXEC DBMS_SCHEDULER.CREATE_CREDENTIAL(credential_name=>'oracle', username=>'oracle', password=>'secure_password', database_role=>'SYSDBA');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT credential_name,username,database_role FROM dba_scheduler_credentials WHERE owner='YJAQUIER';
 
CREDENTIAL_NAME                USERNAME                                                         DATABAS
------------------------------ ---------------------------------------------------------------- -------
ORACLE                         oracle                                                           SYSDBA

Recreating the job using it:

SQL> EXEC DBMS_SCHEDULER.CREATE_JOB(job_name=>'yannick01_job',program_name=>'yannick01_prog',enabled=>FALSE,auto_drop=>FALSE,comments=>'Test job yannick01',credential_name=>'ORACLE');
 
PL/SQL PROCEDURE successfully completed.

And everything worked like a charm and a new row has been added to my test table…

Schedule the job can be easily done with start_date, repeat_interval and end_date parameters of DBMS_SCHEDULER.CREATE_JOB procedure or by creating a specific schedule object.

References

2 thoughts on “How to submit an external script with Oracle scheduler

  1. Thank you for the helpful post. I am trying this with oracle-xe 11g, but I get all kinds of errors. Does your post also work with Oracle XE db the same way as the regular oracle db? Thank you.

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>