Table of contents
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
- DBMS_SCHEDULER Extjob Fails With “Login Executable Not Setuid-Root” (Doc ID 961019.1)
- Scheduled Job Running Shell Script Fails With ORA-27369 (Doc ID 391820.1)
- Scheduler (DBMS_SCHEDULER) in Oracle Database 10g
Ludovic Kuty says:
You made my day 🙂 I didn’t know it ran as nobody.
Yannick Jaquier says:
Thanks for comment and happy to have helped !
jason says:
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.
Yannick Jaquier says:
Welcome ! I have not tried it myself but I do not see any restrictions for this feature in XE edition. For reference:
https://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm