Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Configuring Oracle Scheduler

This section contains:

Setting Oracle Scheduler Privileges

You must have the SCHEDULER_ADMIN role to perform all Oracle Scheduler administration tasks. Typically, database administrators already have this role with the ADMIN option as part of the DBA role. For example, users SYS and SYSTEM are granted the DBA role. You can grant this role to another administrator by issuing the following statement:

GRANT SCHEDULER_ADMIN TO username;

Because the SCHEDULER_ADMIN role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Object and system privileges are granted using regular SQL grant syntax. An example is if the database administrator issues the following statement:

GRANT CREATE JOB TO scott;

After this statement is executed, scott can create jobs, schedules, programs, file watchers, and credentials in his schema. Another example is if the database administrator issues the following statement:

GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, adam can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

Setting Chain Privileges

Scheduler chains use underlying Oracle Streams Rules Engine objects along with their associated privileges. To create a chain in their own schema, users must have the CREATE JOB privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in their own schema. These can be granted by issuing the following statement:

GRANT CREATE RULE, CREATE RULE SET, CREATE EVALUATION CONTEXT TO user;

To create a chain in a different schema, users must have the CREATE ANY JOB privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than their own. These can be granted by issuing the following statement:

GRANT CREATE ANY RULE, CREATE ANY RULE SET, 
   CREATE ANY EVALUATION CONTEXT TO user;

Altering or dropping chains in schemas other than the users's schema require corresponding system Rules Engine privileges for rules, rule sets, and evaluation contexts.

See Also:

"Chain Tasks and Their Procedures" for more information regarding chain privileges.

Setting Scheduler Preferences

There are several systemwide Scheduler preferences that you can set. You set these preferences by setting Scheduler attributes with the SET_SCHEDULER_ATTRIBUTE procedure. Setting these attributes requires the MANAGE SCHEDULER privilege. The attributes are:

  • default_timezone

    It is very important that you set this attribute. Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. See "Using the Scheduler Calendaring Syntax". They normally retrieve the time zone from start_date, but if no start_date is provided (which is not uncommon), they retrieve the time zone from the default_timezone Scheduler attribute.

    The Scheduler derives the value of default_timezone from the operating system environment. If the Scheduler can find no compatible value from the operating system, it sets default_timezone to NULL.

    It is crucial that you verify that default_timezone is set properly, and if not, that you set it. To verify it, run this query:

    SELECT DBMS_SCHEDULER.STIME FROM DUAL;
     
    STIME
    ---------------------------------------------------------------------------
    14-OCT-04 02.56.03.206273000 PM US/PACIFIC
    

    To ensure that daylight savings adjustments are followed, it is recommended that you set default_timezone to a region name instead of an absolute time zone offset like '-8:00'. For example, if your database resides in Miami, Florida, USA, issue the following statement:

    DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');
    

    Similarly, if your database resides in Paris, you would set this attribute to 'Europe/Warsaw'. To see a list of valid region names, run this query:

    SELECT DISTINCT TZNAME FROM V$TIMEZONE_NAMES;
    

    If you do not properly set default_timezone, the default time zone for repeating jobs and windows will be the absolute offset retrieved from SYSTIMESTAMP (the time zone of the operating system environment of the database), which means that repeating jobs and windows that do not have their start_date set will not follow daylight savings adjustments.

  • email_server

    This attribute specifies an SMTP server address that the Scheduler uses to send e-mail notifications for job state events. It takes the following format:

    host[:port]
    

    where:

    • host is the host name or IP address of the SMTP server.

    • port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.

    If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications.

  • email_sender

    This attribute specifies the default e-mail address of the sender for job state e-mail notifications. It must be a valid e-mail address. If this attribute is not set or set to NULL, then job state e-mail notifications that do not specify a sender address do not have a FROM address in the e-mail header.

  • email_server_credential

    This attribute specifies the schema and name of an existing credential object. The default is NULL.

    When an e-mail notification goes out, the Scheduler determines if the email_server_credential points to a valid credential object that SYS has execute object privileges on. If the SMTP server specified in the email_server attribute requires authentication, then the Scheduler uses the user name and password stored in the specified credential object to authenticate with the e-mail server.

    If the email_server_credential is specified, then the email_server attribute must specify an SMTP server that requires authentication.

    If the email_server_credential is not specified, then the Scheduler supports sending notification e-mails through an SMTP server for which authentication is not configured.

    Note:

    This functionality is available with Oracle Database 11g release 2 (11.2.0.2).
  • email_server_encryption

    This attribute indicates whether encryption is enabled for this SMTP server connection, and if so, at what point encryption starts, and with which protocol.

    Values for email_server_encryption are:

    NONE: The default, indicates no encryption.

    SSL_TLS: Indicates that either SSL or TLS are used, from the beginning of the connection. The two sides determine which protocol is most secure. This is the most common setting for this parameter.

    STARTTLS: Indicates that the connection starts in an unencrypted state, but then the command STARTTLS directs the e-mail server to start encryption using TLS.

    Note:

    This functionality is available with Oracle Database 11g release 2 (11.2.0.2).
  • event_expiry_time

    This attribute enables you to set the time in seconds before a job state event generated by the Scheduler expires (is automatically purged from the Scheduler event queue). If NULL, job state events expire after 24 hours.

  • log_history

    This attribute controls the number of days that log entries for both the job log and the window log are retained. It helps prevent logs from growing indiscriminately. The range of valid values is 0 through 1000000. If set to 0, no history is kept. Default value is 30. You can override this value at the job class level by setting a value for the log_history attribute of the job class.

