Oracle 10g Job Scheduling

g-Solutions

Oracle Job Scheduling

1       Oracle Database Scheduler Manual

Prior to version 10g of the Oracle database, JOBS were scheduled with the DBMS_JOBS package. All database jobs in Oracle 10g or higher should be scheduled with the Oracle Scheduler.

Improvements of Oracle Scheduler over DMBS Jobs Scheduler (legacy scheduler);

·         Captures history execution times for each scheduler jobs

·         Captures jobs CPU Time utilization during executions

·         Supports alert notification for job failures

·         Added flexible job scheduler time frameworks (for example, run daily at 6 AM, 4 PM, 9 PM)

1.1.1          Database Job Scheduling Best Practices

The following are a few guidelines to follow when scheduling database Jobs

  1. All non key business database processing must be executed off peak hours from the peak user community's time zone (i.e. US EST)
  2. All long running Oracle maintenance operations should be executed Friday evening through Monday morning based on the majority user community's time zone.
  3. All jobs that have data that conflicts with maintenance operations should scheduled around the maintenance jobs. For example, if statistics are updated over the weekend, daily updates of tables where stats are gathered should only be executed during the weekdays.
  4. All database jobs should be scheduled using the Oracle scheduler in version 10g or higher.
  5. All scheduled database job name should be prefixed with an abbreviation that indicates its affiliation or function (for example: 'DB long running job monitoring. Executes every 30 minutes')
  6. All scheduled database job should have a comment that describes the job's function along with its execution rate.
  7. All DB job privilege users should have the minimum database permissions to complete the needed tasks.
  8. The database long running monitoring should check at a maximum duration of 1 hour (recommendation every 30 minutes) during peak working hours of the majority user community's time zone.
  9. The database long session monitoring should check at maximum duration of 1 hour during peak working hours of the majority user community's time zone.
  10. The database jobs failure monitoring should check at maximum duration of 4 hours during peak working hours of the majority user community's time zone (recommended 3 hours).

 

 

1.2           DB_MON_MGR Job Monitoring MANUAL

The DB_MON_MGR package is a database mechanism that reports on long running sessions as well as long running/failed/disabled jobs.

Package Location

This package exposes various methods that can be scheduled to run at a desired interval.

Procedure

Notes

check_all

Calls check_jobs and check_sessions

check_jobs

Calls check_running_jobs, check_failed_jobs and check_disabled_jobs

check_running_jobs

Monitor long running database jobs

check_failed_jobs

Monitor failed database jobs

check_disabled_jobs

Monitor disabled job

check_sessions

Sends an email indicating long running user sessions

 

The following is recommendation calendar scheduler for monitoring the database jobs.

 

 

DB_MON_MGR Procedure

Script

check_running_jobs

begin   DBMS_SCHEDULER.CREATE_JOB (

job_name        => 'DB_MON_CHECK_RUNNING_JOBS'

,job_type         => 'PLSQL_BLOCK'

,job_action      => 'DB_MON_MGR.check_running_jobs;'

, start_date        => sysdate+(1/24)/30,

repeat_interval     => 'FREQ=MINUTELY;BYMINUTE=0,30'

,ENABLED            => TRUE

,AUTO_DROP       => FALSE

,COMMENTS        => 'DB long running job monitoring. Executes every 30 minutes');

end;

check_failed_jobs

begin   DBMS_SCHEDULER.CREATE_JOB (

job_name        => 'DB_MON_CHECK_FAILED_JOBS'

,job_type         => 'PLSQL_BLOCK'

, job_action      => 'DB_MON_MGR.check_failed_jobs'

, start_date        => sysdate+(3/24)

, repeat_interval     => 'FREQ=DAILY; BYHOUR=0,3,6,9,12,15,18,21'

, ENABLED            => TRUE

, AUTO_DROP       => FALSE

, COMMENTS        => 'DB failed job monitoring. Executes every 3 hours');

end;

check_disabled_jobs

