Pro Oracle Database 11g Administration (96 page)

Each entry in the crontab is a single line comprised of six fields. The first five fields specify the execution time and frequency. These entries can be separated by commas or hyphens. A comma indicates multiple values for an entry, whereas a hyphen indicates a range of values. An entry can also be an asterisk (*), which indicates that all possible values are in effect. Here’s an example to help clarify.

The following entry sends an e-mail saying “wake up” every half hour from 8 a.m. to 4:30 p.m. Monday through Friday:

577

CHAPTER 21 ■ AUTOMATING JOBS

0,30 8-16 * * 1-5 echo "wake up" | mailx -s "wake up" [email protected] On some Linux systems, you can skip a value within a range by following the entry with /.

For example, if you wanted to run a job every other minute, use 0-59/2 in the minute column. You can also use a slash (/) with an asterisk to skip values. For example, to run a job every fourth minute, you would use */4 in the minute column.

The sixth field in the crontab can be one or more Linux commands or a shell script. Or put it another way, the sixth column can be any combination of commands or a script that you can run on one line from the Linux command line.

The cron utility has a few quirks that need further explanation. The fifth column is the day of the week. Sunday is designated by either a 0 or a 7, Monday by a 1, Tuesday by a 2, and so forth, to Saturday, which is indicated with a 6.

The hour numbers in the second column are in military time format ranging from 0 to 23. The fourth column (month of year) and fifth column (day of week) can be represented with numeric values or by three-letter character abbreviations. For example, the following entry in the crontab uses three-letter character abbreviations for months and days:

0,30 8-16 * Jan-Dec Mon-Fri echo "wake up" | mailx -s "get up" [email protected] There also appear to be overlapping columns such as the third column (day of the month) and the fifth column (day of the week). These columns allow you to create flexible schedules for jobs that need to run on schedules such as the 1st and 15th day of the month or on every Tuesday. Put an asterisk in the column that you’re not using. If you need to run a job on the 1st and 15th and every Tuesday, then fill in both columns.

If you’re running a shell script from cron that contains a call to an Oracle utility such as sqlplus or rman, ensure that you instantiate (source) any required OS variables such as ORACLE_SID and ORACLE_HOME.

If you don’t source these variables, you’ll see errors such as the following when your shell script runs from cron:

sqlplus: command not found

When cron runs a script as a user, it doesn’t run the user’s startup or login files (like .bashrc).

Therefore, any script (being run from cron) needs to explicitly set any required variables. You can directly set the variables within the script or call another script (such as Oracle’s oraenv script) that exports these variables.

■
Tip
Don't schedule every job that you enter in cron to run all at the same time. Rather, spread them out so as not to bog down cron or the system at any particular point in time.

Scheduling a Job to Run Automatically

To schedule a job, you must add a line in your cron table specifying the time you want the job to execute.

There are two methods for adding entries in your cron table:

• Editing the cron table file directly

• Loading the cron table from a file

These two techniques are described in the following sections.

578

CHAPTER 21 ■ AUTOMATING JOBS

Loading the cron Table from a File

The other way to modify your cron table is to load it directly with a file name using the following syntax: $ crontab

Here the crontab utility will load the contents of the specified file into your cron table. The recommended steps to modify your cron table with this method are as follows: 1. First create a file with the contents of your existing cron table, like so: $ crontab -l > mycron.txt

Listed below is a sample entry from a cron table on a database server:

#---------------------------------------------------------------------------------

# min(0-59) hr(0-23) dayMonth(1-31) monthYear(1-12) dayWeek(0/7-6) commandOrScript

#---------------------------------------------------------------------------------

# RMAN backups, dk: 01-may-10, updated.

1 16 * * * /u01/oracle/bin/rmanback.bsh INV >/u01/oracle/bin/log/bck.log 2>&1

#-----------------------------------------------------------------

# Tablespace check, sp: 17-jul-09, created.

5 * * * * /u01/oracle/bin/tbsp_chk.bsh INV 10 1>/u01/oracle/bin/log/tbsp.log 2>&1

#-----------------------------------------------------------------

Take note of a few aspects in the above cron table entry. I always place a line at the top of every cron table (on every database server) that briefly describes the meanings of the date scheduling features:

# min(0-59) hr(0-23) dayMonth(1-31) monthYear(1-12) dayWeek(0/7-6) commandOrScript I also separate each entry with a comment line. This makes the cron table entry much more readable:

#-----------------------------------------------------------------

I also put a brief note (with my initials) describing what the cron job and the last time that I edited the cron entry:

# RMAN backups, dk: 01-may-10, updated.

If you manage dozens of database servers (each with its own cron job) with multiple DBAs, you'll need some mechanism (and it doesn't have to be sophisticated) for tracking who made changes and when.

2. Next, make a copy of your cron table before you edit it. This allows you to revert to the original in the event you introduce errors and can’t readily figure out what’s incorrect. This also provides you with an audit trail of changes to your cron table:

$ cp mycron.txt mycron.jul29.txt

3. You can now edit the mycron.txt file with your favorite text editor: $ vi mycron.txt

For example, to schedule a script named backup.bsh to run daily at 11:05 p.m., enter the following into the file:

580

CHAPTER 21 ■ AUTOMATING JOBS

#---------------------------------------------------------------------------------

# File backup, dk: 20-oct-10, inserted.

5 23 * * * /home/oracle/bin/backup.bsh

#---------------------------------------------------------------------------------

4. When you are finished making edits, load the crontab back, as shown here: $ crontab mycron.txt

