Script not working in Cron

[Beverly Wixon]

Hi all,
On a production database, two scripts set to run weekly are not running. I am going to focus on the analyze schema script here. The script will run successfully when executed alone. We had to do that since it did not run in the cron. However, we do not wish to continue to do that since it needs to be run weekly. In fact, after running it successfully by itself, the next time it was supposed to run, we received the log of the last successful run from the Unix mailx command. I have checked the /var/adm/cron/log file. The return code is "rc=1", so I am sure it did not run.

Below are the lines from the cron to run the analyze schema script as well as the lines from the script. I have compared it to others analyze schema scripts on other servers (those that are running). The only difference I have found is that the others tend to be 'COMPUTE' instead of 'ESTIMATE'. I have been asked NOT to change this. Any ideas as to why this is not running? Any input will be greatly appreciated. Thanks everyone.

Beverly

FROM THE CRON:
00 8 * * 0,1 /ora0/oracle/SQL/anal_schemas

FROM THE /ora0/oracle/SQL/anal_schemas SCRIPT:
#!/bin/ksh -x
#
# anal_schemas - jww - May 15, 2001
#
# rewritten from older script which didn't work
#
#

rm analyze_schemas.log
spool analyze_schemas.log

ORACLE_SID='dbname'
ORACLE_BASE='/path explicitly stated'
ORACLE_HOME='/path explicitly stated'
export ORACLE_HOME ORACLE_SID ORACLE_BASE
PSWD=`cat /path explicitly stated`
sqlplus system/$PSWD@dbname @$HOME/SQL/anal_schemas.sql
EEADD=`cat /path`

spool off

That is the end of the executable script in the cron job. Then, of course, the script called by
the executable is the anal_schemas.sql script (below).

FROM THE CALLED SQL SCRIPT:
spool /ora0/oracle/SQL/dbname_anal_schemas.log

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "schema name Start Time" from
dual;
exec dbms_utility.analyze_schema('schema_name','ESTIMATE','',30);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End Time" from dual;

(This is repeated for each schema to be analyzed. It ends with a simple spool off.)
spool off
/

The only difference I have found on the other scripts (which run successfully) is that

exec dbms_utility.analyze_schema('schema_name','ESTIMATE','',30);

is replaced with

exec dbms_utility.analyze_schema('schema_name','COMPUTE');

I am new to this and really need to get this fixed. Thank you so very much for any assistance.

Beverly Wixon
Space Gateway Support

[Tim Krego_1]

I am new to this so I don't have a definite answer, this is all I have to offer as a suggestion.

I was working on a cron script to send an Oracle report this weekend. I did have problems with the owner and group that the script was assigned to.

The message you posted needs more knowledge of HP/UX and Oracle than I have right now. :)

[John Palmer]

Hi,

cron has a very limited environment and this is inherited by any jobs that it issues.

You also need to be aware that profile and .profile scripts are NOT executed by cron issued jobs.

Your problem is almost certainly that your Oracle environment is not set properly. Specifically $ORACLE_HOME/bin is not set in the PATH variable so that sqlplus will not be located.

Try getting cron to start a job which just does the 'env' command and compare this with output from 'env' when you are logged in.

The best solution is to have a subscript that sets up an Oracle environment depending on the SID, sourcing ORACLE_HOME from file /etc/oratab and run it from your script. Oracle supply one - /usr/local/bin/oraenv

Hope this helps,
John

[Rainer von Bongartz]

Well I suspect problems with your environement variables !

cron does not take environement variables you are setting in yout shell

try running your scrip with the at command (which takes your environement). I this works you have to include all necessary env variables into your script

[John Poff]

Hello,

I have some questions. First, which owner runs the cron job? Is it run as root or as an oracle user? Also, who owns the anal_schemas script and what are the permissions on it.

JP

[Ravi_8]

hi,
add the user in /var/adm/cron/cron.allow

[Beverly Wixon]

Thanks to John Palmer. I added the PATH='$ORACLE_HOM/bin/sqlplus' line and am testing it. I'm not sure what you meant by "Try getting cron to start a job which just does the 'env' command and
compare this with output from 'env' when you are logged in. " Or what you meant by setting up the subscript. I don't have a /usr/local/bin/oraenv file.

