dbms_scheduler: How can I change the default user and shell running the scripts?

[shew01]

We are running Oracle 10.2.0.3 on Solaris 10. I have a strong background with third party enterprise job schedulers (UC4 and CA AutoSys), and I am trying to avoid a big ticket purchase by implementing Oracle Scheduler at a customer site. I need to run various shell scripts under different Unix users’ credentials, and I need to save the output from the scripts to log files on the Operating System for potential later review.

Here is a sample test script that I am running:

#!/usr/bin/ksh

set +x # Make sure that command echo is turned off (to clean up output)
ScriptName=$(basename $0) # Equivalent shorthand code is: ${0##*/}
ScriptPath=$0 # $0 gets set by the preceeding $(basename $0) syntax

date +"%n%D %T %A%n"

ServerName=$(hostname) # Unix host name
UserName=$USER # Unix user name ($LOGNAME holds the same information)
StartingDirectory=$(pwd) # Starting Unix directory

echo "ServerName=$ServerName"
echo "UserName=$UserName"
echo "LOGNAME=$LOGNAME"
echo "StartingDirectory=$StartingDirectory"
echo "ScriptName=$ScriptName"
echo "ScriptPath=$ScriptPath"
echo "SHELL=$SHELL" # Unix shell name

Here is the Oracle PL/SQL syntax I am using to launch the job:

begin
dbms_scheduler.create_job(
job_name => 'jps_test_executable',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
number_of_arguments => 2
);

dbms_scheduler.set_job_argument_value (
job_name => 'jps_test_executable',
argument_position => 1,
argument_value => '-c'
);

dbms_scheduler.set_job_argument_value (
job_name => 'jps_test_executable',
argument_position => 2,
argument_value => './tmp/OracleScheduler/display_shell.ksh drtest incr1>/tmp/OracleScheduler/display_shell.log'
);

dbms_scheduler.enable('jps_test_executable');
end;
/

This job runs, but the resulting login credentials are unexpected. Here is the output from the job:

12/11/07 14:10:33 Tuesday

ServerName=radfa4122p07840
UserName=
LOGNAME=
StartingDirectory=/
ScriptName=display_shell.ksh
ScriptPath=./tmp/OracleScheduler/display_shell.ksh
SHELL=/bin/sh

Can anyone help me resolve these issues?

1. The Operating System username (i.e., $USER and $LOGNAME) returns as blank or null (I’m not sure which). How can I specify which Operating System user will run the job?
2. The shell appears to be Bourne, even when I specifically execute a “ksh” command in a variant of the script. How can I force the job to run under the Korn shell?

Any and all help is appreciated!

J

[davey23uk]

whats up with cron? all jobs will run as the oracle user on the database

[shew01]

We are trying to move away from cron. You know, we need a new sexy GUI interface... LOL

Actually, we are looking for more complex processing, like Oracle job chaining. But, at this point, I'm just looking for proof-of-concept.

[shew01]

whats up with cron? all jobs will run as the oracle user on the database

Are you sure? If that is the case, shouldn't the log file be owned by the Unix oracle account, rather than "nobody?"

ls -l display_shell.log
-rw-rw-rw- 1 nobody nobody 225 Dec 12 07:14 display_shell.log

Also, the job cannot read or write any files in the oracle account's $HOME directory tree.

[ebrian]

The scheduler will run as nobody by default. You should configure the file $ORACLE_HOME/rdbms/admin/externaljob.ora to include the user oracle and it's corresponding group.

[shew01]

Apparently, $ORACLE_HOME/rdbms/admin/externaljob.ora controls this as of Oracle 10.2.0..2 or 10.2.0.3, depending on the Operating System. See Oracle MetaLink Note: 391820.1 for additional information. This does not appear to be documented very well elsewhere.