Automating Tasks with the Scheduler

Automating Tasks with the Scheduler

Objectives • After completing this lesson, you should be able to: • Simplify management tasks by using the Scheduler • Create a job, program, schedule, and window • Reuse Scheduler components for similar tasks • View information about job executions and job instances

Scheduling Needs Compute table andindex statistics twice a day Replicate table datavia materializedview refreshes Run daily job to backup database Create month-end report on the last day of each month Check for queued events every 10 minutes Start the batch load at 4:00 a.m. Generate daily report on invalid server access attempts

Scheduler Concepts ResourceConsumer Group Resource plan Window group Job class Window Schedule Program Job Arguments Arguments

Privileges for Scheduler Components System and object privileges SCHEDULER_ADMIN role CREATE JOB CREATE ANY JOB EXECUTE ANY PROGRAM EXECUTE ANY CLASS MANAGE SCHEDULER CREATE [ANY] JOB EXECUTE ANY PROGRAM EXECUTE ANY CLASS MANAGE SCHEDULER EXECUTE ON <program or class> ALTER ON <job, program, or schedule> ALL ON <job, program, schedule, or class>

Creating a Scheduler Job • There are many ways to create a job: • Specifying the components in-line as part of the CREATE_JOB procedure • Specifying the task directly and using a saved schedule • Calling a saved program and a saved schedule • Calling a saved program and specifying the schedule directly

Creating a Scheduler Job: Example • Create a job that calls a backup script every night at 23: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=>TRUNC(SYSDATE)+23/24, repeat_interval=>'TRUNC(SYSDATE+1)+23/24', /* next night at 11:00 PM */ comments => 'Nightly incremental backups'); END; /

Setting the Repeat Interval for a Job • Using a calendaring expression: • Using a datetime expression: repeat_interval=> 'FREQ=HOURLY; INTERVAL=4' repeat_interval=> 'FREQ=DAILY' repeat_interval=> 'FREQ=MINUTELY; INTERVAL=15' repeat_interval=> 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=15' repeat_interval=> 'SYSDATE + 36/24' repeat_interval=> 'SYSDATE + 1' repeat_interval=> 'SYSDATE + 15/(24*60)'

Calendaring Expressions BYMONTH BYWEEKNO BYYEARDAY BYMONTHDAY BYDAY BYHOUR BYMINUTE BYSECOND YEARLY MONTHLY WEEKLY DAILY HOURLY MINUTELY SECONDLY INTERVAL(1-999) Interval Specifiers Frequency

Using Scheduler Programs BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'CALC_STATS2', program_action => 'HR.UPDATE_SCHEMA_STATS', program_type => 'STORED_PROCEDURE', enabled => TRUE); DBMS_SCHEDULER.CREATE_JOB( job_name=>'HR.GET_STATS2', program_name=>'HR.CALC_STATS2', start_date=>'20-DEC-04 07.00.00 AM Greenwich', repeat_interval=> 'FREQ=HOURLY;INTERVAL=2', end_date => '20-DEC-05 07.00.00 AM Greenwich', comments => 'Explicitly scheduled job'); END; /

Creating a Program Using EM

Specifying Schedules for a Job One Timejob • Example schedules: • Wednesday, December 26, 2002, at 2:00 p.m. • Every fourth Thursday • Every Monday at 8:00 a.m., starting on January 29, 2004, and ending on March 31, 2004 Start time Schedule Repeating job End time or limit

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=4', comments => 'Every 4 hours'); DBMS_SCHEDULER.CREATE_JOB( job_name => 'HR.GET_STATS', program_name => 'HR.CALC_STATS2', schedule_name => 'STATS_SCHEDULE'); END; /

Using EM to Create Schedules

Advanced Scheduler Concepts ResourceConsumer Group Resource plan Window group Job class Window Schedule Program Job Arguments Arguments

Creating a Job Class

Creating a Job Class Resource Consumer Group Service Logging Level Log History Job Class EXECUTE DBMS_SCHEDULER.CREATE_JOB_CLASS( - job_class_name => 'ADMIN_JOBS', - resource_consumer_group => 'DAYTIME_JOBS', - logging_level => DBMS_SCHEDULER.LOGGING_OFF);

Job Logging • By default, all job runs are logged. • The amount of information logged can be controlled at the job class level. EXECUTE DBMS_SCHEDULER.CREATE_JOB_CLASS( - job_class_name => 'ADMIN_JOBS', - resource_consumer_group => 'DAYTIME_JOBS', - logging_level => DBMS_SCHEDULER.LOGGING_RUNS, - log_history => 30);

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-04 06.00.00 PM EST', repeat_interval => 'FREQ=DAILY; BYHOUR=18', duration => '0 12:00:00', end_date => '31-DEC-04 06.00.00 AM EST', comments => 'Every day at 6:00 PM'); END; /

ADMIN_JOBS Job4 Job5 Prioritizing Jobs Within a Window Daytime Window APPL_JOBS Job1 Job2 OTHER Job3

Enabling and Disabling Scheduler Components • Enable the CALC_STATS2 program: • Disable the GET_STATS job: EXEC DBMS_SCHEDULER.ENABLE('HR.CALC_STATS2'); EXEC DBMS_SCHEDULER.DISABLE('HR.GET_STATS');

Managing Jobs • Run a job: • Stop a job: • Drop a job, even if it is currently running: DBMS_SCHEDULER.RUN_JOB('HR.JOB1'); DBMS_SCHEDULER.STOP_JOB('HR.JOB2'); DBMS_SCHEDULER.DROP_JOB('HR.JOB1,HR.JOB2, SYS.JOBCLASS1');

Managing Programs • Enabling a program: • Disabling a program: • Dropping a program: EXECUTE DBMS_SCHEDULER.ENABLE( - 'HR.PROG1,HR.PROG2'); EXECUTE DBMS_SCHEDULER.DISABLE( - 'HR.PROG1,HR.PROG2'); EXECUTE DBMS_SCHEDULER.DROP_PROGRAM( - program_name => 'HR.PROG1', - force => FALSE );

Managing Programs with EM

Managing Schedules • CREATE_SCHEDULE • SET_ATTRIBUTE (to alter a schedule) • DROP_SCHEDULE EXEC DBMS_SCHEDULER.SET_ATTRIBUTE( - name => 'HR.STATS_SCHEDULE', - attribute => 'START_DATE', - value => '01-JAN-2004 9:00:00 US/Pacific'); EXEC DBMS_SCHEDULER.DROP_SCHEDULE( - schedule_name => 'HR.STATS_SCHEDULE');

Managing Windows DBMS_SCHEDULER.OPEN_WINDOW( window_name =>'DEC_NIGHTS', duration => '1 0:00:00'); DBMS_SCHEDULER.CLOSE_WINDOW('DEC_NIGHTS'); DBMS_SCHEDULER.DISABLE( name => 'SYS.DEC_NIGHTS', force=>TRUE); DBMS_SCHEDULER.DROP_WINDOW( window_name =>'DEC_NIGHTS, DEC_DAYS', force => TRUE);

Window Priority • An order of precedence applies for when windows overlap: • The window with the highest priority opens first • If windows have same priority, then the window that is active remains open • If at the end of a window there are multiple windows defined, the window that has the highest percentage of time remaining, opens • An open window that is dropped is automatically closed W1 W2 W3 6 a.m 8 a.m 10 a.m 12 p.m. 2 p.m

Managing Attributes of Scheduler Components • Change an attribute for a job: • Remove a program comment by setting it to NULL: BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'HR.GET_STATS', attribute => 'MAX_FAILURES', value => 3); END; / EXEC DBMS_SCHEDULER.SET_ATTRIBUTE_NULL( - 'HR.CALC_STATS2','COMMENTS');

Managing Attributes of Scheduler Components • Retrieve attribute values for the GET_STATS job created by the HR user: SELECT max_failures MAX_FAIL, job_priority, schedule_limit SCHED_LIMIT, logging_level FROM user_scheduler_jobs WHERE job_name = 'GET_STATS' AND job_creator = 'HR'; MAX_FAIL JOB_PRIOR SCHED_LIMIT LOGGING_LEVEL -------- --------- ------------ -------------- 3 3 LOW

Managing Attributes of the Scheduler • You can set three attribute values at the global level, affecting all Scheduler components: • default_timezone • max_job_slave_processes • log_history EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE( - attribute => 'log_history', - value => '14');

Viewing Job Execution Details • The DBA_SCHEDULER_JOB_RUN_DETAILS view has a row for each job instance. • Each row contains information about the job execution for that instance. 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

Viewing Job Logs • The DBA_SCHEDULER_JOB_LOG view has a row for each job operation or modification. SELECT job_name, operation, owner FROM ALL_SCHEDULER_JOB_LOG; JOB_NAME OPERATION OWNER ----------- --------- ------ GET_STATS2 ALTER HR TESTJOB RUN HR TESTJOB RETRY_RUN HR TESTJOB FAILED HR TESTJOB DROP HR COMPUTE_STATS CREATE HR

Purging Job Logs • You can purge the job logs: • Automatically, through the PURGE_LOG job using default values • Using the PURGE_LOG job, but modifying the conditions that determine when entries are purged • On demand, by calling the DBMS_SCHEDULER.PURGE_LOG procedure EXECUTE DBMS_SCHEDULER.PURGE_LOG( - log_history => 1, - job_name => 'TESTJOB');

Summary • In this lesson, you should have learned how to: • Simplify management tasks by using the Scheduler • Create a job, program, schedule, and window • Reuse Scheduler components for similar tasks • View information about job executions and job instances

Practice 17 Overview: Automating Tasks with the Scheduler • This practice covers the following topics: • Creating a job that runs a program outside of 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