Oracle Scheduler New Features - Oracle 11g

Oracle Scheduler functionality has been enhanced in the new release.Among the important new features of Oracle Scheduler are the concepts of a light weight job,remote external jobs, and support for Data Guard.We’ll explore these features in the following sections.

Lightweight Jobs

A Scheduler job is a task you schedule to run one or more times.Jobs conta in two things:the action to be taken and its sche dule.You can create a job by explicitly specifying the action to be performed by the job and its schedule in the job definition itself, and you can also create a job by using an exist ing program object and schedule object to specify the action and the frequency of execution.

In Oracle Database 10g,there was simply only one type of job you could create.Oracle Database 11g lets you create two types of jobs:regular jobs and lightweight jobs.Regular jobs are the jobs supported by the Scheduler in Oracle Database 11g.Regular jobs entail quite a bit of overhead,because they have to be created and dropped after each execution by the Scheduler.Regular jobs offer advantages such as the ability to use other users’programs,and they offer fine-grained control over the privileges to run the job.

A lightweight job inherits the privileges,and in some cases the job meta data itself, from a job template. Lightweight jobs aren’t self-contained jobs like regular Scheduler jobs,since they inherit the job characteristics from the job template.If the Scheduler needs to create and drop a large number of jobs every day,lightweight jobs are preferable to regular jobs.Use regular jobs for tasks that you need to execute infrequently. Lightweight jobs, since they aren’t database objects, don’t cause overhead by having to create and drop them. In addition, they are faster to create and need less space for storing their metadata.

You create a lightweight job by using the create_job procedure of theDBMS_SCHEDULER package.In addition to the usual arguments that you must supply to create a regular job,you also specify the value LIGHTWEIGHT for the new attribute job_style.You don’t have to specify the job_style attribute when creating a regular job.The following example shows how to create a lightweight job by specifying a program as a job template.

The following statement creates a lightweight job that uses the program test_prog as a job template:

In the example,the template attribute refers to the program (or stored procedure) that contains the job action. Note that you must use a job template when creating a lightweight job.That is, you can’t create a light weight job that contains all the information in the job creation statement itself.You also can’t assign privileges directly in a lightweight job because the job will acquire or inherit the privileges from the job template.

Remote External Jobs

A Scheduler job that runs outside the Oracle database,such as an operating system shell script, is known as an external job.You specify the value execut able for the job_type attribute when dealing with an external job.These external jobs, which you are familiar with from the Oracle Database 10g release, are called local external jobs now,since they run locally on the same host where the scheduling database is running.

Oracle Database 11g introduces a new type of operating system external job, called a remote external job,which runs,not on the host that’s running the Oracle database but on a remote host.The database uses a Scheduler agent (formally known as Oracle External Scheduler Agent) on a remote host to start remote external jobs and return their results to the scheduling database. The remote host needs only the Scheduler agent and not an Oracle database.The Scheduler agent must register separately with each of the Oracledatabases that you want to grant permission to in order to run a remote external job on the server hosting the Scheduler agent.

Installing,Registering,and Managing the Scheduler Agent

