DBA Hub

📋Steps in this guide1/9

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 1 (12.1)

Oracle Database 12c Release 1 adds more functionality to the Oracle scheduler.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

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 and 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. All the examples use the following credential to allow them to authenticate to the OS user that runs the job. With these in place, we can look at the three new job scripts.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 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;

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;
/
2

EXTERNAL_SCRIPT

The of is similar to an job, except that it allows you to build a script on the fly and run it as the , 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 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". Notice the use of the credential, which indicates which OS user the script should run as, and provides authentication.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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>
3

SQL_SCRIPT

The of runs the specified job as a 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. 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. There are a few things to notice about these examples. - The credential supplied in the command is the OS user that will run SQL*Plus, not the database connection credential. - When you create the credential for the database connection, you need to supply the relevant "tnsnames.ora" entry or you will have connection problems, especially on multitenant environments. - The database connection credential is associated with the job using the procedure, by specifying the attribute. - These examples use an in-line definition of the SQL*Plus script in the parameter, but we could just as easily have used a pre-written script on the database server's file system.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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>

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>
4

BACKUP_SCRIPT

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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>
5

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. - ERRORS - OUTPUT - BINARY_ERRORS - BINARY_OUTPUT 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 procedure to return the stdout/stderr from external jobs, as shown below. The [ DBA | ALL | USER]_SCHEDULER_CREDENTIALS views has been deprecated in favour of the [DBA | ALL | USER]_CREDENTIALS views.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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>
6

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 they feel similar. You can see examples of its use in this article and here .
7

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 .
8

Multitenant Option and the Scheduler

For the most part, the scheduler usage appears unchanged by the multitenant option, with a few minor exceptions. - The initialization parameter should be set at the root container level. It can be set at the PDB level in 12.1.0.2, but it doesn't work properly, so don't do it ( MOS Doc ID 2175443.1 ). Thanks to Patrick Jolliffe for pointing me to the MOS note. - Most global scheduler attributes should be set at the PDB level. - The job queue coordinator considers jobs in all PDBs to decide which one to start next. If the job initialization parameter is set too low, jobs may be slow to start if the slaves are taken up by another PDB. - Windows can be opened at the CDB level, to control resources used by each PDB, and at the PDB level, to control the resources used internally in the PDB. - Although the job slaves are common to the CDB, they switch between PDBs to perform their tasks. - If a PDB is closed with the IMMEDIATE option, any jobs running are closed and will need to be recovered before they can restart. The jobs may move to another node in a RAC configuration.
9

Scheduler Features List

Here are quick links to articles on scheduler features that span multiple versions. - Simple Example - Programs - Schedules - Jobs - Job Classes - Windows - Windows Groups - Enable, Disable and Attributes - Configuring The Scheduler - Calendar Syntax Examples - Extracting DDL - Scheduler Views - Time Zones - Implicit Commit - Jobs That Raise Events - Event-Based Jobs - Job Chains - Remote External Jobs - Credentials - Detached Jobs - Lightweight Jobs - Scheduler Support for Data Guard - Email Notification - File Watcher - Remote Database Jobs - Multiple Destination Jobs - Script Jobs - DBMS_CREDENTIAL - Data Guard Database Rolling Upgrade Support - Multitenant Option and the Scheduler - In-Memory Jobs - Job Incompatibility Definitions - Scheduler Resource Queues For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!