Learning is not a spectator sport

One of the cool but often rarely exploited facilties in the Oracle Job Scheduler is the ability to run tasks outside of the database, for example, shell scripts or other programs on the OS. But it does require getting all the relevant ducks in a row, and if you haven’t, then sometimes the errors can be a little cryptic.

I was helping a customer troubleshoot why their OS scripts were not successfully being executed by the scheduler, so I thought I would summarise some of the common problems here.

To set things up, here is the simple shell script I want to be able to run from the scheduler. It takes a parameter from the command, and everything gets stored in output.txt

[oracle@db239 admin]$ cat /tmp/test.sh 
#!/usr/bin/bash
echo $1 > /tmp/output.txt
id >> /tmp/output.txt

and this script runs fine from the command line.

$ /tmp/test.sh Hello
$ cat /tmp/output.txt 
Hello
uid=1000(oracle) gid=1000(oracle) groups=1000(oracle)

This is probably the most common area of frustration, namely that we can demonstrate that the script runs as we would expect, but it won’t run from the scheduler. Let’s look at some common issues

1) Arguments not command line

We pass “Hello” from the command line, but the scheduler is not a command line “surrogate”; it is a program that runs executables. Thus the following scheduler call, whilst being an intuitive choice, is not correct

SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_JOB(
  3       job_name=>'RUN_A_SHELL',
  4       job_type=>'EXECUTABLE',
  5       job_action=>'/tmp/test.sh Hello');
  6  end;
  7  /

This would try to run a program called “/tmp/test.sh Hello“, not the program “/tmp/test.sh”. If you want to pass arguments to an executable, this must be done explicitly within the scheduler, as below

SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_JOB(
  3       job_name=>'RUN_A_SHELL',
  4       job_type=>'EXECUTABLE',
  5       job_action=>'/tmp/test.sh',
  6       enabled=>FALSE,
  7       number_of_arguments => 1
  8       );
  9
 10       DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
 11          job_name => 'RUN_A_SHELL',
 12          argument_position => 1,
 13          argument_value => 'Hello');
 14
 15      DBMS_SCHEDULER.ENABLE('RUN_A_SHELL');
 16  end;
 17  /

PL/SQL procedure successfully completed.

With that out of the way, we now jump into the OS to see if our output file got created.

[root@vbox ~]# ls -l /tmp/out*
ls: cannot access '/tmp/out*': No such file or directory

No luck (yet). Which brings us to the next common issue

2) The external configuration file

Whenever a scheduler job (any job, not just external script), an excellent place to look is the ADR trace location. In the scheduler job trace file from the above call, we see the following:

FREEPDB1(3):Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_j000_3894.trc:
ORA-12012: error on auto execute of job "SYSTEM"."RUN_A_SHELL"
ORA-27369: job of type EXECUTABLE failed with exit code: 274666 Oracle Scheduler error: Cannot access or open configuration file.

Unfortunately, the error does not tell us the name or location of the “configuration file” so you’ll just have to take my word for it 🙂
We need to have a file in $ORACLE_HOME/rdbms/admin called “externaljob.ora”. Within that file, we need to nominate the OS user and group that our external jobs will be run under.
I’ve set mine to nobody/nobody (which also should be the default when you do a default install of the software).

Note: If you create a job as the SYS user, it will run as the ‘oracle’ account. So obviously this is generally a schema you do not want to be scheduling external jobs from, and if you, you need to extremely careful.

[root@vbox admin]# pwd
/opt/oracle/product/23ai/dbhomeFree/rdbms/admin

[root@vbox admin]# cat externaljob.ora
run_user = nobody
run_group = nobody

With the file in place, I try again and … still no luck.

[root@vbox ~]# ls -l /tmp/out*
ls: cannot access '/tmp/out*': No such file or directory

Which brings us to the next common issue

3) The external configuration file permissions

The point of having this controlled by a file external to the database, is to that we can separate responsibilities between the database (the DBA) and the operating system (the OS administrator) because these might be two different people, two different departments who might even be located in two different building, cities or even continents. A look at the trace file created gives us the important clue.

FREEPDB1(3):Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_j000_3894.trc:
ORA-12012: error on auto execute of job "SYSTEM"."RUN_A_SHELL"
ORA-27369: job of type EXECUTABLE failed with exit code: 274662 Oracle Scheduler error: Config file is not owned by root or is writable

So I can fix that by ensuring that only root has any write permissions on the file.

[root@vbox admin]# chown root:oinstall externaljob.ora 
[root@vbox admin]# chmod 640 externaljob.ora 

With that change made, I try again and … still no luck.

[root@vbox ~]# ls -l /tmp/out*
ls: cannot access '/tmp/out*': No such file or directory

4) Who is executing your script

The trace file now is a little cryptic.

FREEPDB1(3):Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_j001_3896.trc:
ORA-12012: error on auto execute of job "SYSTEM"."RUN_A_SHELL"
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:Waiting for extjob to send child failed with status: 62
ORA-27301: OS failure message: Timer expired
ORA-27302: failure occurred at: sjsec 6d

This one often catches people out. What tends to happen is that a database admin will login to the OS as (say) ‘oracle’, and do all of their work as that user. Which means that they probably created the shell script under the ‘oracle’ user, and only the oracle user can run it. Remember, we have just created a configuration file that says the script will be run as the user ‘nobody’, so your script needs to be runnable as ‘nobody’. In my simple example,
I will just tweak the permissions

[oracle@vbox tmp]$ chmod 755 test.sh 

but in a real production scenario (depending on what the script is doing) you might want to firm things up from a security perspective. Perhaps you will have a dedicated user and group for running such scripts, so that the owner/editor of the script is not the same as the user that is running the script to ensure no malicious script modifications.

Finally after these changes, I do indeed get an output file which means my job was run.

[root@vbox ~]# ls -l /tmp/out*
-rw-r--r-- 1 nobody nobody 63 Aug 25 12:56 /tmp/output.txt
[root@vbox ~]# cat /tmp/output.txt
Hello
uid=65534(nobody) gid=65534(nobody) groups=65534(nobody)

You can see that the output file is owned by ‘nobody’ as indicated also by the output of the ‘id’ command within the script.

So everything is fine and it seems we should be good to go. But what happens when I try add in a call to SQL*Plus
in my script

which brings us to the final common issue

5) You start with “nothing”

Remember, we are not establishing a typical ‘oracle’ login shell, so we commence with the absolute bare minimum environment. I added an ‘env’ command to the script and we can that the output is hardly changed.

So you need to ensure that you set up all of the environment you need to run programs, for example, setting the PATH to find ‘oraenv’, then setting ORACLE_SID, and then running oraenv to setup the standard Oracle environment.

Thankfully, most of these things are one-and-done, and now you’re ready to exploit this very cool scheduler feature.