Mr. & Mrs. Johnson

Saturday we went on a road trip to Clifton Park, New York (outside Albany) for the wedding of our long-time friend, Denise and her soon-to-be, Randy. It looked like the weather was not going to cooperate as it rained just about the entire way. About 15 minutes before we pulled into Clifton Park, the clouds started breaking and the sun came out. What a perfect day for a wedding.

Denise and I worked together at the computer center when we were in school. Always bubbly and upbeat, you just knew she was one of those special people in the world. Denise also lived with my wife (then gf) when we were in school. I've always known Denise as a strong, independent, easygoing woman. When we met Randy about a year ago, the two seemed inseparable and make each other very happy.

All had a wonderful time at the wedding. The service was very unostentatious with a small bridal party, best man, and Pastor Chuck presiding over the nuptials. The reception was elegant and afforded lots of time to catch-up with friends. My only regret is that I would have liked to see Ann (another college friend) doing the YMCA, but alas, the DJ didn't serve that one up. Maybe that's just an '80s thing.

Just to show how thoughtful the newlyweds are, when we checked in to the Hotel there was a gift-basket containing snacks and drinks.

Congratulations to Mr. & Mrs. Johnson. May your new life be filled with happiness forever.

A New High

$2.899/gal this morning for gas. Over $33 to fill the Honda.

Installing Standard Edition

I installed 10.2.0.1 Enterprise Edition on RHEL 4 a couple weeks ago and got it working with a couple small tweaks to the OS. Yes, I know 10gR2 is not supported on RHEL 4 yet, but by the time I got to production it will be.

A couple days later I got a new project that will require 10.2.0.1 Standard Edition on RHEL 4. Not thinking about it, I used the installer that I downloaded from OTN to install. I chose the custom install (like I always do), de-selected the "Enterprise Options" and click,click,click, I was done.

Once I started the database things got interesting. My banner said:

sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 26 20:31:32 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options

OK, that's not cool. I de-install and re-install thinking maybe I screwed something up. Nope, same thing.

"Surely, someone has run into this problem before", I think to myself. I searched metalink for a while and noticed some bugs with the install so I created a TAR.

Me: I installed SE and came out with this banner. I did this, this, and this. I am seeing this, this, and this, which I think is incorrect.
TAR: Did you install from part # XYZ? There have been issues with that set of disks.
Me: No, I downloade from OTN.
TAR: Oh, install from CD.
Me: Isn't it the same software?
TAR: You downloaded from one of the first OTN downloads. There might have been a problem.
Me: But, I don't have the CD. I was told in TAR ### that CD's aren't ready for shipment.
TAR: create another TAR and tell them you need CDs because you're working on TAR ###.
Me: OK.

I create another TAR and get the CDs shipped to me. I copy from the CD to my local box and install from there. Same thing.

Me: OK, I installed from CD and got the same thing.
TAR: Ship us the installActions log and these other logs.
Me: Here they are.

Wait 3 days.

TAR: The log shows you installed EE. Install SE.
Me: No, I chose "Custom", unchecked the EE options, and installed.
TAR: Install SE.
Me: But that's the way I've done it before with no problems.
TAR: Install SE.
Me: But I don't want OEM, Apache, or any of the other stuff installed.
TAR: That's the way it's been done since Oracle7. That other stuff won't hurt, just install it.
Me: That's not true, but OK.

I saw this was going nowhere, so I closed the TAR. A few minutes later I get an email from Oracle Support saying my TAR has been updated.

TAR: Yeah, you're right. But the way you get around it is install everything for SE and then de-install the options you don't want.

Before Insert

One of my developers came to me with this problem. He is trying to manipulate the data coming into a table by using an INSERT TRIGGER. For the life of me, I can't figure out why this doesn't work (I must be missing something simple):

SQL> drop table xyz
 2  /

Table dropped.

SQL> create table xyz
 2     (
 3      x number(10),
 4      y varchar2(20),
 5      z varchar2(4))
 6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
 2     before insert on xyz
 3     for each row
 4  begin
 5
 6     :new.z := '777';
 7
 8  end;
 9  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        X Y                    Z
---------- -------------------- ----
        1 123456789            777

SQL>

