There are several sites that explain quickly how to run an external OS job from the Oracle Job Scheduler, but I found several things that none of them covered that I needed for it to work in the real world. Here’s what I did and some extras.
First, you will need to grant the create external job privilege to your user:
grant create external job to myuser;
Then you can create your job:
BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'LOAD_STUFF', job_type => 'EXECUTABLE', job_action => '/stage/stuff/LoadStuff.ksh', repeat_interval => 'FREQ=DAILY; BYHOUR=14', number_of_arguments => 2, enabled => FALSE); END; /
In my case the script required passing some command line parameters to it. This won’t work if you include them as part of the JOB_ACTION clause, so you have to define them as job arguments. I’m not sure if you can define them together as one argument, I just did it as two – you can try one if you like.
exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 'LOAD_STUFF', argument_position => 1, argument_value => '-s'); exec DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name => 'LOAD_STUFF', argument_position => 2, argument_value => 'mydb');
In this case I was running on RAC, and didn’t have an ACFS shared filesystem, so I needed to make sure the script was always run on the same instance because the script was using a file that is loaded onto a local filesystem on one node. To do this just set the instance for the job.
Once those are defined you can finally enable the job.
The next catch with this is that by default Oracle runs any executable as the nobody:nobody Linux user. If your script requires any privileges that nobody doesn’t have then you will have to change who Oracle runs executables as.
The simple, but not security conscious way to do this is to modify the run_user and run_group variables in $ORACLE_HOME/rdbms/admin/externaljob.ora. I did this because the system is an internal system, well protected, and we could not have a password for the oracle linux account.
If you want to be more secure about it you can specify the OS user credentials to use with the DBMS_SCHEDULER.CREATE_CREDENTIAL procedure. That will store the OS credentials (user/password) with the job. This is important because then only that job has the ability to access that account instead of any external job.
Troubleshooting is a little more interesting because anything not trapped by the OS script goes back to the scheduler, so checking failures means looking in the %_SCHEDULER_JOB_RUN_DETAILS view and then possibly also your scripts log file if it has one.
I hope this helps make creating external Oracle scheduler jobs easier.