Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [DOC] Oracle Scheduler ======

Oracle has a built-in scheduler that helps you automate jobs from within the oracle database database. The dbms_scheduler package contains various functions and procedures that manage the scheduler, although this can also be achieved via the [[http://datadisk.co.uk/html_docs/oracle/oem.htm|OEM]]. The scheduler is like cron, it will schedule jobs at particular time and run them. All scheduler tasks can be views through dba_scheduler_jobs view. You cannot schedule Operating System jobs (either scripts or Binary) via the scheduler this must be done via cron

The scheduler uses a modular approach to managing tasks which enables the reuse of similar jobs.

====== Basic scheduler components ======
The scheduler has 5 basic components

|**Jobs**|a job instructs the scheduler to run a specific program at a specific date/time, a job can run execute PL/SQL code, a native binary executable, java application or a shell scripts.|
|**Schedules**| when and how frequently a job should run (start date, optional end date, repeat interval), you can also run a job when a specific database event occurs. |
|**Programs**|contains the metadata about a scheduler job. A program includes the program name, the program type (PL/SQL, shell script) and the program action which is the actual name of the program or script to run.|
|**Events**|the scheduler uses oracle streams advanced queuing feature to raise events and start database jobs based on the events. An event is a message sent by an application or process when it notices some action or occurrence.|
|**Chains**|you can use the concept of a scheduler chain to link related programs together. Thus running of a specific program could be made contingent on the successful running of certain other programs.|

====== Advanced scheduler components ======


|**Job Classes (groups)**| associate one or more jobs with a resource manager consumer group and also control logging levels, you can use classes to perform assign job priority levels for individual jobs, with higher-priority jobs always starting before a lower-priority job specify common attributes for a set of jobs |
|**Windows**| a window in date/time when a job should launch a interval of time when the job can run |
|**Window Groups**|logical method of grouping windows|

====== Scheduler Architecture ======
The architecture consists of the job table, job coordinator and the job workers (slaves), the job table contains information about jobs (job name, program name and job owner). The job coordinator regularly looks in the job table to find out what jobs to execute, the job coordinator creates and manages the job worker processes which actually execute the job.

  * Job table - houses all the active jobs
  * Job coordinator - insures that jobs are being run on time, spawns and removes slave jobs, write/read job info to/from cache, query job table, pass job information to jobs slaves
  * Job slaves - process that carry out the jobs. Updates job log when job completes.

Processes

  * [[http://datadisk.co.uk/html_docs/oracle/processes.htm|CJQ0]] - (job coordinator) monitors dba_scheduler_jobs table for jobs then launches jobs slaves.
  * [[http://datadisk.co.uk/html_docs/oracle/processes.htm|Jnnn]] - (job slaves) processes that carry out the task

Note: Jnnn is limited by the JOB_QUEUE_PROCESSES, default = 10, if zero scheduler will not run (only requirement to start Scheduler)

The scheduler_admin role contains all scheduler system privileges, with the admin_option clause, it will allow you to

  * create, drop or alter job classes, windows and window groups
  * stop any job
  * start and stop windows prematurely

There are a number of privileges regarding the scheduler

  * Create job
  * Create any job
  * Execute any program
  * Execute any class
  * Manage scheduler
  * Execute on <job, program or class>
  * Alter on <job, program or class>
  * All on <job, program or class>

====== Enabling/Disabling ======
When enabling a job all sub-jobs are enabled, when enabling a window only that window gets enabled not sub-windows, when referencing a window always prefix with a SYS.

|Enabling| dbms_scheduler.enable%%('%%backup_job');\\ dbms_scheduler.enable%%('%%backup_job', backup_program, SYS.window_group_1);      (enable multiple jobs) |
|Disabling|dbms_scheduler.disable%%('%%backup_job');|

====== Attributes ======
These are the only way to alter a schedule. By default objects are set to false when created.

  * dbms_scheduler.set_attribute - <name>,<attribute>,<value>
  * dbms_scheduler.set_attribute_null - <name>,<attribute> (set value to NULL)

|Alter schedule| dbms_scheduler.set_attribute_null(name=> 'test_job', attribute=> 'end_date'); Note: sets end date to NULL |

====== Creating a job ======
A schedule defined within a job object is know as an **inline** schedule, where as an independent schedule object is referred to as a **stored schedule**. Inline schedules cannot be reference by any other objects.

When a job exceeds its END_DATE attribute it will be dropped only if the auto_drop attribute is set to true, otherwise it will be disabled. In either case the state column will be set to completed in the job table.

  * //Job_name// - job name
  * //Job_type// - can be any of the following plsql_block, stored_procedure, executable
  * //Job_action// - pl/sql code, stored procedure or a executable
  * //Number_of_arguements// - the number of arguments that the job accepts range is 0 (default) to 255.
  * //Program_name// - program associated with this job
  * //Start_date// - the start date
  * //Repeat_interval// - states how often the job should be run (see [[#intervals|intervals]])
  * //Schedule_name// - identifies the job
  * //End_date// - the end date (job will be set to completed and the enable flag set to false)
  * //Job_class// - the class the job is assigned to
  * //Comments// - comments
  * //Enabled// - true job is enable, false (default) job is disabled
  * //Auto_drop// - the jobs is dropped once completed (run once only jobs) default is true.

Jobs support an overload procedure based on the number of arguments.

|Create Job| dbms_scheduler.create_job (\\   Job_name=> 'cola_job',\\   Job_type=> 'PLSQL_BLOCK',\\   Job_action=> 'update employees set salary = salary * 1.5;',\\   Start_date=> '10-oct-2007 06:00:00 am',\\   Repeat_interval=> 'FREQ=YEARLY',\\   Comments=> 'Cost of living adjustments'\\ ); |
|Display Jobs| select job_name, enabled, run_count from user_scheduler_jobs; Note: default job is disabled by default (false) |
|Copying|dbms_scheduler.copy_job%%('%%cola_job', 'raise_job');|
|Stopping| dbms_scheduler.stop_job(job_name=> 'cola_job', force=> true); Note: using force stops the job faster |
|Deleting| exec dbms_scheduler.drop_job%%('%%cola_job'); Note: removes the job permanently |
|Displaying| select job_name, enabled, run_count from user_scheduler_jobs; Note: copied job is disabled by default(false) |
|Running| dbms_scheduler.run_job%%('%%cola_job', true);\\ dbms_scheduler.run_job%%('%%cola_job', false); Note:\\ true - runs immediately, synchronously, control does not return to user, no run count update\\ false - runs immediately, asynchronously, control does return to user, updates run count |
|Priority| dbms_scheduler.set_attributes(\\   name => 'test_job',\\   attribute => 'job_priority',\\   value => 1\\ ); Note: priorities are between 1-5, 1 being the highest (default is 3) |

====== Job Classes ======

  * Group larger jobs together, characteristics can be inherited by all jobs within the group
  * Classes can be assigned to a resource consumer group
  * Jobs can prioritize with the class

All jobs must belong to one class default is DEFAULT_JOB_CLASS

  * Job_class_name - unique name within the sys schema
  * Resource_consumer_group - resource group to which job belongs
  * Service - service which the jobs belongs to, used in RAC
  * Logging_level - (see below)
  * Log_history - how long log history is kept default is 30 days
  * Comments - comments

===== Logging levels =====
  * //DBMS_SCHEDULER.LOGGING_OFF// - no logging for any jobs in this class
  * //DBMS_SCHEDULER.LOGGING_RUNS// - info is written to the jobs log (start time, successful, etc)
  * //DBMS_SCHEDULER.LOGGING_FULL// - record management operations on the class, suck as creating new jobs, disable/enabling

|Creating| dbms_scheduler.create_job_class (\\   Job_class_name=> 'low_priority_class',\\   Resource_consumer_group=> 'low_group',\\   Logging_level=> DBMS_SCHEDULER.LOGGING_FULL,\\   Log_history=> 60,\\   Comment=> 'low priority job class'\\ ); |
|Dropping|dbms_scheduler.drop_class%%('%%low_priority_class, high_priority_class');|
|Assigning|dbms_scheduler.set_attribute(\\   name => 'reports_jobs',\\   attribute => 'job_class',\\   value => 'low_priority_class'\\ );|
|Prioritizing|dbms_scheduler.set_attribute(name => 'reports_jobs', attribute => 'job_priority', value => 2);|
|Alter attributes|dbms_scheduler.alter_attributes (\\   name => 'reports_jobs',\\   attribute => 'start_date',\\   value => '15-JAN-08 08:00:00'\\ );|

====== Scheduler programs ======
  * Program_name - the name of the program
  * Program_type - can be any of the following plsql_block, stored_procedure, executable
  * Program_action - pl/sql code, stored procedure or a executable
  * Number_of_arguements - the number of arguments that the job accepts range is 0 (default) to 255.
  * Enabled - true job is enable, false (default) job is disabled
  * Comments - comments

|Creating the program| dbms_scheduler.create_program (\\   Program_name => 'stats_program',\\   Program_type => 'stored_procedure',\\   Program_action => 'dbms_stats.gather_schema_stats',\\   Number_of_arguments => 1,\\   Comments => 'gather stats for a schema'\\ ); |
|Creating the argument| dbms_scheduler.define_program_argument(\\   Program_name => 'stats_program',\\   Argument_position => 1,\\   Argument_type => 'varchar2'\\ ); |
|Dropping the argument| dbms_scheduler.drop_program_argument(\\   Program_name => 'stats_program',\\   Argument_position => 1\\ ); |
|Dropping the program| dbms_scheduler.drop_program(\\   Program_name => 'stats_program',\\   force => true\\ ); |

====== Programs ======

You use the SET_JOB_ARGUMENTS or SET_JOB_ANYDATA_VALUE to set the program arguments.

  * Program_name - programs name
  * Program_type - can be any of the following plsql_block, stored_procedure, executable
  * Program_action - pl/sql code, stored procedure or a executable
  * Number_of_arguments - the number of arguments that the job accepts range is 0 (default) to 255.
  * Enabled - true job is enable, false (default) job is disabled
  * Comments - comments

|Creating programs| dbms_scheduler.create_program(\\   Program_name => 'stats_program',\\   Program_type => 'stored_procedure',\\   Program_action => 'dbms_stats.gather_schema_stats',\\   Number_of_arguments => 1,\\   Comments => 'Gather stats for a schema'\\ ); |
|Define program argument|dbms_scheduler.define_program_argument(\\   program_name => 'stats_program',\\   argument_position => 1,\\   argument_type => 'varchar2'\\ );|
|Drop program argument|dbms_scheduler.drop_program_argument(\\   program_name => 'stats_program',\\   argument_position => 1\\ );|
|Drop program| dbms_scheduler.drop_program(\\   program_name => 'stats_program',\\   force => true\\ ); |
|Enable/Disable|dbms_scheduler.enable_program%%('%%stats_program');\\ dbms_scheduler.disable_program%%('%%stats_program');|

====== Schedules ======

  * Schedule_name - name of the schedule must be unique
  * Start_date - the start_date
  * End_date - the end_date
  * Repeat_interval - states how often the job should be run
  * Comments - comments

|Create| dbms_scheduler.create_schedule(\\   schedule_name => 'nightly_8_schedule',\\   start_date => systimestamp,\\   repeat_interval => 'FREQ=DAILY; BYHOUR=20',\\   comments => 'run nightly at 8:00pm'\\ ); |
|Remove|dbms_scheduler.drop_schedule%%('%%nightly_8_schedule');|

====== Intervals ======
===== Interval elements =====

  * FREQ - required and values are yearly, monthly, weekly, daily, hourly, minutely, secondly
  * INTERVAL - how often it repeats default 1 means every day, 2 would be every other day
  * BYMONTHLY - can use (1-12) or (JAN-DEC) or (1,3,12), etc
  * BYWEEKNO - the week number
  * BYYEARDAY - the date of the year as a number
  * BYMONTHDAY - (1-31), -1 eans last day of the month
  * BYDAY - (MON-SUN)
  * BYHOUR - (0-23)
  * BYMINUTE - (0-59)
  * BYSECOND - (0-59)

===== Interval rules =====

  * Frequency must be first element
  * Elements must be separated by a semi-colon and each one can only be represented once.
  * Element values must be separated by a comma.
  * Elements are case-insensitive and whitespaces are allowed
  * BYWEEKNO, frequency must be set to yearly
  * Can use negative numbers (-1) with the BY elements ( BYMONTH -1 will return last day of month)
  * BYDAY when used with yearly or monthly you can use -1SAT last Saturday of month -2SAT second last Saturday, etc
  * Monday is always the first day of the week.
  * Calendaring does not use time zones/ daylight saving (dst)

===== Interval examples =====

  * Every Monday - freq=weekly; byday=mon
  * Every other Monday - freq=weekly; byday=mon interval=2;
  * Last day of each month - freq=monthly; bymonthday=-1;
  * Every 7 jan - freq=yearly; bymonth=jan; bymonthday=7;
  * 2 nd wed of each month - freq=monthly; byday=2wed;
  * Every hour - freq=hourly
  * Every 4 hours - freq=hourly; interval=4;
  * Hourly on 1 st day of month - freq=hourly; bymonthday=1;
  * 15 th day of every other month - freq=monthly; bymonthday=15; interval=2

|Testing Interval| dbms_scheduler: <calendar_string>,<start_date>,<return_date_after>,<next_run_date> declare\\   start_date timestamp;\\   return_date_after timestamp;\\   next_run_date timestamp;\\ BEGIN\\   start_date := to_timestamp_tz( '10-oct-2007 10:00:00', 'DD-MON-YYYY HH24:MI:SS')\\   return_date_after := start_date;\\   for i in 1..10 loop\\      dbms_scheduler.evaluate_calendar_string( 'freq=monthly; interval=2; bymonthday=15', start_date, null,next_run_date);      dbms_output.put_line%%('%%next_run_date: ' %%||%% next_run_date);\\   end loop;\\ END;\\ / |

====== Managing Chains ======
In order to manage chains you need both the create job and rules engine privileges, their are many other options that allow you to drop a chain, drop rules from a chain, disable a chain, alter a chain and so on (see the Oracle docs for more information)

|Privilege| dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, 'vallep'),\\ dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, 'vallep'),\\ dbms_rule_adm.create_evaluation_context_obj, 'vallep')\\ |
|Create|dbms_scheduler.create_chain(\\   chain_name => 'test_chain',\\   rule_set_name => NULL,\\   evaluation_interval => NULL,\\   comments => NULL\\ );|
|Define chain| dbms_scheduler.define_chain_step%%('%%test_chain', 'step1', 'program1');\\ dbms_scheduler.define_chain_step%%('%%test_chain', 'step2', 'program2');\\ dbms_scheduler.define_chain_step%%('%%test_chain', 'step3', 'program3'); Note: a chain step can point to a program, an event or another chain |
|Define chain rules| dbms_scheduler.define_chain_rule%%('%%test_chain', 'TRUE', 'START step1');\\ dbms_scheduler.define_chain_rule%%('%%test_chain', 'step1 completed', 'start step2, step3');\\ dbms_scheduler.define_chain_rule%%('%%test_chain', 'step2 completed and step3 completed', end); Note:\\ the 1st rule states that step1 should be run, which means the scheduler will start program1\\ the 2nd rule states that step2 and step3 should run if step1 has completed sucessfully\\ the final rule states that when step2 and step3 finish the chain will end |
|Embedding Jobs in Chains| BEGIN\\ dbms_scheduler.create_job(\\   job_name => 'test_chain_job',\\   job_type => 'CHAIN',\\   job_action => 'test_chain',\\   repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',\\   enabled => true\\ );\\ END; OR BEGIN\\ dbms_scheduler.run_chain(\\   chain_name => 'my_chain1',\\   job_name => 'quick_chain_job',\\   start_steps => 'my_step1, my_step2');\\ END; Note: the first option creates a job which runs the chain, you also have the option of using run_chain to run a chain without creating a job first. |

====== Managing Events ======

You can create both jobs and schedules that are based strictly on events and not calendar time. There are two attributes that need highlighting

|**event_condition**|is conditional expression that takes its value from the event source queue table and uses Oracle streams advanced queuing rules. You specify object attributes in this expression and prefix them with tab.user_data. Review the dbms_aqadm package to learn about advanced queuing and related rules.|
|**queue_spec**|determines the queue into which the job-triggering event will be queued.|

There are many more options than below please refer to the Oracle documentation for a full listing.

|Create event based Job| BEGIN\\ dbms_scheduler.create_job(\\   job_name => 'test_job',\\   program_name => 'test_program',\\   start_date => '15-JAN-08 08:00:00',\\   event_condition => 'tab.user_data.event_name = %%''%%FILE_ARRIVAL%%'''%%,\\   queue_spec => 'test_events_q',\\   enabled => true,\\   comments => 'An event based job');\\ END; Note: the job will run when the event indicates that a file has arrived. |
|Create event based schedule| BEGIN\\ dbms_scheduler.create_event_scheule(\\   schedule_name => 'appowner.file_arrival',\\   start_date => systimestamp,\\   event_condition => 'tab.user_data.object_owner = %%''%%APPOWNER%%''%%\\      and tab.user_data.event_name = %%''%%FILE_ARRIVAL'\\      and extract hour from tab.user_data.event_timestamp < 12',\\   queue_spec => 'test_events_q');\\ END; Note: the schedule will start the job when the event indicates that a file has arrived before noon |

====== Windows ======

  * Window_name - name of window in the SYS schema
  * Resource_plan - the resource plan used by the window
  * Start_date - the start date
  * Duration - how long the window stays open
  * Schedule_name - schedule name associated with window
  * Repeat_interval - how often the window repeats
  * End_date - the end_date
  * Window_priority - only relevant when 2 windows are open, values are LOW (default) or HIGH

|Creating a window using a schedule (so schedule will open window)| dbms_scheduler.create_window(\\   window_name => 'work_hours_window',\\   start_date => '14-JAN-08 08:00:00',\\   duration => interval '10' hour,\\   resource_plan => 'day_plan',\\   schedule_name => 'work_hours_schedule',\\   window_priority => 'high',\\   comment => 'Work Hours Window'\\ ); |
|Opening a window manually| dbms_scheduler.open_window(\\   window_name => 'work_hours_window',\\   duration => interval '20' minute,\\   force => true\\ ); |
| Closing window manually |dbms_scheduler.close_window( window_name=> 'work_hours_window' );|
|Disable window|dbms_scheduler.disable_window( name => 'work_hours_window');|
|Displaying window logs| select log_id, trunc(log_date) log_date, window_name, operation from dba_scheduler_window_log;\\ select log_id, trunk(log_date) window_name, actual_duration from dba_scheduler_window_details; |

====== Purging logs ======

|Purge Logs| dbms_schedule.purge_log(log_history => 14, which_log => 'JOB_LOG'); |
|Set scheduler log parameter|dbms_scheduler.set_scheduler_attribute( 'log_history', '60');\\ dbms_scheduler.set_scheduler_attribute( which_log=> [window_log %%|%% job_log], '60');|

====== Display information/Querys ======
  * Stopping the scheduler:
<code>exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE') ; </code>
  * Starting the scheduler:
<code>exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE') ;</code>
  * Checking the status:
<code>select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;</code>

  * Info about a finished job:
<code>select job_name, status, error# from dba_scheduler_job_run_details where job_name = 'FAIL_JOB';</code>
  * Run count per job:
<code>select job_name, state, run_count from dba_scheduler_jobs;</code>
  * Next events:
<code>select window_name, next_start_date from dba_scheduler_windows;</code>
  * Query the log:
<code>select log_id, trunc(log_date) log_date, owner, job_name, operation from dba_scheduler_job_log order by log_id;</code>
  * Longer info about a job:
<code>
col LOG_ID FORMAT 9999;
COL OWNER FORMAT A10;
COL LOG_DATE FORMAT A40 ;
COL JOB_NAME FORMAT A30;
COL JOB_SUBNAME FORMAT A10 WORD WRAPPED ;
COL JOB_CLASS FORMAT A25 WORD WRAPPED ;
COL OPERATION FORMAT A10 ;
COL STATUS FORMAT A10 ;
select LOG_ID, OWNER, LOG_DATE, JOB_NAME, JOB_CLASS, OPERATION, STATUS, ADDITIONAL_INFO
	from dba_scheduler_job_log 
	where LOG_ID=JOB_ID ;
</code> 
* Even more info about the job:
<code>
col LOG_ID FORMAT 9999;
COL OWNER FORMAT A10;
COL LOG_DATE FORMAT A40 ;
COL JOB_NAME FORMAT A30;
COL JOB_SUBNAME FORMAT A10 WORD WRAPPED ;
COL JOB_CLASS FORMAT A25 WORD WRAPPED ;
COL OPERATION FORMAT A10 ;
COL STATUS FORMAT A10 ;
select LOG_ID, OWNER, LOG_DATE, JOB_NAME,  STATUS, ADDITIONAL_INFO
	from DBA_SCHEDULER_JOB_RUN_DETAILS
	where LOG_ID=JOB_ID ;
</code>



^ **Useful Views** ^ Description ^
^**%%*_%%scheduler_schedules**|all defined schedules|
^**%%*_%%scheduler_programs**|all defined programs|
^**%%*_%%scheduler_program_arguments**|all registered program arguments and default values if exist|
^**%%*_%%scheduler_jobs**|all defined jobs both enabled and disabled and if they are running/executing|
^**%%*_%%scheduler_global_attribute**|current values of all scheduler attributes|
^**%%*_%%scheduler_job_classes**|all defined job classes|
^**%%*_%%scheduler_windows**|all defined windows|
^**%%*_%%scheduler_job_run_details**|details on all completed (successful or failed) jobs|
^**%%*_%%scheduler_window_groups**|all window groups|
^**%%*_%%scheduler_wingroup_members**|all members of all groups|
^**%%*_%%scheduler_running_jobs**|the state info on all jobs that are currently being run/executed.|
^**%%*_%%scheduler_job_run_details**|check status and duration of execution for all jobs|
^**%%*_%%scheduler_job_log**|enables you to audit job-management activities|

====== Default Jobs ======

  * PURGE_LOG - deletes entries from the scheduler job if more than 30 days
  * GATHER_STATS_JOBS - gathers optimiser statistics has two windows weeknight_window and weekend_window.

====== Links ======
  * [[http://datadisk.co.uk/html_docs/oracle/scheduler.htm|Original Documentation]]
  * [[http://www.oracle-base.com/articles/misc/sql-developer-31-scheduler-support.php|Using DBMS_SCHEDULER from sqldeveloper]]