8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
- Script Jobs
- Scheduler View Changes
- DBMS_CREDENTIAL
- Data Guard Database Rolling Upgrade Support
- Multitenant Option and the Scheduler
- Scheduler Features List
Related articles.
- All Scheduler Articles
- DBMS_CREDENTIAL : Persist Database and OS Credentials in Oracle Database 12c Release 1 (12.1)
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
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 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.
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.
- The credential supplied in the
CREATE_JOB
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
SET_ATTRIBUTE
procedure, by specifying theCONNECT_CREDENTIAL_NAME
attribute. - These examples use an in-line definition of the SQL*Plus script in the
JOB_ACTION
parameter, but we could just as easily have used a pre-written script on the database server's file system.
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.
- 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 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 they feel similar.
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.
- The
JOB_QUEUE_PROCESSES
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
JOB_QUEUE_PROCESSES
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.
Scheduler Features List
Here are quick links to articles on scheduler features that span multiple versions.
For more information see:
- DBMS_SCHEDULER
- DBMS_CREDENTIAL
- All Scheduler Articles
- DBMS_CREDENTIAL : Persist Database and OS Credentials in Oracle Database 12c Release 1 (12.1)
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
Hope this helps. Regards Tim...