OK, so far so good. The trigger populated the Z value like I expected. However, when I pass in a string that is longer than the Z field, I get:

SQL> insert into xyz values (1, '123456789','123456789');
insert into xyz values (1, '123456789','123456789')
                                      *
ERROR at line 1:
ORA-12899: value too large for column "JEFFH"."XYZ"."Z" (actual: 9, maximum: 4)


SQL>
SQL> commit;

Commit complete.

I can't explain why in a BEFORE INSERT trigger Oracle would care what the length of the string is before the trigger even fires. Any hints?

Before Insert, Part II

Heath Sheehan said...

Row level triggers fire for each row that is affected by the triggering statement. That would imply that all validity checks have to be passed before the triggers fire. Any explicit or implicit data conversions have to result in valid data. Any check constraints have to pass, etc.

If the data isn't valid, the row isn't inserted and there's nothing for which the trigger should fire.


Yeah, but if that's true, why doesn't a NOT NULL constraint elicit the same behaviour?

SQL> drop table xyz
  2  /

Table dropped.

SQL> create table xyz
  2     (
  3      x number(10),
  4      y varchar2(20),
  5      z varchar2(4) not null)
  6  /

Table created.

SQL>
SQL> create or replace trigger xyz_bi
  2     before insert on xyz
  3     for each row
  4  begin
  5
  6     IF :new.z IS NULL THEN
  7        :new.z := '777';
  8     END IF;
  9
 10  end;
 11  /

Trigger created.

SQL>
SQL> insert into xyz values (1, '123456789','123');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123

SQL>
SQL> insert into xyz values (1, '123456789',NULL);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from xyz;

         X Y                    Z
---------- -------------------- ----
         1 123456789            123
         1 123456789            777

SQL>

Job Shock

I read with interest IT Workers Confront 'Job Shock' by Thorton A. May in the latest Computerworld. Mr. May brings up a very valid point that users have extraordinary tools in their hands to do the work that IT Professionals used to do. It brought to mind one of my first programming projects way back in 1989. I was to write a program that pulled data out of an Ingres database, sort and group it in illogical ways, and spit it out on a landscape page. The tool of choice was C and it took me six weeks to write. Today, I'd dump some summarized data in a CSV file and let the user mess with it.

Mr. May goes on to wonder "What if paid IT employment was to steadily disappear?". Good question. I can tell you the people at my first job are still employed. No, they're not writing too many C programs anymore, but web interfaces. They give their users the tools to get their own data. Last I knew, there were just as many people as when I left.

Mr. May goes on to point out how to "save" your fat IT career. All his points are well taken, but the bottom line is your career is your business. If you let you business stagnate, it will die. (If this sounds familiar, thanks for reading).

dbms_stats.alter_database_tab_monitoring

While implementing my new statistics gathering procedure in production, I ran into a snag with permissions.

The very first step in my process is to setup my "analyze" user and create a job that runs every night that turns MONITORING on for any new tables. Sounds simple enough, right?

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 9 21:14:18 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> create user analyzer identified by youbetcha
2  temporary tablespace temp
3  default tablespace tools
4  quota unlimited on tools;

User created.

SQL> grant create session to analyzer;

Grant succeeded.

SQL> grant alter session to analyzer;

Grant succeeded.

SQL> grant execute on dbms_stats to analyzer;

Grant succeeded.

OK, that should be enough, right? Let's give it a try.

SQL> connect analyzer/youbetcha
Connected.

SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

Hmm. I looked at the package spec and find out it's run with Invoker Rights and not Definer's rights. Ah, I must need "ANALYZE ANY" privilege.

SQL> grant analyze any to analyzer;

Grant succeeded.

That must be it, let's try it again.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);
BEGIN dbms_stats.alter_database_tab_monitoring(TRUE); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10733
ORA-06512: at "SYS.DBMS_STATS", line 10752
ORA-06512: at line 1

OK, that ain't it. I did some research on Metalink, AskTom, and poured through the docs, but didn't really get anywhere. Then I thought, how about a Trace? I started a level 12 trace and then re-executed the procedure. Under the covers, dbms_stats.alter_database_tab_monitoring just does a "ALTER TABLE xyz MONITORING". I get it, I need "ALTER ANY TABLE".