begin   DBMS_SCHEDULER.CREATE_JOB (

job_name        => 'DB_MON_CHECK_DISABLED_JOBS'

,job_type         => 'PLSQL_BLOCK'

,job_action      => 'DB_MON_MGR.check_disabled_jobs'

,start_date        => sysdate+(1/24)

,repeat_interval     => 'FREQ=DAILY; BYHOUR=8,12,16',

ENABLED            => TRUE,

AUTO_DROP       => FALSE,

COMMENTS        => 'DB failed job monitoring. Runs at 8 AM, 12 PM and 4 PM EST');

end;

check_sessions

begin   DBMS_SCHEDULER.CREATE_JOB (

job_name        => 'DB_MON_CHECK_SESSIONS'

,job_type         => 'PLSQL_BLOCK'

,job_action      => 'DB_MON_MGR.check_sessions'

, start_date        => sysdate+(1/24)

,repeat_interval     => 'FREQ=HOURLY; BYMINUTE=35'

,ENABLED            => TRUE

,AUTO_DROP       => FALSE

,COMMENTS        => 'DB session job monitoring. Runs every hour');

end;

 

1.2.1          Configuration

 

Two database tables (MONITORING_REG and MONITORING_PARAMS) are used to configure the behavior of the DB_MON_MGR session/job monitoring processes.

 

Table Name

Notes

MONITORING_REG

The monitoring registration table containing users who will receive sessions and job notifications.

MONITORING_PARAMS

The monitoring parameters table containing configuration information such as the email sender accounts, email subjects for jobs/session notifications, etc.

 

 

Users receiving alerts from the DB_MON_MGR package must be added to the MONITORING_REG table. The use of this table is similar to a "publish and subscribe" design pattern. Subscribed users are added to this table to receive jobs, sessions or all types of monitoring notifications.

 

MONITORING_REG

Column

Notes

USERNAME

The registered user's network login.

EMAIL_ADDRESS

The registered user's email address where notifications will be sent.

TOPIC

This indicates the category of the notifications the register's user is interested in receiving via email.

 

Possible values

  • ALL – all notifications will be sent.
  • SESSIONS – only long running users sessions notification all be sent
  • JOBS – only job related notifications will be sent

 

Custom properties used for monitoring must be inserted into the MONITORING_PARAMS table. The use of this table is similar to a "properties" configuration file.

 

MONITORING_PARAMS

Column

Notes

PARAM_NM

The name of the parameter (see table below for the list of allowed parameter names).

PARAM_VALUE

The value of the parameter

 

The following are the parameter names allowed in the MONITORING_PARAMS table;

Parameter NAME

Notes

EMAIL_SENDER

Indicates the email address that should be used when sending monitoring jobs.

 

Example db_monitoring@.com

IGNORE_JOB_NM

Indicates the database job name that should be ignored when monitoring jobs.

 

MONITORING_JOB

IGNORE_JOB_OWNER

Indicates the database job owner that should be ignored when monitoring jobs. As an example, this parameter can be used to prevent the monitoring module for reporting on other monitoring users or system level accounts.

 

Example:  DB_MONITOR

IGNORE_SESSION_OSUSER

Indicates the operating system level user sessions that should be ignored when monitoring sessions.

 

Example: oraepm

IGNORE_SESSION_USERNAME

Indicates the database username sessions that should be ignored when monitoring sessions.

 

Example: DBSNMP

JOBS_SUBJECT

Indicates the email subject used for sending job notifications;

 

'Automated DB Job monitoring'

SESSIONS_SUBJECT

Indicates the email subject used for sending sessions notifications;

 

'Automated DB User Session monitoring'

 

 

 

1.2.2          Check Jobs

 

The new Oracle Scheduler logs all job activity. It maintains information such as the status of jobs along with their time to complete. This stored information can be queried with Oracle's Enterprise Manager or a SQL query.

 

The DB_MON_MGR package supports the generation of email alerts based on stored job scheduled information. The check_jobs procedure will identify long running, disabled and failed (broken) jobs.

 

Note that the Oracle scheduler also supports monitoring of jobs. Administrators can also flag the Scheduler to raise an event if any unexpected behavior occurs and indicate the actions that should be taken if the specified event occurs. For example if a job failed an administrator should be notified.

 

