; www-oracle-base-com
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

www-oracle-base-com

VIEWS: 222 PAGES: 10

  • pg 1
									                                                                                          Ads by Google    Oracle DBA     Oracle ADF   Job Oracle   Oracle 8I   Oracle Cards


        Home        Articles     Scripts     Forums      Blog     Certification  Misc     Search     About      Printer Friendly
  Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Miscellaneous | Oracle RAC | Oracle Apps | Linux

 Home » Articles » 11g » Here                                                                                                                                       Search

                                                                                                                 Ads by Google

Scheduler Enhancements in Oracle Database 11g Release 1
Oracle 10g Release 1 introduced the new Oracle scheduler and Oracle 10g Release 2 enhanced it. Now Oracle
11g adds more functionality to the Oracle scheduler.
                                                                                                                        Database Monitoring
    l   Remote External Jobs
            ¡ Database Configuration
            ¡ Oracle Scheduler Agent Installation
                                                                                                                        MonitorPerformance, Status User Activity,
            ¡ Credentials
                                                                                                                                  Table Space, SGA
            ¡ Creating Remote External Jobs
            ¡ Returning stdout and stderr                                                                                        www.manageengine.com
            ¡ Disabling Remote External Job Functionality
    l   Detached Jobs
    l   Lightweight Jobs
    l   Scheduler Support for Data Guard
    l   Oracle Enterprise Manager

Remote External Jobs

Oracle 10g introduced the concept of external jobs. Oracle 11g takes this one step further by allowing the database to schedule external jobs which run on a remote server.
The remote server doesn't have to have an Oracle client or database installation, but it must have an Oracle Scheduler Agent installation. This agent is responsible for
executing the jobs and communicating with the database server that initiated the job.

For a user to create local or remote external jobs, they must be granted the CREATE JOB and CREATE EXTERNAL JOB privileges.

        CONN / AS SYSDBA
        GRANT CREATE JOB TO test;
        GRANT CREATE EXTERNAL JOB To test;

Make sure the scheduler's default timezone is set correctly.

        BEGIN
          DBMS_SCHEDULER.set_scheduler_attribute(
             attribute => 'default_timezone',
             value     => 'Europe/London');
        END;
        /

Database Configuration

Before we can create a remote external job we need to do some configuration on the database server. First we must check XML DB is installed and has the HTTP port set.
If the following command returns an "object does not exist" error, XML DB is not installed on the database and must be installed before you proceed.

        SQL> CONN / AS SYSDBA
        Connected.
        SQL> DESC RESOURCE_VIEW

If XML DB is already installed, check the HTTP port has been set using the GETHTTPPORT function in the DBMS_XDB package.

        CONN / AS SYSDBA
        SELECT DBMS_XDB.gethttpport FROM dual;

        GETHTTPPORT
        -----------
               8080

        1 row selected.

        SQL>

If it is not set, set it using the SETHTTPPORT procedure.

        CONN / AS SYSDBA
        EXEC DBMS_XDB.sethttpport(8080);

Run the "prvtrsch.plb" script, located in the "$ORACLE_HOME/rdbms/admin" directory.

        CONN / AS SYSDBA
        @?/rdbms/admin/prvtrsch.plb

Finally, set a password for the scheduler agent registration using the DBMS_SCHEDULER package.

        EXEC DBMS_SCHEDULER.set_agent_registration_pass('agent_passwd');

Oracle Scheduler Agent Installation
Once the server configuration is complete, we need to install the Oracle Scheduler Agent on the machine we wish to run remote jobs against. The agent software is on the
Transparent Gateways disk available with the database software downloads from Oracle Technology Network. After running the installer, proceed with the following
installation.

On the "Welcome" screen, click the "Next" button.




Select the "Oracle Scheduler Agent" option and click the "Next" button.




Enter the appropriate name and path for the Oracle home, then click the "Next" button.




Enter the hostname and port for the agent installation, then click the "Next" button. This is the hostname for the machine running the agent, not the database server. The
port should be an unused port greater than 1023.
Click the "Install" button on the "Summary" screen.




Wait while the installation takes place.




Once the installation is complete, click the "Exit" button and "OK" the subsequent message dialog.
Once the agent installation is complete, register it against any databases wishing to run external jobs on this machine using the schagent utility, passing in the hostname
of the database and the HTTP port of XML DB. The schagent utility is present in the "$ORACLE_HOME/bin" directory of the agent installation.

       C:\>cd C:\app\oracle\product\11.1.0\tg_1\bin

       C:\app\oracle\product\11.1.0\tg_1\bin>schagent -registerdatabase bart.localdomain 8080
       Agent Registration Password ? *************
       *

       Oracle Scheduler Agent Registration
       Agent Registration Successful!

       C:\app\oracle\product\11.1.0\tg_1\bin>

The schagent utility is also used to stop and start the agent on UNIX style platforms.

       $ schagent -stop
       $ schagent -start

On Windows platforms, simply stop and start the <home-name>_OracleSchedulerExecutionAgent service.

The agent configuration information is stored in the "$ORACLE_HOME/schagent.conf" file.

Credentials

Credentials are database objects that hold a username/password pair for authenticating local and remote external jobs. They are created using the CREATE_CREDENTIAL
procedure in the DBMS_SCHEDULER package. The procedure also allows you to specify the Windows domain for remote external jobs executed against a Windows server.
Credentials owned by SYS

       CONN test/test

       BEGIN
         -- Basic credential.
         DBMS_SCHEDULER.create_credential(
           credential_name => 'TIM_HALL_CREDENTIAL',
           username        => 'tim_hall',
           password        => 'password');

         -- Credential including Windows domain.
         DBMS_SCHEDULER.create_credential(
            credential_name => 'TIM_HALL_WIN_CREDENTIAL',
            username        => 'tim_hall',
            password        => 'password',
            windows_domain => 'localdomain');
       END;
       /

Information about credentials is displayed using the [DBA|ALL|USER]_SCHEDULER_CREDENTIALS views.

       COLUMN credential_name FORMAT A25
       COLUMN username FORMAT A20
       COLUMN windows_domain FORMAT A20

       SELECT credential_name,
              username,
              windows_domain
       FROM   user_scheduler_credentials
       ORDER BY credential_name;


       CREDENTIAL_NAME                    USERNAME             WINDOWS_DOMAIN
       -------------------------          -------------------- --------------------
       TIM_HALL_CREDENTIAL                tim_hall
       TIM_HALL_WIN_CREDENTIAL            tim_hall             LOCALDOMAIN

       2 rows selected.

       SQL>
Credentials are dropped using the DROP_CREDENTIAL procedure.

        EXEC DBMS_SCHEDULER.drop_credential('TIM_HALL_CREDENTIAL');
        EXEC DBMS_SCHEDULER.drop_credential('TIM_HALL_WIN_CREDENTIAL');

For backwards compatibility, it is not mandatory to specify credentials for local external jobs. If no credentials are set the default users are:

    l   Jobs in the SYS schema run as the user who installed the Oracle software.
    l   The default user for non-SYS jobs on UNIX platforms is specified by the run-user and run-group attributes in the
        "$ORACLE_HOME/rdbms/admin/externaljob.ora" file.
    l   The default user for non-SYS jobs in Windows platforms is the user running the "OracleJobSchedulerSID" Windows service.

Oracle recommend using credentials for all local and remote external jobs as the default values may be deprecated in future.

The operating system user specified by the credential must have the necessary privileges to perform the required action. On Windows platforms this must include the "Log
on as batch job" security policy, applied using the "Local Security Policies" dialog.

Creating Remote External Jobs

Remote external jobs are similar to local external jobs. Both require a JOB_TYPE, or PROGRAM_TYPE for program definitions, set to 'EXECUTABLE'. Commands and batch
files that require parameters must set the parameters using the SET_JOB_ARGUMENT_VALUE procedure. The following job performs a directory listing of the "/tmp"
directory. Notice how the directory name is specified as a parameter. In addition to setting the CREDENTIAL_NAME attribute, the job also includes the DESTINATION
attribute, signifying this is a remote external job. This attribute is set to the "hostname:port" of the scheduler agent. If the DESTINATION attribute is not set, or set to
"localhost", the job runs as a local external job.

        BEGIN
          -- UNIX
          DBMS_SCHEDULER.create_job(
            job_name             => 'unix_command_job',
            job_type             => 'EXECUTABLE',
            number_of_arguments => 1,
            job_action           => '/bin/ls',
            auto_drop            => FALSE,
            enabled              => FALSE);

           DBMS_SCHEDULER.set_job_argument_value('unix_command_job',1,'/tmp');

          DBMS_SCHEDULER.set_attribute('unix_command_job', 'credential_name', 'TIM_HALL_CREDENTIAL');
          DBMS_SCHEDULER.set_attribute('unix_command_job', 'destination', 'marge.localdomain:65001');
          DBMS_SCHEDULER.enable('unix_command_job');
        END;
        /

