Site Logo

IzzySoft


Das inoffizielle Android-HandbuchDas inoffizielle Android-Handbuch
Buy at Amazon for EUR 16,99
Das inoffizielle Android-SystemhandbuchDas inoffizielle Android-Systemhandbuch
Buy at Amazon for EUR 7,00
Die besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und StudiumDie besten Android-Apps: Android-Systemtools. Fotografie & Freizeit. Büro-Tools, Schule und Studium
 
As of 2019-11-13 11:31
prices & availability might be subject to change.

Start OS jobs from within Oracle

This article will describe how to run OS commands from inside an Oracle database. It requires an Oracle database at least of version 10g.

Contents

Configuring the Scheduler

Linux/Unix

As described in the Metalink document 391820.1, some tasks are to fulfill first to get the scheduler ready for "external jobs". So the configuration file $ORACLE_HOME/rdbms/admin/externaljob.ora needs some adjustments:

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

Alternatively, the group can also be dba, but the owner must be root. Except for root, no other user must have write permission to this file.

Now to the question: Under which OS user shall our "external jobs" tun? For security reasons, Oracle recommends an unprivileged user (e.g. nobody:nobody) here. But if you trust the users being able to create external jobs (or the admin is the only one), you may also decide for oracle:dba.

So place the user you decided for at the end of the file $ORACLE_HOME/rdbms/admin/externaljob.ora:

run_user = nobody
run_group = nobody

Finally, adjust permissions on the file $ORACLE_HOME/bin/extjob:

chown root:oinstall $ORACLE_HOME/bin/extjob
chmod 4750 $ORACLE_HOME/bin/extjob

Same as above also applies here: The group may as well be dba, everything else has to stay as described.

Windows

Generally spoken, above thoughts should apply here as well. But on Windows, the scheduler does not seem to care about those two files at all. Rather, here:

  1. The service OracleJobScheduler%ORACLE_SID% must be running
  2. User (and thus indirectly the group) must be configured via that service (Properties => Log On)

Required privileges

To create an external job, the database user needs a.o. the privileges CREATE JOB and CREATE EXTERNAL JOB.

Create an External Job

Now we can create an OS job:

DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'my_job',
   job_type => 'executable',
   job_action => '/path/to/executable',
   enabled => true,
   auto_drop => false
);

Pretty self-explaining – but permit a hint: job_name will be required to execute the job, so make it short and consise. If you set auto_drop => true, the job will automatically be removed immediately after its last execution.

Last execution? Well, above example is minimal. Of course you can specify further settings as e.g. schedules (it's the scheduler, right?). Details can be found in the Oracle documentation.

At least on Windows, one should redirect the output of the executable (e.g. job_action => 'C:\test.bat >nul'. As soon as output is generated, the scheduler tends to fail - so this will prevent that failure.

Run an Externen Job

Did you not specify a schedule at creation, or need to run the job out of the schedule, this can be done using the following command:

DBMS_SCHEDULER.RUN_JOB('my_job');

You may recognize the parameter to the procedure: it is the job_name defined at creation time.

Caveats

Start Directory

Scripts do not necessarily run from within the directory they are located in – but usually from the root directory /. If you need the scripts directory, at least in the Bash it's easy to tell:

cd ${0%/*}

ORA-27370: job slave failed to launch a job of type EXECUTABLE

ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:getting external job pid failed with status: 2
ORA-27301: OS failure message: No such file or directory

This usually means you did not follow the steps above, or missed at least a part of it. Check the permissions on both a.m. files, and the configured user as well.

ORA-27369: job of type EXECUTABLE failed with exit code: Incorrect function

In this case there's something wrong with the executed OS command:

ORA-27369: job of type EXECUTABLE failed with exit code: Not owner

ORA-27369: job of type EXECUTABLE failed with exit code: Not owner
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1

A quite common error – its reason is not always easy to tell. "Not owner" does not necessarily relate to the command called itself, but can as well be something the called script is touching. A classic:

. $HOME/.bashrc

Well thought – but in this case unfortunately wrong: it leads directly to above error, since $HOME is not defined. Thus the resulting file is /.bashrc, which is not owned by the executing user (in fact, it probably even does not exist at all - but we all now about the precision of our Oracle error messages, don't we?).

ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2

For those crazy people who run Oracle on a playstat… ahem, Windows: You need to have the OracleJobScheduler%ORACLE_SID% service started.

2018-12-16