Thanks to Ranier. Which environment variables are set that cron does not accept? Exactly which script should I try running with the @ command? I thought it was set that way. (@$HOME/SQL/anal_schemas.sql -- isn't that what you mean?)

Thanks to John Poff. This is the info from the long list (ll). I think it will answer your questions.

-rw-r--r-- 1 oracle dba 1196 Jun 25 10:16 oracle_cron


-rwxr-xr-x 1 oracle dba 605 Jun 22 08:40 anal_schemas

-rw-r--r-- 1 oracle dba 2211 Jun 21 13:46 anal_schemas.sql

Thanks to Ravi. I will add it for the test run.

[Beverly Wixon]

Ravi, the owner "Oracle" is in /var/adm/cron/cron.allow already. Thanks though.

[Herve BRANGIER]

Hi,

Try to see where your ORACLE's environment
variables are initialized. I think that you
need to read your .profile or another file
to initalize them.

HTH

Herve

[Beverly Wixon]

Thanks, Herve. Would you mind telling me how to do that? I am extremely new to this. This is my bosses script. He says it ran before, but stopped in early June. It's not a database I work on, so I don't know when it worked or when it stopped except for what I have read in the log file which goes back to April and always shows an rc greater than 0 (which I am told means it did not run).

[John Palmer]

Here is a simple script to set-up an Oracle environment:-
-------------cut here-------------
export ORACLE_SID=${1:-????}
ORAINFO=$(grep "^${ORACLE_SID}:" /etc/oratab 2>/dev/null)
if [[ -n ${ORAINFO} ]];
then ORAINFO=${ORAINFO%%\#*}
IFS=:
set ${ORAINFO}
unset IFS

export ORACLE_HOME=${2}
export PATH=${PATH}:${ORACLE_HOME}/bin
export SHLIB_PATH=${ORACLE_HOME}/lib

else print "No info in /etc/oratab for SID ${ORACLE_SID}"
ORACLE_SID=""
fi
------------cut here---------------------

Substitute whatever SID you want as the default in place of ????.

If you put this script into a directory that is already on your path then add the following line to your main script:-
. ora_env
or if the directory is not included in PATH then:-
. /

PATH must contain the directory name '$ORACLE_HOME/bin' not the name of the command such as '$ORACLE_HOME/bin/sqlplus'.

What I meant by a 'subscript' was using the '. ' command to execute a script within your current script not as a separate process (commonly known as 'dotting' the script).

Regards,
John

[Rainer von Bongartz]

Beverly,


at is your standard unix command to execute jobs at
a specific time

i.e.

at 22:30
/ora0/oracle/SQL/anal_schemas


this will create your job /var/spool/cron/atjobs/*.a

you can look at this file and will see what environement
variables are set.

By the way:
if this job works you can use the job xyz.a and start this
through cron. you will be sure then that all varaibales are set.

See also: man at

[Beverly Wixon]

To John Palmer. Wow! Our definitions of simple are way different. :) I'm trying it though. Isn't this (following) basically the same, except that it's missing the $ORACLE_HOME/bin directory.

ORACLE_SID='repp'
ORACLE_BASE='/ora0/oracle/app/oracle/product'
ORACLE_HOME='/ora0/oracle/app/oracle/product/8.0.5'
export ORACLE_HOME ORACLE_SID ORACLE_BASE
PSWD=`cat /ora0/oracle/passwd/system`
sqlplus system/$PSWD@repp @$HOME/SQL/anal_schemas.sql


To Rainer: Thanks. I don't have the permissions to run the at command, so I sent your reply to the sysadm asking her to run it.

Thanks a bunch.

[John Palmer]

Yes it's doing the same thing but it avoids hard coding pathnames into your scripts. When you come to upgrade Oracle, just amend the entry in /etc/oratab and voila all your scripts still work!

Regards,
John

[Beverly Wixon]

To John Palmer -- Oh. I think I get it. This is a totally separate script, not just variables set at the start of every script (as it is done here). Then, after upgrading the rdbms, I would only have to edit one file (the etc you mentioned). And, since we are upgrading databases, this makes sense to me. Thanks for the info. It may make things much easier for me in the future -- when I actually write a Unix scripts. Unfortunately, right now, I still need to figure out why this one (which I did not write) won't work. Any ideas after looking at the scripts themselves.

[John Palmer · Accepted solution]

I think that you're just missing
PATH=${PATH}:${ORACLE_HOME}/bin

[Beverly Wixon]

Okay, John. I have added the following lines and have it set to test in a few minutes. Thanks, again.

ORACLE_PATH='/ora0/oracle/app/oracle/product/8.0.5/bin'

export PATH=$ORACLE_PATH
export SHLIB_PATH=$ORACLE_HOME/lib

[John Palmer]

You need
PATH=$PATH:$ORACLE_PATH

otherwise you will lose your normal PATH settings (/usr/bin etc)

[Beverly Wixon]

PATH=${PATH}:${ORACLE_HOME}/bin

or

PATH=$PATH:$ORACLE_PATH

I'm sorry, but I am getting very confused.

[John Palmer]

PATH=${PATH}:${ORACLE_HOME}/bin
is preferable.

${PATH} is another way of writing $PATH.

PATH=$PATH:$ORACLE_PATH gives the same result because you have already set ORACLE_PATH to '/ora0/oracle/app/oracle/product/8.0.5/bin'

Regards,
John

[Beverly Wixon]

Thank you so much, John. I learned a few things anyway. Now, we'll see if it works. My fingers are definitely crossed. This is the way it now reads. Please let me know if you see anything else I need to fix.

ORACLE_SID='dbname'
ORACLE_BASE='/ora0/oracle/app/oracle/product'
ORACLE_HOME='/ora0/oracle/app/oracle/product/8.0.5'
PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_HOME ORACLE_SID ORACLE_BASE
export PATH=${PATH}:(ORACLE_HOME}/bin
export SHLIB_PATH=$ORACLE_HOME/lib
PSWD=`cat /ora0/oracle/passwd/system`
sqlplus system/$PSWD@dbname @$HOME/SQL/anal_schemas.sql

[Herve BRANGIER]

Beverly

Sorry to be so inprecise ;-)

I see that John give you all informations you
need to make your tests....

Good luck

Herv?

[Beverly Wixon]

Well, John, I tested it. Still did not run sucessfully.

Thanks for helping, Herve. Trust me, I need all the help I can get right now. I am definitely out of my league in this arena.