Pythian Group

Have your scheduler jobs changed run times since the daylight savings time change?

The issue hit me this week. On one client the scheduler jobs had changed run time. The strange thing is the scheduled time was 00:05

   SQL> select repeat_interval, next_run_date from dba_scheduler_jobs
where job_name = 'myjob';

   REPEAT_INTERVAL                     NEXTY_RUN_DATE
   ----------------------------------  ---------------------------------
   FREQ=DAILY; BYHOUR=00; BYMINUTE=05  10-11-10 00:05:00.900000000 -04:00

But it has started running at 23:05:

   SQL> select log_id, log_date from  DBA_SCHEDULER_JOB_LOG where
job_name = 'DWMAINLOAD' order by log_date desc ;

   LOG_ID LOG_DATE
   -----  ----------------------------------
   13363       10-11-08 23:53:18.346555000 -05:00
   13289       10-11-07 23:47:49.976860000 -05:00
   13164       10-11-07 00:52:40.331728000 -04:00
   13042       10-11-06 00:54:01.231236000 -04:00
   12968       10-11-05 00:52:45.072446000 -04:00
   12889       10-11-04 00:52:17.909098000 -04:00
   12809       10-11-03 00:52:18.978296000 -04:00
   12729       10-11-02 00:52:48.055781000 -04:00

As this hit just after the DST, at first I thought that I might be
hitting a DST bug. I checked and both the OS time and oracle sysdate
were correct and consistent with each other :

   SQL>  select to_char(sysdate, 'yyyy/mm/dd hh24:mi') from dual;

   TO_CHAR(SYSDATE,
   ----------------
   2010/11/09 08:09

   SQL> !date
   Tue Nov  9 08:10:02 EST 2010

A co-worker suggested I check the timezone settings for the database.
It was close to the problem and got me part-way to the solution, but
there was no timezone setting at the database level.

     SQL> show parameter tz

     NAME                                 TYPE        VALUE
     ------------------------------------ -----------
------------------------------
     nls_time_tz_format                   string
     nls_timestamp_tz_format              string

     SQL> show parameter time

     NAME                                 TYPE        VALUE
     ------------------------------------ -----------
------------------------------
     control_file_record_keep_time        integer     28
     cursor_space_for_time                boolean     FALSE
     ddl_lock_timeout                     integer     0
     distributed_lock_timeout             integer     60
     log_checkpoint_timeout               integer     1800
     nls_time_format                      string
     nls_time_tz_format                   string
     nls_timestamp_format                 string
     nls_timestamp_tz_format              string
     resumable_timeout                    integer     0
     timed_os_statistics                  integer     0
     timed_statistics                     boolean     TRUE

I checked and found the DBMS_SCHEDULER had NLS settings for each job.

      NLS_LANGUAGE='AMERICAN'
      NLS_TERRITORY='AMERICA'
      NLS_CURRENCY='$'
      NLS_ISO_CURRENCY='AMERICA'
      NLS_NUMERIC_CHARACTERS='.,'
      NLS_CALENDAR='GREGORIAN'
      NLS_DATE_FORMAT='DD-MON-RR'
      NLS_DATE_LANGUAGE='AMERICAN'
      NLS_SORT='BINARY'
      NLS_TIME_FORMAT='HH.MI.SSXFF AM'
      NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM'<bold><font color=red>
      NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR'
      NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR'</bold></font>
      NLS_DUAL_CURRENCY='$'
      NLS_COMP='BINARY'
      NLS_LENGTH_SEMANTICS='BYTE'
      NLS_NCHAR_CONV_EXCP='FALSE'

However, these are just formatting settings. I was getting warmer but still not there. Finally, I found it! If the global attribute default_timezone is not set, the scheduler assumes timezone based on the start_date of the job. That was exactly my problem. So now I want to set the timezone using:

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

But where do I get the value for the timezone? I found it is in the V$TIMEZONE_NAMES view. BE CAREFUL ! I found that some values existed in multiple timezones, so I made sure I found one that ONLY existed in the timezone I wanted.

   SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES where tzname = 'America/Chicago';
         America/Chicago       LMT
         America/Chicago       CST
         America/Chicago       CWT
         America/Chicago       CDT
         America/Chicago       EST

Today was a mini-Pythian day at RMOUG. Both Alex and myself gave two presentations each. My first session was on using LDAP with Oracle Applications. It was at 8:30am and there were a few brave souls that fought the snow and icy road conditions to make it. The group was small and I was pleasantly surprised that almost everyone in the room indicated that they were currently using LDAP, although most were NOT using Oracle LDAP offering ( Oracle Internet Directory).

George Trujillo was presenting on MySQL for the Oracle DBA in the same room, so I stayed and learned a few things. Although I must have been really tired as I missed his comments on the Blackhole engine for database replication. George also shared some of his experiences from customer sites visits. All in all, I am glad I stayed.

Riyaj Shamsudeen had an interesting session on “De-bunking the Myths about redo-undo-rollback”. I enjoyed this presentation style of making an assertion and challenging us as to whether it was true or not. Naturally he had demonstrations to prove each case. The presentation was informative, yet easy to follow and understand.

I managed to sneak in for part of George Trujillo’s session on “Demystifying Middleware Technologies”. I am sorry that I had to miss the first part of it, as I had to run thru my demos for my presentation that followed.

I was lucky enough ( a little tongue in check there!!) to present the last session of the day on Oracle 11g DataPump. It was very fulfilling to present as most of the attendees had export / import experience and had not started using Data Pump yet.

Although I will not be partaking in the after-conference skiing, tonight I plan on joining several other Oracle Geeks for a dinner in the mountains. And when I say Oracle Geek, I mean that in the kindest, loving way. Hope to get lots of hugs ! ;-)

