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!