Wednesday, October 24, 2007

USING CRON WITH ORACLE

USING CRON WITH ORACLE

by Raghu Viswanathan
Oracle Support - Unix Group

The objective of this bulletin is to acquaint the reader with the use of cron
and to provide some examples on how it is used with ORACLE. We will use the
Unix att universe for the purpose of discussion. Differences between att and
bsd are documented at the end of this report.

For simplicity, let us assume that the database is owned
by the account 'oracle' and that the demo account is called 'scott'.
Let us also assume that $ORACLE_HOME is located at /usr/oracle and
the SID is S

What is cron?. Cron is a clock daemon utility provided by
unix. This utility, which is located in /etc/cron executes commands
specified in the file /usr/lib/crontab at times specified in crontab
by the user. cron checks this file EVERY MINUTE.

Not all users are allowed to use cron. In order to use it,
your userid must be listed in a file called /usr/lib/cron/cron.allow
which is owned by root. Contact your system administrator to get your
userid in this file. From now on, let us assume that the file
cron.allow only contains userids root and oracle.

Every user listed in cron.allow will have his/her personal
crontab file. Each user can only have one crontab file. This crontab
will list all the commands that the user wants to execute and what
times thereof.

How to set up your own crontab file:
------------------------------------

All crontab file contain lines in the following format:

* * * * *

where the *'s (often called the five time fields) reference digits
that collectively make up the time, and reference the
command to be executed. The five time fields in successive order
are:

Field Range
------------------------

minute 0-59
hour 0-23
day of the month 1-31
month of the year 1-12
day of the week 0-6 (0 is Sunday)

You may directly use * for any one of these values which means that
all possible values are accepted. For example, to execute an echo
command everyday at 7:29 in the evening you can set up the following
line:

29 19 * * * echo its dinner time.

You can enter more requests on consecutive lines. Once
you have entered all these into a file (say, mycron) using your
favorite editor, you can now submit this file as YOUR CRONTAB file
to the system. To do this type:

crontab mycron

You can always add more requests in your file mycron and
then resubmit it using the above command. The system will only retain
your most recent submission.

To view your current submitted file type:

crontab -l

To delete your current submitted file type:

crontab -r


Your Results:
-------------

Now that you have submitted your crontab file, the echo
command will execute everyday at 7:30 in the evening. The results are
sent to you via unix mail.


Uses of Cron:
-------------

As you can see, cron is very useful. System administrators
use cron often to perform administrative chores automatically.
Typically, such activities are performed at night when the system load
is low. From a database standpoint, we can use cron to perform backups
of the database every night or we can run SQL reports that will spool
output files every night to keep track of system usage.

Scenario:
---------

Let us set up a very simple example - let us say that we wish
to know the contents of the emp and dept tables in the scott account
everyday at 11:30 at night and save the results in a file called
scottout. In order to do this, we will need 3 files:

sql script containing the queries - call this scottcron.sql
a shell script to execute scottcron.sql - call this scottshell
a crontab file to run the shell script - call this mycron
(this last file has to be submitted to cron)

Let us also say that these files and the results will be
located in the directory /usr/oracle/personal.

Step 1 - Writing the SQL script:
--------------------------------

spool /usr/oracle/personal/scottout
select * from emp;
select * from dept;
exit;

Now we need to set up a shell script that will enter SQL*Plus
as scott/tiger and execute scottcron.sql. We also need to write the
crontab file. Let us approach this from two angles:

Approach A - Executing as ROOT:
-------------------------------

In this approach we will let the ROOT execute the commands
and save it in /usr/oracle/personal. We can set up the shell file
scottshell as follows:

sqlplus scott/tiger @/usr/oracle/personal/scottcron

and the crontab file as:

30 23 * * * su - oracle -c "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"


Essentially, root is the super user and can thus impersonate oracle
without a password. su oracle will enable root to become oracle.
The -c means 'execute the following command as oracle'. The extra '-'
between su and oracle is crucial - because of this, the script is being
executed as if the user oracle had logged in himself. In other words
all environment variables like $ORACLE_HOME,$ORACLE_SID and $PATH
are set correctly.

This is the approach we use at Oracle Support when maintaining our own online
support system. Our crontab file looks like the following:

