UNIX for Advanced & Expert Users

If in cron_allow file, user name is listed, this user can execute crontab. I, as a uers in that UNIX saver, have 3 other scripts to call a bounch of PL/SQL scripts to do the work. They all work perfectly. This script is also working perfectly when I login and manually execute. Only thing is that I schedule it in crontab, it will failed with these mesage:

02/27/07 04:00:00: Database xxxprd.world is up and running.

02/27/07 04:00:00: Counting extraction records script started.

02/27/07 04:00:00: Error counting
/app/xxxx/scripts/xxxx/validation/cnt_xxxx_act.sql.
02/27/07 04:00:00: Exiting script.

I have tried different ways such as running .profile to set up oracle environment, using full path to locate PL/SQL scripts. It is hard for me to understand why manually execution worked and crontab execution failed.

Here is the basic part of script:

#Initialize variables.
ORACLE_SID=$1
ORACLE_HOME=/oracle/product/9.2.0
WORK_DIR=`dirname $0`
MINUTES=0
DBDOWN=1
#FILECOUNT=0
#FILETRANSFERED=1


# Change to working directory.
cd $WORK_DIR

# Set environment variables.
export ORACLE_SID
export ORACLE_HOME
export PATH=$PATH:/oracle/product/9.2.0:/app/oracle/product/9.2.0/bin
export FILENAME

HERE IS THE EXECUTION PART

for filename in validation/cnt_*.sql
do
sqlplus -s > temp.log 2>&1 dbuser/passwd@$ORACLE_SID @$filename
if [ $? = 0 ]
then date +"%D %T: $filename successully counted." >> count.log
else date +"%D %T: Error counting $filename." >> count.log

The error message came from if [ $? = 0] condition, which means crontab couldn't get first comdition, then go to second comdition. IF if condition is wrong, how could I manually execute successfully. Please help. Thanks.

This doesn't look like cron issue..may be sql is exiting. so the cron is catching that error...look for sql..if possible put spool on that sql..

gopidesaboyina:

Actually, I think it is the problem that script look for env variables settings. programming in script is not wrong, cron didn't set up these env variables. So I added some command in crontab and tested. It seems work fine. I will monitor tonight. If it works again, I will come to report. Thanks for your help.

This is the report:

I created a env_profile in home directory and add piece of code in crontab. When crontab execute, it will go to that profile to find variables. Then shell script run successfully.

hi duke,

i too have the same problem with crontab it is working manually. but if i schedule it at crontab it doesn't work . please tell me what is the piece of code written for env_profile.

thanks
vaddi