topic Re: Script not working in Cron in Operating System - HP-UX

Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 12:51:45 GMT

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

Re: Script not working in Cron

Tim Krego_1 — Mon, 25 Jun 2001 12:57:57 GMT

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

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 13:15:00 GMT

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

Re: Script not working in Cron

Rainer von Bongartz — Mon, 25 Jun 2001 13:18:17 GMT

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

Re: Script not working in Cron

John Poff — Mon, 25 Jun 2001 13:20:55 GMT

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

Re: Script not working in Cron

Ravi_8 — Mon, 25 Jun 2001 13:24:43 GMT

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 13:37:52 GMT

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.

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 13:41:04 GMT

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

Re: Script not working in Cron

Herve BRANGIER — Mon, 25 Jun 2001 13:41:59 GMT

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 13:51:38 GMT

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

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 13:55:37 GMT

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

Re: Script not working in Cron

Rainer von Bongartz — Mon, 25 Jun 2001 14:04:25 GMT

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 14:32:55 GMT

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.

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 14:39:57 GMT

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 14:49:53 GMT

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.

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 14:58:04 GMT

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 15:11:27 GMT

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

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 15:15:06 GMT

You need
PATH=$PATH:$ORACLE_PATH

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

Re: Script not working in Cron

Beverly Wixon — Mon, 25 Jun 2001 15:26:18 GMT

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

or

PATH=$PATH:$ORACLE_PATH

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

Re: Script not working in Cron

John Palmer — Mon, 25 Jun 2001 15:35:00 GMT

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