If your file doesn’t conform to the cron syntax, you’ll receive an error such as the following:

"mycron.txt":6: bad day-of-week

errors in crontab file, can't install.

In this situation, either correct the syntax error or reload the original copy of the cron table.

Redirecting cron Output

Whenever you run a Linux shell command, by default the standard output (of the command) will be displayed on your screen. Also, if any error messages are generated, they will by default be displayed on your terminal. You can use either > or 1> (they are synonymous) to redirect any standard output to an operating system file. Additionally, you can use 2> redirect any error messages to a file. The notation of 2>&1 instructs the shell to send any error messages to the same location as standard output.

When you create a cron job, you can use these redirection features to send the output of a shell script to a log file. For example, in the following cron table entry, any standard output and error messages generated by the backup.bsh shell script are captured in a file named bck.log: 11 12 * * * /home/oracle/bin/backup.bsh 1>/home/oracle/bin/log/bck.log 2>&1

If you don’t redirect the cron job output, then any output will be e-mailed to the user who owns the cron job. You can override this behavior by specifying the MAILTO variable directly within the cron table.

In this next example, you want to aggravate the system administrator and send cron output to the root user:

MAILTO=root

11 12 * * * /home/oracle/bin/backup.bsh

If you don’t want the output to go anywhere, then redirect it to the proverbial bit bucket. The following entry sends the standard output and standard error to the /dev/null device: 11 12 * * * /home/oracle/bin/backup.bsh 1>/dev/null 2>&1

Troubleshooting cron

If you have a cron job that isn’t running correctly, follow these steps to troubleshoot the issue: 1. Copy your
cron
entry, paste it to the operating system command line, and manually run the command. Often a slight typo in a directory or file name can be the source of the problem. Manually running the command will highlight errors like this.

581

CHAPTER 21 ■ AUTOMATING JOBS

2. If the script runs Oracle utilities, ensure that you
source
(set) the required operating system variables within the script such as ORACLE_HOME and

ORACLE_SID. Oftentimes these variables are set by startup scripts (like HOME/.bashrc) when you log on. Since cron doesn’t run a user’s startup scripts, any required variables must be set explicitly within the script.

3. Ensure that the first line of any shell scripts invoked from cron specifies the name of the program that will be used to interpret the commands within the script. For example, #!/bin/bash should be the first entry in a Bash shell script.

Since cron doesn’t run a user’s startup scripts (like HOME/.bashrc), you can’t assume that your operating system user’s default shell will be used to run a command or script evoked from cron.

4. Ensure that the cron background process is running. Issue the following from the operating system to verify:

$ ps -ef | grep cron

5. If the cron daemon (background process) is running, you should see something similar to this:

root 2969 1 0 Mar23 ? 00:00:00 crond

6. Check your e-mail on the server. The cron utility will usually send an e-mail to the operating system account when there are issues with a misbehaving cron job.

7. Inspect the contents of the /var/log/cron file for any errors. Sometimes this file has relevant information regarding a cron job that has failed to run.

Examples of Automated DBA Jobs

In today's often chaotic business environment, it's almost mandatory to automate jobs. If you don't automate, you might forget to do a task or you may introduce error into the procedure if performing a job manually. If you don't automate, you may find yourself replaced or outsourced by a more efficient or cheaper set of DBAs.

When I automate jobs, usually the script will only send an e-mail in the event of a failure.

Generating an e-mail upon success often leads to a full mailbox. Some DBAs like to see success messages. I usually don't.

DBAs automate a wide variety of tasks and jobs. Almost any type of environment requires that you create some sort of operating system script that encapsulates a combination of OS commands, SQL

statements, and PL/SQL blocks.

The following scripts in this chapter are a sample of the wide variety of different types of tasks that DBAs will automate. This set of scripts is by no means complete. Many of these scripts you may not need in your environment. The point is to give you a good sampling of the types of jobs automated and the techniques used to accomplish a given task.

■
Note
Chapter 3 contains basic examples of some core scripts that DBAs require. This section provides advanced examples of tasks and scripts that DBAs commonly automate.

582

CHAPTER 21 ■ AUTOMATING JOBS

Starting and Stopping Database and Listener

In many environments, it's desirable to have the Oracle database and listener automatically shutdown and startup when the server reboots. If you have that requirement, then follow the next several steps to automate your database and listener shutdown and startup:

1. Edit the
/etc/oratab
file, and place a
Y
at the end of the entry for the databases you want to automatically restart when the system reboots. You might need
root
privileges to edit the file:

# vi /etc/oratab

2. Place within the file a line similar to this for your environment: O11R2:/oracle/app/oracle/product/11.2.0/db_1:Y

3. In the previous line, O11R2 is the database name, and

/oracle/app/oracle/product/11.2.0/db_1 specifies the directory ORACLE_HOME.

The Y on the end of the string signifies that the database can be started and stopped by the ORACLE_HOME/bin/dbstart and ORACLE_HOME/bin/dbshut scripts.

You can replace the Y with an N if you do not want the database automatically stopped and restarted.

■
Note
With some Unix systems (such as Solaris), the oratab file is usually located in the /var/opt/oracle directory.

4. As
root
, navigate to the
/etc/init.d
directory, and create a file named
dbora
:

# cd /etc/init.d

# vi dbora

5. Place the following lines in the
dbora
file. Make sure you change the values of variables
ORA_HOME
and
ORA_OWNER
to match your environment. This is a bare bones script of what you minimally would need to stop and start a database and listener: