Automating Tasks with the Scheduler

Automating Tasks with the Scheduler

Key Comp. & Steps Schedules Job Chains Adv.Concepts Objectives • After completing this lesson, you should be able to: • Simplify management tasks by using the Scheduler • Create a job, program, and schedule • Monitor job execution • Use a time-based or event-based schedule for executing Scheduler jobs • Use job chains to perform a series of related tasks • Use advanced Scheduler concepts to prioritize jobs

Simplifying Management Tasks Performing a seriesof month-end tasks on the last day of each month Replicating table datavia materializedview refreshes Running a dequeueprocedure as soonas a message isenqueued Running a daily job to back up database Computing table and index statistics twice a day Generating an hourly report on invalid server access attempts Starting the batch load as soon as the filearrives on the file system Rebuilding an index whenfinished rebuilding the current index

A Simple Job WHEN WHAT

Key Components and Steps • To simplify management tasks with the Scheduler, perform the following steps: 1. Create a program. 2. Create and use a schedule. 3. Create and submit a job. 4. Monitor a job. Program Schedule Job attributes Job Arguments

1. Creating a Program BEGIN • DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'CALC_STATS2', program_action => 'HR.UPDATE_HR_SCHEMA_STATS', program_type => 'STORED_PROCEDURE', enabled => TRUE); • END; /

2. Creating and Using Schedules BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'stats_schedule', start_date => SYSTIMESTAMP, end_date => SYSTIMESTAMP + 30, repeat_interval => 'FREQ=HOURLY;INTERVAL=1', comments => 'Every hour'); END; /

3. Creating and Running a Job

4. Monitoring a Job SELECT job_name, status, error#, run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS; JOB_NAME STATUS ERROR# RUN_DURATION ---------------- ------ ------ ------------ GATHER_STATS_JOB SUCCESS 0 +000 00:08:20 PART_EXCHANGE_JOB FAILURE 6576 +000 00:00:00

Key Comp. & Steps > Schedules Job Chains Adv.Concepts Using a Time-Based or Event-Based Schedule Schedule Time Event

Creating a Time-Based Job • Example: Create a job that calls a backup script every night at 11:00, starting tonight. BEGIN • DBMS_SCHEDULER.CREATE_JOB( • job_name=>'HR.DO_BACKUP', • job_type => 'EXECUTABLE', • job_action => '/home/usr/dba/rman/nightly_incr.sh', • start_date=> SYSDATE, • repeat_interval=>'FREQ=DAILY;BYHOUR=23', /* next night at 11:00 PM */ • comments => 'Nightly incremental backups'); • END; • /

Creating an Event-Based Schedule • To create an event-based job, you must set: • A queue specification (where your application enqueues messages to start a job) • An event condition (same syntax as an Oracle Streams AQ rule condition) that if TRUE starts the job Oracle Database 10g Scheduler Event ADT (AbstractData Type) Queue Application

Creating Event-Based Scheduleswith Enterprise Manager

Creating an Event-Based Job • Example: Create a job that runs if a batch load data file arrives on the file system before 9:00 a.m. BEGIN • DBMS_SCHEDULER.CREATE_JOB( • job_name=>'ADMIN.PERFORM_DATA_LOAD', • job_type => 'EXECUTABLE', • job_action => '/home/usr/dba/rman/report_failure.sh', • start_date => SYSTIMESTAMP, • event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'' and tab.user_data.event_type = ''FILE_ARRIVAL'' and tab.user_data.event_timestamp < 9 ', • queue_spec => 'HR.LOAD_JOB_EVENT_Q'); • END; event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'' and tab.user_data.event_type = ''FILE_ARRIVAL'' and tab.user_data.event_timestamp < 9 ', queue_spec => 'HR.LOAD_JOB_EVENT_Q');

Event-Based Scheduling • Event types: • User- or application-generated events • Scheduler-generated events • Events raised by Scheduler jobs: • JOB_START • JOB_SCH_LIM_REACHED • JOB_SUCCEEDED • JOB_DISABLED • JOB_FAILED • JOB_CHAIN_STALLED • JOB_BROKEN • JOB_ALL_EVENTS • JOB_COMPLETED • JOB_RUN_COMPLETED • JOB_STOPPED Example of raising an event: DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup', 'raise_events', DBMS_SCHEDULER.JOB_FAILED);

