Oracle crontab problem

[BGiulio]

Hi Dudes,

I've got some problem with the scheduling of some simple oracle script on my system:
this script launched from the command line works correctly:

#! /bin/sh

/opt/app/oracle/product/8.0.5/bin/sqlplus<< EOF
username/password
begin
......
end;
/
exit;
EOF


the problem is that this script seems not to be working put into the CRONTAB, the error returned is

Message file sp1.msb not found
Error 6 initializing SQL*Plus

Do you have any idea about what could the problem is?

Thanks already!

[Hai Nguyen_1]

It seems that the environment settings are different.

Hai

[James Beamish-White · Accepted solution]

It may be that some of the oracle user variables are not exported.

Do you have the output of the cron piped to a file? i.e.

$ cat /var/spool/cron/crontabs/oracle
0 01 * * * /tmp/oracle_script.sh > /tmp/mylog.txt 2>&1

You might get more interesting info there...

Cheers,
James

[A. Clay Stephenson]

Even though you are running as probably Oracle, you do not have the usual expected environment under cron. You need to set and export all the usual ORACLE env vars (ORACLE_HOME,ORACLE_SID,...) inside your script.

[Pierce Byrne_1]

As the other replies suggest try setting env Vars in your script.
For security so you don't have to include user/pass in the script you might want to use an OPS$ account?

[Jerry Jaynes]

This is the shutdown script that we refer to in our oracle cron, remember you must SU to oracle and then run crontab -e.

It has all the variables we use and it works obviously well.

Good luck!

Jerry

[Yogeeraj_1]

Hello,

I found a simple "centralized" way to do it.

Hope this helps!

Best Regards
Yogeeraj
------------------------------------------------------------
In the crontab for the root user:
#*******************************************************************************
# min|hour |day |month|day |script
# | |of mo| |of wk|
#----|-----|-----|-----|-----|--------------------------------------------------
05,10,15,20,25,30,35,40,45,50,55 08,09,10,11,12 * * * echo "/prod/global/admin/dba/myscript.sh"|su - oracle8i 1>/prod/global/admin/dba/logfiles/output-myscript.crn 2>/prod/global/admin/dba/logfiles/error-myscript.crn
#*******************************************************************************
# END OF TABLE day0->Sunday day6->Saturday
#*******************************************************************************

[Jon A. Miller]

Set your environment via oraenv:

For example: passing your ORACLE SID as a parameter:

#! /usr/bin/ksh
export PATH=$PATH:/usr/bin/X11:/usr/local/bin
umask=022
export ORACLE_SID=${1}
export ORAENV_ASK=NO
. oraenv

[Jack Werner]

If you can run the script as user oracle, then simply source ~oracle/.profile in the script that cron schedules. This should set the proper oracle environment.