topic Re: Oracle crontab problem in Operating System - HP-UX

Oracle crontab problem

BGiulio — Thu, 25 Jul 2002 14:22:22 GMT

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!

Re: Oracle crontab problem

Hai Nguyen_1 — Thu, 25 Jul 2002 14:40:13 GMT

It seems that the environment settings are different.

Hai

Re: Oracle crontab problem

James Beamish-White — Thu, 25 Jul 2002 14:40:40 GMT

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

Re: Oracle crontab problem

A. Clay Stephenson — Thu, 25 Jul 2002 14:41:42 GMT

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.

Re: Oracle crontab problem

Pierce Byrne_1 — Fri, 26 Jul 2002 06:39:58 GMT

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?

Re: Oracle crontab problem

Jerry Jaynes — Fri, 26 Jul 2002 10:18:17 GMT

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

Re: Oracle crontab problem

Yogeeraj_1 — Fri, 26 Jul 2002 11:05:04 GMT

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
#*******************************************************************************

Re: Oracle crontab problem

Jon A. Miller — Fri, 26 Jul 2002 12:22:28 GMT

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

Re: Oracle crontab problem

Jack Werner — Fri, 26 Jul 2002 18:10:40 GMT

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.