msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Event-based Scheduler Jobs and Oracle Streams Advanced Queuing: A Powerful Combination

Problem:

An external or internal application wants to send inbound data to your Oracle database, like for example a purchase order or a receivable invoice. You want to consume this data in real time.

Assumptions:

The version of your Oracle database is 11gR2 or above. The only “tool” you have is the Oracle database, i.e. you do not have any middleware (like SOA suite) installed. The external or internal application can connect to your Oracle database.

Solution:

Use Oracle Streams Advanced Queuing (AQ) for messaging. Use an event-based scheduler job for real time data consumption. Both are standard features of the Oracle database.

Implementation Steps:

The following is a fully functional example of the solution, starting from creating a new database user and ending with consuming the message in the database.

Continue reading…

Comments Off | Filed in Oracle | Tags: ,


Handy Oracle Scheduler Troubleshooting Tip

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.

I’ve found the following query handy for investigating an ORA-12012 in an alert log. ORA-12012 is a dbms_scheduler execution error and it provides a job number without a name, where the Scheduler views give job names without numbers. So, to relate the job number to a name:

SELECT d.job_name, d.job_action
  FROM dba_scheduler_jobs d JOIN sys.scheduler$_job s
    ON d.job_action = s.program_action
 WHERE s.obj# = &job_number;

Maybe this will be useful for others as well.

Comments Off | Filed in Oracle, Tips | Tags:


Five Features You Ought to Know About the Oracle Scheduler

I really enjoyed Kscope last week. It was educational, entertaining and social. Many thanks to ODTUG and the Oracle ACE program and to everyone who made it happen.

Check out Markus‘s awesome Flickr album to get a taste of how good the conference was, especially All Aboard The Queen Mary event. :)

My presentation went very well, judging from the postive feedback I got from the audience. Here is the paper that I submitted to Kscope:

Continue reading…

Comments Off | Filed in Oracle | Tags: ,


Shell Script Output to Oracle Database Via External Table

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.

Sometimes code written in PL/SQL needs to test values in the operating system. However, Oracle tools such as sys_context do not support every possibility: you could not (for example) use sys_context or utl_inaddr to discover if the Internet Time Protocol was enabled.

Fortunately, there is a way to get the output of a shell script into an Oracle external table.

Here is an example with a trivial shell script. It is based on a Sun environment (and DB 10gR2) but could easily be adapted to Linux or another Unix. The steps are:

  1. Set permissions on some files in Oracle bin.
  2. Write a shell script in the right style.
  3. Create some Oracle objects, mainly an external table and a Scheduler job.

Here goes.

Continue reading…

Comments Off | Filed in Oracle | Tags: ,


Oracle Scheduler Event-Based Jobs are Useful, Here is a Quick Example

Here is an example of how you would use event-based scheduling to create an Oracle Scheduler job that has to run after the successful completion of another job.

Continue reading…

4 Comments | Filed in Oracle | Tags: ,


Did You Know About File Watchers?

Starting with Oracle Database 11g Release 2, an event-based Scheduler job can be started based on the arrival of a file in a directory on the server. The file arrival event is raised by a new Scheduler object called a file watcher.

As per the docs: A file watcher defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.

Ittichai has an excellent example of using the file watcher. The following is similar example, but uses BLOBs instead of CLOBs. In this example, we want a Scheduler job to be triggered as soon as an image file is deposited into a directory on the database server. We want the job to be triggered once for each file. The job reads the image file and saves it into a BLOB column in a table. We will then use Oracle SQL Developer to retrieve the image from the table and view it in the built-in viewer.

Continue reading…

9 Comments | Filed in Oracle | Tags: ,


25+ Unique Ways to Schedule a Job Using the Oracle Scheduler

One of the powerful features of the Oracle Scheduler is its calendaring syntax. This syntax is used whenever you want to define a recurring job or create a named schedule. You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or of the named schedule that the job references. The result of evaluating the repeat_interval is a set of timestamps. The Scheduler runs the job at each timestamp.

Here are some repeat_interval examples that demonstrate the versatility and flexibility of the calendaring syntax:

