Pro Oracle Database 11g Administration (95 page)

Listing 21–3.
Information for the RMAN_BACKUP Job

SELECT

job_name

,last_start_date

,last_run_duration

,next_run_date

,repeat_interval

FROM dba_scheduler_jobs

WHERE job_name='RMAN_BACKUP';

Here is a snippet of the output (the output has been wrapped to fit on the page): JOB_NAME LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE REPEAT_INTERVAL

----------- --------------- ----------------- ----------------- -----------------

RMAN_BACKUP 21–OCT-10 02.12 +000000000 00:00: 22-OCT-10 02.11.0 FREQ=DAILY;BYHOUR

.59.257151 PM - 41.933585 0.300000 PM -06:0 =14;BYMINUTE=11

06:00 0

Each time a job runs, a record of the job execution is logged in the data dictionary. To check on the each statuses of job executions, query DBA_SCHEDULER_JOB_LOG. There should be one entry for every time a job has run, like so:

SELECT

job_name

,log_date

,operation

,status

FROM dba_scheduler_job_log

WHERE job_name='RMAN_BACKUP';

571

CHAPTER 21 ■ AUTOMATING JOBS

Here is some sample output:

OB_NAME LOG_DATE OPERATION STATUS

----------- ---------------------------------------- ---------- ----------

RMAN_BACKUP 21–OCT-10 02.13.41.196695 PM -06:00 RUN SUCCEEDED

Modifying Job Logging History

By default, Oracle Scheduler keeps 30 days worth of log history. You can modify the default retention period via the SET_SCHEDULER_ATTRIBUTE procedure. For example, this changes the default number of days to 15:

SQL> exec dbms_scheduler.set_scheduler_attribute('log_history',15); To completely remove the contents of the log history, use the PURGE_LOG procedure: SQL> exec dbms_scheduler.purge_log();

Modifying a Job

You can modify various attributes of a job via the SET_ATTRIBUTE procedure. This example modifies the RMAN_BACKUP job to run weekly on Monday:

BEGIN

dbms_scheduler.set_attribute(

name=>'RMAN_BACKUP'

,attribute=>'repeat_interval'

,value=>'freq=weekly; byday=mon');

END;

/

For this particular example, you can verify the change by selecting the REPEAT_INTERVAL column from DBA_SCHEDULER_JOBS. The following is the output from running query from the prior section of Viewing Job Details:

JOB_NAME LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE REPEAT_INTERVAL

----------- --------------- ----------------- ----------------- -----------------

RMAN_BACKUP 21–OCT-10 02.12 +000000000 00:00: 25-OCT-10 12.00.0 freq=weekly; byda

.59.257151 PM - 41.933585 0.500000 AM -06:0 y=mon

06:00 0

From the prior output, the job will run on the next Monday, and since there was no BYHOUR and BYMINUTE options specified (when modifying the job), it is now scheduled to run at the default time of 12:00 a.m.

Stopping a Job

If you have a job that has been running for an abnormally long time, you may want to abort it. Use the STOP_JOB procedure to stop a currently running job. This example stops the RMAN_BACKUP job while it is running:

SQL> exec dbms_scheduler.stop_job(job_name=>'RMAN_BACKUP');

572

CHAPTER 21 ■ AUTOMATING JOBS

The STATUS column of DBA_SCHEDULER_JOB_LOG will show STOPPED for jobs stopped using the STOP_JOB

procedure.

Disabling a Job

You may want to temporarily disable a job because it's not running correctly. You want to ensure that the job does not run while you're troubleshooting the issue. Use the DISABLE procedure to disable a job: SQL> exec dbms_scheduler.disable('RMAN_BACKUP');

If the job is currently running, consider stopping the job first or using the FORCE option of the DISABLE procedure:

SQL> exec dbms_scheduler.disable(name=>'RMAN_BACKUP',force=>true); Enabling a Job

You can enable a previously disabled job via the ENABLE procedure of the DBMS_SCHEDULER package. This example re-enables the RMAN_BACKUP job:

