How do oracle external jobs work – reference for the new oracle dba





Below article contains a discussion on oracle external jobs and what should be done to fix problems with oracle external jobs.

An external job is a normal database scheduler job which actually calls scripts outside the database such as shell scripts.

Hi,

> In my script, I have log files but I cannot see it. I guess the job fails directly without
> hitting the redirection line in the script.

Yes that is what I mean to have redirection in your script itself. Perhaps have a simple
date > /tmp/out.log
at the beginning of the script ? The other way, having the redirection as part of the job_action will not work.

> But OS user and database user are two different accounts at different level.
> I am using 10.2.0.2 RAC. The Unix script runs successfully every night.

Right and external jobs need to run as some OS user. On 10.2.0.2 regular user external jobs run an the OS user that owns the $ORACLE_HOME/bin/extjob executable which is nobody by default (and typically nobody can’t do a lot).

On 10.2.0.2 external jobs in the SYS schema run as the OS user that oracle runs as so that may be useful for some purposes.

Hope this clears things up a bit.
-Ravi

If nothing is running then you should check your permissions, in particular, the user that the job runs as must have execute access on $ORACLE_HOME/bin and all parent directories. So for e.g. if your $ORACLE_HOME is /opt/oracle/db then you would have to make sure that

chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

Thank you Ravi for your help to the community. I am having trouble running a simple sqlplus script (UNIX) from the scheduler (Oracle 10.2.0.3 on Solaris). I would like to understand better how the whole thing works…

1) Create a job as a user (who has the create job, create external job permissions – but isn’t necessarily the user in externaljob.ora). Since I am setting up a GUI front end using Application Express, I am guessing that the user creating the job is the schema owner for the workspace.

2) If it is an "immediate" run, the scheduler goes to the location identified for the script (shell script in my case which does specifically set the environment variables ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH) and tries to run it. What user does it do this as? The schema and job owner does not have an OS userid…

3) The script includes a direct call to sqlplus ($ORACLE_HOME/bin/sqlplus << EOF etc…) – does the scheduler try to get into sqlplus as the schema/job owner?

If I see a 0 on the returned error and there is "SUCCESS" – but no spooled file or proof that the sql code ran, what was successful?

If I see "ORA-27369: job of type EXECUTABLE failed with exit code: Not owner" – does this refer to the directory where I want to create the spool file? (or what?)

I have read and re-read your how-to and a lot of the forum entries – and have set the permissions/ownerships accordingly – without success.

What exactly does Oracle do with nobody:nobody? Use that as the OS user? What happens if the user isn’t an OS user?

I tried with nobody:nobody in externaljob.ora but that didn’t work. (there is not an OS user called nobody so I’m not sure how it could).
I tried with oracle:dba and was able to get a successful run (but, as mentioned, I’m not sure what was successful since there isn’t a .lst file that I can find).

Thanks,
Steve

Steve,

The Oracle 10g Scheduler is pretty brain dead in terms of security. 11g fixes a lot of that.

In 10g, every external job runs under the same account, the one specified in $ORACLE_HOME/rdbms/admin/externaljob.ora . If you want your job to work, I recommend using:

run_user = oracle
run_group = dba

Be aware that Solaris, at least at our site, does not have some of the standard environment variables defined when the Oracle job starts executing the script. We had to "go around the barn" to populate them. For example, we wrote a generic Korn script (named dbms_scheduler_shell.ksh below) to populate the missing variables and then call the real script that we wanted to run. Our dbms_scheduler_shell.ksh script also translates our goobledygook server names into something readable–your code will not likely need to do that. Following that is the SQL that we used to define the DBMS_SCHEDULER job. If you want a log file, you have to handle the redirection yourself in 10g. 11g handles that. Please see the "argument_value" in the SQL below for an example of the redirection that we use.

By the way, the only way I know to avoid hardcoding a password is to connect to the database with:

sqlplus / as sysdba

I highly recommend executing the following in your shell script for debugging purposes. You will likely be amazed at what is missing, at least on Solaris.

env | sort

Hope this helps. I spent WEEKS on getting this to work at our site for the first time.

Jonathan

http://forums.oracle.com/forums/thread.jspa?threadID=555102&start=30&tstart=0

Hi Steve,

