topic Re: corntab sqlplus in Operating System - HP-UX

corntab sqlplus

Eduardo Uribe_1 — Mon, 06 Aug 2001 11:38:00 GMT

Hi guys!

It's me again, the crontab guy.
Well, after solving most of my problems of the last issue loading the user environment before doing anything, here's my second problem, it is driving me crazy so I need your knowledge.

I'm trying to run an Oracle script from my shell script with this line:

/u1/app/oracle/product/8.1.6/bin/sqlplus -silent @

The error that it shows is:

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

I'm sure the whole ORACLE environment variables are loaded because I loaded .profile variables and checked them with the "env" command. ORACLE_HOME, ORACLE_SID, PATH, SHLIB_PATH, CLASSPATH.
The shell works fine from the command line but it doesn't work at all when running the same shell from the crontab.

Thanx in advance !

Re: corntab sqlplus

Joseph C. Denman — Mon, 06 Aug 2001 12:03:18 GMT

If it runs from the command prompt and does not run from the cron, you can just about bet it is an env variable. Try this:

In your script, before the sqlplus call do
env > /tmp/myenv.txt

Kick off the script via cron. Then check this file for all the variables. I bet your missing one.

...jcd...

Re: corntab sqlplus

Paula J Frazer-Campbell — Mon, 06 Aug 2001 12:05:01 GMT

Hi
To be on the safe side always give the full path to every command and file in your cron entry.

If it works from command like but not from cron I would suggest that is your problem.

Paula

Re: corntab sqlplus

Magdi KAMAL — Mon, 06 Aug 2001 12:17:13 GMT

Hi Eduardo,

Try just before your sqlplus command in the cronjob

env > /tmp/oracle.cron.env

login as oracle and type :
env > /tmp/oracle.interactive.env

diff /tmp/oracle.cron.env /tmp/oracle.interactive.env

And fetch out what differences are there .

Magdi

Re: corntab sqlplus

Alexander M. Ermes — Mon, 06 Aug 2001 12:38:54 GMT

Hi there.
Another chance is to write a shell script to be started as user root. Sample :

#
ORACLE_SID=${1}
export ORACLE_SID
DBHOME=/home/dba/oracle/product/${ORACLE_SID}/bin
export DBHOME
PATH=$PATH:.:$DBHOME
#
#
# ORACLE_SID=${1}
# export ORACLE_SID
ORAENV_ASK=NO
export ORAENV_ASK
#
. /usr/local/bin/oraenv
#
if [ $? != 0 ]; then
echo "Failure in running oraenv"
exit 1
fi
echo $ORACLE_SID
#
$DBHOME/sqlplus system/manager @/u004/alex/dba_data_files.sql $ORACLE_SID -s &
#

Next would be the sql-script :

set termout off
set lines 80
set pages 65
column Name format a50
column TB-Name format a10
column MB format 99,999
clear break
clear compute
break on TB-Name skip2 nodup on report skip3 nodup
compute sum of MB on TB-Name
compute sum of MB on report
break
compute
spool /var/tmp/dba_data_files&1
select file_name "Name",
tablespace_name "TB-Name",
bytes/1048576 "MB"
from dba_data_files
order by
tablespace_name,
file_name
;
spool off

Now the crontab line :

00 08 * * * /u001/local/bin/dba_data_files.sh

Attach the ORACLE_SID to this line and it should work.
Rgds
Alexander M. Ermes

Re: corntab sqlplus

Brian Pyle — Mon, 06 Aug 2001 17:25:45 GMT

I would have to agree with most of the above responses. It will probably end up being a missing environment var. I do run these all the time, but I always use
"su - oracle_user -c to run this from cron. That way I get my environment. I then make sure I set the critical ones in the script also. Here is an

example eis .profile:

# @(#) $Revision: 72.2 $

# Default user .profile file (/usr/bin/sh initialization).

############################################################
# check for a Terminal before running any terminal commands
# bhp 4-22-99
############################################################
tty 1> /dev/null
if [ $? = 0 ]
then
############################################################

# Set up the terminal:
if [ "$TERM" = "" ]
then
tset -s -Q -m :dtterm
# eval ` tset -s -Q -m ':?dtterm' `
# else
# eval ` tset -s -Q `
fi

stty erase "^H" kill "^U" intr "^C" eof "^D"
stty hupcl ixon ixoff
tabs

############################################################
fi # end terminal test
############################################################

# Set up the search paths:
PATH=$PATH:.

# Set up the shell environment:
set -u
trap "echo 'logout'" 0

# Set up the shell variables:
export EDITOR=vi

