SH Script Execution Problems with Cronjob

UNIX AIX
1

Hi,

I have created a sh script to startup and shutdown the oracle database, when I execute the script thru command line it execute successfully, but when I call the script thru cronjob it does not execute.

The scripts are as follows:

 
LOG=/oracle/times.log
export ORACLE_SID=prod
echo $ORACLE_SID" Standby Database Open READ ONLY initiated   " `date` >> $LOG
sqlplus "/as sysdba"<<EOF
recover managed standby database cancel;
shutdown immediate;
startup mount;
alter database open read only;
EOF
echo $ORACLE_SID" Standby Database Open READ ONLY completed   " `date` >> $LOG

 
LOG=/oracle/times.log
export ORACLE_SID=prod
echo $ORACLE_SID" Standby Database Close READ ONLY initiated  " `date` >> $LOG
sqlplus "/as sysdba"<<EOF
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
EOF
echo $ORACLE_SID" Standby Database Close READ ONLY completed  " `date` >> $LOG
echo $ORACLE_SID" Standby Database Recovery Mode Started      " `date` >> $LOG
echo "===============================================================================" >> $LOG

Cron job is as follows:

 
00 0,9,12 * * * sh /oracle/scripts/standby_open_reporting.sh
00 2,21,13 * * * sh /oracle/scripts/standby_close_reporting.sh

Below is output of times.log file

prod Standby Database Close READ ONLY initiated   Wed Jun 29 21:00:00 PKST 2011
prod Standby Database Close READ ONLY completed   Wed Jun 29 21:00:00 PKST 2011
prod Standby Database Recovery Mode Started       Wed Jun 29 21:00:00 PKST 2011
===============================================================================
prod Standby Database Open READ ONLY initiated    Thu Jun 30 00:00:00 PKST 2011
prod Standby Database Open READ ONLY completed    Thu Jun 30 00:00:00 PKST 2011
prod Standby Database Close READ ONLY initiated   Thu Jun 30 02:00:00 PKST 2011
prod Standby Database Close READ ONLY completed   Thu Jun 30 02:00:00 PKST 2011
prod Standby Database Recovery Mode Started       Thu Jun 30 02:00:00 PKST 2011
===============================================================================
prod Standby Database Open READ ONLY initiated    Thu Jun 30 09:00:00 PKST 2011
prod Standby Database Open READ ONLY completed    Thu Jun 30 09:00:00 PKST 2011
2

You need to set the proper environment variables.

so cron finds sqplplus

1 Like
3

Thanks!
I haved added the environment variables in my shell script and it is running fine now thru cron job.
Can I executed the .profile in shell script instead of adding the environment variables in shell script?

4

you can add.

but instead of adding the .profile, you can create a file and out the necessary variables and source it

1 Like
6

Would you please advice how can I do that?

7

see this example

Running SQLPlus and PL/SQL Commands From A Shell Script dbamac

 
. /usr/local/bin/oraenv
1 Like
8

You probably use ksh as your scripting language. Then it is advisable to NEVER rely on any environment (like the one set in ~/.profile or ~/.kshrc ) but always set it explicitly. This way you never have to worry about the script being called from command line, cron or wherever.

I have for this a central file /usr/local/lib/ksh/f_env where i store all the necessary PATH- and other environment-variables, do things i want done in all my scripts (for instance: start some logging so that if a script breaks i can at least find out where it was broken), etc.. All my scripts source in this script as the first thing to do. Here is an example:

#! /bin/ksh
# This is an example script

. /usr/local/lib/ksh/f_env

# here starts the scripts code
command_1
command_2
[...]
exit 0

I hope this helps.

bakunin