Before you can create and schedule a remote external job,you must first install and configure the Scheduler agent.You can do this by installing Oracle Data base Gate way.For our Linux server, we downloaded and installed Oracle Database Gateway 11g Release 1 ( for Linux x86 from the Oracle web site (you can also install it from the database CD pack).Here are the installation configuration and registration steps for the Scheduler agent:

  1. Run the Oracle Universal Installer from the directory where you saved the Oracle Database Gateway software files, as shown here:
  2. $ /11g/gateways/runInstaller Of course,you must first unzip the downloaded installation file (linux_11g in our case) before you can invoke the Oracle Universal Installer.
  3. On the Select a Product to Install page, which appears after the Welcome page,select Oracle Scheduler Agent On the Oracle Scheduler Agent page, specify the Scheduler agent host name and port number.
  4. Once the installation of the agent is complete, you must run the configuration script as the root user.A successful installation is reported with a message on the End of Installation page.Click Exit to leave the Oracle Universal Installer.
    Once you install the Scheduler agent, it’s time to register the agent with the database that you’ll be using to run external jobs on the host housing the agent. Before you can register the agent with a particular database, you must first “set up” that database by issuing the following command as the user sys:
    $ @ORACLE_HOME/rdbms/admin/prvtrsch.plb
  5. Once you run the prvtrsch.plb script, you must set a registration password for the Scheduler agent using the set_agent_registration_pass procedure,as shown in the example here:

In this example,sammyy1 is the Scheduler agent registration password for the data base host.Now you’re ready to register the Scheduler agent with a database.Here’s the command to register a Scheduler agent with a database:

$ schagent –registerdatabase database_host database_xmldb_http_port

You can find out the value for the database_xmldb_http_port (the port on which the database listens for HTTP connections) by issuing the following query:

SQL> select dbms_xdb.gethttpport() from dual;

Once you register the agent with the database that will run remote external jobs on the agent’s host server, start the Scheduler agent with the following command (on a Unix/Linux server):

You can stop the Scheduler agent with the following command:

You can disable remote external jobs by dropping the database user remote_ scheduler_ agent. This will disable the registration of all new Scheduler agents as well as all remote external jobs.You can create the user again by executing the prvtrsch.plb script again.

Now that you’ve learned how to create and manage the Scheduler agent,let’s look at howyou actually create a remote external job.

Creating a Remote External Job

Creating a remote external job is a bit more intricate than creating a local external job.In addition to the usual job attributes, you must supply the following additional information when creating a remote external job:

  1. You must specify a credential for the remote external job.Oracle recomm ends specifying the credential even for local external jobs,but it isn’t mandatory to do so.Before you can specify a credential for a remote external job,you must first create that credential using the create_credentialprocedure,as shown here:
  2. SQL> exec dbms_scheduler.create_credential('testcredential', 'salapati','sammyy1'); In the example, the database creates a credential named testcre dential,with the sername salapati and password sammyy1.You can grant any user in the data base the privilege to use a credential by using the grant statement,as shown here: SQL> grant execute on testcredential to newuser; Once you create a credential,you can specify the credential for any external job by using the set_attribute procedure,as we’ll show in our example in this section.You can view all the creden tials in a database by querying the DBA_SCHEDULER_CREDENTIALS view.
  3. You must use the destination attribute when creating a remote external job to specify the destination host and port.You can find the port number,which is the port on which the Scheduler agent listens,by viewing the file schagent. conf, located in the Scheduler agent home directory (on theremote host).

The following example shows how to create a remote external job:

The create_job procedure shown here creates a remote external job named deletelogs.The set_attribute procedure sets the credent ial name and the desti nation (host and port number) for the external job.Note that you must enable the new remote external job deletejobs using the enable procedure after sett ing the creden tial and destination attributes.

Scheduler Support for Data Guard

If you’re using a physical standby database,then all Scheduler-related changes made on the primary database are applied to the physical standby database,like any other database change. For primary database and logical standby databases,you can now specify that a job can run only if the database is in the primary database role or only when the database is in the logical standby database role.

Use the set_attribute procedure of the DBMS_SCHEDULER package to set the job attribute database_role to either primary or logical standby.Upon a swtichover or a failover,the Scheduler will automatically run the job specific to the role.In the case of a failover,the job event log will show all success ful DML changes on the primary database until the time of the failover.

The following example shows how to run a job in the two data base roles: the primary data base role and the logical standby database role.We create two copies of a job and assign different values for the database_role attribute (primary and logical standby) to each of the copies.

If you want to run a job in both roles,you can do so by copying the job and then changing the database_role attribute value to the required value (primary or logical standby) after enabling the copied job.

In the following example we create a job named test_primary on the primary database.We then make a copy of this job and change the database_role attribute value to logical standby.When the primary database becomes alogical standby database,the copied job will run as per the schedule.

You can query the DBA_SCHEDULER_JOB_ROLES view to ensure that the job roles are switched,as shown here:

The query reveals that our primary and standby jobs are assigned the correct job roles.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics