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 12c Release 1 (12.1)

The Oracle scheduler (DBMS_SCHEDULER) has been enhanced with each release since it was introduced in Oracle 10g Release 1. In Oracle Database 12c Release 1, the scheduler has added more functionality, with a focus on ease of use.

Related articles.

Script Jobs

Script jobs are all about improving the ease of use of the Oracle Scheduler. They don't provide anything that was not already possible, but they reduce the likelihood of mistakes and make task-specific schedules much clearer.

All three of the new script jobs are essentially external jobs for running shell scripts, SQL*Plus or RMAN. As a result, they require the CREATE JOB and CREATE EXTERNAL JOB privileges. They will also need credentials to access an OS user. The following permissions are necessary to allow the "TEST" user to run the examples in this article.

-- Set the necessary privileges
CONN sys@pdb1 AS SYSDBA

GRANT CREATE JOB TO test;
GRANT CREATE EXTERNAL JOB TO test;
GRANT CREATE CREDENTIAL TO test;

All the examples use the following credential to allow them to authenticate to the OS user that runs the job.

CONN test/test@pdb1

-- Create a credential so script is run as the
-- correct OS user on the database server.
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'oracle_ol6_121',
    username        => 'oracle',
    password        => 'oracle'
  );
END;
/

With these in place, we can look at the three new job scripts.

EXTERNAL_SCRIPT

The JOB_TYPE of EXTERNAL_SCRIPT is similar to an EXECUTABLE job, except that it allows you to build a script on the fly and run it as the JOB_ACTION, as if it were an existing shell script. It can also be used to run an existing script by specifying the full file system path to the script in the JOB_ACTION parameter. On Windows servers the script is run using "cmd.exe". On UNIX/Linux servers the script is run using "sh", unless the first line of the script indicates a specific shell using a shebang, for example "#!/bin/bash".

CONN test/test@pdb1

-- Create a job with a shell script defined in-line.
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);
  l_script   VARCHAR2(32767);
BEGIN
  l_job_name := DBMS_SCHEDULER.generate_job_name;
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);

  l_script := '#!/bin/bash
export PATH=$PATH:/bin
ls /home/oracle > /tmp/test.txt';

  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => TRUE
  );
END;
/
JOB_NAME=JOB$_290

PL/SQL procedure successfully completed.

SQL>

-- Check the status of the job.

COLUMN job_name FORMAT A20
SELECT job_name, status, error#
FROM   user_scheduler_job_run_details
ORDER BY job_name;

JOB_NAME             STATUS                             ERROR#
-------------------- ------------------------------ ----------
JOB$_290             SUCCEEDED                               0

SQL>

Notice the use of the credential, which indicates which OS user the script should run as, and provides authentication.

SQL_SCRIPT

The JOB_TYPE of SQL_SCRIPT runs the specified job as an SQL*Plus script, which means you get access to all the SQL*Plus functionality without having to manually shell out to the operating system and initiate SQL*Plus.

CONN test/test@pdb1

-- Create a job with a SQL*Plus script defined in-line,
-- including an explicit connect.
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);
  l_script   VARCHAR2(32767);
BEGIN
  l_job_name := DBMS_SCHEDULER.generate_job_name;
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);

  -- Notice the explicit database connection in the script.
  l_script := 'CONN test/test@pdb1
SPOOL /tmp/test.lst
SELECT SYSDATE, USER FROM dual;
SPOOL OFF';

  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'SQL_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => TRUE
  );
END;
/
JOB_NAME=JOB$_330

PL/SQL procedure successfully completed.

SQL>

-- Check the status of the job.

COLUMN job_name FORMAT A20
SELECT job_name, status, error#
FROM   user_scheduler_job_run_details
ORDER BY job_name;

JOB_NAME             STATUS                             ERROR#
-------------------- ------------------------------ ----------
JOB$_330             SUCCEEDED                               0

SQL>

The previous script required an explicit connect, which is not very secure. To counter that, the SQL*Plus connection can be put into a credential itself, as shown in the following example.

CONN test/test@pdb1

-- Create a credential so SQL*Plus connects
-- to the correct database user.
-- Notice the service is included in the username.
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'test_pdb1_ol6_121',
    username        => 'test@pdb1',
    password        => 'test'
  );
END;
/

-- Create a job with a SQL*Plus script defined in-line,
-- using a credential, rather than an explicit connect.
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);
  l_script   VARCHAR2(32767);
BEGIN
  l_job_name := DBMS_SCHEDULER.generate_job_name;
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);

  l_script := 'SPOOL /tmp/test.lst
SELECT SYSDATE, USER FROM dual;
SPOOL OFF';

  -- Create the job in disabled mode.
  -- The credential supplied is the OS user that should
  -- run SQL*Plus.
  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'SQL_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => FALSE
  );

  -- Set the credential used for SQL*Plus to
  -- connect to the database;
  DBMS_SCHEDULER.set_attribute(
    name      => l_job_name,
    attribute => 'connect_credential_name',
    value     => 'test_pdb1_ol6_121'
  );

  -- Enable the job.
  DBMS_SCHEDULER.enable(l_job_name);