Run at 10:00 pm daily from Monday to Friday:
FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;

Run every hour:
FREQ=HOURLY;INTERVAL=1;

Run every 5 minutes:
FREQ=MINUTELY;INTERVAL=5;

Run every Friday at 9:00 am (All three examples are equivalent):
FREQ=DAILY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=WEEKLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;
FREQ=YEARLY; BYDAY=FRI; BYHOUR=9; BYMINUTE=0; BYSECOND=0;

Run every other Friday:
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

Run on Monday of weeks 5, 10 and 15 every year:
FREQ=YEARLY; BYWEEKNO=5,10,15; BYDAY=MON

Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

Run on the next to last day of every month:
FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th (Both examples are equivalent):
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every January 10, 11, 12, 13 and 14 (Both examples are equivalent):
FREQ=YEARLY; BYDATE=0110,0111,0112,0113,0114
FREQ=YEARLY; BYDATE=0110+SPAN:5D;

Run every 10 days:
FREQ=DAILY; INTERVAL=10;

Run daily at 4:15, 5:15, and 6:15PM:
FREQ=DAILY; BYHOUR=16,17,18; BYMINUTE=15; BYSECOND=0;

Run on the 15th day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

Run on the 29th day of every month:
FREQ=MONTHLY; BYMONTHDAY=29;

Run on the second Wednesday of each month:
FREQ=MONTHLY; BYDAY=2WED;

Run on the last Friday of the year:
FREQ=YEARLY; BYDAY=-1FRI;

Run every 50 hours:
FREQ=HOURLY; INTERVAL=50;

Run on the last day of every other month:
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

Run hourly for the first three days of every month:
FREQ=HOURLY; BYMONTHDAY=1,2,3;

Run on the 60th, 120th and 180th days of the year:
FREQ=YEARLY; BYYEARDAY=60,120,180;

Run on the last workday of every month (assuming that workdays are Monday through Friday):
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Here are some more complex examples which reference the following named schedules:

BEGIN
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'COMPANY_HOLIDAYS',
      repeat_interval => 'FREQ=YEARLY; BYDATE=0704,0905,1124,1125,1225;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'JUL4',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=JUL; BYMONTHDAY=4;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'MEM',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=MAY; BYMONTHDAY=30;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'LAB',
      repeat_interval => 'FREQ=YEARLY; BYMONTH=SEP; BYMONTHDAY=5;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'LAST_SAT',
      repeat_interval => 'FREQ=MONTHLY; BYDAY=SAT; BYSETPOS=-1;');
   sys.DBMS_SCHEDULER.create_schedule (
      schedule_name => 'END_QTR',
      repeat_interval => 'FREQ=YEARLY; BYDATE=0331,0630,0930,1231;');
   sys.DBMS_SCHEDULER.create_schedule (
    schedule_name => 'FISCAL_YEAR',
    repeat_interval => 'FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4;');
END;
/

Run on the last workday of every month, excluding company holidays:
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=COMPANY_HOLIDAYS; BYSETPOS=-1

Run at noon every Friday and on company holidays:
FREQ=YEARLY; BYDAY=FRI;BYHOUR=12; INCLUDE=COMPANY_HOLIDAYS

Run on these three holidays: July 4th, Memorial Day, and Labor Day:
JUL4,MEM,LAB

Run on the last day of the month that is either a Saturday or last day of a quarter:
FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=LAST_SAT,END_QTR

Run on the last Wednesday of every quarter of the fiscal year:
FREQ=FISCAL_YEAR;BYDAY=-1WED

Run on the last work day of the 2nd and 4th quarters of the fiscal year (assuming that workdays are Monday through Friday):
FREQ=FISCAL_YEAR;BYDAY=MON,TUE,WED,THU,FRI;BYPERIOD=2,4;BYSETPOS=-1

When constructing your calendar expression it is important to note the following about start dates and repeat intervals (via the documentation):

The Scheduler retrieves the date and time from the start date of the job or named schedule and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, then the month and day that the job runs on are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, then the day of the month that the job runs on is retrieved from the start date. BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified. For example:

start_date: 4/15/05 9:20:00 repeat_interval: freq=yearly

is expanded internally to:

FREQ=YEARLY;BYMONTH=4;BYMONTHDAY=15;BYHOUR=9;BYMINUTE=20;BYSECOND=0

To test the definition of the calendar string without having to actually schedule the job use the very handy procedure DBMS_SCHEDULER.evaluate_calendar_string. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job will be. Consult my previous post or this forum post for examples of how to use evaluate_calendar_string.

By the way, for a creative use of the evaluate_calendar_string procedure check David Aldridge’s post: Generating Lists of Dates in Oracle the DBMS_Scheduler Way.

Happy scheduling!

Comments Off | Filed in Oracle | Tags: ,


The Oracle Scheduler, Daylight Saving Time and Timezones

The Oracle Scheduler is a very powerful and flexible scheduling engine that is part of the Oracle database. In this article, we will observe how the Scheduler behaves when daylight saving time (DST) begins and ends in time zones that use DST.

The datetime value is sufficient for Oracle to determine whether daylight saving time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight saving goes into or comes out of effect. For example, in the US/Pacific region, when daylight saving goes into effect (as of this writing the next one is on Sunday March 13, 2011 at 2:00 a.m.), the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight saving goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 a.m. and 2 a.m. is repeated (source).

The question is, what happens to your repeating Oracle Scheduler jobs during these boundary periods?

Let’s say you have a job you want to schedule every 6 hours starting at 5 a.m. You want the job to run every day at 5 a.m., 11 a.m., 5 p.m. and 11 p.m. (or 5, 11, 17 and 23 in HH24). The calendar expression for this schedule (the value of the repeat_interval in DBMS_SCHEDULER.create_job for example) could be defined in two ways:

  1. ‘FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0′
  2. ‘FREQ=HOURLY; INTERVAL=6′

What I am going to show you next are important differences between the two calendar expressions, specifically relating to the boundary cases and timezone definition.

For the test below, I am going to use a very handy procedure: DBMS_SCHEDULER.evaluate_calendar_string. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job.

We’ll start by setting the value of nls_timestamp_tz_format. This parameter defines the default timestamp with time zone format to use with the TO_TIMESTAMP_TZ functions. I made sure that both TZR and TZD are part of the format.

TZR represents the time zone region name. Examples are ‘Australia/North’, ‘UTC’, and ‘Singapore’. TZD represents an abbreviated form of the time zone region name with daylight saving information. Examples are ‘PST’ for US/Pacific standard time and ‘PDT’ for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view (source).

    eddie@DB112> SET SERVEROUTPUT ON;
    eddie@DB112> ALTER SESSION
      2  SET nls_timestamp_tz_format = 'MM-DD-YYYY HH24:MI:SS tzr tzd';

    Session altered.

Next I declare an inline procedure that is basically a wrapper around evaluate_calendar_string , just to save typing and repetition as I will be calling it multiple times:

eddie@DB112> DECLARE
  2     PROCEDURE list_next_run_dates (
  3        start_date_i        IN TIMESTAMP WITH TIME ZONE,
  4        calendar_string_i   IN VARCHAR2,
  5        date_count_i        IN INTEGER DEFAULT 8 )
  6     IS
  7        l_return_date_after   TIMESTAMP WITH TIME ZONE;
  8        l_next_run_date       TIMESTAMP WITH TIME ZONE;
  9     BEGIN
 10        l_return_date_after := start_date_i;
 11
 12        FOR i IN 1 .. date_count_i
 13        LOOP
 14           DBMS_SCHEDULER.evaluate_calendar_string (
 15              calendar_string     => calendar_string_i,
 16              start_date          => start_date_i,
 17              return_date_after   => l_return_date_after,
 18              next_run_date       => l_next_run_date);
 19
 20           DBMS_OUTPUT.put_line (l_next_run_date);
 21
 22           l_return_date_after := l_next_run_date;
 23        END LOOP;
 24     END list_next_run_dates;