I had a great time today at RMOUG. Still very tired from traveling yesterday, I missed the opening session. I managed to make it to Bradley Brown’s APEX session, only a little late. I was quite impressed with some of the things that can be done in APEX. Brad demonstrated a sample application with master detail records in different regions of the screens with colour and with context changing automatically based on which records were selected. I like the way that Brad used SQL statements and returned different data based on values set in the page data.

Daniel Liu did a good job of presenting 11g New Features for DBAs. It will be interesting to see how partitioning on virtual columns work. The new 11g data compression also sounds very interesting. It now works on a block level, rather than table level and dynamically re-compresses data as necessary. I wonder what new wait events are related to this new workload ?

I attended the “Building Dynamic Google Gadgets in Java” session. I was a little concerned that it would be heavy into Java and I might get lost. I was pleasantly surprised at how little Java there was and how simple the gadgets were to use and develop. Hopefully I will get a chance to play with this when I get back.

For the rest of the afternoon, I attended Graham Wood’s presentation on “The DB Time Performance Method”, followed by Michael Rosenblum’s presentation on “Autonomous Transactions”. I had used them a lot in a previous life for error logging and processing child records did not think I was going to get too much out of the presentations. I was very pleasantly surprised and thoroughly enjoyed some of the tricks used and interesting ways that Michael was using autonomous transactions.

I ended the day of presentations with Mogens Norgaard’s Tuning session. OK…perhaps it is not fair to say it was Mogens’s presentation as he did rely heavily on audience participation, especially from Daniel Fink, Jonathan Lewis and Graham Wood. I am not sure he even let them know that he would be calling on them to co-present. But he did bribe them handsomely with some very rare Danish Beer.

Time has flown so incredibly fast these past few months. It is hard to believe that I leave here tomorrow to fly to Denver for RMOUG. I have been very fortunate to get selected give two presentations (one on Data Pump and the other on Oracle Application Server and LDAP).

I am really looking forward to catching up with “my mates” in the Oracle community. I will send regular updates when I get to RMOUG. But, to be honest I am a little scared, no more like petrified, to face the cold weather again. This year in Ottawa has had quite a bit of snow and I am not that fond of shoveling it.

Even though the weather has been much wetter and colder in Sydney this year than in previous years, it has not stopped me from trying sailing, catching ferry rides, climbing the Sydney Harbour Bridge, going dancing, or hitting the beaches. I love all the greenery. There is nothing quite like walking down the street and getting hit with a whiff of Lemon Grass or fragrant Jasmine. It will be MONTHS before my garden is in bloom. I am glad I took lots of pictures while I have been here so I can remember this magical place while I am waiting for summer in Ottawa.

As you may or may not know, I have been spending time in the Pythian Office in Sydney, Australia. Before I came, family and friends did some research and found that Australia was suffering from several years of drought and desperately needed rain. So I packed nothing but sunny summer clothing. I am not sure if my first mistake was relying on the word of others or not having a contingency plan. This has been the wettest summer in years. It rained practically every day for nearly three weeks in a row when I first arrived. If it wasn’t raining, it was cloudy and threatening to rain.

In a way, I should have known better. As a DBA, I have learned to confirm information and have contingency plans. For instance, when doing a database upgrade, you ensure that you have a backup and can revert back if any issues come up. Of course, in a work environment, you normally make plans, get them checked by co-workers and do one or more “dry runs” whenever possible.