<5 time fields> su - oracle -c "shell1"
<5 time fields> su - oracle -c "shell2"
<5 time fields> su - oracle -c "shell3"
<5 time fields> su - oracle -c "shell4"
<5 time fields> su - oracle -c "shell5"


Approach B - Executing as oracle:
---------------------------------

In this approach, oracle will submit the crontab file
instead of root. The shell file scottshell will now look like:

ORACLE_HOME=/usr/oracle; export ORACLE_HOME;
ORACLE_SID=S; export ORACLE_SID;
/usr/oracle/bin/sqlplus scott/tiger @/usr/oracle/personal/scottcron
and the crontab file will look like:

30 23 * * * "/usr/oracle/personal/scottshell" >
"/usr/oracle/personall/scotterror"

A common mistake is to forget to set ORACLE_HOME and
ORACLE_SID - this usually yields to a ORA 2700 error. This is not a
bug - when cron runs the script it uses the environment similar to
the one used by root when it does an 'su' to oracle WITHOUT
executing the login scripts of oracle. Thus the ORACLE variables are
not set. As a matter of fact, you can also set the PATH explicitly to
avoid using full path names.

We can do an export or run a report in the same way.


Differences with BSD Environment:
---------------------------------

In the ATT environment, we could never edit the crontab
file directly - we had to keep resubmitting our personal cronfiles.
In BSD versions, there is only one configuration file called
/usr/lib/crontab. Version 4.3 also has a crontab.local. You may
either use one or the other, not both.

Also, on some systems, the crontab files are set up in a
seven-field format. The first 5 fields are the time fields, the sixth
is the userid and seventh is the command to be executed.

If you are not using cron, be sure to remove your crontab
file - otherwise the system will continually execute cron!

11 comments:

Navnath said...

good work friend..

Anonymous said...

I always emailed this website post page to
all my associates, as if like to read it afterward my contacts will too.


Feel free to surf to my blog :: Email Console

Karen said...

Come la scorsa stagione, il nuovo maglia di Roma combina una base rosso con accenti in una tonalità più scura del colore. Ma mentre il suo predecessore dispone di maniche scure, la camicia casa Roma 2016-17 è dotato di un modello di strisce in rosso scuro sulla parte anteriore, mentre il colore è utilizzato anche per il rivestimento intorno al collo.maglie calcio,
poco prezzo Maglia real madrid 2017
maglia AC milan online

Karen said...

Our Tennessee Titan Flag is constructed of polyester, measures 3x5 feet, and has two plastic D-rings for attaching to our 6' aluminum flagpole or tailgate pole. The perimeter of our Tennessee Titan Flag is double stitched and the Tennessee Titan team logos are dye sublimated into the flag so they won't peel. Due to its large size, these flags are also perfect to hang on any wall in your game room, sports room, or office.
Falcons house divided flags
Ravens american flagsnfl house divided flags
dallas cowboys american flag

raybanoutlet001 said...

nike blazer
ugg boots
ugg boots
nike factory outlet
polo ralph lauren
ugg outlet
los angeles lakers jerseys
ugg boots
coach outlet
true religion outlet store

Unknown said...

hermes belt
jordan 11
adidas ultra boost
nike air zoom
balenciaga shoes
adidas ultra boost
adidas tubular
baseball jerseys
longchamp outlet
golden goose

jeje said...

nike huarache
nike huarache sale
yeezy boost 350 v2
nike roshe one
yeezy shoes
gucci belts for men
nike air max 2017
pandora bracelet
air jordans
air max 90

yanmaneee said...

nike air force 1 low
nhl jerseys
kyrie 5
jordan 12
golden goose francy
christian louboutin shoes
nike air max
goyard tote
balenciaga sneakers
kobe byrant shoes

yanmaneee said...

golden goose sneakers
bape hoodie
supreme clothing
kevin durant shoes
yeezy supply
yeezys
kyrie shoes
longchamp
nike dunks
longchamp handbags

stoorin said...

look here gucci replica bags great site gucci replica bags content high end replica bags

noughr said...

right here replica ysl bags australia linked here replica bags toronto browse around these guys replica goyard bags