This package handles the needed monitoring changes due to the use of the DBMS_SCHEDULER for jobs. See the configuration section for support on how to exclude certain jobs for being alerted. The email format is HTML.

1.2.3          Checking Running Jobs

The procedure 'check_running_jobs' monitors long running database jobs.

It will email the database job information to all users that are registered for the 'JOBS' or 'ALL' topic in the MONITORING_REG table (see sample below).

 

R 1 -     Impact(H) – Effort(L) – Owner(M) – Monitoring – Open – Long Running Jobs

 

The 'check_running_jobs' procedure uses information stored in the MONITORING_JOBS_RULES to determine when to monitor, which jobs and the max time allowed for each. This 'rules' table allows administrator to customize the monitoring. For example, administrator can be alerted when any job runs more than a specified time during normal business hours.

 

Column

Type

Notes

JOB_NM_REGEXP_LIKE

VARCHAR2

The exact job name or a wild card pattern. The SQL like operator is used to compare the running job name with this value (i.e. '.B.*_STATS.*')

START_DT_D

NUMBER

Start day of the week

Values: 1-7 (1- Sunday)

END_DT_D

NUMBER

End day of the week

Values: 1-7 (7- Saturday)

START_HH24

NUMBER

Starting hour of the day

 

Values: 0-23 (0 midnight)

END_HH24

NUMBER

Starting hour of the day

 

End period

0-23 (12 noon)

MAX_TIME_INTERVAL

INTERVAL DAY TO SECOND

The maximum allowed execution time;

 

Format DDD HH:MM:SS

 

Example 000 02:00:00

 

As an example, the following data informs the monitoring manager that all jobs must be completed within 1 hour Sunday thru Saturday (all day);

 

1.2.4          Check Disabled Jobs

 

The procedure 'check_disabled_jobs' reports on all jobs that are disabled. It will email the disabled database job information to all users that are registered for the 'JOBS' or 'ALL' topic in the MONITORING_REG table (where the job has not been marked to be ignored in the MONITORING_PARAMS table). Jobs are often disabled by system administrators in order to perform various maintenance operations. Email alerts generated from this procedure serve as a reminder to administrators to re-enable them (if forgotten). It is recommended to run this procedure at a less frequent rate than checking running or failed jobs. See the sample below for the information reported.

 

R 2 -     Impact(M) – Effort(L) – Owner(M) – Monitoring – Open – Disabled Jobs

 

 

1.2.5          Check Failed Jobs

The procedure 'check_failed_jobs' monitors failed database jobs. It will email the failed database job information to all users that are registered for the 'JOBS' or 'ALL' topics in the MONITORING_REG table. The procedure queries all failed jobs for the current day (i.e. all of today's failures). See sample below.

 

R 3 -     Impact(M) – Effort(L) – Owner(M) – Monitoring – Open – Failed Jobs

 

1.2.6          Procedure check_sessions

 

This procedure 'check_sesions' reports user sessions active more than a configured amount of time. For example, the procedure can report on sessions that have been active 2 or 3 hours. The procedure also identifies other blocked sessions waiting on resources held by these long running user sessions.

R 4 -     Impact(H) – Effort(L) – Owner(M) – Monitoring – Open – Long running sessions

 

The following is an example of reported user sessions.

 

Column

Type

Notes

USERNAME_REGEXP_LIKE

VARCHAR2

The exact user name or a REGEXP wild card pattern. The SQL like operator is used to compare the session user name with this value (i.e. 'green_acct_.*')

START_DT_D

NUMBER

Start day of the week

Values: 1-7 (1- Sunday)

END_DT_D

NUMBER

End day of the week

Values: 1-7 (7- Saturday)

START_HH24

NUMBER

Starting hour of the day

 

Values: 0-23 (0 midnight)

END_HH24

NUMBER

Starting hour of the day

 

End period

0-23 (12 noon)

MAX_HR

NUMBER

The maximum allowed session activity duration in hours;

 

Example 0.5 = 30 minutes