Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Saturday, February 6, 2010

How to add an Oracle related script to Unix crontab

Dear All,

After a long period of time not publishing any blog posts, I'm back again to share my Oracle knowledge with you and get your reviews and feedbacks about the published subjects.

I want to share a point with you, which most of you would already have been aware of it.

When you want to prepare an Oracle related script which will connect to an Oracle database and do some work and schedule it in Unix crontab (in my case IBM AIX), you should pay attention to set the environment variables related to database which will be connected to and running on the same server.

These environment settings are normally set in the profile of the Unix user, running the script in Unix, most of the time this Unix user is also the owner of the database running on this server.

In case of scheduling a shell script in crontab, the scheduled script does not have any profile setting automatically and you should prepare a separate script including these Oracle related environment settings or you can directly embed these setting to your database related Unix shell script.

Below are the examples of these kinds of Unix shell scripts.

Regards,
Ural

[user01@server01]:/oracle > crontab -l
00 00 * * * /oracle/scripts/script.sh > /oracle/scripts/script.log 2>&1

[user01@server01]:/oracle > more /oracle/scripts/script.sh
. /oracle/scripts/set_env.sh
/oracle/scripts/script_02.sh

[user01@server01]:/oracle > more /oracle/scripts/set_env.sh
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/orahome1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
cd /oracle/scripts

[user01@server01]:/oracle > more /oracle/scripts/script_02.sh
d_date=`date +"%Y%m%d%H%M"`
expdp parfile=script_02.par dumpfile=script_02_${d_date}.dmp logfile=script_02_${d_date}.log

3 comments:

Ümit Karaoğul said...

welcome back :) blog world

Ural Ural said...

Thanks Umit

Savaş Külah said...

En son 2007 de yazmışsınız, bu yazıyı görmek gerçekten beni mutlu etti diyebilirm. Çabanızdan dolayı tebrik ederim...