Windows commands and scripts must be run using the "cmd.exe" executable with the first parameter of "/c". To perform an action similar to the previous example, we
would need to use three parameters.

        BEGIN
          -- Windows
          DBMS_SCHEDULER.create_job(
            job_name             => 'win_command_job',
            job_type             => 'EXECUTABLE',
            number_of_arguments => 3,
            job_action           => 'C:\windows\system32\cmd.exe',
            auto_drop            => FALSE,
            enabled              => FALSE);

           DBMS_SCHEDULER.set_job_argument_value('win_command_job',1,'/c');
           DBMS_SCHEDULER.set_job_argument_value('win_command_job',2,'dir');
           DBMS_SCHEDULER.set_job_argument_value('win_command_job',3,'C:\');

          DBMS_SCHEDULER.set_attribute('win_command_job', 'credential_name', 'TIM_HALL_WIN_CREDENTIAL');
          DBMS_SCHEDULER.set_attribute('win_command_job', 'destination', 'marge.localdomain:65001');
          DBMS_SCHEDULER.enable('win_command_job');
        END;
        /

The documentation suggests this should be all that is necessary to run a remote external job, but this does not seem to be the case. Unlike local external jobs, it appears
remote external jobs run as detached jobs, so Oracle does not know when they are complete. It is up to job itself to tell Oracle when it is complete by calling the
END_DETACHED_JOB_RUN procedure.

        BEGIN
          DBMS_SCHEDULER.end_detached_job_run('UNIX_COMMAND_JOB');
          DBMS_SCHEDULER.end_detached_job_run('WINDOWS_COMMAND_JOB');
        END;
        /

This raises a couple of questions:

    l   One of the proposed benefits of remote external jobs is you don't need an Oracle installation on the server executing the job, just an agent installation. If the script
        needs to call the END_DETACHED_JOB_RUN procedure, this will require an Oracle client installation.
    l   The requirement to manually end the detached job run means the functionality for returning stdout and stderr doesn't work for remote external jobs as described in
        the documention.

Returning stdout and stderr

The DBMS_SCHEDULER package includes a GET_FILE procedure for returning the stdout and stderr created by calls to external jobs. Local external jobs write stdout and
stderr information to files in the "$ORACLE_HOME/scheduler/log" directory on the database server. Remote external jobs write this information to the
"$AGENT_HOME/data/log" directory on the remote server.

When a local external job completes, information about the run is written to the ADDITIONAL_INFO column of the [DBA|ALL|USER]_SCHEDULER_JOB_RUN_DETAILS
view, including a name value pair of the EXTERNAL_LOG_ID. Concatenating "_stdout" or "_stderr" to this external log ID gives you the name of the file to pass into the
GET_FILE procedure as the SOURCE_FILE parameter. To see this in action create a local external job similar to that of the previous examples.

       BEGIN
         DBMS_SCHEDULER.create_job(
           job_name             => 'local_unix_command_job',
           job_type             => 'EXECUTABLE',
           number_of_arguments => 1,
           job_action           => '/bin/ls',
           auto_drop            => FALSE,
           enabled              => FALSE);

         DBMS_SCHEDULER.set_job_argument_value('local_unix_command_job',1,'/tmp');

         DBMS_SCHEDULER.set_attribute('local_unix_command_job', 'credential_name', 'TIM_HALL_CREDENTIAL');
         DBMS_SCHEDULER.enable('local_unix_command_job');
       END;
       /

Next, query the USER_SCHEDULER_JOB_RUN_DETAILS view to retrieve the EXTERNAL_LOG_ID and use this value to return the stdout using the GET_FILE procedure.

       SET SERVEROUTPUT ON
       DECLARE
         l_clob             CLOB;
         l_additional_info VARCHAR2(50);
         l_external_log_id VARCHAR2(50);
       BEGIN
         SELECT additional_info, external_log_id
         INTO   l_additional_info, l_external_log_id
         FROM   (SELECT log_id,
                        additional_info,
                        REGEXP_SUBSTR(additional_info,'job[_0-9]*') AS external_log_id
                 FROM   user_scheduler_job_run_details
                 WHERE job_name = 'LOCAL_UNIX_COMMAND_JOB'
                 ORDER BY log_id DESC)
         WHERE ROWNUM = 1;

         DBMS_OUTPUT.put_line('ADDITIONAL_INFO: ' || l_additional_info);
         DBMS_OUTPUT.put_line('EXTERNAL_LOG_ID: ' || l_external_log_id);

         DBMS_LOB.createtemporary(l_clob, FALSE);

         DBMS_SCHEDULER.get_file(
           source_file     => l_external_log_id ||'_stdout',
           credential_name => 'TIM_HALL_CREDENTIAL',
           file_contents   => l_clob,
           source_host     => NULL);

         DBMS_OUTPUT.put_line('stdout:');
         DBMS_OUTPUT.put_line(l_clob);
       END;
       /
       ADDITIONAL_INFO: EXTERNAL_LOG_ID="job_88372_27729"
       EXTERNAL_LOG_ID: job_88372_27729
       stdout:
       gconfd-root
       hsperfdata_oracle
       keyring-9TWYY7
       keyring-WnjRiP
       mapping-oracle
       mapping-root
       orbit-root
       sealert.log
       ssh-KWaTN22006
       virtual-root.tr3Sbw
       vmware-config0


       PL/SQL procedure successfully completed.

       SQL>

The documentation claims this functionality will also work with remote external jobs in the same way, but there is a problem here as these jobs appear to run as detached
jobs, so it is up to the script to notify Oracle when it is complete using the END_DETACHED_JOB_RUN procedure. This procedure optionally accepts an
ADDITIONAL_INFO parameter, so it is up to the script to provide the necessary EXTERNAL_LOG_ID to support the GET_FILE procedure functionality. To see this we will
repeat the previous example as an external job by setting the DESTINATION attribute.

       BEGIN
         DBMS_SCHEDULER.create_job(
           job_name             => 'remote_unix_command_job',
           job_type             => 'EXECUTABLE',
           number_of_arguments => 1,
           job_action           => '/bin/ls',
           auto_drop            => FALSE,
              enabled                      => FALSE);

          DBMS_SCHEDULER.set_job_argument_value('remote_unix_command_job',1,'/tmp');

         DBMS_SCHEDULER.set_attribute('remote_unix_command_job', 'credential_name', 'TIM_HALL_CREDENTIAL');
         DBMS_SCHEDULER.set_attribute('remote_unix_command_job', 'destination', 'marge.localdomain:65001');
         DBMS_SCHEDULER.enable('remote_unix_command_job');
       END;
       /

If we check the remote server, the stdout file is present.

       # pwd
       /u01/app/oracle/product/11.1.0/gt_1/data/log
       # ls
       job_88373_8_stdout job_ids
       #

We can identify the EXTERNAL_LOG_ID is "job_88373_8" by looking at the stdout file name, or by checking the contents of the "job_ids" file.

       # cat job_ids
       job_88373_8 "DB11G.WORLD" "oel5-11g.localdomain" "TEST" "REMOTE_UNIX_COMMAND_JOB" - "tim_hall" "/bin/ls"
       #

Next, signal the end of the job run by passing the external job ID information to the END_DETACHED_JOB_RUN procedure.

       BEGIN
         DBMS_SCHEDULER.end_detached_job_run(
            job_name        => 'remote_unix_command_job',
            error_number    => 0,
            additional_info => 'EXTERNAL_JOB_ID="job_88373_8"');
       END;
       /

Retrieve the contents of the stdout file in the same way as before, but this time pass the agent location information in the SOURCE_HOST parameter of the GET_FILE
procedure.

       SET SERVEROUTPUT ON
       DECLARE
         l_clob             CLOB;
         l_additional_info VARCHAR2(50);
         l_external_log_id VARCHAR2(50);
       BEGIN
         SELECT additional_info, external_log_id
         INTO   l_additional_info, l_external_log_id
         FROM   (SELECT log_id,
                        additional_info,
                        REGEXP_SUBSTR(additional_info,'job[_0-9]*') AS external_log_id
                 FROM   user_scheduler_job_run_details
                 WHERE job_name = 'REMOTE_UNIX_COMMAND_JOB'
                 ORDER BY log_id DESC)
         WHERE ROWNUM = 1;

          DBMS_OUTPUT.put_line('ADDITIONAL_INFO: ' || l_additional_info);
          DBMS_OUTPUT.put_line('EXTERNAL_LOG_ID: ' || l_external_log_id);

          DBMS_LOB.createtemporary(l_clob, FALSE);

          DBMS_SCHEDULER.get_file(
            source_file     => l_external_log_id ||'_stdout',
            credential_name => 'TIM_HALL_CREDENTIAL',
            file_contents   => l_clob,
            source_host     => 'marge.localdomain:65001');

         DBMS_OUTPUT.put_line('stdout:');
         DBMS_OUTPUT.put_line(l_clob);
       END;
       /
       ADDITIONAL_INFO: EXTERNAL_JOB_ID="job_88373_8
       EXTERNAL_LOG_ID: job_88373_8
       stdout:
       command
       gen_cfg2html.txt
       hsperfdata_oracle
       hsperfdata_root
       hsperfdata_tim_hall
       orbit-oracle
       orbit-root


       PL/SQL procedure successfully completed.

       SQL>

Disabling Remote External Job Functionality

To prevent a database from executing remote external jobs, simply drop the REMOTE_SCHEDULER_AGENT user.
       SQL> DROP USER REMOTE_SCHEDULER_AGENT CASCADE;


Detached Jobs

Detached jobs allow you to run jobs in a separate processes, independent of the scheduler. If it is an external job, it is also independent of the database state, meaning the
job continues to run after the database is shutdown. Once a detached job is initiated, the scheduler marks the job as running then ceases to track its progress. It is up to
the detached job to signal its completion using the END_DETACHED_JOB_RUN procedure. The following example creates an external job to restart the database, proving the
job runs independently of the database state.

First create a script owned by the oracle user called "restart_db_script" with the following contents.

       #!/bin/bash

       export    ORACLE_BASE=/u01/app/oracle
       export    ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
       export    ORACLE_SID=DB11G
       export    LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

       $ORACLE_HOME/bin/sqlplus / as sysdba <<EOF

       SHUTDOWN IMMEDIATE;
       STARTUP;

       EXEC DBMS_SCHEDULER.end_detached_job_run('restart_db_job', 0, null);

       EOF

       exit 0

Notice how the last action of the SQL script is the call to the END_DETACHED_JOB_RUN procedure to signal that the job is complete.

Make sure the script is executable.

       $ chmod u+x restart_db

Next, connect to SQL*Plus as the SYS user and create a program object to run the script. Make sure the DETACHED attribute is set to TRUE.

       CONN / AS SYSDBA

       BEGIN
         DBMS_SCHEDULER.create_program(
           program_name   => 'restart_db_program',
           program_type   => 'executable',
           program_action => '/u01/app/oracle/dba/restart_db_script',
           enabled        => TRUE);

         DBMS_SCHEDULER.set_attribute('restart_db_program', 'detached', TRUE);
       END;
       /

Next, create a job that runs immediately using the program.

       BEGIN
         DBMS_SCHEDULER.create_job(
            job_name       => 'restart_db_job',
            program_name   => 'restart_db_program',
            enabled        => TRUE);
       END;
       /

       DISCONNECT

You can watch the background processes shutting down and restarting using the "ps -ef | grep ora" command. Once the database is back you can query the
DBA_SCHEDULER_JOB_RUN_DETAILS view to check the status of the job.

       CONN / AS SYSDBA
       SELECT status,
              run_duration
       FROM   dba_scheduler_job_run_details
       WHERE job_name = 'RESTART_DB_JOB';

       STATUS     RUN_DURATION
       ---------- --------------------
       SUCCEEDED +000 00:01:12

       1 row selected.

       SQL>


Lightweight Jobs

Regular jobs, like programs and schedules, are schema objects and as such take time to create and drop. Under normal circumstances this overhead is hardly noticeable,
but it can become apparent when you need to create large numbers of short-lived jobs. For example, you may wish to use jobs to decouple processes, or to parallelize
them. In these circumstances you may see improved performance using lightweight jobs.

Lightweight jobs have a JOB_STYLE attribute of 'LIGHTWEIGHT', the default being 'REGULAR', and must be based on a program object with an object type of
'PLSQL_BLOCK' or 'STORED_PROCEDURE'. Lightweight jobs are not schema objects and therefore require less meta data, so they have quicker create and drop times.
Since they are not schema objects, you cannot grant privileges on them, so lightweight jobs inherit their privileges from their referenced program objects.

The following example compares the creation time for regular and lightweight jobs. First, create a program suitable for a lightweight job. In this case, the PL/SQL block does
no work.

        BEGIN
          DBMS_SCHEDULER.create_program(
             program_name   => 'lightweight_program',
             program_type   => 'PLSQL_BLOCK',
             program_action => 'BEGIN NULL; END;',
             enabled        => TRUE);
        END;
        /

The following script displays the time taken to create 100 lightweight jobs and 100 regular jobs.

        SET SERVEROUTPUT ON
        DECLARE
          l_start    NUMBER;
        BEGIN

          l_start := DBMS_UTILITY.get_time;

          FOR i IN 1 .. 100 LOOP
            DBMS_SCHEDULER.create_job (
              job_name        => 'lightweight_job_' || i,
              program_name    => 'lightweight_program',
              job_style       => 'LIGHTWEIGHT',
              enabled         => TRUE);
          END LOOP;

          DBMS_OUTPUT.put_line('LIGHTWEIGHT (hsecs): ' || (DBMS_UTILITY.get_time - l_start));

          l_start := DBMS_UTILITY.get_time;

          FOR i IN 1 .. 100 LOOP
            DBMS_SCHEDULER.create_job (
              job_name        => 'regular_job_' || i,
              program_name    => 'lightweight_program',
              job_style       => 'REGULAR',
              enabled         => TRUE);
          END LOOP;

          DBMS_OUTPUT.put_line('REGULAR (hsecs): ' || (DBMS_UTILITY.get_time - l_start));
        END;
        /
        LIGHTWEIGHT (hsecs): 174
        REGULAR (hsecs): 412

        PL/SQL procedure successfully completed.

        SQL>

The output clearly shows there is less overhead associated with the creation of lightweight jobs.

Scheduler Support for Data Guard

The Oracle 11g scheduler now supports Data Guard environments, allowing jobs to be run dependent on their role in the environment.

In physical standby configurations, all scheduler changes are applied to the physical standby. In logical standby configurations jobs can be created to run specifically on the
primary or logical standby database. This is done by setting the DATABASE_ROLE job attribute to 'PRIMARY' or 'LOGICAL STANDBY'. During a switchover or failover, the
new role of the database is recognized and the appropriate jobs will be run based on the new role.

        BEGIN
          DBMS_SCHEDULER.create_job (
              job_name      => 'primary_job',
              program_name  => 'primary_job_prog',
              schedule_name => 'primary_job_schedule',
              enable        => TRUE);

          DBMS_SCHEDULER.set_attribute('primary_job','database_role','PRIMARY');
        END;
        /


Oracle Enterprise Manager (OEM)

The current version of Oracle Enterprise Manager (OEM) has no built in support for the new scheduler features.

For more information see:

    l   External Jobs
    l   Detached Jobs
    l   Lightweight Jobs
    l   Scheduler Support for Oracle Data Guard
    l   DBMS_SCHEDULER
    l   Scheduler in Oracle Database 10g
    l   Scheduler Enhancements in Oracle 10g Database Release 2
    l   Scheduler Enhancements in Oracle Database 11g Release 2

Hope this helps. Regards Tim...

Back to the Top.


                                                                   1 comments, read/add them...




                                                Home | Articles | Scripts | Forums | Blog | Certification | Misc | About



                                                                           Copyright & Disclaimer     




                                                                                                                           converted by Web2PDFConvert.com

								
To top