Sorry I wasn’t able to get back to you before . I have some specific answers to your questions.

1) I think so but don’t know. Easy enough to figure out though – create the job and then query the all_scheduler_jobs view to see the owner.

2) This is covered in the parent post for this thread – in summary
– in 11g if you specify a credential , it runs as the OS user specified there
– in 10.2.0.2 and up it runs as the OS user/group specified in $ORACLE_HOME/rdbms/admin/externaljob.ora
– prior to 10.2.0.2, it runs as the owner of the $ORACLE_HOME/bin/extjob executable

3) No. The external job has no knowledge of the oracle schema it is running as. If you are running PL/SQL code you should try to use regular pl/sql jobs if possible. If you absolutely need sqlplus, you can use OS authentication for sqlplus so you don’t have to specify a password.

4) This just means that the script ran to completion and the final line of the script returned a error number (return code) of zero (every OS command has a return code / error number). It is not a guarantee that every line of the script ran successfully. For 10gR2 and before you should redirect stderr (and probably stdout) to a file for future analysis . For 11g if you use a credential we store stderr and stdout in separate files and allow for their retrieval from pl/sql .

What the script is responsible for doing is ensuring that it returns 0 whenever it has succeeded and non-zero whenever it fails. This is fairly standard.

5) "Not owner" is not really the error message. Instead the scheduler is converting the error number into a system error message which happens to be "not owner" . What you need to find is what error number corresponds to that system error message on your operating system (probably 1) and then figure out why your script is returning that error code.

6) Where exactly are you seeing nobody:nobody ? the contents of externaljob.ora should look like
run_user = nobody
run_group = nobody

Jobs with no credential specified will run with the run-user and run-group specified (as the OS user and group). If either does not exist, the Scheduler should throw an error. It is possible that the user nobody does exist but doesn’t have required permissions to run the required actions.

Hope this helps, reply if you have any further questions.

-Ravi

http://forums.oracle.com/forums/thread.jspa?messageID=2689963

GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER

NOTE: Users using 11g should use the new method of specifying a credential which eliminates many of the issues mentioned in this note.

This guide covers several common questions and problems encountered when using
dbms_scheduler to run external jobs, either on Windows or on UNIX.

What operating system (OS) user does the job run as ?

External jobs which have a credential (available in 11g) run as the user
specified in the credential. But for jobs without credentials including
all jobs in 10gR1 and 10gR2 there are several cases.

– On UNIX systems, in releases including and after 10.2.0.2 there is a file $ORACLE_HOME/rdbms/admin/externaljob.ora . All external jobs not in the SYS schema and with no credential run as the user and group specified in this file. This should be nobody:nobody by default.

– On UNIX systems, in releases prior to 10.2.0.2 there was no "externaljob.ora" file. In this case all external jobs not in the SYS schema and with no credential run as the owner and group of the $ORACLE_HOME/bin/extjob file which should be setuid and setgid. By default extjob is owned by nobody:nobody except for oracle-xe where it is owned by oracle:oraclegroup and is not setuid/setgid.

– On Windows, external jobs not in the SYS schema and with no credential run as the user that the OracleJobScheduler Windows service runs as. This service must be started before these jobs can run.

– In all releases on both Windows and UNIX systems, external jobs in the SYS schema without a credential run as the oracle user.

What errors are reported in *_SCHEDULER_JOB_RUN_DETAILS views ?

If a job fails, the first place to look for diagnostic information is the *_SCHEDULER_JOB_RUN_DETAILS set of views. In 10gR2 and up the first 200 characters of the standard error stream is included in the additional_info column.

In all releases, the error number returned by the job is converted into a
system error message (e.g. errno.h on UNIX or net helpmsg on Windows) and that
system error message is recorded in the additional info column. If there is no
corresponding message the number is displayed.

In 11g and up the error number returned by the job is additionally recorded in
the error# column. In earlier releases 27369 would always be recorded in the
error# column.

Generic Issues Applicable to UNIX and Windows

– The job action (script or executable) must return 0 or the job run will be marked as failed.

– Always use the full pathname to executables and scripts.

– Do not count on environment variables being set in your job. Make sure that the script or executable that your jobs runs sets all required environment variables including ORACLE_HOME, ORACLE_SID, PATH etc.

