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.
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 (126.96.36.199.0) 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:
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:
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.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.