See Oracle Database PL/SQL Packages and Types Reference for the syntax for the SET_SCHEDULER_ATTRIBUTE procedure.

Using the Oracle Scheduler Agent to Run Remote Jobs

Using the Oracle Scheduler agent, the Scheduler can schedule and run two types of remote jobs:

  • Remote database jobs: Remote database jobs must be run through an Oracle Scheduler agent. Oracle recommends that an agent be installed on the same host as the remote database.

    If you intend to run remote database jobs, the Scheduler agent must be release 11.2 or later.

  • Remote external jobs: Remote external jobs run on the same host that the Scheduler agent is installed on.

    If you intend to run only remote external jobs, release 11.1 of the Scheduler agent is sufficient.

You must install Scheduler agents on all hosts that remote external jobs will run on. You should install Scheduler agents on all hosts running remote databases that remote database jobs will be run on.

Each database that runs remote jobs requires an initial setup to enable secure communications between databases and remote Scheduler agents, as described in "Setting up Databases for Remote Jobs".

Enabling remote jobs involves the following steps:

  1. Enabling and Disabling Databases for Remote Jobs

  2. Installing and Configuring the Scheduler Agent on a Remote Host

  3. Performing Tasks with the Scheduler Agent

See Also:

Enabling and Disabling Databases for Remote Jobs

This section covers these topics:

Setting up Databases for Remote Jobs

Before a database can run jobs using a remote Scheduler agent, the database must be properly configured, and the agent must be registered with the database. This section describes the configuration, including the required agent registration password in the database. You will later register the database, as shown in "Registering Scheduler Agents with Databases".

You can limit the number of Scheduler agents that can register, and you can set the password to expire after a specified duration.

Complete the following steps once for each database that creates and runs remote jobs.

To set up a database to create and run remote jobs:

  1. Ensure that shared server is enabled.

    See "Enabling Shared Server".

  2. Using SQL*Plus, connect to the database as the SYS user.

  3. Enter the following command to verify that the XML DB option is installed:

    SQL> DESC RESOURCE_VIEW
    

    If XML DB is not installed, this command returns an "object does not exist" error.

    Note:

    If XML DB is not installed, you must install it before continuing.
  4. Enable HTTP connections to the database as follows:

    1. Determine whether or not the Oracle XML DBM HTTP Server is enabled:

      Issue the following command:

      SQL> SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
      

      If this statement returns 0, Oracle XML DBM HTTP Server is disabled.

    2. Enable Oracle XML DB HTTP Server on a nonzero port by logging in as SYS and issuing the following commands:

      SQL> EXEC DBMS_XDB.SETHTTPPORT (port);
      SQL> COMMIT;
      

      where port is the TCP port number on which you want the database to listen for HTTP connections.

      port must be an integer between 1 and 65536, and for UNIX and Linux must be greater than 1023. Choose a port number that is not already in use.

      Note:

      This enables HTTP connections on all instances of an Oracle Real Application Clusters database.
  5. Run the script prvtrsch.plb with following command:

    SQL> @?/rdbms/admin/prvtrsch.plb
    
  6. Set a registration password for the Scheduler agents using the SET_AGENT_REGISTRATION_PASS procedure.

    The following example sets the agent registration password to mypassword.

    BEGIN
      DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
    END;
    /
    

    Note:

    You must have the MANAGE SCHEDULER privilege to set an agent registration password. See Oracle Database PL/SQL Packages and Types Reference for more information on the SET_AGENT_REGISTRATION_PASS procedure.

    You will do the actual registration further on, in "Registering Scheduler Agents with Databases".

Disabling Remote Jobs

You can disable remote jobs on a database by dropping the REMOTE_SCHEDULER_AGENT user.

To disable remote jobs:

  • Submit the following SQL statement:

    DROP USER REMOTE_SCHEDULER_AGENT CASCADE;
    

Registration of new scheduler agents and execution of remote jobs is disabled until you run prvtrsch.plb again.

Installing and Configuring the Scheduler Agent on a Remote Host

Before you can run remote jobs on a particular host, you must install and configure the Scheduler agent, described in this section, and then register and start the Scheduler agent on the host, described in "Performing Tasks with the Scheduler Agent". The Scheduler agent must also be installed in its own Oracle home.

To install and configure the Scheduler agent on a remote host:

  1. Download or retrieve the Scheduler agent software, which is available on the Oracle Database Client media included in the Database Media Pack, and online at:

    http://www.oracle.com/technology/software/products/database

  2. Ensure that you have first properly set up any database on which you want to register the agent.

    See "Enabling and Disabling Databases for Remote Jobs" for instructions.

  3. Log in to the host you want to install the Scheduler agent on. This host runs remote jobs.

    • For Windows, log in as an administrator.

    • For UNIX and Linux, log in as the user that you want the Scheduler agent to run as. This user requires no special privileges.

  4. Run the Oracle Universal Installer (OUI) from the installation media for Oracle Database Client.

    • For Windows, run setup.exe.

    • For UNIX and Linux, use the following command:

      /directory_path/runInstaller
      

    where directory_path is the path to the Oracle Database Client installation media.

  5. On the Select Installation Type page, select Custom, and then click Next.

  6. On the Select Product Languages page, select the desired languages, and click Next.

  7. On the Specify Install Location page, enter the path for a new Oracle home for the agent, and then click Next.

  8. On the Available Product Components page, select Oracle Scheduler Agent, and click Next.

  9. On the Oracle Database Scheduler Agent page:

    1. In the Scheduler Agent Hostname field, enter the host name of the computer that the Scheduler agent is installed on.

    2. In the Scheduler Agent Port Number field, enter the TCP port number that the Scheduler agent is to listen on for connections, or accept the default, and then click Next.

      Choose an integer between 1 and 65535. On UNIX and Linux, the number must be greater than 1023. Ensure that the port number is not already in use.

      OUI performs a series of prerequisite checks. If any of the prerequisite checks fail, resolve the problems, and then click Next.

  10. On the Summary page, click Finish.

  11. (UNIX and Linux only) When OUI prompts you to run the script root.sh, enter the following command as the root user:

    script_path/root.sh
    

    The script is located in the directory that you chose for agent installation.

    When the script completes, click OK in the Execute Configuration Scripts dialog box.

  12. Click Close to exit OUI when installation is complete.

  13. Use a text editor to review the agent configuration parameter file schagent.conf, which is located in the Scheduler agent home directory, and verify the port number in the PORT= directive.

  14. Ensure that any firewall software on the remote host or any other firewall that protects that host has an exception to accommodate the Scheduler agent.