– It is not recommended to pass in a complete command line including arguments as the action. Instead it is recommended to pass in only the path to and name of the executable and to pass in arguments as job argument values.

– Scripts with special characters in the execution path or script name may give problems.

– Ensure that the OS user your job runs as has the required privileges/permissions to run your job. See above for how to tell who the job runs as.

– External job actions cannot contain redirection operators e.g. > > | && ||

– In general try getting a simple external job working first e.g. /bin/echo or ipconfig.exe on Windows. Also try running the job action directly from the commandline as the OS user that the job will run as.

Windows-specific Issues

– The OracleJobScheduler Windows service must be started before external jobs will run (except for jobs in the SYS schema and jobs with credentials).

– The user that the OracleJobScheduler Windows service runs as must have the "Log on as batch job" Windows privilege.

– A batch file (ending in .bat) cannot be called directly by the Scheduler. Instead cmd.exe must be used and the name of the batch file passed in as an argument. For example

begin

 dbms_scheduler.create_job(‘myjob’,

   job_action=>’C:\WINDOWS\SYSTEM32\CMD.EXE’,

   number_of_arguments=>3,

   job_type=>’executable’, enabled=>false);

 dbms_scheduler.set_job_argument_value(‘myjob’,1,’/q’);

 dbms_scheduler.set_job_argument_value(‘myjob’,2,’/c’);

 dbms_scheduler.set_job_argument_value(‘myjob’,3,’c:\temp\test.bat’);

 dbms_scheduler.enable(‘myjob’);

 end;

/

– In 10gR1 external jobs that wrote to standard output or standard error streams would sometimes return errors. Redirect to files or suppress all output and error messages when using 10gR1 to run external jobs.

UNIX-specific Issues

– When running scripts, make sure that the executable bit is set.

– When running scripts directly, make sure that the first line of the script in a valid shebang line – starting with "#!" and containing the interpreter for the script.

– In release 10.2.0.1, jobs creating a large amount of standard error text may hang when running (this was fixed in the first 10.2.0.2 patchset). If you are seeing this issue, redirect standard error to a file in your job. This issue has been seen when running the expdp utility which may produce large amounts of standard error text.

– the user that the job runs as (see above section) must have execute access on $ORACLE_HOME/bin and all parent directories. If this is not the case the job may be reported as failed or hang in a running state. For example if your $ORACLE_HOME is /opt/oracle/db then you would have to make sure that

chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

– On oracle-xe, the primary group of your oracle user (if it exists) must be dba before you install oracle-xe for external jobs to work. If you have an oracle user from a regular Oracle installation it may have the primary group set to oinstall.

– On oracle-xe, the extjobo executable is missing so external jobs in the SYS schema will not work properly. This can be fixed by copying the extjob executable to extjobo in the same directory ($ORACLE_HOME/bin).

– Check that correct permissions are set for external job files – extjob and externaljob.ora (see below)

Correct permissions for extjob and externaljob.ora on UNIX

There is some confusion as to what correct permissions are for external job related files.

In 10gR1 and 10.2.0.1 :

– rdbms/admin/externaljob.ora should not exist

– bin/extjob should be setuid and setgid 6550 (r-sr-s—). It should be owned by the user that jobs should run as and by the group that jobs should run as.

– bin/extjobo should have normal 755 (rwxr-xr-x) permissions and be owned by oracle:oraclegroup

In 10.2.0.2 and higher

– rdbms/admin/externaljob.ora file must must be owned by root:oraclegroup and be writable only by the owner i.e. 644 (rw-r–r–) It must contain at least two lines: one specifying the run-user and one specifying the run-group.

– bin/extjob file must be also owned by root:oraclegroup but must be setuid i.e. 4750 (-rwsr-x—)

– bin/extjobo should have normal 755 (rwxr-xr-x) permissions and be owned by oracle:oraclegroup

In 11g and higher

Same as 10.2.0.2 but additionally bin/jssu should exist with root setuid
permissions i.e. owned by root:oraclegroup with 4750 (-rwsr-x—)

Internal Error numbers for UNIX on 10.2.0.2 or 10.1.0.6 or higher

If you are not using a credential and are using version 10.2.0.2 or higher or 10.1.0.6 or higher you may come across an internal error number. Here are the meanings for the internal error numbers.

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

Author: admin