Unable to run sqlplus from cron-called script

[MCubitt]

Running a script from cron which has sqlplus calls, fails.


In the script I am doing su - oracle -c /home/oracle/IFSPtoIFSD/

Should this not work?



I get:

stty: tcgetattr: A specified file does not support the ioctl system call.
ksh: /home/oracle/IFSPtoIFSD: 0403-006 Execute permission denied.
------------------------------------------

(Does the directory have to by executable?!!)

/home/oracle/IFSPtoIFSD/copy1off.unix[40]: sqlplus: not found.
/home/oracle/IFSPtoIFSD/copy1off.unix[47]: fmtcontrol.unix: not found.

 

[KenCunningham]

Does your script contain the necessary environmental variables (eg $ORACLE_HOME, $PATH etc) for cron to find the necessary information to be able to run sqlplus etc? If you can find out the environmental variables used when the script is run interactively (I'm assuming it works when run from the command line?), then make sure these are explicitly set in the script itself when running it from cron. I would also recommend putting "s around the command in the crontab itself. You mighta also be better off putting the /full/path/to/command (eg fmtcontrol.unix above) in your script. HTH.

 

[dsn1]

The 1st problem looks to me that you are trying to run a directory

su - oracle -c /home/oracle/IFSPtoIFSD/

which would fit in with the error message

ksh: /home/oracle/IFSPtoIFSD: 0403-006 Execute permission denied.

and also that you have a script called

/home/oracle/IFSPtoIFSD/copy1off.unix


Change the su to actually run a script rather than the directory.

Check the .profile of oracle and see if there is a stty command which does not work in a batch environment which would account for the message

stty: tcgetattr: A specified file does not support the ioctl system call.


Dave

 

[alexhu]

e.g. I run this via Oracle's cron, but the same principle applies

export ORACLE_SID=pkms
export ORACLE_HOME=`grep ^$ORACLE_SID /etc/oratab | cut -f2 -d:`
export ORACLE_BASE=$ORACLE_HOME/../../
export PATH=$PATH:$ORACLE_HOME/bin:/bin
export COBDIR=/usr/lib/cobol
export LIBPATH=$LIBPATH:$ORACLE_HOME/lib:$COBDIR/coblib
export LOG_DIR=/home/oracle/scripts/logs
export TODAY=`date +%j`

echo "recreate_index.sql starting" `date` >> $LOG_DIR/recreate_index.log
sqlplus pkmshist/pkmshist @/home/oracle/scripts/ind.sql
echo "recreate_index.sql complete" `date` >> $LOG_DIR/recreate_index.log


Alex

 

[dsn1]

I am not sure how the copy1off.unix is being invoked since the su as given would seen not to run it , however the issue with the "not found" errors is due to the commands not being in the path

In the script ensure that ORACLE_SID is defined and exported and that you call sqlplus by its' full hierarchic name ( e.g. /oracle/product/8.1.7/bin/sqlplus ) rather than relying the path to be correct

Dave

 

[MCubitt]

Thanks for the tip. It appears to be working better now. Will do a full test when I get a dedicated system again! Thanks.

 

[MCubitt]

Sorry - missed a host of replies before posting mine.

Cron says:
36 11 * * 5 /home/oracle/IFSPtoIFSD/copy1off.unix

The start of copy1off.unix has:
copypath=/home/oracle/IFSPtoIFSD
ORACLE_HOME=/oracle/app/oracle/product/9.2.0.1.0
su - oracle -c $copypath

I will drop the "-c $copypath" as it seems redundant.

 

[MCubitt]

Oh.. and my script (copy1off.unix) has:

ORACLE_SID=IFSP; export ORACLE_SID
$ORACLE_HOME/sqlplus 'sys/oracle as sysdba' @$copypath/gettrace.sql >> $copypath/copy.log

 

[MCubitt]

That did not look right, so changed it to:

ORACLE_HOME=/oracle/app/oracle/product/9.2.0.1.0/bin; export ORACLE_HOME
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
PATH=$PATH:$ORACLE_HOME/bin:/bin; export PATH

Am I getting close?!

 

[alexhu]

You definately ned to include ORACLE_SID=IFSP; export ORACLE_SID

Alex

 

[MCubitt]

dsn:

Yep, there is: stty erase ^H

So it's nothing to worry about I guess.