SQL> connect / as sysdba
Connected.
SQL> grant alter any table to analyzer;

Grant succeeded.

SQL> connect analyzer/youbetcha
Connected.
SQL> exec dbms_stats.alter_database_tab_monitoring(TRUE);

PL/SQL procedure successfully completed.

Simple as that. Now all I have to do is create a dbms_job and run it every day:

SQL> declare
2     lJobNo INTEGER;
3     lJob   VARCHAR2(2222);
4  begin
5
6     lJob := 'dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);';
7     dbms_job.submit(
8        job=>lJobNo,
9        what=>lJob,
10        next_date=>SYSDATE,
11        interval=>'trunc(sysdate+1)');
12
13     dbms_output.put_line('job submitted: ' || to_char(lJobNo) || '...');
14
15  end;
16  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job, last_date, last_sec,
2     next_date, next_sec, broken,
3     failures, what
4  from user_jobs;

     JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B   FAILURES WHAT
---------- --------- -------- --------- -------- - ---------- -------------------------
      23 09-AUG-05 21:39:31 10-AUG-05 00:00:00 N          0 dbms_stats.alter_database_tab_monitoring(monitoring=>TRUE);                                        

That's done, on to the next step!

Thanks Doug

I'd been lurking in Doug Burns' Blog for a couple of weeks and found most of his posts interesting. Interestingly, enough, that last week I put a link on my blog to his so I could share with others. Then he goes and changes blog hosts. Now I have to update my blog links to include his new site. Thanks Doug.

Oracle Job Scheduling

Managing jobs in Oracle 9i and below was a pretty straight forward process; start the job queue processes and submit a PL/SQL Block using dbms_job. If you wanted any level of detail you had to wrap the dbms_job functionality around a package and some other tables. Job scheduling was basic and it generally worked. Any complicated scheduling or interaction with the OS and you were out of luck and had to revert to cron.

I'm not sure what I expected when I started reading Oracle Job Scheduling, by Dr. Tim Hall, a few days ago. I've got a decent handle on dbms_job and have used it extensively for all sorts of maintenance tasks. How different could job scheduling in 10g be? Believe me, it's different.

Dr. Hall explains how the new dbms_scheduler packages works and the details of each call. In addition he explains in detail how the new INTERVAL type works and gives very through examples. Chapter 4 is by far the most valuable chapter as it explain four different methods to schedule dependant jobs. The examples in this chapter are an extension of Tims experience in the real-world implementing solutions. Later on, the book explains about how to monitor the new scheduler and how to view the job logs.

I've got to admit I breezed over the sections on OEM and OS Scheduling. I don't use OEM and there's nothing I really need to know about cron.

This book was a good read. Don't get me wrong, it's no A Dog Year: Twelve Months, Four Dogs and Me. It's a technical book through and through. I would definitely recommend it if you are planning on using the Oracle job scheduler to implement complex business schedules.

Oh, and by about the third time I saw:

-- ****************************************************
-- Copyright 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- ****************************************************

I was ready to barf.

Hiring Round 3

I can't freakin' believe it! You may recall my sagas about finding a new Database Administrator (here and here). Well, it's happened again.

We interviewed about 20 canidates for the open DBA position and narrowed the choice down to two very qualified candidates. Each had their own strengths and weaknesses. It was a difficult choice, but we finally chose one over the other and offered NewDBA2 the position. NewDBA2 thought about it for about an hour before he called us back to accept the position. A start date was scheduled for two weeks from the next Monday. As recently as yesterday, NewDBA2 contacted the recruiter and reiterated his excitement for the new position. In fact, they decided to go to lunch today to celebrate.

Then I got the call. NewDBA2 is sorry to say that he has been offered another position and can't start on Monday. Yes, Monday, three days away.

That's twice in a row. I had two people commit to a job and at the last minute they backed out. Personally, if I don't like something about the job, I just don't take it. Sigh, back to the recruiters.

Around New York

The Wife and took in a matinee showing of The Lion King (which was spectacular) on Saturday. Our plan was to take a train to New York City, see the show, go to dinner, and be back by the evening. We've been to New York a number of times the last five years, so we've gotten pretty good at getting around town. We're used to crowds, pan handlers, and those annoying hawkers trying to get you on a bus tour.

As we exited the theatre in Times Square, there were lots of people around. I mean LOTS of people. Seems some dingbats with cameras were filming a "man on the street" type reality TV show about fashion faux-pas and everybody wanted to be on. We only had to go four blocks to get to our restaurant and 15 minutes to get there. Fashionistas in training were swarming the cameras so we crossed the street - right under the MTV studio. Some guy was giving away CD's and of course the TRL crowd was grabbing them up by the second. We weren't encouraged by the 1/2 block line outside Bubba Gump, but still pushed on. The next block, we traveled down 45th street and got out of Times Square. A block later it was like a ghost town with hardly any people at all. We finally got to the restaurant only 5 minutes late and had a great dinner and still was able to catch the train at a decent hour. Except for the crowds, a great day.

Parking

We were out and about the other day running errands in one of the snooty towns on the Gold Coast when we came upon a Carvel. I'm never one to pass up Carvel, so I flipped my blinker on and started pulling into the parking lot. The Tahoe driving lady ahead of me started taking a spot right up front, but half way in, she stopped and started backing up. Meanwhile, I'm half in the lot and my tail end is sticking out into a four lane road. I mumble "WTF?" as Mrs. Tahoe just about backs into me and takes another spot in the lot. Ah, she saw it was a handicap spot. My wife looks at me with rolled eyes like I did something wrong. As as last ditch effort to save my manhood I mumble "That's how people get killed." (Jesus, that was stupid. People get killed from a fender bender, yeah right.)

So Mrs. Tahoe gets out with Grandma Tahoe and her brood and goes to the counter. I notice one of the little Tahoes wants to get behind the counter and touch everything. Then he's looking up and around and Grandma Tahoe quietly grabs his hand. The other two little Tahoes are ordering their ice cream; one with sprinkles, one with chocolate syrup. Mrs. Tahoe orders a fat-free cup (no sprinkles) for herself, a chocolate cup for Grandma (chocolate sprinkles), and a cup (no sprinkles) for Jr. Tahoe.

When we make it outside the Tahoes are enjoying their ice cream. Jr. Tahoe has barely touched his and is looking around touching this and that. I get it, Jr. Tahoe is probably challanged. I feel even worse about the parking as Mrs. Tahoe can probably justify parking in the handicap spot. We finish our ice cream, Jr. Tahoe looks at me and I smile back. He smiles. Cool, maybe he forgives me at least.

As we're walking out past "the" parking spot, Grandma-but-I-won't-admit-it Mercedes pulls into the handicap spot, jumps out of the S-class and heads for the counter. I shake my head knowing I'll get another look if I say something. Out of the blue, my wife says, "Um, you know that's a handicap spot, don't you?" Grandma-but-I-won't-admit-it Mercedes looks startled but heads back to the car and looks at the handicap signs as plain as day. She keeps circling the car like the signs will magically disappear until the Tahoes get up and head for their troop transport. Grandma Tahoe shoots her a look but Mrs. Tahoe is too busy tending to messy faces to notice. Busted. As we're pulling out, I see Grandma-but-I-won't-admit-it Mercedes slinks back to the car and moves it.

"That's how people get killed", I mutter to a smirk.

SA Superhero

Fortunately for me, the best SA was on-duty. After the server rebooted itself about 6 times, we decided to swap it out for a spare. A couple disk swaps, attach the SAN filesystems and we were back in business in 2 hours.

Waiting on my SA

One of my DB Servers is in a perpetual state of rebooting. Waiting for the SA to figure out what's going on. [sarcasm]I love being on-duty.[/sarcasm]

The price you pay

The price of gas never really bothered me. You see, my everyday car is a Honda Civic and I typically get about 37 miles per gallon. Sometimes more, sometimes less. I used to drive about 130 miles a day back and forth to work and it cost about $50 a week for gas. I recently moved and now a tank of gas lasts me about 10 days. My wife drives half the miles I do and when I fill her 4 Runner's tank it's always been $35 or more.

I filled my tank tonight and the pump clicked off at $30.03. $30.03 to fill a Honda! I've always put more than $20 in, but I'd never broken $30. I know at $2.599/gallon we've got nothing to complain about in the U.S. compared to other parts of the world, but sheesh. This is starting to cut into my mad money.

Preparing for "Oracle Job Scheduling"

I got Tim Hall's new book, Oracle Job Scheduling, a day after I started installing Oracle 10g R2 on my home system. I had just installed it at work on one of my development systems and it was literaly click, click, click, done. I figured it would be about the same at home and I could start learning about the job scheduler that night. Started downloading from OTN and, BAM, filesystem filled up. I guess it wouldn't hurt to remove the old software, right? First I removed 9.2.0.5 and only freed up about a gig. I won't need 10.1 now that I got 10.2, right? So I removed it. Then I started the 10.2.0.1 install and the software seemed to install OK. During the database creation, however, I got an ORA-12547 message. I checked out a couple things on metalink and figure my libaio needs to get updated. Off to rhn.redhat.com to get the right rpm and install it. Then I remake oracle. Finally, oracle starts and I can create the database.

I know I promised Tim I'd review his book on my blog. I open up the book to an icon caricature of Don Burleson giving me a gigantic "thumbs up". I can't deal with this, I'm going to bed.

There's a storm a brewin'

Seems as though DBASupport.com has compromised the presentation of their forums in order to accomodate advertisers and their own interests. Several members (myself included) have expressed their dislike of the new format. Join in on the discussion and tell us what you think.

Futzing with 10g Connection Manager

I'd like to think I'm somewhat proficient using Oracle's Connection Manager product. After all, I first implemented CMAN in 8.1.6 for connection pooling. I then used CMAN to receive database traffic through a firewall. Pretty simple stuff, but I could get it to work. Sure, CMAN had it's shortcomings, but there were ways around most of them. Migrating through the 9i versions was no problem; install the cman in a new $OH, copy the cman.ora file and start it up. No fuss, no muss.

While investigating a related connection problem, Oracle Support suggested testing my problem on a 10g connection manager. "No problem", I said confidently knowing the 9i upgrade was a piece of cake. Lets just say things are a little different in the 10g world. Oracle has addressed some of CMAN's shortcomings in the latest release. Below are some of the things I encountered during my upgrade.

Looking through a new window
The first thing I noticed was cmctl now has a new set of commands. I found myself doubting that I ever ran connection manager in the past. A quick review of the 10g connection manager architecture gave me a foundation for the new environment.

CMAN.OR Migration
10g comes with this neat little tool called cmmigr. I used this tool to migrate my working cman.ora file into a non-working cman.ora file.

Tweaking cman.ora file #1
Here's where the real work came in. I had to tweak my cman.ora file to include my host's IP number. One of the nice things about 9.2 cman was you could use (HOST=) in your configuration and the connection manager would automatically use the hostname it was running on. I used this to my advantage to have a generic cman.ora file that I could run on multiple hosts. Since this is only a test, I'll leave this mystery for later.

Starting CMAN
I have what I think is a correct cman.ora file now. I start cmctl and issue the ADMINISTER command. No problem. I then try to STARTUP the connection manager and get a "TNS-04012: unable to start Oracle Connection Manager". The next thing you learn is there is an alert.log file in $OH/network/log that you can check. I had a couple errors in my alert.log that looked like:

(LOG_RECORD=(TIMESTAMP=18-JUL-2005 11:43:35)(EVENT=Failed to start listener process)(REASON=)(OPN=65)(NS1=12545)(NS2=12560)(NT1=515)(NT2=2))

Hmm, nothing useful there. I go back to the docs and see that cman now needs a listener to pass connections off to cmgw. So I try to start a listener process on my own and realized there was no lsnrctl in $OH/bin. Aha! I only installed cman and not the listener. Install the listener components and retry. SUCCESS!

Statcounter

Just after Tom Kyte put a link on his blog to mine, I decided to put a counter on the page. I looked at a couple other sites I respected and I decided to use statcounter from www.statcounter.com. My statcounter looks just like Tom's (except maybe the scale is off by a factor of 100!)

Firefox 1.0.5

Learned from my statcounter that some of you are using Firefox 1.0.5. I didn't know it was even out yet! Needless to say, I upgraded right away.