Interesting Daylight Saving Time Problem

I was teaching an Oracle Database Administration class last week and ran into an interesting problem involving the change to Daylight Saving Time  (you did know that it's Daylight Saving, not Daylight Savings, right?)

For this class, we usually install just the base version of Oracle Database 10gR2 (10.2.0.1.0), because we're on a private network behind a firewall and we don't cover how to upgrade and patch Oracle until the advanced class. And then we show Enterprise Manager Database Control.

Except that this week, it wouldn't come up. We got Java errors, and the home page said that the status of the database instance couldn't be determined. When we tried to start the DB Console service (this is on Windows), it said it was already running; but when we tried to shut it down, it said it wasn't running. Very weird.

In doing a web search for the Java messages (Google is my friend), time and again we were advised to check how our time zone was set. That seemed odd; you wouldn't think that would matter. But it does: the EM agent checks to see that its time zone is (a) valid, and (b) configured is the same as the server.

Enter Daylight Saving Time. In 2007, the US and Canada changed the start date of Daylight Savings Time to be in mid-March (March 11 that year) instead of the first Sunday in April as it had been. This required patching Oracle products with a new timezone file, so they could properly detect the change. (The document ID on My Oracle Support is 359145.1).  This year, we changed to DST on Sunday, March 14.

The class was held the week of March 29, and the unpatched 10.2.0.1 software still thought we should be on Standard Time until April 4. So the EM agent wouldn't start. Had we done the class a week later, or three weeks earlier, this wouldn't have been a problem; and in fact we have done this class many times since 2007 and never hit this issue because the timing wasn't JUST right.

So we did an unscheduled lab to download 10.2.0.4 patchset and apply it, and EM came right up.

Stuff like this underscores the importance of patching your databases, even in a training environment!

There's something to learn from a training perspective here too. We often think that if things go wrong, our students will lose faith in us and think we don't know what we're talking about. But in this case, I was able to model the proper response a DBA should take: stay calm, research the issue, test. I've had students comment in the past when things went wrong that seeing how to resolve such issues was an unexpected bonus of the class. This class felt the same, they enjoyed the "detour" and the problem solving.

Jonathan Lewis Seminar in Dallas

I got to hear Oracle performance guru Jonathan Lewis today in an all-day seminar in Dallas. "Writing Optimal SQL" was part 2 of a two-part series here in Dallas, sponsored by Dallas Oracle Users Group (DOUG).

Jonathan started out by reviewing some general principles, such as the importance of design and optimizing for the global situation (many queries) not just a single query. "Making me go fast might make you go slower," he said.

Performance comes from precision in selecting just the blocks and rows you need to select, and from the structure of the data (where it's located).

My favorite part of the seminar was his discussion on coding style for SQL, a topic that is often ignored in performance discussions. His view that "Computers are cheap; humans are expensive" is one that I've been preaching for years in my SQL and PL/SQL classes.  "You have two audiences," I tell my classes, "the compiler, and the poor person who comes after you to maintain your code.  Be kind.  Someday that person will be you." Jonathan's take is that you should write SQL as a close translation of the business purpose of the query, and only fiddle with it if performance isn't acceptable.

He also suggested: "Don't tune the SQL; tune the end users," meaning that if a requirement is unclear or the data creates unexpected results, go back to the person requesting the report and get clarification instead of burning a lot of time over-engineering the solution to fit every possible case.

This talk could easily have been titled, "Unintended Side-Effects of SQL" because Jonathan went through many scenarios in which results differed from what might have been expected, and why it turned out that way.

It was a great session.  Thanks to Speak-Tech  and to DOUG's Mary Elizabeth McNeely, president and programs chair, for bringing Jonathan Lewis to town.

Oracle 11gR2 Podcast by Tom Kyte

Tom Kyte recorded a podcast this month about the new features of Oracle Database 11gR2. The podcast, part of the Oracle Database podcasts series, can be accessed here:

http://www.oracle.com/database/podcasts.html

In it, he describes an exciting new feature: the ability to compile stored procedures, packages, etc. while users are online using the same schema! The feature, called Edition-based Redefinition, refers to the current objects as an "edition".  The developer creates a child edition based upon the current one, then does development against the child edition. When it comes time to switch over, new sessions can begin using the new edition, as existing sessions continue to use the old. An upgrade that used to take an hour of downtime for compilations can appear to users to take no time at all. It still takes an hour to compile, he said, but the users are not locked out of the system while it's happening.

He also talks about the ability to create a table, but defer to allocation of storage until the table is first used. This will make it much faster to install complex software applications that have many optional tables in them. Only the metadata in the data dictionary will be created during the install, and tables never used will therfore not take up space.

It's a short talk, about 10 minutes, but well worth listening to.

Controlling Controlfile Backups

Did you know that you can control the name of your controfile trace backups? I didn't, until Bob Morgan of Stonehill College in Easton, MA pointed out some additional syntax in the ALTER DATABASE command.

I already knew that you could control the filename and location when backing up the controlfile in binary, via:

ALTER DATABASE BACKUP CONTROLFILE TO '<pathame>';

And I knew that you could get a text copy of the commands necessary to rebuild the controlfiles, via:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The resulting text commands are placed in the user's trace file for the session. To find the proper trace file requires knowing the session number; it's in the filename.  (Though I usually cheat, and go to the directory pointed to by the user_dump_dest parameter, sort by modification time, and my session is near the top.)

What Bob pointed out was the variation:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<filename>';

This still writes to the backup_dump_dest directory, but names the tracefile with your choice of filename instead of the default one that includes the session number.  Much easier to find.

Thanks, Bob!

Clips

Here are some clips of my published work, including both technical and non-technical subjects. These articles averaged 1200 words each.

---

Five More Things to Unlearn About Oracle (TechRepublic.com, 4/19/04) Here’s an example using the sample data in the SCOTT schema. An employee record is deleted, and the change committed: DELETE FROM emp WHERE empno = 7934; COMMIT; The row is missing from further SELECT statements, and even a ROLLBACK command cannot bring the row back. However, a Flashback Query can display the contents of the table as it was 10 minutes ago, when the deleted row still existed: SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '10' MINUTE) WHERE empno = 7934; This SELECT statement can be used as the subquery of an INSERT statement to reload the deleted data. Be aware, however, that INSERT will be subject to any constraints on the table, and that any INSERT triggers on the table will be executed. Full article

Oracle Collaboration Suite Database Middleware (TechRepublic.com, 2/13/03) Oracle Corporation is hoping that the third time will be the charm for its latest try at an office communications platform with the Oracle Collaboration Suite (OCS). Two earlier products - Oracle InterOffice and Oracle Office Server - weren’t widely adopted. This time, however, both the underlying Oracle database and the Application Server middleware are more mature, and its main competitor's customers are facing expensive version upgrades. Sara Radicati, president and CEO of The Radicati Group, spoke to press and analysts at the November 2002 OracleWorld Conference in San Francisco. She identified three key pressures that IT departments are feeling right now... Full article

The Benefits from Using Wireless in Database Administration (TechRepublic.com, 1/28/03) It's 2:30 P.M. and you're working on-site for one of your customers. Your pager bleats out an urgent call from a second client across town whose server is down. You can try to help the second client on the phone—a slow, tedious process that may create political issues with the current customer. Or you can leave the one client to drive to the other. Either way, the second client’s server is down, and the clock is ticking. The rapid expansion of wireless Internet access is making this scenario easier to handle. Now you can take out your Palm or Pocket PC and quickly correct the issue regardless of your location. You've become the wireless database administrator. Full article

Microsoft's IT Lifecycle Frameworks (TechRepublic.com, 10/24/01) IT consultants at large firms use a company-defined project methodology to ensure that each project covers all the right steps. The methodology specifies actual forms and reports to be used at various stages of the project and specific rules for how the project will be conducted. But what if you’re an independent or work for a smaller firm? You could imitate the larger firms and develop your own project methodology at great expense. But why reinvent the wheel? For the past six years, Microsoft has been developing and refining its own set of project management tools. Originally developed within Microsoft Consulting Services (MCS), the IT Lifecycle Frameworks are available for free on Microsoft’s Web site. Full article

No pain, no gain: Seeing opportunity as a free agent (TechRepublic.com, 9/19/01) A salesperson I know starts each conversation about a client by asking: “Where is their 'pain'?” He knows that unless they are motivated to change, he is unlikely to make a sale. The same holds true for you as a free-agent consultant; unless your client sees a need to change something, they won’t hire you. Therefore, understanding what motivates people to change is the key to spotting opportunities. An opportunity is a problem you can help with—at a profit. Learning to see opportunity means looking for all three parts of that definition... Full article

Training Tip: When You Train, Train

I think it was The Inner Game of Tennis, by Timothy Gallwey, that first taught me this lesson: when you are preparing for a class, prepare. But when it comes time to stand up in front and start the class, train.

You may not feel as prepared as you would like to be. The conditions may not be as ideal as you would like them to be. But once class starts, you are who you are, and the conditions are what they are. You go for it.  You give your current self to the group, just as you are, and you do your best. You train.

It's hard to stop the inner, critical voice that reacts when you make a mistake. It's hard not to wince or get flustered when your lesson plan just plain doesn't work for a given group. But training is as much a performance as a play (except that many of the lines are not scripted), and the show must go on. Adapt, go where it takes you in the moment.

Of course, you can and should review your performance in the classroom. Every time. But you do that afterwards, when the room is empty or you're at yet another chain restaurant having dinner. There have been weeks when I've devoted my hotel time to staying one day ahead of the students, constantly reviewing the day just past and prepping like mad for the day to come.

Disney is a model for me in this regard. In their theme parks, they have "on stage" and "off stage" areas. When you're on stage, in front of paying customers, you're expected to be in character the whole time, even if you're not on duty and just passing from one place to the next. No gossiping about your friends or school, no talk of religion, politics, or sex. That's for off stage, when you're behind the scenes. Some criticize this, saying that it is artificial and inhumane for workers to be unable to be themselves. But I think it's the one key thing that makes going to a Disney park different than other ones. You're immersed in the story, and they're careful to eliminate anything that distracts from that experience.

When you're training, you're on stage too. Play the part the best you know how. Then later, off stage, plan how you're going to make it an even better show next time.

Training Tip: Training By Walking Around

In his best-selling business book In Search of Excellence, Tom Peters profiles companies such as Hewlett-Packard and United Airlines that practice "Management By Walking Around" (MBWA for short). Management is encouraged to get out of the office and into the field as often as possible to see things firsthand.

Too often, trainers feel constrained by the podium or other setup at the front of the room. They lecture from one spot, and answer questions from the front as well. "Training By Walking Around" means circulating in the room, teaching from various points.

Using a variety of media (computer, white board, easel, etc.) can help get you moving. It also provides variety to hold the attendees' attention.

This is especially important during lab exercises. It's tempting to retreat behind the front desk at such times; after all, you've been standing and talking, and now is your chance to sit, right? Wrong. Try this experiment: during a lab, ask if anyone needs anything.  Usually, you'll get no responses.  A minute later, casually get up and walk up and down the aisles. Chances are, someone will stop you and ask a question.

I will usually sit for the first five to ten minutes of a lab, then circulate, asking if everyone has gotten started okay. After another five to ten minutes, I'll circulate again, asking if anyone needs "another pair of eyes" to look at an error message.  Before the end of the lab, I'll make another pass silently. Usually by then people are stuck on a particular problem and will call me over as I pass by them.

I've even taught from the back of the classroom. I'd been presenting for a while, and could tell the group needed an energizer.  So I had them all stand and turn around, and I walked to the back (which was now in front of them.)  I did a contest quiz with a prize -- but because they were facing away from their notes and the computers, they couldn't reference them! We laughed about this, the act of standing re-energized the group, and we continued normally afterwards.