No. 10146
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:
* * * * * <command>
where the *'s (often called the five time fields) reference digits that collectively make up the time, and <command> 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 wishto 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 sqlplus 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 keep executing it forever!!!.