Then I call list_next_run_dates multiple times, each time with a different start date and calendar string. In all cases, the month, day and year portion of the start date is the same, March 11, 2011. I made sure that the run dates span the period when daylight saving goes into effect (March 13, 2011 at 2:00 a.m.):

 25  BEGIN
 26   DBMS_OUTPUT.put_line ('1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY');
 27   list_next_run_dates (
 28      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 29      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 30
 31   DBMS_OUTPUT.put_line ('2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY');
 32   list_next_run_dates (
 33      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 34      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 35
 36   DBMS_OUTPUT.put_line ('3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY');
 37   list_next_run_dates (
 38      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 39      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 40
 41   DBMS_OUTPUT.put_line ('4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY');
 42   list_next_run_dates (
 43      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 44      'FREQ=HOURLY; INTERVAL=6');
 45
 46   DBMS_OUTPUT.put_line('5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY');
 47   list_next_run_dates (
 48      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 49      'FREQ=HOURLY; INTERVAL=6');
 50
 51   DBMS_OUTPUT.put_line ('6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY');
 52   list_next_run_dates (
 53      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 54      'FREQ=HOURLY; INTERVAL=6');
 55  END;
 56  /

Let’s examine the results:

Specifying FREQ=DAILY does not account for DST change. We lose one hour between these two runs as there are only five hours between “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 05:00:00 US/Pacific PDT”. Notice that PST becomes PDT when DST starts:

1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

The second test is the same as the first except that I add 10 minutes to the start date. The result stays the same. The start date provides the month, day and year after which the job can run. It also provides the timezone. However, the hour, minute and seconds are read from the calendar string, “BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0″:

2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

Again, there is no change in the result if we use a timezone offset (-08:00) instead of a timezone region (US/Pacific). Because of the fact that we are using FREQ=DAILY, the DST is not taken into account. Moreover, a timezone offset is not DST aware.

3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Now, let’s switch to FREQ=HOURLY. Here, we are using a timezone region which means that we care about DST. The calendar expression in this case is an interval of 6 hours. This interval is preserved when DST changes. There are 6 hours between these two dates: “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 06:00:00 US/Pacific PDT”. However, notice that the scheduled hours have changed from 5,11,17,23 to 6,12,18,00:

4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 06:00:00 US/Pacific PDT
03-13-2011 12:00:00 US/Pacific PDT
03-13-2011 18:00:00 US/Pacific PDT
03-14-2011 00:00:00 US/Pacific PDT

The calendar expression ‘FREQ=HOURLY; INTERVAL=6′ does not contain hours, minutes and seconds definitions. In this case, where the hours, minutes and seconds are not explicitly defined in the calendar string, their values are defaulted from the start date. That’s why we see 10 minutes added to all the scheduled runs:

5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:10:00 US/Pacific PST
03-12-2011 11:10:00 US/Pacific PST
03-12-2011 17:10:00 US/Pacific PST
03-12-2011 23:10:00 US/Pacific PST
03-13-2011 06:10:00 US/Pacific PDT
03-13-2011 12:10:00 US/Pacific PDT
03-13-2011 18:10:00 US/Pacific PDT
03-14-2011 00:10:00 US/Pacific PDT

If we use a timezone offset instead of a timezone region, DST is not honored:

6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Lessons Learned:

  • When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT that display’s the timezone information, more specifically the “TZR TZD” mask so that you can quickly see in what format the data is stored.
  • Timezone offsets are by definition not DST aware. So any job using an offset as timezone will run at a wrong time after a DST change.
  • To have a job that automatically adjusts for DST you need to make sure it’s defined with a timezone region name that actually uses DST rules.
  • If you want your daily repeating job to run at specific hours every day and you do not want these hours to change when DST changes, make sure you have a calendar expression that uses FREQ=DAILY and define the hours in “BYHOUR=…”.
  • If you want your daily repeating job to run at a specific hourly interval and you do not mind the hour of day change when DST changes, make sure you have a calendar expression that uses FREQ=HOURLY and define the interval in “INTERVAL=…”.

Sources and resources:

6 Comments | Filed in Oracle, Tips | Tags: