Building a Schedule
Note: This section summarizes the features of the scheduling jobs in Oracle Scheduler using the Oracle Scheduler integration. Refer to Scheduling Jobs with Oracle Scheduler for additional information.
To use the Oracle Scheduler the scheduler objects must be loaded, and the command lines use the provided Oracle Scheduler Interface PL/SQL package.
Recommended Tools
There are several tools that are recommended to be used to define, maintain and manage the Oracle Scheduler:
• PL/SQL interface - There is a PL/SQL based command line interface that can be used to define the scheduler objects and control the scheduler through the DBMS_SCHEDULER package from any SQL interface tool. For more information about the PL/SQL interface, refer to the DBMS_SCHEDULER Reference. This is useful for direct access environments. It is also used by the other tools, through generated statements, to manage the Oracle Scheduler.
• Oracle SQL Developer - Oracle SQL Developer includes an interface to manage scheduler objects including wizards to define key objects. For example:
• Oracle Enterprise Manager - The database targets associated with the Oracle Scheduler allow for the definition, management and monitoring scheduler objects. For example:
It is possible to use any of the interfaces above to manage your schedule objects. Refer to the help pages associated with each tool for additional advice on individual objects.
Defining Programs
Note: Definition of Program Objects is optional and can be replaced directly with Job definitions. It is recommended that Program Objects be used for maximum reuse.
The first step in the process is to define each batch control as a Program object in the scheduler using the following attributes:
Attribute | Recommended content |
|---|---|
program_name | Name of the Program. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. |
comments | A short description of the program. This is useful for searching the program. |
enabled | Set to true to enable this program to be used by the scheduler. |
program_type | Specify the PL/SQL Block as the type of program for the Oracle Scheduler Interface. |
program_action | Specify the interface commands line for the execution. You may include any overrides, if desired, at this time. For optimization purpose, only specify the parameters that are not inherited from the global options or are not defaulted on the batch control. It is recommended to specify the Batch Control to avoid issues in submission. An example of a simple command line is: begin OUAF_BATCH.Submit_Job(batch_code => 'BATCH01'); end; |
There are number of ways that can be used to specify the program using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_SCHEDULER.CREATE_PROGRAM procedure using the above parameters to define the program object. |
SQL Developer | Use the New Program menu item of the Scheduler tree to define the new program using a right click action on the Program node. |
Enterprise Manager | Using the Administration, Oracle Scheduler, Programs menu item, specify the program information as above. Use the Source field to hold the PL/SQL command line for the interface. |
You may create more than one program for the same batch control if there are different parameters is needed for different executions.
Defining Job Classes
Note: The use of Job Classes is optional. It is only used to group jobs for attribute inheritance, service affinity, monitoring purposes and/or resource profiling. DEFAULT_JOB_CLASS is used if no job class is specified.
Note: Job Class is only one technique for prioritizing jobs. Refer to Prioritizing Jobs for more information.
If Job Classes are to be used, they need to be set up to be used by the jobs allocated to them. The attributes of a Job Class are as follows:
Attribute | Recommended content |
|---|---|
job_class_name | Name of the Job Class. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. |
comments | A short description of the job class. This is useful for searching for the job class. |
resource_consumer_name | Name of the resource consumer group associated with jobs in this job class. Optional. |
service | Name of database service associated with this job class name. Optional. |
logging_level | Sets the level of logging is performed by the Oracle Scheduler. Optional. |
log_history | Sets the retention period, in days, log history is retained by the Oracle Scheduler. Optional. |
There are number of ways that can be used to specify the job class using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure using the above parameters to define the job class object. |
SQL Developer | Use the New Job Class menu item of the Scheduler tree to define the new job class using a right click action on the Scheduler node. |
Enterprise Manager | Using the Administration, Oracle Scheduler, Job Class menu item, specify the class information as above. |
Defining Schedules
Note: This section is only a subset of the capabilities of the schedule object. For more advanced discussions and different scenarios refer to the Creating and Managing Schedules to Define Jobs in the Oracle Database documentation.
Before defining jobs and chains you may need to define a schedule to run them against. Schedules can be time based and/or event based. It is also possible to create multiple schedules to support complex scheduling requirements. For example, you can create a holiday schedule with the dates that are blacklisted for execution in another schedule.
The following attributes are typically populated for the schedule object:
Attribute | Recommended Content |
|---|---|
schedule_name | Name of the schedule. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. |
comments | A short description of the schedule. This is useful for searching the schedule. |
start_date | Optional start date and time when the schedule becomes valid. If not populated, then the date jobs or windows are activated is used. |
repeat_interval | This specifies how often the schedule repeats. It uses the Calendaring Syntax supported by the Oracle Scheduler. Even schedules decide the repeat interval for checking the event. |
end_date | Optional date and time after jobs and windows allocated to this schedule will not execute. |
There are number of ways that can be used to specify the schedule using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_SCHEDULER.CREATE_SCHEDULE procedure using the above parameters to define the schedule object. This interface is recommended for complex Calendaring Syntax support. |
SQL Developer | Use the New Credential menu item of the Scheduler tree to define the new schedule using a right click action on the Schedule node. |
Enterprise Manager | Using the Administration, Oracle Scheduler, Schedules menu item, specify the schedule information as above. |
Defining Remote Destinations and Credentials
Note: This section outlines the process of specifying destinations and credentials for remote processing. If the database scheduler is housed within the product database (the default installation) then this section only applies to File Watcher type jobs.
Note: In Oracle Database 12c, the CREATE_CREDENTIAL procedure for DBMS_SCHEDULER has been deprecated and replaced with the CREATE_CREDENTIAL procedure within the DBMS_CREDENTIAL package.
When executing Jobs and File Watchers that are remote from the scheduler database and location, it is required that a destination be defined for the job and relevant credentials for the job to execute on that destination. The following needs to be set up:
• To execute product jobs remotely, the product database server needs to be defined as a destination and the product database credentials should be specified. It is recommended that the values expressed in BATCH_DBUSER and BATCH_DBPASS be specified as credentials.
• For File Watcher jobs, the product server where the file is housed must be defined as a destination. The operating system credentials for the batch process must be specified to access the file must be specified as credentials.
• There is no explicit process for creating non-database related destinations in the API (there is no CREATE_AGENT_DESTINATION procedure). When an agent registers with the database scheduler as part of the startup procedure, it is automatically added as a destination. If you wish to register remote databases then use the CREATE_DATABASE_DESTINATION package.
• For Database Destinations, the database TNS information must exist on the scheduler database to connect remotely.
• At least one product related database destination must be registered for remote invocation of product jobs.
• The Oracle Scheduler agents must be executing for the scheduler to initiate and track remote jobs.
To specify the credentials the following attributes must be provided:
Attribute | Recommended Content |
|---|---|
credential_name | Name of the credential. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. |
comments | This attribute is not used by the DBMS_SCHEDULER package but can be provided for documentation purposes. |
username | The userid to be used for connection. This attribute is required and is case sensitive. |
password | The password associated with the username. This value is stored in obfuscated format and is not available in Oracle Scheduler views. |
database_role | This is the database role associated with this user. This is not used for product jobs but is required for any DBA tasks requiring privilege access. |
windows_domain | For Windows Server implementations only. This is the windows domain for the user to connect to. |
There are number of ways that can be used to specify the credentials using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_CREDENTIAL.CREATE_CREDENTIAL procedure using the above parameters to define the credential object. |
SQL Developer | Use the New Credential menu item of the Scheduler tree to define the new credential using a right click action on the Credentials node. Oracle SQL Developer uses DBMS_CREDENTIAL for credential management. |
Enterprise Manager | Oracle Enterprise Manager uses its own security system for credentials. Refer to the Enterprise Manager Cloud Control Security Guide for more details. |
When specifying Database Destinations, the following attributes need to be specified:
Attribute | Recommended Content |
|---|---|
destination_name | Name of the destination. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. |
comments | This attribute is not used by the DBMS_SCHEDULER package but can be provided for documentation purposes. |
agent | The agent name as specified in the agent configuration file. |
tns_name | Oracle service name as documented in the TNS file attached to the configuration file. This is used to connect from the scheduler database to the remote database. |
There are number of ways that can be used to specify the database destination using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_CREDENTIAL.CREATE_DATABASE_DESTINATION procedure using the above parameters to define the credential object. |
SQL Developer | Use the New Database Destination menu item of the Scheduler tree to define the new destination using a right click action on the Database Destinations node. |
Enterprise Manager | Oracle Enterprise Manager uses its own security system for destinations. Refer to the Enterprise Manager Cloud Control Security Guide for more details. |
Defining Jobs
Note: This section is only a subset of the capabilities of the job object. For more advanced discussions refer to the Jobs section in the Oracle Database documentation.
Note: Product jobs should never be defined as Lightweight jobs. Those are reserved for specific Oracle products.
The key object in the Oracle Scheduler is the Job object. This object is a collection of data that tells the scheduler what, when, where and how to execute a process. In the case of the product, a job defines how to execute an instance of a Batch Control whether it is independent or part of a Chain.
Each Job definition has a set of attributes that define the Job:
Attribute | Recommended content |
|---|---|
job_name | Name of the job. This name should not include any embedded characters or '-' character according to Oracle object naming conventions. It is recommended to replace '-' in Batch Controls with '_' to avoid naming issues. |
comments | Description of Job. Useful for finding jobs. |
job_type | If programs have been used, then the type STORED_PROCEDURE should be used. If programs are not used, then specify PLSQL_BLOCK with the action containing the source PL/SQL command. |
job_action | Value depends on the specification of the job_type: • If job_type is STORED_PROCEDURE, then this will contain the fully qualified name of the program to execute. • If job_type is PLSQL_BLOCK, then this will contain the PL/SQL command to execute the OUAF_BATCH interface. |
start_date | Optional start date and time when the schedule becomes valid. If not populated, then the date jobs or windows are activated is used. If schedule_name is specified, then this date and time may be null to inherit the value from the schedule or chain. |
repeat_interval | Optional. Definition of how often the job will repeat. It uses the Calendaring Syntax supported by the Oracle Scheduler. Even schedules decide the repeat interval for checking the event. If schedule_name is specified, then this value may be null to inherit the value from the schedule or chain. |
schedule_name | Optional. Name of schedule object dictating the schedule for this job. |
job_class | Optional. Name of job class allocated to this job. |
end_date | Optional date and time after jobs and windows allocated to this job will not execute. If schedule_name is specified, then this date and time may be null to inherit the value from the schedule or chain. |
credential_name | Optional. Name of credential to use for remote invocation for this job. |
destination_name | Optional. Name of destination to use for remote invocation for this job. For remote product jobs this must be the location of the product database. |
enabled | Whether this job is enabled or not. Only enabled jobs (true) are executed. |
auto_drop | Whether after execution of this job, the job definition will be dropped. This is typically set to true for temporary jobs only. It is recommended to set to false for all regular product jobs. |
There are additional attributes to manage the advanced features of the job. Refer to the Job Object for a discussion of the additional parameters.
There are number of ways that can be used to specify jobs using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Use the DBMS_SCHEDULER.CREATE_JOB procedure using the above parameters to define the job object. |
SQL Developer | Use the New Job menu item of the Scheduler tree to define the new job using a right click action on the Jobs node. |
Enterprise Manager | Using the Administration, Oracle Scheduler, Jobs menu item, specify the job information as above. |
Defining Chains
Note: It is recommended to review the Creating and Managing Job Chains documentation for additional advice.
Once the jobs and schedule have been defined the next step is to define the set of job chains which configure the order and rules for a sequence of related jobs. Typically a job chain represents a set of business related processes that must be scheduled and processed in a specific order. For example, a nightly set of jobs that constitute a set of business processes is considered a chain.
There are a various rules for a chain:
• There is a begin event and an end event that are automatically generated for a chain. The chain is considered ended when all jobs directly related to the end event are complete.
• Relationships in the chain are based upon job state events to dictate next actions. Typically, this is SUCCEEDED but can support other events. For example, it is possible to start the next job in a chain based upon a FAILED event to perform cleanup activities if required.
There are a number of key steps outlined in Creating and Managing Job Chains that should be performed with the following additions building chains that include product jobs:
• The naming conventions for the objects defined in the chain are subject to rules for all Oracle dictionary objects. It is recommended to prefix customizations with CM_ to avoid conflicts with base provided objects.
• It is recommended to implement a chain step per batch control that is applicable to the chain. The step name can match the Batch control or a valid alternative for your implementation.
• For each sequence in the chain, set the appropriate state in a Chain Rule for the setup. For example, if StepB must start only if StepA is complete, then set the condition for the rule for Step A as StepA SUCCEEDED and action as START StepB. Complex rules are supported. Refer to Adding Rules to a Chain for more details.
There are number of ways that can be used to specify chains using the various tools available:
Interface | Method of Definition |
|---|---|
PL/SQL | Refer to Creating and Managing Job Chains for the sequence of statements to execute. |
SQL Developer | Use the New Chain menu item of the Scheduler tree to define the new chain using a right click action on the Chains node. |
Enterprise Manager | Using the Administration, Oracle Scheduler, Chains menu item, specify the chain information. |
Other Scheduler Objects
There are several scheduler objects not covered in the above documentation that can be used by implementations using the following guidelines:
• File Watchers - These objects create events based upon an arrival of a file in a configured location. To use File Watchers, a destination and credentials need to be defined along with the File Watcher itself. The file needs to be accessible by the process that is using it.
• Windows - These are definitions of times and dates where certain conditions; restrictions or resource profiles can be used. Oracle Scheduler supports discrete or overlapping Windows.
Note: Whilst supported, Oracle does not recommend overlapping windows.
• Groups - It is possible to group certain scheduler objects for efficient administration and clustering purposes.