Running script manually vs within cron = different results
Running Solaris 9 SPARC
Logging in as user oracle (or as root) and running a shell script manually (executing from the command line) everything works, when running the very same shell script from within the oracle crontab file things fail.
I believe I have added to the shell script what is necessary in the way of environment variables, etc. ORACLE_HOME, PATH and export(ed) both
What could the difference be.
[435 byte] By [
farmboy] at [2007-11-26 9:57:48]
# 1
> Logging in as user oracle (or as root) and running a
> shell script manually (executing from the command
> line) everything works, when running the very same
> shell script from within the oracle crontab file
> things fail.
>
> I believe I have added to the shell script what is
> necessary in the way of environment variables, etc.
> ORACLE_HOME, PATH and export(ed) both
>
> What could the difference be.
That's usually the difference. To confirm you've got it, run it the same way cron will and see if there's any difficulty.
oracle% env -i PATH=/usr/bin TZ=US/Pacific /path/to/your/script
Obviously, set the TZ to whatever timezone is valid for your environment. That's pretty much identical to what cron will do. Does it still work?
--
Darren
# 2
It appears that the cron executed sqlplus session does not wait for a table to truncate before establishing a datalink to a remote oracle db.
whereas
the command line execution of the process appears to wait for the local table truncate to end..
bottom line: the cron session is allowing oracle to move data from remote db in a table which is still in the process of being truncated from a sqlplus command just prior to establishing the db link and subsequent select as statement to populate the file that is still being truncated..
The command execution of the very same shell script (manually) appears to wait for the truncate to complete prior to executing the db link to a remote oracle system and thus the select as statement fails to populate the tables locally.
I'd be interested in learning why the cron does not wait
Thanks
# 4
You are correct.. the cron itself does not establish the data link to the remote database.
However, there is something going on that is not readily apparent with the shell executing from the cron.
the truncate and the database link are in one sqlplus script, the truncate db command truncates the local database table, the database link is then created, the local database table is then populated as per a select statement from the remote database.
I can not understand why this same shell script runs successfully from the command line and fails when executed from within a cron job.
I absolutely agree, the cron simply executes the sqlplus line and should have nothing more to do with the execution of the "lines" within the sqlplus script. It has always seemed to me that it's the sqlplus that's causing the problem of "not waiting" Yet I ask myself "how can that be", it runs ok from the commandline!! does that not remove the script from being the problem?
I have got this running successfully, by simply putting the local truncate after the creation of the database link instead of before the it.
Thanks for your time...yet i still would like to know Why?
# 5
Impossible to tell from the information you've gathered so far.
Not working with databases significantly, I don't know what troubleshooting options are best available. Is there some sort of error code that tells you *why* the operation fails? (permissions? DB error? etc...)
You might run the shell script in the reduced environment under truss, and then truss cron right before it execs the job. I'd recommend:
truss -f -ae -t exec <job>or
truss -f -ae -t exec -p <cron's PID>
That will give you a display of exactly how cron is executing the script. As an example, here's an 'ls' that I invoked:
# truss -f -ae -t exec ls
2165:execve("/usr/bin/ls", 0xFFBFFE1C, 0xFFBFFE24) argc = 1
2165:argv: ls
2165:envp: TERM=xterm SHELL=/sbin/sh SSH_CLIENT=10.245.6.6 4034 22
2165:SSH_TTY=/dev/pts/1 USER=root PATH=/usr/sbin:/usr/bin
2165:MAIL=/var/mail//root PWD=/lib/svc TZ=US/Pacific SHLVL=1
2165:HOME=/ LOGNAME=root
2165:SSH_CONNECTION=10.245.6.6 4034 10.1.1.7 22 _=/usr/bin/truss
2165:OLDPWD=/lib/svc/method
And via cron:
[...]
2176:execve("/usr/bin/sh", 0xFFBFDBE0, 0x0002CFAC) argc = 3
2176:argv: sh -c /usr/bin/ls
2176:envp: HOME=/ LOGNAME=root PATH=/usr/sbin:/usr/bin
2176:SHELL=/usr/bin/sh TZ=US/Pacific
2178:execve("/usr/bin/ls", 0x00039F58, 0x00039F60) argc = 1
2178:argv: /usr/bin/ls
2178:envp: HOME=/ LOGNAME=root PATH=/usr/sbin:/usr/bin
2178:SHELL=/usr/bin/sh TZ=US/Pacific
[...]
Note that although I put a binary in crontab (/usr/bin/ls), cron doesn't exec it directly. Instead it passes it to /usr/bin/sh to exec. Also we see the reduced environment. I'd have to explicitly reduce my environment to replicate that...
# truss -f -ae -t exec env -i TZ=US/Pacific PATH=/usr/sbin:/usr/bin SHELL=/usr/bin/sh LOGNAME=root HOME=/ /usr/bin/ls
2200:execve("/usr/bin/env", 0xFFBFFDA4, 0xFFBFFDC8) argc = 8
2200:argv: env -i TZ=US/Pacific PATH=/usr/sbin:/usr/bin
2200:SHELL=/usr/bin/sh LOGNAME=root HOME=/ /usr/bin/ls
2200:envp: TERM=xterm SHELL=/sbin/sh SSH_CLIENT=10.245.6.6 4034 22
2200:SSH_TTY=/dev/pts/1 USER=root PATH=/usr/sbin:/usr/bin
2200:MAIL=/var/mail//root PWD=/lib/svc TZ=US/Pacific SHLVL=1
2200:HOME=/ LOGNAME=root
2200:SSH_CONNECTION=10.245.6.6 4034 10.1.1.7 22 _=/usr/bin/truss
2200:OLDPWD=/lib/svc/method
2200:execve("/usr/bin/ls", 0xFFBFFDC0, 0x00023338) argc = 1
2200:argv: /usr/bin/ls
2200:envp: HOME=/ LOGNAME=root SHELL=/usr/bin/sh
2200:PATH=/usr/sbin:/usr/bin TZ=US/Pacific
done!
But that's just a way of looking at differences. Being able to ask the sql script for what error is affecting it may get you the information more directly.
--
Darren
