Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 11g Release 2

Since its introduction in Oracle 10g Release 1, the "new" Oracle Scheduler has been improved with every release. Once again, the latest release of the database comes with new scheduler features.

Related articles.

JOB_QUEUE_PROCESSES Parameter Change

In 11g Release 2, both DBMS_JOB and DBMS_SCHEDULER share the same job queue coordinator, so the value of the JOB_QUEUE_PROECESSES parameter affects both schedulers for the first time. If the value is set to 0, both schedulers will be disabled. If the value is set to non-zero, both schedulers will be enabled.

Email Notification

The DBMS_SCHEDULER package now includes procedures to control email notifications associated with jobs.

An email notification is associated with a job using the ADD_JOB_EMAIL_NOTIFICATION procedure. The subject and body of the email notification can be customized using a number of variables that are identified using the "%variable-name%" format. Fortunately, the default values of the subject and body parameters provide plenty of information, so customizing these values is not really necessary.

The events parameter determines which job events fire a notification, while the filer_condition parameter can reduce the notifications by filtering out those events that do not meet specific criteria, based on the SCHEDULER$_EVENT_INFO Object Type.

Before using email notifications, you must tell the scheduler which SMTP server to use to send the emails. You can also optionally set a default sender address, which is used if the sender parameter is not specified when defining the notification. Both these scheduler attributes are set below.

CONN / AS SYSDBA

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mycompany.com');
END;
/

Next we must define a job to associate the notification to. The following job is an empty stub that fires once a minute for 1 hour.

CONN test/test

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_notification_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; bysecond=0',
    end_date        => SYSTIMESTAMP + 1/24,
    enabled         => TRUE);
END;
/

With the job in place we can associate an email notification with it. The following notification fires when the job starts or succeeds. It uses the default subject and body.

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'test_notification_job',
  recipients =>  'tim@mycompany.com',
  events     =>  'job_started, job_succeeded');
END;
/

The following notification only fires if a job fails with a "600" error code.

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name         =>  'test_notification_job',
  recipients       =>  'tim@mycompany.com',
  events           =>  'job_failed',
  filter_condition => ':event.error_code=600');
END;
/

We can see the notification details using the %_SCHEDULER_NOTIFICATIONS views.

SET LINESIZE 100
COLUMN job_name FORMAT A25
COLUMN recipient FORMAT A20
COLUMN filter_condition FORMAT A30

SELECT job_name, recipient, event, filter_condition
FROM user_scheduler_notifications;

JOB_NAME                  RECIPIENT            EVENT               FILTER_CONDITION
------------------------- -------------------- ------------------- ------------------------------
TEST_NOTIFICATION_JOB     tim@mycompany.com    JOB_STARTED
TEST_NOTIFICATION_JOB     tim@mycompany.com    JOB_SUCCEEDED
TEST_NOTIFICATION_JOB     tim@mycompany.com    JOB_FAILED          :event.error_code=600

SQL>

We can use the same view to see the subject and body of the email that will be sent for the specific notification.

COLUMN subject FORMAT A30
COLUMN body FORMAT A45

SELECT subject, body
FROM   user_scheduler_notifications
WHERE  event = 'JOB_STARTED';

SUBJECT                        BODY
------------------------------ ---------------------------------------------
Oracle Scheduler Job Notificat Job: %job_owner%.%job_name%.%job_subname%
ion - %job_owner%.%job_name%.% Event: %event_type%
job_subname% %event_type%      Date: %event_timestamp%
                               Log id: %log_id%
                               Job class: %job_class_name%
                               Run count: %run_count%
                               Failure count: %failure_count%
                               Retry count: %retry_count%
                               Error code: %error_code%
                               Error message:
                               %error_message%

SQL>

Email notifications are removed using the REMOVE_JOB_EMAIL_NOTIFICATION procedure. In addition to the job_name parameter, it accepts recipients and events parameters. In both cases, if these are set to NULL (the default values), it is assumed notifications for all recipients/events for the specific job should be removed. The examples below show the removal of a specific event notification and all notifications for a specified job.

BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'test_notification_job',
  recipients =>  'tim@mycompany.com',
  events     =>  'job_succeeded');
END;
/

SELECT job_name, recipient, event, filter_condition
FROM user_scheduler_notifications;

JOB_NAME                  RECIPIENT            EVENT               FILTER_CONDITION
------------------------- -------------------- ------------------- ------------------------------
TEST_NOTIFICATION_JOB     tim@mycompany.com    JOB_STARTED
TEST_NOTIFICATION_JOB     tim@mycompany.com    JOB_FAILED          :event.error_code=600

SQL>

BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'test_notification_job');
END;
/

SELECT job_name, recipient, event, filter_condition
FROM user_scheduler_notifications;

no rows selected

SQL>

File Watcher

A file watcher is a new scheduler object that enables a new type of even-based job that is triggered by the arrival of a file in a specified location. File watchers can be defined to monitor locations on the local server and remote servers, provided they have an agent installed on them.

By default, file watchers only check for the arrival of files every 10 minutes, but this default interval can be changed by issuing the following statement.

CONN / AS SYSDBA

BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/

I've set the interval to 1 minute for these tests, but I would probably not set it that low in a production system.

The file watcher will need access to the OS to check for files, so we must create a credential for it to log on with. I'm going to use the Oracle software owner, but for a real system I would advise using the lowest privileged user possible.

BEGIN
  DBMS_SCHEDULER.create_credential(
    credential_name => 'local_credential',
    username        => 'oracle',
    password        => 'oracle');
END;
/

Next we create the file watcher itself. Using a "?" in the directory_path parameter signifies the ORACLE_HOME. The file_name parameter can reference a specific file name or a wildcard. Setting the destination parameter to NULL indicates the local server. For remote servers set it to a valid external destination, as shown by the ALL_SCHEDULER_EXTERNAL_DESTS view.

BEGIN
  DBMS_SCHEDULER.create_file_watcher(
    file_watcher_name => 'test_file_watcher',
    directory_path    => '/tmp/test',
    file_name         => '*.txt',
    credential_name   => 'local_credential',
    destination       => NULL,
    enabled           => FALSE);
END;
/

Next we create a scheduler program to access the event raised by the file watcher. The program must reference the event_message to retrieve information about the file, such as its name.

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name        => 'file_watcher_test_prog',
    program_type        => 'stored_procedure',
    program_action      => 'file_watcher_test_proc',
    number_of_arguments => 1,
    enabled             => FALSE);
END;
/

BEGIN
  DBMS_SCHEDULER.define_metadata_argument(
    program_name       => 'file_watcher_test_prog',
    metadata_attribute => 'event_message',
    argument_position  => 1);
END;
/

Next we define the stored procedure that we referenced in the program definition. It must accept and argument of the SCHEDULER_FILEWATCHER_RESULT type. The procedure defined below inserts the file name and size into a table.

CREATE TABLE file_watcher_output (
  message VARCHAR2(4000)
);

CREATE OR REPLACE PROCEDURE file_watcher_test_proc (p_sfwr SYS.SCHEDULER_FILEWATCHER_RESULT) AS
  l_message file_watcher_output.message%TYPE;
BEGIN
  l_message := p_sfwr.directory_path || '/' || p_sfwr.actual_file_name || ' (' || p_sfwr.file_size || ')';

  INSERT INTO file_watcher_output (message)
  VALUES (l_message);
  COMMIT;
END;
/

Next we create a job that references the objects we've just created. The queue_spec parameter is set to the name of the file watcher, while the program_name parameter is set to the name of the program object we defined, not the procedure name.

BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'file_watcher_test_job',
    program_name    => 'file_watcher_test_prog',
    event_condition => NULL,
    queue_spec      => 'test_file_watcher',
    auto_drop       => FALSE,
    enabled         => FALSE);
END;
/

By default, the arrival of new files will be ignored if the job is already running. If you need the job to fire for each new arrival, regardless of whether the job is already running or not, set the PARALLEL_INSTANCES attribute for the job to true. The job will then be run as a lightweight job.

BEGIN
  DBMS_SCHEDULER.set_attribute('file_watcher_test_job','parallel_instances',TRUE);
END;
/

With all the pieces in place, we can now enable all the objects we created.

EXEC DBMS_SCHEDULER.enable('test_file_watcher');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_prog');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_job');

Next create some files in the directory monitored by the file watcher.

$ echo "This is a test" > /tmp/test/test_file_1.txt
$ echo "This is a test too" > /tmp/test/test_file_2.txt
$ echo "Yes another test" > /tmp/test/test_file_3.txt

Querying the output table allows us to see that the job was triggered as expected.

SELECT * FROM file_watcher_output;

MESSAGE
--------------------------------------------------------------------------------
/tmp/test/test_file_1.txt (15)
/tmp/test/test_file_2.txt (19)
/tmp/test/test_file_3.txt (17)

SQL>

Information about file watchers is available from the *_SCHEDULER_FILE_WATCHERS views.

SET LINESIZE 100
COLUMN file_watcher_name FORMAT A20
COLUMN destination FORMAT A15
COLUMN directory_path FORMAT A15
COLUMN file_name FORMAT A10
COLUMN credential_name FORMAT A20

SELECT file_watcher_name, destination, directory_path, file_name, credential_name 
FROM   user_scheduler_file_watchers;

