HELP! How to Running Oracle Scripts from CRON? 1

[KOG]

HI folks,

I have two big oracle scripts that needs to be run first thing every morning, the problem is these two scripts takes at least 25 minutes each to run. It deletes the old records from the table and repopulates it by extracting data from many other tables, this is for analyse purpose.

It is important that these two scripts are run every morning.

Now I have done some research from web but I am not too certain if this is correct, can you check if my crontab entry is correct and the script I created for invoking sqlplus and running 2 oracle scripts.

We are using AIX 4.3 and Oracle 8.0.5.

CRONTAB entry;

0 6 * * 1-5 /u/oramon/orascript 2 > /dev/null

I am not too sure why 2 > /dev/null should be added, I copied it from other entries on the crontab.

ORASCRIPT;

su - oracle /* Oracle user for running script? */
ORACLE_SID=blb export

{Folks is there any other environment settings that I should add?}

sqlplus -s bcisv4/<password>@SID
execute up_acthist /* will that run the script? */
execute up_seg
exit
EOF

Is that all I need or is there anything I should add to the ORASCRIPT? Is execute command correct for running oracle script or should I use RUN?

Pls help me, thanking you all in advance.

Cheers

Katherine

 

[Yegolev]

We use this in our crontab:

> /dev/null 2>&1

It redirects STDOUT (1) to /dev/null and then redirects STDERR (2) to wherever 1 went. /dev/null is a nice place to put stuff you don't ever want to see, think of it as a black hole from which nothing escapes. In your case you are discarding the error output of the script.

You'll have to ask someone else about Oracle. However if that script is a Korn shell script (if so it will start with this line: #!/bin/ksh) you just enter your commands as you would from the command line. I'm not a DBA so I may not know something important about Oracle stuff.

 

[bjverzal]

As it stands, cron will indeed run the script as you desire. However, once the script starts, the script will stop at the &quot;su&quot; command, because a new shell is started.

You are best doing this:

CRONTAB entry;
0 6 * * 1-5 /u/oramon/orascript 2 > /dev/null
[No changes from what you've done]

ORASCRIPT1
su - oracle -c &quot;<pathname_to_script>/ORASCRIPT2&quot;

ORASCRIPT2
ORACLE_SID=blb export

{Folks is there any other environment settings that I should add?}

sqlplus -s bcisv4/<password>@SID
execute up_acthist /* will that run the script? */
execute up_seg
exit
EOF


In summary,

- &quot;cron&quot; runs ORASCRIPT1 as &quot;root&quot;
- ORASCRIPT1 runs ORASCRIPT2 as &quot;oracle&quot; user

Check the Oracle sqlplus on your syntax, I'm not totally sure on it

Bill.

 

[johnknapp]

In my oracle scripts, I specify the Oracle SID and export it and use the ORAENV_ASK=NO variable to suppress interactive prompting for the SID name when you call in the Oracle oraenv environment variable file .... note the period (dot) prior to the oraenv command with a space between the two ....see below:

#!/usr/bin/ksh
#
for sid in MOST DBAT
do
su - oracle -c &quot;export ORAENV_ASK=NO; export ORACLE_SID=${sid};. oraenv; svrmgrl&quot; << EOF
connect internal
shutdown abort
startup restrict
shutdown
exit
EOF
done

the oraenv file will set all the correct variables if the SID is explicitly stated.

 

[KOG]

Many thanks for all your inputs folks, much appreciated.

Regards

Katherine

 

[Yegolev]

Random ENV from my dbenv*.sh:

ORACLE_PSRV=$SAPSID; export ORACLE_PSRV

ORACLE_SID=$DBSID; export ORACLE_SID

ORACLE_HOME=/oracle/D18; export ORACLE_HOME

ORA_NLS=/oracle/D18/ocommon/NLS_723/admin/data; export ORA_NLS

ORA_NLS32=/oracle/D18/ocommon/NLS_733/admin/data; export ORA_NLS32

ORA_NLS33=/oracle/D18/ocommon/nls/admin/data; export ORA_NLS33

SAPDATA_HOME=/oracle/D18; export SAPDATA_HOME

Lots more where that came from. =)