# set oracle environment
export SAVEPATH=$PATH
export ORACLE_HOME=/opt/oracle_mp/app/oracle/product/7.3.4
export ORACLE_SID=jeep
export ORACLE_TERM=hp
export PATH=$ORACLE_HOME/bin:$PATH

example cron:

30 7 * * 1-5 su - eis -c /usr/local/bin/rfa_report 2>&1 >> /sdf/custom_logs/cron.log

Also attatched a script.

Good Luck

Brian :)

Re: corntab sqlplus

Brian Pyle — Mon, 06 Aug 2001 17:27:43 GMT

Also I didn't see any reference to TWO_TASK...are you using that, and is it set right ? :)

Brian

Re: corntab sqlplus

Jared Westgate_1 — Mon, 06 Aug 2001 18:10:10 GMT

Hey Eduardo,

I get the exact same error message if I forget to initialize my cron environment. What I usually do (and I'm sure there's a better way to do this) is create a script that sets up the environment and fires off the sql commands. Then I just execute that script from cron. For example:

test.sh:
export ORACLE_SID
export ORACLE_HOME
export SHLIB_PATH
export ORACLE_TERM
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORA_NLS33=
export ORACLE_BASE=
export ORACLE_DOC=$ORACLE_BASE/doc
export TMPDIR=/tmp
/path/sqlplus user/pass /home/oracle/test.sql

I also use a sid script that sets up all the above parameters for you, for when I don't want to type all the above in! Actually, it's a very good way to maintain all the oracle paths in one place. Just write it so that you can pass in a parameter, then change your code. For example:

test.sh:
sid DEV #setup the oracle environment for DEV
/path/sqlplus user/pass /home/oracle/test.sql

I hope this helps some. Let me know if you'd like the code for my sid script.

Jared

Re: corntab sqlplus

Tracey — Mon, 06 Aug 2001 18:29:28 GMT

I am just installing Oracle and on Friday I got that exact same error message - it was because I did not have ORACLE_SID set.

Re: corntab sqlplus

Wodisch — Mon, 06 Aug 2001 19:05:01 GMT

Hello Edoardo,

try starting your script from Root's crontab with the
command line like:
su - oracle -c sqlplus user/password@tnsname @ script

this should be working - which means, it IS an env var
thing! So, write a small shell script, where you set the
variables ORACLE_HOME, and ORACLE_SID, perhaps
even SHLIB_PATH in case you did not relink oracle on
your platform, and call your script from within that script
as you do from the command line.
That should be it.

HTH,
Wodisch

Re: corntab sqlplus

Bill Thorsteinson — Mon, 06 Aug 2001 21:30:57 GMT

Here is the code I use in my cron jobs for Oracle to
set the environment. The SID is always the first
parameter.

PATH=${PATH%:}:/oracle/product/8.1.5/bin
ORACLE_SID=$1
ORAENV_ASK=NO
export ORACLE_SID
. oraenv

Re: corntab sqlplus

Manuel P. Ron — Tue, 07 Aug 2001 05:23:27 GMT

You avoid any problem if you try to run as oracle user, not as root user.

To achieve it, modify your crontab line:

su - -c sqlplus -silent @

It works.

Re: corntab sqlplus

Eduardo Uribe_1 — Tue, 07 Aug 2001 05:30:18 GMT

Well... tahnx for all your answers... right now I'm doing some testing based on some answers but I would like to clear some things.
1.- I don't want my script or any part of it to be run as root or as oracle this means forgetting about using "su -" in the script. Because it works fine from the command line using a different user.
So I discard all answers regarding su command.
2.- It seems that I'm forgetting an environment variable...
Here's the list of the variables that command "env" gives in the script ran as a cron job:

SHLIB_PATH
PATH
NLS_LANG
ORACLE_BASE
CLASSPATH
ORACLE_SID
ORATAB
LD_LIBRARY_PATH
ORACLE_HOME
ORACLE_PFILE
ORAENV_ASK

All of them are set correctly, Am I missing any?
Any more ideas????

Re: corntab sqlplus

Jack Werner — Tue, 07 Aug 2001 14:59:12 GMT

Eduardo,

You must put $ORACLE_HOME/bin in the $PATH env variable. If your script sources a script that establishes the proper oracle environment(ie, shlib,nls_lang etc) then you should have no problems. Try the ~oracle/.profile or ~oracle/.cshrc depending on your account's default shell

good luck

Re: corntab sqlplus

Jack Werner — Tue, 07 Aug 2001 15:04:02 GMT

Eduardo,

I forgot to mention that the form of sourcing any script in a script is picky; ". ~oracle/.profile" for instance will assure that the variables set by ~oracle/.profile are inherited by your script, since the "." assures your default shell runs the ~oracle/.profile.

CIAO