corntab sqlplus

[Eduardo Uribe_1]

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 !

[Joseph C. Denman]

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...

[Paula J Frazer-Campbell]

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

[Magdi KAMAL]

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

[Alexander M. Ermes]

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

[Brian Pyle]

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 :)

[Brian Pyle]

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

Brian

[Jared Westgate_1]

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

[Tracey]

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.

[Wodisch]

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

[Bill Thorsteinson]

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

[Manuel P. Ron]

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.

[Eduardo Uribe_1]

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????

[Jack Werner]

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

[Jack Werner]

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