SQL> exec dbms_scheduler.enable(name=>'RMAN_BACKUP');

■
Tip
You can check to see if a job has been disabled or enabled by selecting the ENABLED column from DBA_SCHEDULER_JOBS.

Copying a Job

If you have a current job that you want to clone, you can use the COPY_JOB procedure to accomplish this.

This procedure takes two arguments: the old job name and the new job name. Here's an example of copying a job where RMAN_BACKUP is a previously created job and RMAN_NEW_BACK is the new job that will be created:

begin

dbms_scheduler.copy_job('RMAN_BACKUP','RMAN_NEW_BACK');

end;

/

The copied job will be created but not enabled. You must enable the job first (see the prior section in this chapter) before it will run.

Running a Job Manually

You can manually run a job outside of its regular schedule. You might want to do this to test the job to ensure that it's working correctly. Use the RUN_JOB procedure to manually initiate a job. This example manually runs the previously created RMAN_BACKUP job:

573

CHAPTER 21 ■ AUTOMATING JOBS

BEGIN

DBMS_SCHEDULER.RUN_JOB(

JOB_NAME => 'RMAN_BACKUP',

USE_CURRENT_SESSION => FALSE);

END;

/

The USE_CURRENT_SESSION parameter instructs Oracle Scheduler to run the job as the current user (or not). A value of FALSE means run the job as the user it would run as when regularly scheduled (asynchronously).

Deleting a Job

If you no longer require a job, you should delete it from the scheduler. Use the DROP_JOB procedure to permanently remove a job. This example removes the RMAN_BACKUP job:

BEGIN

dbms_scheduler.drop_job(job_name=>'RMAN_BACKUP');

END;

/

The code will drop the job and remove any information regarding the dropped job from the DBA_SCHEDULER_JOBS view.

Oracle Scheduler versus cron

DBAs often debate whether they should use Oracle Scheduler or the Linux/Unix cron utility for scheduling and automating tasks. The benefits that Oracle Scheduler has over cron include the following:

• Can make the execution of a job dependent on the completion of another job

• Robust resource balancing and flexible scheduling features

• Can run jobs based on a database event

• The Oracle Scheduler DBMS_SCHEDULER PL/SQL package syntax works the same regardless of the OS

• Can run status reports using the data dictionary

• If working in clustered environment, no need to worry about synchronizing multiple cron tables for each node in the cluster

• Can be maintained and monitored via Enterprise Manager

The Oracle Scheduler is implemented via the DBMS_SCHEDULER PL/SQL package. It's fairly easy to create and maintain jobs using this utility (as shown previously in this chapter). While Oracle Scheduler has many benefits, many DBAs prefer to use a scheduling utility such as cron. The advantages of using cron include the following:

574

CHAPTER 21 ■ AUTOMATING JOBS

• Easy to use; simple, tried and true; only takes seconds to create and/or modify jobs

• Almost universally available on all Linux/Unix boxes; for the most part, runs nearly identically regardless of the Linux/Unix platform (yes, there are minor differences)

• Database agnostic; operates independently of the database and works the same regardless of the database vendor or database version

• Works whether the database is available or not

The prior lists aren’t comprehensive, but should give you a flavor of the uses of each scheduling tool. I prefer to use cron, but if you require a more sophisticated scheduler, then consider using Oracle Scheduler. The following sections in this chapter provide information on how to implement and schedule automated jobs via cron.

Automating Jobs via cron

The cron program is a job-scheduling utility that is ubiquitous in Linux/Unix environments. This tool derives its name from
chronos
(the Greek word for time). The cron (the geek word for scheduler) tool allows you to schedule scripts or commands to run at a specified time and repeat at a designated frequency.

How cron Works

When your Linux server boots up, a cron background process is automatically started to manages all cron jobs on the system. The cron background process is also known as the cron daemon. This process is started on system startup by the /etc/init.d/crond script. You can check to see whether the cron daemon process is running with the ps command:

$ ps -ef | grep crond | grep -v grep

root 3049 1 0 Aug02 ? 00:00:00 crond