Performing Tasks with the Scheduler Agent

The Scheduler agent is a standalone program that enables you to schedule and run external and database jobs on remote hosts. You start and stop the Scheduler agent using the schagent utility on UNIX and Linux, and the OracleSchedulerExecutionAgent service on Windows.

This section covers these topics:

About the schagent utility

The executable utility schagent performs certain tasks for the agent on Windows, UNIX and Linux, as indicated by the options in Table 30-1.

Use schagent with the appropriate syntax and options as follows:

For example:

UNIX and Linux: AGENT_HOME/bin/schagent -status

Windows: AGENT_HOME/bin/schagent.exe -status

Table 30-1 schagent options

Option Description

-start

Starts the Scheduler Agent.

UNIX and Linux only

-stop

Prompts the Scheduler agent to stop all the currently running jobs and then stop execution gracefully.

UNIX and Linux only

-abort

Stops the Scheduler agent forcefully, that is, without stopping jobs first. From Oracle Database Release 11.2.

UNIX and Linux only

-status

Returns this information about the Scheduler Agent running locally: version, uptime, total number of jobs run since the agent started, number of jobs currently running, and their descriptions.

-registerdatabase

Register the Scheduler agent with the base database or additional databases that are to run remote jobs on the agent's host computer.

-unregisterdatabase

Unregister an agent from a database.


Using the Scheduler Agent on Windows

The Windows Scheduler agent service is automatically created and started during installation. The name of the service ends with OracleSchedulerExecutionAgent.

Note:

Do not confuse this service with the OracleJobScheduler service, which runs on a Windows computer on which an Oracle database is installed, and manages the running of local external jobs without credentials.

Starting the Scheduler Agent

Start the Scheduler agent with the following command:

To start the Scheduler agent:

  • Do one of the following:

    • On UNIX and Linux, run the following command:

      AGENT_HOME/bin/schagent -start
      
    • On Windows, start the service whose name ends with OracleSchedulerExecutionAgent.

Stopping the Scheduler Agent

Stopping the Scheduler agent prevents the host on which it resides from running remote jobs.

To stop the Scheduler agent:

  • Do one of the following:

    • On UNIX and Linux, run the schagent utility with either the -stop or -abort option as described in Table 30-1:

      AGENT_HOME/bin/schagent -stop
      
    • On Windows, stop the service whose name ends with OracleSchedulerExecutionAgent. This is equivalent to the -abort option.

Registering Scheduler Agents with Databases

As soon as you have finished configuring the Scheduler Agent, you can register the Agent on one or more databases that are to run remote jobs. You can also log in later on and register the agent with additional databases.

  1. If you have already logged out, then log in to the host that is running the Scheduler agent, as follows:

    • For Windows, log in as an administrator.

    • For UNIX and Linux, log in as the user with which you installed the Scheduler agent.

  2. Use the following command for each database that you want to register the Scheduler agent on:

    • On UNIX and Linux, run this command:

      AGENT_HOME/bin/schagent -registerdatabase db_host db_http_port
      
    • On Windows, run this command:

      AGENT_HOME/bin/schagent.exe -registerdatabase db_host db_http_port
      

    where:

    • db_host is the host name or IP address of the host on which the database resides. In an Oracle Real Application Clusters environment, you can specify any node.

    • db_http_port is the port number that the database listens on for HTTP connections. You set this parameter previously in "Enabling and Disabling Databases for Remote Jobs". You can check the port number by submitting the following SQL statement to the database:

      SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
      

      A port number of 0 means that HTTP connections are disabled.

    The agent prompts you to enter the agent registration password that you set in "Enabling and Disabling Databases for Remote Jobs".

  3. Repeat the previous steps for any additional databases to run remote jobs on the agent's host.