Creating Complex Schedules INCLUDE EXCLUDE INTERSECT

Job chain Job Key Comp. & Steps Schedules > Job Chains Adv.Concepts Creating Job Chains 1. Create a chain object. 2. Define chain steps. 3. Define chain rules. 4. Starting the chain: • Enable the chain. • Create a job that points to the chain.

Example of a Chain Dependency Scheduling BULK_LOAD_CHAIN START Load_data_evt Do_bulk_load Job 1 2 Stop_when_ disk_full_evt Rebuild_indx Schedule 5 3 END Run_reports (HR.GEN_REPORTS) 4

1. Creating a Chain Object Create_job_chain_1.jpg 1 2 3 4 5

2. Defining Chain Steps DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP ( chain_name => 'bulk_load_chain', step_name => 'load_data_evt', event_condition => 'tab.user_data.object_owner = ''HR'' and tab.user_data.object_name = ''DATA.TXT'' and tab.user_data.event_type = ''FILE_ARRIVAL'' ', queue_spec => 'HR.LOAD_JOB_EVENT_Q'); DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'bulk_load_chain', step_name => 'do_bulk_load', program_name => 'hr.load_data_prog); DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'bulk_load_chain', step_name => 'rebuild_indx', program_name => 'hr.rebuild_indexes'); 1 2 3

3. Defining Chain Rules Create_job_chain_2.jpg

4. Starting the Chain BEGIN DBMS_SCHEDULER.ENABLE ('bulk_load_chain'); END; / BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'bulk_load_chain_job', job_type => 'CHAIN', job_action => 'bulk_load_chain', repeat_interval => 'freq=daily;byhour=7; byminute=5;bysecond=0', enabled => TRUE); END; /

Monitoring Job Chains [DBA | ALL | USER]_SCHEDULER_CHAINS [DBA | ALL | USER]_SCHEDULER_CHAIN_RULES [DBA | ALL | USER]_SCHEDULER_CHAIN_STEPS [DBA | ALL | USER]_SCHEDULER_RUNNING_CHAINS

Key Comp. & Steps Schedules Job Chains > Adv.Concepts Advanced Scheduler Concepts Resource consumer group Resource plan Window group Job class Window Job chain Schedule Program Job Arguments Arguments Time Event

Creating a Job Class EXECUTE DBMS_SCHEDULER.CREATE_JOB_CLASS( - job_class_name => 'ADMIN_JOBS', - resource_consumer_group => 'DAYTIME_JOBS', - logging_level => DBMS_SCHEDULER.LOGGING_OFF);

Creating a Window • Create a window for the month of December that uses the END_OF_YEAR resource plan and is active every night from 6:00 p.m. to 6:00 a.m. Eastern Standard Time (EST). BEGIN DBMS_SCHEDULER.CREATE_WINDOW( window_name => 'DEC_NIGHTS', resource_plan => 'END_OF_YEAR', start_date => '01-DEC-03 06.00.00 PM EST', repeat_interval => 'FREQ=DAILY; BYHOUR=18', duration => '0 12:00:00', end_date => '31-DEC-03 06.00.00 AM EST', comments => 'Every day at 6:00 PM'); END; /

Job Priority Job1 1 Job2 2 Job3 3 Job4 5 Job5 2 Prioritizing Jobs Within a Window Daytime window APPL_JOBS Job1 Job2 OTHER Job3 ADMIN_JOBS Job4 Job5

Summary • In this lesson, you should have learned how to: • Simplify management tasks by using the Scheduler • Create a job, program, and schedule • Monitor job execution • Use a time-based or event-based schedule for executing Scheduler jobs • Use job chains to perform a series of related tasks • Use advanced Scheduler concepts to prioritize jobs

Practice Overview:Automating Tasks with the Scheduler • This practice covers the following topics: • Creating a job that runs a program outside the database • Creating a program and a schedule • Creating a job that uses a program and a schedule • Altering the program and schedule for the job and observing the behavior change of the job • Monitoring job runs

Automating Tasks with the Scheduler