When the sun finally came out and stayed out, I found myself in paradise. The weather is warm and when the wind blows it feels like a tropical breeze. When I meet and speak with Australians, I jokingly refer to “the island” — Australia being the only country that is also a continent and an island. At times I sit by the water and just watch the boats floating in their moorings and feel sad about having to leave here in the near future. Although Sydney is a large city, it has a relaxed pace, a friendly atmosphere, and I have completely fallen in love with her. I will be sad to leave.

There is just so much to see and do in Australia. With limited time, I spent most weekends doing the local Sydney attractions. I did not manage to make it to everything this city has to offer, let alone make side-trips. I can tell you my time here has been magical.

I have had so many wonderful experiences that just make me feel like I am on top of the world! For the first time in my life I tried, and fell in love with, sailing. Tonight is my last night participating in the Twilight Races.

Read the rest of this entry . . .

While perusing the Oracle 11g Data Pump documents recently, I noticed a new parameter that was introduced in Oracle 10g, but I had missed it there. The parameter is TABLE_EXISTS_ACTION, and it applies only to the Data Pump Import.

Basically, this feature allows you to decide how to handle importing data if a table already exists. The options are SKIP (defaut), APPEND, TRUNCATE, and REPLACE. Do these parameters look familiar? Possibly reminiscent of the SQL*Loader parameters (INTO TABLE x INSERT/REPLACE/TRUNCATE/APPEND)? They are very similar and they work the same way, with the exception of SKIP.

Here’s an explanation of each option.

• SKIP: The default value for TABLE_EXISTS_ACTION. If the table exists, then SKIP will cause the table (and any related data) to not be loaded. The table will be skipped.
• TRUNCATE: This will truncate the data in the table and load the data. If the table already exists, no metadata will be applied.
• APPEND: This will append the data to the end of the table. Again, if the table already exists, no metadata will be applied.
• REPLACE: This will drop the existing object, recreate the object with the information from the dumpfile, and then load the data.

Let’s create a simple test case to demonstrate. Read the rest of this entry . . .

Rain, rain, go away! It has been raining pretty well every day since I arrived in Sydney. It is putting a real damper (pun intended) on my sightseeing.

Going stir-crazy from sitting in with all the rain, I was looking for something to do. Paul (from the Pythian Group Australia office) suggested either shopping in Chatswood or going to Crows Nest. It cleared up a little and I managed to walk to Crows Nest from where I am staying. I wasn’t sure I would recognize it, but when I saw the Crows Nest Hotel right on the corner of Pacific Highway, I knew I was in the right place.

Crows Nest is a lovely area of town with lots and lots of shops and restaurants. They have a natural food store that serves organic coffee. Have I mentioned how big coffee is in Australia? You have to go to McDonald’s to get regular drip coffee like I am used to from Tim Horton’s. I have heard that Sydney has a very large Greek and Italian population and I suspect that contributes to the excellent coffee shops here. I am still learning how to order among terms such as “long”, “flat”, “short”, “cappuccino” (okay, that one I have seen before), and the list goes on.

A few other things I have noticed since arriving here:

Read the rest of this entry . . .

I have arrived in Sydney!!

I have been in Sydney for a few days now. I was staying at Robert Menzies College (right across the street from MacQuarrie University or “Uni”). During the summer, the college offers short-term accommodation for $60 / day. Not too bad, as long as you are willing to stay in student residences and share a bathroom with 15 other women. Actually, considering that the price includes your meals at the cafeteria, it is a pretty decent price ( especially if you manage to be there at mealtimes).

I was staying there because I needed to be at MacQuarrie yesterday morning for my LSAT exam. No matter how much you prepare for the LSAT, it is a tough exam. I had the worst time with the logic games. I can always solve them, but they take me 20 minutes and you have about 8.5 minutes per game. Hopefully I got some marks for all the questions I had to guess at !!

It was very interesting doing the LSAT. You show Photo ID at the door and they take a thumbprint image on the admittance form. We had to be there by 8:30 and by the time we completed all the administration it was 9:15. We had one ten minute break and were not done until at least 1:15pm. Grueling!!! We had about one minute between ending one session and then starting on the next section, six sections in total. I used the Kaplan material for preparing and they recommend that you read each section, critically, and then when you move to the next section, just forget it. To me, the LSAT is like that, just do it, then move on.

So after the LSAT, I spent the afternoon looking for accommodations. Very interesting experience getting around the city. It is always fun to travel by bus and train to areas of town, you know nothing about, to find a place to live