END;
/
JOB_NAME=JOB$_336

PL/SQL procedure successfully completed.

SQL>

-- Check the status of the job.

COLUMN job_name FORMAT A20
SELECT job_name, status, error#
FROM   user_scheduler_job_run_details
ORDER BY job_name;

JOB_NAME             STATUS                             ERROR#
-------------------- ------------------------------ ----------
JOB$_336             SUCCEEDED                               0

SQL>

There are a few things to notice about these examples.

BACKUP_SCRIPT

The JOB_TYPE of BACKUP_SCRIPT runs the specified job as an RMAN script, without having to manually shell out to the operating system to run RMAN. The JOB_ACTION can specify an in-line backup script, or a full path to a backup script on the database server file system.

CONN test/test@pdb1

-- Create a job with an RMAN script defined in-line,
-- including an explicit connect.
SET SERVEROUTPUT ON
DECLARE
  l_job_name VARCHAR2(30);
  l_script   VARCHAR2(32767);
BEGIN
  l_job_name := DBMS_SCHEDULER.generate_job_name;
  DBMS_OUTPUT.put_line('JOB_NAME=' || l_job_name);

  -- Define the backup script.
  l_script := 'connect target /
run { 
  backup archivelog all delete all input;
}';

  DBMS_SCHEDULER.create_job(
    job_name        => l_job_name,
    job_type        => 'BACKUP_SCRIPT',
    job_action      => l_script,
    credential_name => 'oracle_ol6_121',
    enabled         => TRUE
  );
END;
/
JOB_NAME=JOB$_338

PL/SQL procedure successfully completed.

SQL>

-- Check the status of the job.

COLUMN job_name FORMAT A20
SELECT job_name, status, error#
FROM   user_scheduler_job_run_details
ORDER BY job_name;

JOB_NAME             STATUS                             ERROR#
-------------------- ------------------------------ ----------
JOB$_338             SUCCEEDED                               0

SQL>

Scheduler View Changes

The most important change to the scheduler views is the addition of the following columns to the [DBA | ALL | USER]_SCHEDULER_JOB_RUN_DETAILS views.

These are extremely useful for diagnosing problems during job runs, especially in dynamically created script jobs. They also save you from having to use the DBMS_SCHEDULER.GET_FILE procedure to return the stdout/stderr from external jobs, as shown below.

SQL> SELECT output FROM all_scheduler_job_run_details WHERE job_name = 'JOB$_338';

OUTPUT
--------------------------------------------------------------------------------

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 18 20:19:30 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: CDB1 (DBID=797615285)

RMAN> 2> 3>
Starting backup at 18-DEC-13
current log archived

OUTPUT
--------------------------------------------------------------------------------
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=223 RECID=3 STAMP=834522466
input archived log thread=1 sequence=224 RECID=4 STAMP=834524375
channel ORA_DISK_1: starting piece 1 at 18-DEC-13
channel ORA_DISK_1: finished piece 1 at 18-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_18/o1_mf_
annnn_TAG20131218T201937_9c40ttb4_.bkp tag=TAG20131218T201937 comment=NONE

OUTPUT
--------------------------------------------------------------------------------
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2013_1
2_18/o1_mf_1_223_9c3yz2yo_.arc RECID=3 STAMP=834522466
archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2013_1
2_18/o1_mf_1_224_9c40tps3_.arc RECID=4 STAMP=834524375
Finished backup at 18-DEC-13

Starting Control File and SPFILE Autobackup at 18-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2013_12_18/o1_mf
_s_834524379_9c40twl0_.bkp comment=NONE

OUTPUT
--------------------------------------------------------------------------------
Finished Control File and SPFILE Autobackup at 18-DEC-13

RMAN>

Recovery Manager complete.


SQL>

The [DBA | ALL | USER]_SCHEDULER_CREDENTIALS views has been deprecated in favour of the [DBA | ALL | USER]_CREDENTIALS views.

DBMS_CREDENTIAL

The credential related sub-programs of the DBMS_SCHEDULER package have been deprecated and replaced by the new DBMS_CREDENTIAL package.

From a usage perspective, it feels the same. You can see examples of its use in this article and here.

Data Guard Database Rolling Upgrade Support

Oracle Data Guard can be used to perform rolling upgrades and patches of the database by using a transient logical standby database. The scheduler now supports this by replicating jobs created on the primary database to the transient logical standby database.

You can read about the DBMS_ROLLING package and its use in a rolling upgrade here.

Multitenant Option and the Scheduler

For the most part, the scheduler usage appears unchanged by the multitenant option, with a few minor exceptions.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.