You can also check to see whether the cron daemon is running using the service command: $ /sbin/service crond status

crond (pid 3049) is running...

The root user uses several files and directories when executing system cron jobs. The /etc/crontab file contains commands to run system cron jobs. Here is a typical listing of the contents of the

/etc/crontab file:

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

575

CHAPTER 21 ■ AUTOMATING JOBS

This /etc/crontab file uses the run-parts utility to run scripts located in the following directories:

/etc/cron.hourly, /etc/cron.daily, /etc/cron.weekly, and /etc/cron.monthly. If there is a system utility that needs to run other than on an hourly, daily, weekly, or monthly basis, then it can be placed in the

/etc/cron.d directory.

Each user can create a crontab (also known as a cron table) file. This file contains the list of programs that you want to run at a specific time and interval. This file is usually located in the

/var/spool/cron directory. For every user who creates a cron table, there will be a file in the

/var/spool/cron directory named after the user. As root, you can list the files in that directory:

# ls /var/spool/cron

oracle root

The cron background process is mostly idle. It wakes up once every minute and checks

/etc/crontab, /etc/cron.d, and the user cron table files and determines whether there are any jobs that need to be executed.

Table 21–1 summarizes the purpose of the various files and directories used by cron. Knowledge of these files and directories will help you troubleshoot any issues as well as understand cron in more detail.

Table 21–1.
Descriptions of Files and Directories Used by the cron Utility
File

Purpose

/etc/init.d/crond

Starts the cron daemon in system boot.

/var/log/cron

System messages related to the cron process. Useful for

troubleshooting problems.

/var/spool/cron/

User crontab files are stored in the /var/spool/cron directory.

/etc/cron.allow

Specifies users who can create a cron table.

/etc/cron.deny

Specifies users who are not allowed to create a cron table.

/etc/crontab

The system cron table that has commands to run scripts located in

the following directories: /etc/cron.hourly, /etc/cron.daily,

/etc/cron.weekly, and /etc/cron.monthly.

/etc/cron.d

A directory that contains cron tables for jobs that need to run on a

schedule other than hourly, daily, weekly, or monthly.

/etc/cron.hourly

A directory that contains system scripts to run on an hourly basis.

/etc/cron.daily

A directory that contains system scripts to run on a daily basis.

/etc/cron.weekly

A directory that contains system scripts to run on a weekly basis.

/etc/cron.monthly

A directory that contains system scripts to run on a monthly basis.

576

CHAPTER 21 ■ AUTOMATING JOBS

Enabling Access to cron

Sometimes when system administrators set up a new box, they don't (by default) enable the use of cron for all users on the system. To verify whether you have access to access cron, type in the following: $ crontab -e

If you receive the following error message, then you do not have access: You (oracle) are not allowed to use this program (crontab)

To enable cron access as the root user, add oracle to the /etc/cron.allow file with the echo command:

# echo oracle >> /etc/cron.allow

Once the oracle entry is added to the /etc/cron.allow file, you can use the crontab utility to schedule a job.

■
Note
You can also use an editing utility (such as vi) to add an entry to the cron.allow file.

The root user can always schedule jobs with the crontab utility. Other users must be listed in the

/etc/cron.allow file. If the /etc/cron.allow file does not exist, then the operating system user must not appear in the /etc/cron.deny file. If neither the /etc/cron.allow nor the /etc/cron.deny file exists, then only the root user can access the crontab utility.

■
Note
On some Unix operating systems (such as Solaris), the cron.allow and cron.deny files are located in the

/etc/cron.d directory.

Understanding cron Table Entries

Your cron table is a list of numbers and commands that the cron background process (cron daemon) will run at a specified time and schedule. The crontab utility expects entries to follow a well-defined format.

It’s a good idea to add a comment line at the beginning of your crontab file that documents the required format:

# min(0-59) hr(0-23) dayMonth(1-31) monthYear(1-12) dayWeek(0/7-6) commandOrScript In the previous line, the number (#) sign in a cron file represents the start of a comment. Any text entered after # is ignored by cron.