I was quite lucky. The first person. Gillian, met me at the Milsons Point train station and drove me around Kirribilli. It is a lovely area just south of North Sydney and reminds me of the Glebe or Westboro. Lots of lovely little shops and restaurants along the street. But it was also small. It felt like a little town within a city. Gillian had a friend who also had a place available, so after seeing Gillian’s place, Eoin picked me up. Eoin also gave me a little tour. Hearing that I was new to the area and had not yet been across the Sydney Bridge, Eoin took me across so I could experience the bridge. He was great, after seeing his place, Eoin, drove me to the third place and offered to swing by later to give me a ride back to MacQuarrie.

So my day consisted of writing a tough exam and then being escorted around town by two complete strangers. It reminds me of the famous quote (from the movie “A Streetcar Named Desire”, which I have never seen, but remember the quote) “. . . I have always depended on the kindness of strangers.” Well that certainly was my case yesterday!!

The first session I attended this morning was “Creating a data grid using Oracle Coherence. It was “hands-on”, so we were supposed to bring our laptops to install software and he was going to do demos. NO ONE in the room brought their laptop. So Tim Middleton did an excellent job of ad-hoc presentation and answering all of our questions for nearly two hours. I can’t say what the hands-on would have been like but I certainly got a lot out of the session the way it was.

During the lunch break, I went to the ASG Booth in the exhibitor hall and had my tarot cards done. I am a number 6 for numerology. Naturally she told me all kinds of wonderful things, including that my children are doing fine and I will have no major problems with them and they are both very intelligent kids (she has that right). The rest I will wait and see if it happens, before I discuss it.

In the afternoon I went to Ramesh Naidu’s presentation on Cluster-wide monitoring for RAC. It covered all the components in a RAC Cluster that need monitoring and the importance of doing so, including Cluster services, ASM, network and database. There was also a good overview and explanation of inter-relationship of the basic RAC components.

Alex had been sharing his internet with me, but he flies out tomorrow morning, so I am not sure when I will have connectivity again. Might not be a few days…but will do my best to post ASAP.

Yesterday morning my co-worker, Alex Gorbachev, arrived from Sydney. We went together to sight-see around the city. First stop was across the street to the IGA to buy a bus pass…sold out ..across the street again ( kitty corner from hotel) to the “Southern Cross Station”. It is a really big train station and used to be called “Spencer Station” because it is on Spencer St.

We stopped off and got a map of Melbourne and made our way to Bourke Street for lunch. It is an area of town like Sparks Street in Ottawa that is closed off from regular traffic, open only to pedestrians and “the tram”. After lunch we caught the tram ( only one stop ) just for the sake of catching it. We walked through Confederaton square, which was interesting only for the unusual buildings and then took the alley to the canal and finally to the park.

Next we headed to the large cathedral. It was HUGE and glorious and open to the public. It was breath-taking in it’s beauty and architecture. While there, both Alex and myself, made a contribution to the candle fund and lit a candle. After having “no joy” ( no luck) finding the other churches in the area ( both were small and closed), we got on the tram system and headed to the botanical gardens.

The gardens were absolutely gorgeous. It was smaller than the map let on and we managed to do the entire park. At first, I thought we would have to skip parts of it…but we managed to see the entire park. On the last side path we did, I somehow lost my footing and tripped and scraped my other knee. So if ‘Australia does not kill me, it is certainly going to get me in really good shape. We left the hotel by noon and did not get back until nearly 6:30. Even with the few stops for lunch and coffee and tram rides, I musta walked for hours and hours !!!

We went to China town for dinner last night, where Alex had real ( and real hot ) chinese food. A combination pot of some sort. He did not look too healthy while eating it. I admire his tenacity for managing to eat it all. It was far too hot and spicy for me.

Today was day one of AUSOUG in Melbourne. The first session I attended was Penny Cookson’s session on Oracle 11g. It is the same one she gave in Perth and I enjoyed it so much that I decided to go again. After I went to Chris Muir session on load testing APEX and JDeveloper. It was very interesting and he demonstrated the tools for capturing and replaying sets of data. The whole process is much simpler than I thought it would be.

In the afternoon, I attended Penny Cookson’s sesson on APEX, followed by Tony Jambu’s session on Histograms. I presented in the 4pm timeslot today, but having my luggage this time, I was much better prepared. I brought the famous “easy button” and used it to make the presentation on LDAP and Oracle Application Server easy. Hopefully by the end of it all, the participants could say “that was easy” !!