FILE_WATCHER_NAME    DESTINATION     DIRECTORY_PATH  FILE_NAME  CREDENTIAL_NAME
-------------------- --------------- --------------- ---------- --------------------
TEST_FILE_WATCHER                    /tmp/test       *.txt      LOCAL_CREDENTIAL

SQL>

General management of file watchers is similar to other scheduler objects. The majority of tasks are accomplished using the SET_ATTRIBUTE, ENABLE and DISABLE procedures. File watchers are dropped using the DROP_FILE_WATCHER procedure, which will only work if there are no references to the file watcher, unless the force parameter is set.

Remote Database Jobs

Oracle 11g Release 1 introduced the concept of Remote External Jobs, which use an agent on a remote host to run the external job. In 11gR2, this functionality has been extended to include remote database jobs. For remote database jobs to work you will need to perform the Remote Scheduler Agent Installation.

Once the installation is complete and the agent is registered with the database, the destination should be visible using the following query.

COLUMN destination_name FORMAT A20
COLUMN hostname FORMAT A20
COLUMN ip_address FORMAT A20

SELECT destination_name, hostname, port, ip_address
FROM   all_scheduler_external_dests;

DESTINATION_NAME     HOSTNAME                   PORT IP_ADDRESS
-------------------- -------------------- ---------- --------------------
RAC1                 rac1.localdomain           1500 192.168.2.101

SQL>

It is now possible to define one or more database destinations for the external destination defined for the remote agent.

With the remote agent configuration complete we need to define the remote database job. The first thing we need is a credential to allow use to log into the remote database.

BEGIN
  DBMS_SCHEDULER.create_credential(
    credential_name => 'test_credential',
    username        => 'test',
    password        => 'test');
END;
/

COLUMN credential_name FORMAT A25
COLUMN username FORMAT A20

SELECT credential_name,
       username
FROM   user_scheduler_credentials;

CREDENTIAL_NAME           USERNAME
------------------------- --------------------
TEST_CREDENTIAL           test

SQL>

Next we define the database destination. The agent parameter should reference the destination name associated with the remote agent. If the tns_name parameter is NULL, the default instance on the remote host is assumed. This default instance is defined by the ORACLE_SID and ORACLE_HOME parameters in the "schagent.conf" file on the remote server. If a tns_name is specified, it can either be a complete Oracle Net connect descriptor, or a regular alias, but it must be resolvable on the database running the scheduler, not just the remote host.

BEGIN
  DBMS_SCHEDULER.create_database_destination (
    destination_name => 'rac1_rac1_dest',
    agent            => 'rac1',
    tns_name         => 'RAC1',
    comments         => 'Instance named RAC1 on host rac1.localdomain');
END;
/

SELECT destination_name, destination_type
FROM   user_scheduler_dests;

DESTINATION_NAME     DESTINAT
-------------------- --------
RAC1_RAC1_DEST       DATABASE

SQL>

We are now able to create the remote database job itself. Notice the credential_name and destination_name parameters are set. The destination_name here refers to the database destination we just defined, not the external destination associated with the agent.

BEGIN
 DBMS_SCHEDULER.create_job (
   job_name            =>  'remote_db_job', 
   job_type            =>  'stored_procedure',
   job_action          =>  'test_proc',
   start_date          =>  SYSTIMESTAMP,
   repeat_interval     =>  'freq=minutely; bysecond=0',
   end_date            =>  SYSTIMESTAMP + 1/24,
   credential_name     =>  'test_credential',
   destination_name    =>  'rac1_rac1_dest',
   enabled             =>  TRUE);
END;
/

We can display the run details for the remote database job, just like any other job.

SET LINESIZE 120
COLUMN job_name FORMAT A20
COLUMN destination FORMAT A20
COLUMN actual_start_date FORMAT A20
COLUMN run_duration FORMAT A20
COLUMN status FORMAT A10

SELECT job_name,
       destination,
       TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
       run_duration,
       status,
       error#
FROM   user_scheduler_job_run_details
WHERE  job_name = 'REMOTE_DB_JOB'
ORDER BY actual_start_date;

JOB_NAME             DESTINATION          ACTUAL_START_DATE    RUN_DURATION         STATUS         ERROR#
-------------------- -------------------- -------------------- -------------------- ---------- ----------
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:34:35 +000 00:00:21        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:35:35 +000 00:00:06        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:36:34 +000 00:00:02        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:37:34 +000 00:00:02        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:38:34 +000 00:00:02        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:39:34 +000 00:00:02        SUCCEEDED           0
REMOTE_DB_JOB        RAC1_RAC1_DEST       01-OCT-2009 11:40:34 +000 00:00:02        SUCCEEDED           0

SQL>

Multiple Destination Jobs

Multiple destination jobs allow a single job definition to be executed against many servers. To achieve this we first need to create a destination group using the CREATE_GROUP procedure. This procedure is also used to manage window groups, but I will ignore that in this discussion.

The group_type parameter determines the type of group created (DB_DEST, EXTERNAL_DEST). The member parameter contains a comma separated list of destinations, which must already exist. The syntax for a destinations is as follows.

[[schema.]credential@][schema.]destination

The keyword "LOCAL" can be used to indicate the job should be run on the local machine also. In the case of remote external jobs the LOCAL keyword can be prefixed with a credential, but for database jobs it can not and always runs using the credentials of the owner of the job.

The following code creates a new group with a single member.

BEGIN
  DBMS_SCHEDULER.create_group(
    group_name    => 'test_db_group',
    group_type    => 'DB_DEST',
    member        => 'LOCAL');
END;
/

The ADD_GROUP_MEMBER and REMOVE_GROUP_MEMBER procedures can be used to maintain the member list of the group. The following example adds the destination created in the previous section to the destination group.

BEGIN
  DBMS_SCHEDULER.add_group_member(
    group_name    => 'test_db_group',
    member        => 'test_credential@rac1_rac1_dest');
END;
/

The *_SCHEDULER_GROUPS and *_SCHEDULER_GROUP_MEMBERS views display information about the groups.

COLUMN group_name FORMAT A20

SELECT group_name, group_type, enabled, number_of_members
FROM   user_scheduler_groups;

GROUP_NAME           GROUP_TYPE    ENABL NUMBER_OF_MEMBERS
-------------------- ------------- ----- -----------------
TEST_DB_GROUP        DB_DEST       TRUE                  2

SQL>


COLUMN member_name FORMAT A50

SELECT group_name, member_name
FROM   user_scheduler_group_members;

GROUP_NAME           MEMBER_NAME
-------------------- --------------------------------------------------
TEST_DB_GROUP        "TEST"."TEST_CREDENTIAL"@"TEST"."RAC1_RAC1_DEST"
TEST_DB_GROUP        LOCAL

SQL>

With the destination group in place we can now create a job using it.

BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'multi_dest_job', 
   job_type            =>  'stored_procedure',
   job_action          =>  'test_proc',
   start_date          =>  SYSTIMESTAMP,
   repeat_interval     =>  'freq=minutely; bysecond=0',
   end_date            =>  SYSTIMESTAMP + 1/24,
   credential_name     =>  'test_credential',
   destination_name    =>  'test_db_group',
   enabled             =>  TRUE);
END;
/

The inclusion of the credential_name parameter is unnecessary here, but it specifies a default credential to use if any of the destinations in the group were defined without an explicit credential.

We can display the run details for the multiple destination job, just like any other job.

SET LINESIZE 120
COLUMN job_name FORMAT A20
COLUMN destination FORMAT A20
COLUMN actual_start_date FORMAT A20
COLUMN run_duration FORMAT A20
COLUMN status FORMAT A10

SELECT job_name,
       destination,
       TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
       run_duration,
       status,
       error#
FROM   user_scheduler_job_run_details
WHERE  job_name = 'MULTI_DEST_JOB'
ORDER BY actual_start_date;

JOB_NAME             DESTINATION          ACTUAL_START_DATE    RUN_DURATION         STATUS         ERROR#
-------------------- -------------------- -------------------- -------------------- ---------- ----------
MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:35:00 +000 00:00:00        SUCCEEDED           0
MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:35:00 +000 00:00:03        SUCCEEDED           0
MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:36:00 +000 00:00:03        SUCCEEDED           0
MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:36:00 +000 00:00:00        SUCCEEDED           0
MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:37:00 +000 00:00:00        SUCCEEDED           0
MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:37:00 +000 00:00:03        SUCCEEDED           0
MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:42:34 +000 00:00:02        SUCCEEDED           0
MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:43:34 +000 00:00:02        SUCCEEDED           0
MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:44:34 +000 00:00:02        SUCCEEDED           0

SQL>

Notice that there are entries for all destinations, along with a summary entry for the main initiating job.

Window Group Procedures Deprecated

The window group related procedures have been deprecated in favor of the general group procedures, which are also used to manage destination groups. The table below shows how the old procedure map to the new ones.

Deprecated Procedure New Procedure
CREATE_WINDOW_GROUP CREATE_GROUP
DROP_WINDOW_GROUP DROP_GROUP
ADD_WINDOW_GROUP_MEMBER ADD_GROUP_MEMBER
REMOVE_WINDOW_GROUP_MEMBER REMOVE_GROUP_MEMBER

For more information see:

Hope this helps. Regards Tim...

Back to the Top.