Configuring Oracle Sched uler

This section contains:

Setting Oracle Scheduler Privileges
Setting Scheduler Preferences
Enabling and Disabling Remote Jobs

Setting Oracle Scheduler Privileges

You must have the SCHEDULER_ADMIN role to perform all Oracle Scheduler administration tasks. Typically, database administrators will 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 his own schema, a user must have the CREATE JOB privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in his 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, a user 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 his 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 user's schema will require corresponding system Rules Engine privileges for rules, rule sets, and evaluation contexts.

Setting Scheduler Preferences

There are several system-wide 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. 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 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. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.
email_sender

This 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.
log_history

This 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 999. 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.
max_job_slave_processes

This enables you to set a maximum number of slave processes for a particular system configuration and load. Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute. The default value is NULL, and the valid range is 1-999.

Although the number set by max_job_slave_processes is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.
event_expiry_time

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

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

Enabling and Disabling Remote Jobs

The Scheduler can schedule and run two types of remote jobs: remote database jobs and remote external jobs. A Scheduler agent must be installed on remote hosts so that the originating database can start remote jobs on that host and receive job output and error information. While remote external jobs always run on a remote host, remote database jobs can run on a remote host or on the local host—the same host as the originating database. Remote database jobs that run on the local host also require that a Scheduler agent be installed on the local host. In all cases, the agent must register with every database that must start remote jobs on the agent's host computer. An initial setup is also required for each database that must run remote jobs. This setup enables secure communications between the database and remote Scheduler agents.

Enabling remote jobs involves the following steps:

Setting Up the Database for Remote Jobs
Installing, Configuring, Registering, and Starting the Scheduler Agent

This section describes these steps and also includes the following topics:

Stopping the Scheduler Agent
Registering the Scheduler Agent with Additional Databases
Disabling Remote Jobs

Setting Up the Database 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. To make the registration of remote Scheduler agents secure, you must configure an agent registration password in the database. 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 must create and run remote jobs.

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

Ensure that shared server is enabled.

See "Enabling Shared Server".
Using SQL*Plus, connect to the database as the SYS user.
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.
Enable HTTP connections to the database by submitting the following PL/SQL block:
```
BEGIN
  DBMS_XDB.SETHTTPPORT(port);
END;
/
```
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.
Run the script prvtrsch.plb with following command:
```
SQL> @?/rdbms/admin/prvtrsch.plb
```
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:
The MANAGE SCHEDULER privilege is required 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.

Installing, Configuring, Registering, and Starting the Scheduler Agent

Before you can run remote jobs on a particular host, you must install, configure, register, and start the Scheduler agent on that host. The Scheduler agent must be installed in its own Oracle home. If you intend to run remote database jobs, the Scheduler agent must be release 11.2 or later. If you intend to run only remote external jobs, release 11.1 of the Scheduler agent is sufficient.

On the Windows, Linux, and UNIX platforms, the Scheduler agent software 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

The agent software for other platforms, such as IBM z/OS and IBM iSeries OS/400, is available on the Oracle Scheduler Agent media for those platforms. To install the agents on these platforms, consult the platform-specific documentation.

To install, configure, register, and start the Scheduler agent on a remote Windows, Linux, or UNIX host:

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

See "Setting Up the Database for Remote Jobs" for instructions.
Log in to the host on which you want to install the Scheduler agent. This is a host that must run 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.
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.
On the Select Installation Type page, select Custom, and then click Next.
On the Select Product Languages page, select the desired languages, and click Next.
On the Specify Install Location page, enter the path for a new Oracle home for the agent, and then click Next.]
On the Available Product Components page, select Oracle Scheduler Agent, and click Next.
On the Oracle Database Scheduler Agent page:
1. In the Scheduler Agent Hostname field, enter the host name of the computer on which the Scheduler agent is to run.
2. In the Scheduler Agent Port Number field, enter the TCP port number on which the Scheduler agent is to listen 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, handle them and then click Next.
On the Summary page, click Finish.
(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.
Click Close to exit OUI when installation is complete.
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.
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.
Register the Scheduler agent with a database that is to run remote jobs on the agent's host computer. Use the following command:
```
AGENT_HOME/bin/schagent -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 "Setting Up the Database 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 "Setting Up the Database for Remote Jobs".
Repeat the previous step for each database that is to run remote jobs on the agent's host.
(UNIX and Linux only) Start the Scheduler agent with the following command:
```
AGENT_HOME/bin/schagent -start
```
Note:
On Windows, a Scheduler agent service is automatically created and started during installation. The name of the service ends with OracleSchedulerExecutionAgent. 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.

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 following command:
```
AGENT_HOME/bin/schagent -stop
```
- On Windows, stop the service whose name ends with OracleSchedulerExecutionAgent.

Registering the Scheduler Agent with Additional Databases

After the initial installation and registration of the Scheduler agent, you may want to register the agent with additional databases at a later time. You must always restart the agent after registering with additional databases.

To register the Scheduler agent with additional databases:

Log in to the host that is running the Scheduler agent.
- For Windows, log in as an administrator.
- For UNIX and Linux, log in as the user with which you installed the Scheduler agent.
Run the following command for each additional database with which you want to register the agent:
```
AGENT_HOME/bin/schagent -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 "Setting Up the Database 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 "Setting Up the Database for Remote Jobs".
Restart the Scheduler agent.
- On UNIX and Linux, run these commands:
```
AGENT_HOME/bin/schagent -stop
AGENT_HOME/bin/schagent -start
```
- On Windows, restart the service ending with OracleSchedulerExecutionAgent

Disabling Remote Jobs

You can disable the capability of a database to run remote jobs 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.

Configuring Oracle Scheduler