The Joys of RFID

Welcome all to the “So What?”!! The Hunter family off to the South Pacific? *pffffftttttt* yea right he’s probably syncing up his Blackberry right now or sitting on the couch watching cartoons. Anyway……

I guess I should at least give a little background on myself before I dive into the Oracle silliness. Most of you know me as OracleDoc on the forums and I’m currently working for a Defense contractor who has a contract with the US Army in Germany. I’ve been here for over two years now and I don’t see an end in sight. In all actuality, I rather enjoy what I’m doing because it’s cutting edge and it keeps me on my toes, not to mention it’s in the land of beer and Autobahns!

I’ll give you a little sample of what we do here because I think it’s a great technology and it’s going to be more main stream with the commercial world here very soon (if it hasn’t already).

RFID (Radio Frequency Identification). Go watch this commercial then come back. Basically anything the Army slaps an RF tag on or any other device that has the ability to broadcast its location, my database stores its location. I can then regurgitate that information onto a webpage either textually or as an image on a satellite map. I’m sure you’ve all seen Google earth, so imagine you have a piece of equipment that you want to know where in the world it’s at, you plug in the identification number of the tag and voila`! there it is displayed neatly on a Sat image.

*side note; These things are great for tracking teenagers driving habits

Funny story….couple months ago I was looking for a particular truck that the Army uses solely in Kuwait for transporting food. I plugged in the criteria and said give me all the food carrying trucks that have reported within the last 24 hours and bring it to me on the Sat map. I was expecting to see all these little truck icons scattered throughout Kuwait but to my surprise, I see this one lonely truck icon smack dab in the middle of Beirut. I’m thinking “nawhhh it’s a glitch”…click ”refresh” but nope it was still there. So I plug in the id number of this truck and tell it to give me the history of all its positions. Good Lord…. this truck started out in Kuwait then worked its way up to Iraq, then on over to Syria, then finally stopping in Beirut. At this point my mind is throwing up red flags all over the place because there’s a remarks column next to the tag and it’s saying that the truck was destroyed by an IED (improvised explosive device) several months back. At this point I call in my boss and we start making phone calls.

As it turns out, the truck was stolen and the company that owned the truck instead of saying it was stolen just said it got blown up. You say stolen and the investigation and paper work start, you say blown up and you get reimbursed for the cost of the truck. Needless to say someone got their genitals whacked.

3 days later the little truck icon in Beirut disappeared. I love my job!

Aloha

October 1997 was the last time I took more than a week off for vacation. At that time, I was somewhere south of Summerlands watching Ferry Penguins come ashore at dusk. Anybody (except HJR) know where I was?

Anyway, the moons have aligned and I am taking off to a remote spot in the South Pacific for two weeks and a little R&R. No cell phone, no laptop. I'm not even sure about TV. Since I won't have connectivity for a while, I've invited a few people to be "Guest Bloggers" to fill in while I am away. I know the readers will get a new perspective, and I hope the guest bloggers have a little fun.

Joey Vayo
Actually, this is all Joey's idea. He came up with the idea of "guest blogger" a little while ago and I thought it was a good one. Of course, he was the first one I asked. He's been a regular on DBASupport.com for years and always gives helpful advice.

Steve Prior
Steve's brainchild is http://www.geekster.com. Although he is a developer by trade (boo, hiss), I think he is a tinkerer by heart. A usual conversation with Steve might start out with "I am looking for ODBC drivers for a PROLOG engine that I am building into my smart-home system to automatically flush a toilet..."

Brian Byrd
Another DBASupport.com regular, Brian is a master at making SQL more efficient both from a coding perspective and from the perspective of the database. While Brian and I don't always agree, I certainly respect his knowledge and ability to think about problems using a different approach.

Jeffrey M. Hunter
OK everybody, there is more than one Jeff Hunter in the Oracle world. I usually differentiate us by saying he's the guy with the RAC Paper and I'm the guy with the blog. Someday we're going to run off and create our own consulting company, but until then, I'm sure Jeffrey will check-in with some very exciting information.

David Pittuck
Last, but not least, we have the world renowned DaPi. David likes to be know as the "Old Cranky Philosopher", but is quite enlightened in the world of IT. (He’s also proud of the fact he has a NerdScore of 93). You can check him out at http://www.pittuck.com.

A big thanks to my guest bloggers. I hope you guys have as much fun blogging as I do.

Regularly scheduled programming resumes around September 18th. Take it away guys...

Fun Blogs

I really enjoy peeking into other people's lives for a brief second. I've been reading a couple blogs lately that are very well written and entertaining. If you like reading the likes of Heather B. Armstrong, LeahPeah, or WaiterRant, I think you'll enjoy New York Hack and Everything is Wrong With Me.

9.2.0.8 Patchset

Has anybody heard when the 9.2.0.8 patchset for Solaris and/or Linux will be available? All I see on Metalink is 2006Q3, which we're about half way through...

Compute or Estimate

A question came by me the other day that I had to do a little head-scratching on.

"How can you tell if your table was analyzed with COMPUTE or ESTIMATE?"

Good question. In my enviornment, I know it was COMPUTE because that's how I do my stats. But how would I know otherwise? There's a column called sample_size in dba_tables that indicates how many rows were used for the last sample. Ah, just a simple calculation.

Lets see how it works out. First, create a decent sized table:

SQL> create table big_table as select * from dba_objects;

Table created.

SQL> insert into big_table select * from big_table;

42396 rows created.

SQL> /

84792 rows created.

SQL> /

169584 rows created.

SQL> /

339168 rows created.

SQL> /

678336 rows created.

SQL> commit;

Commit complete.

Of course, we don't have any stats yet, but lets just check it anyway.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                                NO                                     

Sounds good. Lets analyze our table using dbms_stats and see what we get:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, 
     sample_size, num_rows, 
     decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
    /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

Cool, that's what we expect. Now let's analyze and estimate with 1% of the rows:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>1);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES       13371    1337100          1  

That definitely is what I was expecting.

I think I remember that if you estimate more the 25%, Oracle will just COMPUTE. Lets see what happens:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>33);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      447748    1356812 33.0000029  

Here I'm on 9.2.0.5. Previous to 9.2, this would have done 100% of the rows, but in 9.2, it will estimate all the way up to 99%. (Why you would estimate 99% vs. 100%, I have no clue, but it can be done).

Let's see what the block_sampling does:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', 
     estimate_percent=>10, block_sample=>true);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      132743    1327430         10  

Eh, nothing new there.

Lastly, let's see what dbms_stats.auto_sample_size does.

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE',
     estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

That's interesting, 9.2 thinks my best method is to compute. I try this on several 9.2 dbs and different sized tables and it picks 100% every time. So I go to the friendly Metalink and find a couple bugs in 9.2 that indicate auto_sample_size doesn't exactly work in 9.2.

Not sure where I'll use this in the future, but it was an interesting experiment anyway.

Done tweaking

I think I'm done tweaking for a while.  The format is a little less fancy that I envisioned, but to me, it's just as easy to read now as the white & grey.  Plus I like that the text area grows and shrinks to the size of your browser.  When I'm on my desktop with higher resolution I can read more.

Why I got out of the GUI business

I started out life in the IT world as a programmer banging out GUIs for every type of system you can imagine. Every user wanted something different; this one wants green fields, this one wants white. Another wants fonts bigger, another wants them smaller.

On a totally unrelated note, I'm tempted to put the blog back to Grey & White.

Waiting

What do you do when you're waiting for a 17 hour import to finish? Tweak you blog, of course!

Platform migration in a 9i world

One of my 4-way Solaris boxes just wasn't cutting it anymore. We've tuned what we can tune and tweaked what we can tweak. The box was running with an average run queue of 8 lately. Time to upgrade the hardware. We chose a nice fast Linux box from HP and hooked it to an HP EVA disk array.

So how do you move 660GB of data from Solaris to Linux? On Oracle 9i?

Moving to 10g was considered for about 30 seconds, but we decided it was too many changes at one time for one of our most important applications. The next logical choice was export/import. On the first round, it took about 14 hours to export and 4 days to import. OK, that can't be done in a weekend.

The we decided to break the tables up into four somewhat equally sized groups and run export/import in four threads. This actually worked pretty well for the overwhelming majority of tables. About 4 hours for the longest group to export, and 43 hours for the longest thread to import. Still not doable in a weekend, but we're closing in on a reasonable timeframe.

We noticed a lot of time was being taken up creating indexes. So we decided to pre-create the tables without indexes and create indexes in parallel with nologging. This shaved about 6 hours off our total time.

Then we looked and saw two Index Organized Tables were taking a lot of time relative the the number of rows they contained. I then did some testing with import on IOT tables vs. Heap tables and didn't see any significant difference. In fact, the testing I did proved the two IOT tables shouldn't have taken as long as they did. I looked at them a little more closely and found the tables contained a TIMESTAMP field. I did some more testing and compared import with a DATE field vs. import with a TIMESTAMP field and found the TIMESTAMP field was causing the slowdown. Once we identified the TIMESTAMP field was the problem, my boss found note 305211.1 on Metalink which explained our problem. We then timed the import with COMMIT=N for those two tables and found the time went from 26 hours to 17 hours. I thought, "lets try to fill the tables through a database link". The database link yielded the best results of all in about 6 hours.

The last piece of the pie was the FK constraints. We didn't want to re-validate all the constraints since we knew all the data was going to be copied over (after manual verification, that is). So we created the constraint with NOVALIDATE which took relatively no time.

After everything was back in, we estimated statistics on our biggest tables and computed statistics on our smaller tables. It took about three weeks to figure out the method, but the time researching the project paid off when the database was available after 34 hours.

The Cashier and the Cell Phone

The cell phone is everywhere today. You can't walk two blocks without running into 3 people with something stuck to their ear. It's just part of today's world. I'm just as guilty as the next guy of using my cell phone when I probably shouldn't. But the other day, I experienced a person so rude, I just had to tell you about it.

I just finished grocery shopping at my local big-box store. All my items were on the belt for the cashier and he was flicking them by the scanner one by one. The particular market I frequent has about 1 bagger for every 3 cashiers. I'm perfectly able, so I don't mind starting to bag my own groceries while the cashier finishes scanning my order. After all, I'd rather have the dedicated bagger help an old lady than me. I have a mountain of groceries stacked up, but i figure the cashier will help once he's done running my stuff through the scanner.

He scans my last item, tells me $93.14, and whips open his Nextel phone. I continue to bag thinking he'll get right back to me in a second.

"Hey girl" he talks to the phone after the familiar Nextel beep-beep-beep.

"You comin' over?", she asks.

"Who's dere?"

"X and Y are already here, we're waitin' for Z and you."

"Oh. I don't get outa here 'til 8."

I'm done bagging at this point, just a little ticked off. I go to the EFT pad, swipe my ATM card and key in my PIN number. The display reads "Waiting for Cashier". Yeah, no kidding.

Their teen-age banter goes back and forth for another two minutes. The yuppy woman behind me gives the cashier the "What the F*$K are you doing?" look while her significant other blatently flashes his wrist with and obvious look at his Fauxlex.

Two minutes later, we hear "OK, we'll hookup afta I get outa dis place."

He hits the magical combination of buttons to let me pay and I'm off just shaking my head at the poor customer service.

Anti-Midas

Why is it that everytime I touch something with a Microsoft tool, it breaks? For example, I composed my last blog entry using MS Word. Instead of just publishing from Word, I highlighted the whole thing, copied it, and pasted it into blogger's editor. Everything looked fine to me, so I published.

On Monday, I realized I didn't get my feed via RSS. My feed provider said the feed had an invalid format and was complaining about invalid namespaces that were "Mso" something or another. I checked the HTML for my post and sure enough, there were these little tags with proprietary Microsoft stuff in them. Nice.

I edited the HTML and took all the MS garbage out and voila, my feed was back in business.

Funk

I’m in a funk.

A lot of small projects had to take a backseat while I was finishing up my two year project. Now it’s payback time. I have a list of about 20 things that I am working on that aren’t what I’d call real interesting, but they need to get done anyway. Things like cloning Oracle Applications. Yuck. But a necessary evil.

I thought getting some reading under my belt would get me back into the swing of things. I picked up Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions three times in the last two weeks, but just couldn’t get into it. Ventured to a different Barnes and Noble last night to peruse their computer section, but came out with People Magazine.

I get started on a bunch of things, but just can’t seem to finish. In fact, I started on this post about 4 days ago, but just never got around to finishing it. I have four good ideas in my blog “Drafts” area, but just can’t flush out the energy to fully develop them.Sometimes I feel like Sisyphus rolling my stone.

This too shall pass.

Two Years

You may have noticed that the blog hasn't been updated on a regular basis lately. I've been working on a project that has basically consumed me in spurts for the last two years.

Back in the summer of 2004, we decided it was time to undertake an upgrade of some software that I thought would take about six months. During a test upgrade, we discovered that it was a little more complex than we though and that a bunch of technology pieces had to be upgraded before we could upgrade the software. So we put in place a plan to upgrade each technoloy component as a separate phase and implemented them in regular intervals. Once we got one piece implemented, we started upgrading and testing the next technology piece.

Along the way we had to work with various support organizations to get each piece implemented completely correctly. Even though we enlisted those support organizations, piece X version 1.1.8.21 didn't work with piece Y version 8.1.6.1 which lead to another piece that had to be upgraded. We upgraded in about 6 seperate phases, with a couple mini-phases in between. During the same window, we had some staff changes both on the user side and the technology side that made things more difficult. When the project sponsor from the executive side stepped down a month before we were supposed to go live, I got really nervous. We pushed on and were finally able to upgrade the software in a 46 hour marathon session without a hitch.

This has probably been the longest project I have ever worked on as a DBA. After two years of sporadically working on the same project, I'm relieved to have it over with. At the same time, I learned more about the product than I ever dreamed (or wanted to know) and picked up a bunch of technology awareness and project management soft skills that I couldn't have as "just a DBA". Hopefully the business sees it the same way.

Since that time, the vendor has released another update. After two years of work, I'm a version behind now. Back to the drawing board.

Laptop Woes

I've had my Sony Vaio (PCG-GRT250P) for about two years now. I basically use it only for internet browsing, email, and VPNing into work when I am away from the office.

Just about a year ago, it was caught in a vicious reboot cycle. The computer would begin to come up, get to a certain point, and reboot. Any attempt to boot from the harddrive was fruitless. I succombed to handing it over to my crack IT guys and had it back in a couple days with a new drive.

I've been happily spinning along until about a week ago. I'll be working along and all of a sudden the drive starts to spin like crazy and I get a blue screen of death. I have to power the thing off and back on at which point it tells me "Operating System not found". First couple of times I got really worried, but after I learn the pattern, I know to turn it on, reboot, come up in "Safe Mode", shutdown, and startup. At first, it only did it once a day, but now I can run for...




... (OK, I'm back now, it just did it again) ... about 10 minutes before it craps out. Funny thing is, now I have to let it "rest" (or basically cool down) before I start it up again.

I consulted my crack IT guys and sure enough, they said it was the drive again. A new drive is on order and should be here in a couple days. I can...





....(there it goes again) ... live with it until then.

Upgraded "Freeware"

I've used this freeware program called Good Sync on my laptop to sync the "My Documents" and my email folders to my 512M USB key. Sure, it's not perfect, but it's close enough.

I came across Good Sync a few months ago and downloaded version 3.something. I setup a job for each folder and I scheduled it to run every 30 minutes while the USB key was attached.

Today I got a message saying that version 4.6.1 was ready and asked if I wanted to update. I thought "Sure, why not?". I downloaded the software, tried to manually sync up and got an error saying my jobs were too big for the free version of Good Sync and I'd have to pay $19.99 to purchase the "Pro" version. Nice.

A Good Cop?

If you were offended with Typical Greenwich, you'll be even more disgusted with this story.

Happy Birthday Alan Turing

The father of modern Computer Science would be 94 today if he didn't commit suicide at 42.

My Favorite Metalink Articles

When you answer questions in public forums, you typically end up either the same question many times or pointing the poster to a particular slice of the documentation or a Metalink document. Some of the more frequently suggested documents and tools I use from Metalink:

Why is my index not used?
ORA-600/7445 Lookup Facility
Backup & Recovery FAQ
RMAN FAQ
Semaphors & Shared Memory Explained
Shared Memory
Kernal Parameters and how they relate to Oracle
Wait Event Description
How to deal with deadlocks (ORA-00060)
Connection Manager and Firewalls
Using truss
Locking and Latching FAQ
Critical Patches

Updated Links

With all the sheep moving to other pastures, I kinda let my links lapse over the last month or so. I think they're all updated to their current home. If your blog is on my blogroll and is out of date, please let me know and I'll update it.

Google Pagerank

OK, so I jumped on the bandwagon last week and added what I thought was the Google sponsered "Page Rank" on my blog. I checked today, and the icon didn't show up, but the link was still there. I clicked the link and "www.google-pagerank.net" now gets automatically forwarded to Google's main page.

So I searched for "google page rank" and there's a ton of sites that can display your Google Page rank. Perhaps the Google lawyers were working some overtime this weekend to get "www.google-pagerank.net" shutdown?

Shameless

As a devout follower of the Dizwell Principles, I gleefully added my google pagerank to the site after receving today's sermon.

Performancing

Ran across this blog editor while searching for flash-block.  Performancing is a browser based editor for posting to your blog.  The feature that I like is all your blogs are listed and you can choose which blog you post to without having to navigate blogger's menus.  I'll give it a try and see how it works day-to-day (Not that I've been posting that much lately...)

You believe this?

Look at this crap. 99% of my CPU resources are being sucked up by Firefox because of the stupid IBM ad on this web page. I go off that page, and poof, it's down to 8% or less. Thanks IBM.

Things I could live without knowing

Got directed to this from leahpeah.

Compatibilities of tar

Arrgh. More Linux/Solaris incompatibilities. Seems as though the -I flag (include files listed in a file) on Linux is not supported. In fact, it gives you a nice error message "Warning: the -I option is not supported; perhaps you meant -j or -T?". Looks like I'll be twiddling with my .profile in the next couple of days...

Update: My boss pointed out -T works with Solaris and Linux. Doh.

Are we too connected?

This has been a hectic few weeks.

Along with moving, I have two major projects at work that have absolute drop dead dates within a week of each other. During the move last weekend, I was without my cable modem for 43 hours, 21 minutes.

Of course, the two major projects at work still had to get worked on, so I succumbed to working over dialup. And not just any dialup, NetZero freebie dialup. Now I don't have anything against NetZero dialup; it's a great free service if you remember to click on the ads every 15 minutes or so. It's just when you're used to using a cable modem, dialup is quite painful.

Over the four days I was off, I made three calls, was paged twice, and responded to about 24 emails. As the weekend was winding down, I thought to myself, Are we too connected? You can call me at home. You can call me on my cell phone. You can page/text message me. You can email me at work. You can email me at home. If I'm not at home I can be at a hot-spot in 15 minutes to "dial in".

Is anything that urgent?

Hang in there

Been really busy with stuff at work that I can't blog about, so things have been pretty sparse lately. Also, my home office is in a shambles since moving day and I don't even have my computers hooked up yet. I've been jotting down ideas, so stay tuned...

Metalink Update - I'm In

OK, here's the key. Don't do what the emails say, but login with your OLD username and password and you will be prompted to change your userid to your email. You will then get an email with your new username/password at which point you can logout/login and you will be prompted to change the password.

Metalink Update

Oracle support sends me a message saying my TAR has been updated and to go to http://metalink.oracle.com to see the update. Sigh.

Learned from the analyst that their analysts and engineers don't use Metalink, but have a Client/Server GUI that they use...

Metalink Login

Oracle must have done a lot of testing of their Metalink conversion scheduled this past weekend, right? After all, I received no less than three emails outlining what was happening the weekend of May 19th. On Saturday, May 20th I decided to see if any work had been done on my TARs, so I decided to try and login. I tried several times and received a "page not found" every time so I decided they weren't done yet.

I went to metalink yesterday and the login page had changed, so I figured they were done, right? Tried logging in with my email and current password and received this lovely message:

Tried again, same thing. So I figured maybe I should change my password. I clicked on the "forgot password" link and in a couple minutes I had a new password. Tried that password and still no luck.

Now it's Monday morning and I've tried the old password, new password, and I even got another new password. Now I'm trying to login and my browser is just waiting for metalink.oracle.com.


Maybe they forgot to apply pre-requisite patch 1456677 to patch 18277266 which is specified as a prerequisite for 88277266. I supposed they've already tried logging a TAR, but I guess metalink was down.

Welcome to the customer's view of Oracle.

Update
Called Oracle Support, had to wait 33 minutes until I got to talk to a human. "Yes, we know metalink is down. There is no ETA at this point."

Thunderbird Address Book #2

This address book thing is killing me. I tried Beth's suggestion and it deleted the entry for that session, but when I started Thunderbird again, there it was. I also took Ian's suggestion and deleted all the duplicate contacts to no avail. Arrrrgh. Investigating alternative email/RSS readers now...

Typical Greenwich

It was raining on my way to work this morning. Not a driving rain, but heavy enough that the wipers were on. I was stopped at a red light and directly across the intersection from me was a new black Bently.

To my right was an old couple waiting to cross the street. It would be a coin flip whether they were around for the first world war or not. He was in his London Fog jacket and a hat complete with old time galoshes. She looked like she just got off a fishing boat with a yellow slicker and buckle up rubber boots. Both used canes to shuffle along, and he held out his arm for her to hold as they stood waiting. The crossing signal turned to "WALK" and they started their journey across the street.

On this particular corner, the crossing signal counts down to indicate how many seconds you have left. When the signal read 10, they were just in front of my car.

"They're never going to make it", I thought to myself.

Time expired and they just cleared my lane.

My light turned green and I since I was turning left, I had to wait for the Bently to come through anyway. The Bently starts coming through the intersection and stops short when he sees the old couple crossing the street. Then he throws his hands up in disgust and BLOWS THE HORN at them.

The couple stops for a short second, the old man shoots the Bently driver a cold look, and they continue on their way. The Bently driver's mother must be proud.

Thunderbird Address Book

I'm getting a little ticked off at Thunderbird 1.5.0.2 lately. Don't get me wrong, I think it's a great email client and RSS reader. However, I've been running into a particularly annoying issue that has me on the brink of scrapping it.

You see, I just can't delete an entry from my "Personal Address Book". I know, I know, that's a pretty minor detail to be tossing a great email client. I'm a big fan of letting Thunderbird search your local address books for the person you want to address the message to. That's well and good when each person only sends you email from one address, but in the real world people have multiple email addresses.

My problem is my boss sent me an email from his home account a couple weeks ago. I replied no problem and everything was cool. The next time I sent him a message, Thunderbird automatically picked up his home email address and sent it there.

"OK", I thought to myself, "I'll just delete his home email and Tbird won't pick it up anymore."

So I deleted all occurrances from my address books, re-checked that it wasn't in there anymore, sent a test email, and he got it no problem. Shutdown Thunderbird and went home.

The next day I sent him an email again. Thunderbird picked his home email again. WTF? I know I deleted it, but sure enough, his home email was back. I tried deleting again, exiting Tbird and it showed up again. This was driving me crazy. After twiddling with it on and off for about 2 hours, I gave up and unchecked Tools>Options>Addressing>Address Autocompletion>Local Address Books.

Now when I address a message, I either have to know the person's email address or choose it from the address book. Maybe I'm just being impatient and this is one of those "doh" moments...

Cost-Based Oracle Fundamentals

Been busy the last couple of weeks on the new house.

Even so, managed to get through my first pass of Cost-Based Oracle Fundamentals by Jonathan Lewis. I concentrated more on the concepts the author was trying to get across and less on the math of each operation. On the second go-round, I plan on doing the examples one by one and experimenting with some real-world data.

I didn't count, but I found myself saying "Ah-ha" several hundred times. Histograms, Ah-ha. Cardinality, Ah-ha.

There's a lot of hot air on the internet about Clustering Factor, but there is a whole chapter that sets you straight on the concept; both positives and negatives. The section on how reverse key indexes negatively can affect the Clustering Factor is really eye opening.

I personally got a lot out of Chapter 11, Nested Loops. Nested Loop joins are one of the more common access paths chosen and I thought I had a decent understanding of them. This chapter really filled in the gaps that were missing.

And a whole chapter on the 10053 event? Whoa. I'm sure there's a lot more to it, but now at least I have an idea of what's going on with the optimizer when I look at the trace file.

Lets just say the differences Jonathan points out between 9i and 10g scare me. Big time. A lot of differences are pointed out throughout the book, but there's going to be a ton of testing when 10g comes to town.

Cost-Based Oracle Fundamentals is definitely a recommended read.

What's up with Metalink?

REAL SLOW today. Maybe they upgraded to Oracle Linux?

Year Six

Disclaimer:
I have the unfortunate luck of being hired on the same date that Tom Kyte started his blog. Honestly, I've been working on this a couple days so I'm posting it anyway, even if you think I am a copycat.

Six years ago today I started with my current company. This is the longest I've ever had the same title, although my responsibilities have changed over the years. It's also the most time I've spent at the same place.

It was the tail end of the dotCom boom and I had over 40 interviews with companies in the Tri-State area. Most of them didn't pan out, but when it came time to choose, I had three offers to consider. When I accepted this job, I took a chance because I liked the people but didn't think they had enough for me to do. I was the sole DBA for one production database that ran on an Ultra 2 (2 CPUs, 54G of disk, 512M). The telecom company I came from had three E5500's (six CPUs, 2G RAM, 600G disk). In fact, they had more than enough for me to do.

My first project at the new company was to move approximately half of the schemas in the production db to a new server. By the end of the first year, I was managing four db servers. Today, my team of three manages about a dozen db servers and almost two dozen instances. We've gone from a little six-pack of disks to a 3+TB SAN.

My second project was to setup a backup & recovery plan. Good thing, too, because about 6 months later we did a full recovery of one of our major production systems. Fortunately, we only had 3 un-planned recoveries until the blackout in August 2003. Recovered 12 databases that night.

Now MySQL and Linux are at the forefront of of my knowlege adventure. Maybe six years from now our MySQL databases will outnumber the Oracle ones...

Using vacation

I try to be a good corporate citizen. When I'm out of the office I use the vacation program to automatically reply and let people know I'm out of the office. Somehow, I think the 9.2.0.7 patchset has figured out how to look at the DBA's .forward file and know when to have problems. No matter what you think of me, I don't relish the idea of recovering a 600G database using Juno free dialup from Aunt Sally's house.

Using MySQL's LOAD DATA LOCAL

We're starting on a new project using MySQL that will bulk load CSV data once a day and then users can report on it whenever they want. In the days of old (ie. Oracle), we'd simply setup a load job using SQL*Loader or use external tables. In MySQL, loading data is just an extension of SQL using the LOAD DATA command. Since my data was going to be distributed, I wanted
to explore Mike Hillyer's suggestion of using the LOCAL option of LOAD DATA.

First, I created a small table called “testload” :

mysql> create table testload (empid integer, name varchar(20),
bonus integer);
Query OK, 0 rows affected (0.02 sec)

I then decided to try it from the server to verify everything worked as expected:

mysql> -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use user1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
|    1 | jeff  |   30 |
|    2 | user1 |   20 |
|    3 | gail  |   60 |
|    4 | bob   |   40 |
|    5 | john  |   70 |
|    6 | jim   |  100 |
+------+-------+------+
6 rows in set (0.00 sec)

mysql> delete from testload;
Query OK, 6 rows affected (0.00 sec)

Now I know it works as the root user. Lets try as somebody else on the server. First, I check to make sure I have FILE privilege:

mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
1 row in set (0.00 sec)

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'sql1' (using
password: YES)

Hmmm, I don't really get this one since I should be covered by the '%'. Have to investigate that later, but lets grant permission on this host anyway.

mysql@sql1 $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant file on *.* to 'user1'@'sql1' identified by 'nopassword';
Query OK, 0 rows affected (0.00 sec)

Let's check the privs and try again from the same user:

mysql@sql1 $ mysql -u user1 -h sql1 -P3322 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> select host, user, password, file_priv from user
> where user = 'user1';
+------+-------+-------------------------------------------+-----------+
| host | user  | password                                  | file_priv |
+------+-------+-------------------------------------------+-----------+
| %    | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
| sql1 | user1 | *2309AA61C73F02E54890747EAD6FFCB927A66565 | Y         |
+------+-------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

Now that I have given myself privileges on the server, it should
work, right?

mysql> load data infile '/home/users/jeff/foo.txt' into table
testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0


mysql> select * from testload;
+------+-------+------+
| id   | name  |bonus |
+------+-------+------+
| 1    | jeff  |  30  |
| 2    | user1 |  20  |
| 3    | gail  |  60  |
| 4    | bob   |  40  |
| 5    | john  |  70  |
| 6    | jim   | 100  |
+------+-------+------+
6 rows in set (0.00 sec)

Sure enough, that did the trick. On to loading from a client:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> quit

Now what? I go back to the documentation and re-read about the
parameter local_infile. I'm pretty sure I set it, but lets check
anyway:

mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

That's what I thought. I went over the docs once again and saw a
mention of the local-infile argument to the mysql client. So I tried
that:

host1:/home/users/user1/tmp $ mysql -u user1 -h sql1 -P3321 -p
user1 --local-infile -p user1

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 5.0.18


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> load data local infile '/home/users/jeff/foo.txt' into
table testload fields terminated by '|';
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

Nice. That is exactly what I am looking for. Knowing that you can set
preferences in your .my.cnf file, I setup the local-infile option in
my .my.cnf.

host1:/home/users/user1 $ more .my.cnf
[client]
loose-local-infile=1

As long as I'm at it, why not setup the host, port, and user in my
.my.cnf.

[client]
loose-local-infile=1
host=sql1
port=3321
user=user1

Then, it's a simple command to login.

user1@host1 13> mysql -p user1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

I learned a lot about LOAD DATA during this exercise.

1. The local_infile parameter must be set to 1 in the my.cnf
   file on the server.


2. By default, the mysql client doesn't allow you to load data from the client. You must use the local-client flag or set the loose-local-client flag in your .my.cnf file.


3. You must have the FILE privilege.

How to break the Unbreakable, by Oracle

Beware of those patches you're applying.

Using Resource Profiles

I never had the need to use Resource Profiles extensively. Recently, though, I've had the opportunity to investigate this feature.

First things first, the resouce_limit parameter must be set to TRUE. You can either set it in the init.ora or via ALTER SYSTEM.

Next, you create the profile and assign limits to it. Read the descriptions carefully, though, some of the resource parameters may sound self-explanatory, but aren't. For example, you would think SESSIONS_PER_USER would mean the number of times a particular user can login. In fact, it's the number of concurrent sessions that can run at one time.

SQL> create profile really_small limit
2  sessions_per_user 1
3  cpu_per_session   100
4  cpu_per_call      100
5  connect_time       5
6  /

Profile created.

Then you assign the profile to a particular user:

SQL> alter user jh profile really_small;

User altered.

Just for kicks, you can check that your profile is assigned to your user.

SQL> select username, profile from dba_users where username = 'JH';

USERNAME     PROFILE
------------ ---------------
JH           REALLY_SMALL

SQL> select resource_name, resource_type, limit
2  from dba_profiles
3  where profile = 'REALLY_SMALL';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ------------------
COMPOSITE_LIMIT                  KERNEL   DEFAULT
SESSIONS_PER_USER                KERNEL   1
CPU_PER_SESSION                  KERNEL   100
CPU_PER_CALL                     KERNEL   100
LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
IDLE_TIME                        KERNEL   DEFAULT
CONNECT_TIME                     KERNEL   5
PRIVATE_SGA                      KERNEL   DEFAULT
FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
PASSWORD_LIFE_TIME               PASSWORD DEFAULT
PASSWORD_REUSE_TIME              PASSWORD DEFAULT
PASSWORD_REUSE_MAX               PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
PASSWORD_LOCK_TIME               PASSWORD DEFAULT
PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

Your user connects to the database, starts running his monster query, and is promptly disconnected:

$ sqlplus jh/jh@mydb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Apr 4 20:34:23 2006

Copyright (c) 1982, 2005, Oracle.  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> select count(*) from all_objects, all_objects, all_objects;
select count(*) from all_objects, all_objects, all_objects
                 *
ERROR at line 1:
ORA-02392: exceeded session limit on CPU usage, you are being logged off

Sweet.

New Blog

Welcome Beth to the Oracle Blogging community. Check out her blog, Confessions of a database geek.

Oracle SQL Developer

I got used to using Oracle's Raptor when it first came out. It had some quirks, but in general it filled a niche that was missing in the Linux/Unix development environment.

I have installed the production version of Oracle SQL Developer (aka Raptor) and have let a limited group of users know about it. It seems like a lot of the Java exception errors have been fixed and the pointer control is pretty good.

One thing that I found was it is registers a funky PID in v$session.process when you login. The v$session view usually shows a fake PID (1234) for other JDBC Thin sessions, but somehow SQL Developer registers the process id as "pid@hostname". That definitely helps in identifying sessions.

OS Authenticated users don't work, but I wouldn't expect them to work with the Thin driver anyway. Maybe there's an option to use the OCI driver where available?

If the beta users find it stable, I'll unleash it on the general public Monday.

Community Support

I truly believe that you can get great advice from the community at large in technology matters. I, myself, have received lots of advice from quality sites and qualified people over the years. The first time I posted on c.d.o.s I received a reply from some guy named Tom Kyte that suggested I use a new feature of Oracle 8 called "Instead-of" triggers.

I try to make a meaningful contribution where I can. I think there's a lot of people out there that foster this commuity spirit. That's why I was a little frustrated when I saw Tim Hall's blog this morning. Here's a guy who has more than enough things to do and is working on a draft article on how you might install Oracle on FC5 that happens to get indexed by Google. And then he gets lambasted in the newsgroups. Not cool. Not cool at all....

Comparison of MySQL and Oracle Client install

In my environment, I like to install the server software on the server and install a client version of the software in a common area. If you need to link to certain libraries or use programs to connect to the database, I'll put them in /usr/local for everybody to use. My authentication is in the database, so I don't care if you can run a program but not connect to the database.

It's no secret that I'm a MySQL newbie and I've had my share of issues installing the MySQL client. That was kind of a special case since I wasn't using a mainstream compiler, but even so, I was able to get the problem fixed in relatively short order.

One of my tasks lately has been to upgrade the Oracle client to version 10.2. I've installed the client software a couple dozen times and the server software maybe 100 times if I want to be conservative. So when I fired up the installer, I wasn't expecting many surprises.

As expected, I installed the base 10.2.0.1 release in about 20 minutes. I then applied the 10.2.0.2 patch on top of it in another 10 minutes. I ran sqlplus as oracle and was able to connect to a database, so I pushed it out to a group of test users.

Two seconds later: "Ah, sqlplus doesn't work."

"I just ran it, your environment variables must not be setup correctly."

"Ah, sqlplus still doesn't work."

So I logout as oracle and try it from my workstation:

sqlplus system@foo.bar
ld.so.1: sqlplus: fatal: /usr/local/oracle/lib/libsqlplus.so: Permission denied
Killed

sonofa...WTF can be wrong?

Sure enough, I didn't have permission on the library. I logged in as oracle, and I had permission on the file, but world didn't have any permissions.

Hmm, That never happened before. I must have screwed something up.

I re-installed and had the same problem.

I logged a TAR to Oracle support and seems like it's a known bug (4516865).

Another great example of why you get so much more value with Oracle than MySQL. Sigh.

Working on my ORA-00202

I’ve been going crazy fighting an ORA-00202 error for the last couple of weeks.

Whenever we ran a Level 0 backup, some of the monitoring processes were getting a timeout while waiting on the control file. The worse part is the error wasn’t consistent. About 90 minutes into a 3 hour backup we would get this error message thrown in the alert.log.

The message in the trace file seemed to suggest one of the control files was corrupt. Our initial research showed that when we brought the database down in immediate mode, that “diff” reported the control files were indeed different.

My first inclination was that somehow rman was corrupting the control file during the backup. I know, pretty unlikely, but that’s where the symptoms pointed. I created a TAR and provided some info to OCS about my problem. I also learned about the debug option of rman (which is pretty cool, although I couldn’t tell you how to read the file). OCS confirmed that rman wasn’t corrupting the control file (like they would say it was anyway). They suggested it was our monitoring software that was causing a problem. Since I have this software running against multiple databases and this is the only one we’re having a problem with, I didn’t think that made much sense either.

I started investigating the workload on the server. This instance is not used very heavily and is on a pretty beefy server. While the backup was running, the vmstat statistics showed that the filesystem cache kept increasing and the available memory was decreasing. Eventually, the system exhibited classic swapping symptoms, but without the typical indicators.

“Hmm”, I thought, “that shouldn’t happen because of direct I/O.”

Just for kicks, I verified that direct I/O was on by shutting down the database, starting it up in NOMOUNT, starting strace on the dbwriter process, and opening the database. I know that if the datafiles are opened with the O_DIRECT flag, you’re using direct I/O.

But wait, there was no O_DIRECT flag in the trace file.

Aha. I searched Metalink and sure enough, 9.2.0.7 doesn’t include direct I/O out of the box. I already had the filesystemio_options=setall, but apparently, you have to apply two patches on top of 9.2.0.7 for Linux. I applied the two patches in a new $OH, started the database in the new $OH, and retested my backups. Sure enough, the ORA-00202 error went away. My theory is that this "semi-swapping" caused extreme slowdowns on the system which caused the monitoring software to experience long waits for the controlfile. I’m still not clear on why the control files were different when I brought the db down, but the database could be started with either the “good” or the “bad” control file.

How the mighty have fallen

I spent a fair amout of time in a regional telecom company back in the mid to late 90's. We did several services (paging, internet ISP, cable, frame relay, etc.) but the big cash cow was long distance.

The Telecommunications Act of 1996 (TA96) was supposed to change all that. That bill opened up the stranglehold that the baby bells had on the local markets and was supposed to offer up competition for the consumer. By the time I left that company in 2000, the telecom industry was one of the strongest industries in the country.

A couple years later, the company I worked for got gobbled up by some company in Georgia.

The mighty AT&T was bought by SBC, a company it originally spun off.

The troubles at WorldCom/MCI are well publicized.

Now I see Lucent and Alcatel are thinking of getting together.

All this in six short years. Wow, how time flies.

At least it's not Dook...

OK, the Tarheels might not be National Champs this year, but neither will the Dookies.

Necessary Changes

You may notice the blog has undergone a change.

When I checked today, I only saw the links for the feeds. I republished the template again, and still only got the links. I quickly changed back to one of the standard blogger templates and added my links back in. I think it's OK now, but will monitor closely the next couple of days.

Blogspot Issues

I thought maybe I was the only one having issues getting to the blog yesterday until the geek pointed out he was having problems. Then I noticed my RSS reader didn't have my new post from yesterday.

This morning I got a message from Mr. Parallel Query indicating my links were screwed up. Doug suggested I republish to fix the links and sure enough, that worked.

I checked my stats for yesterday and visits were way down. I hope blogspot's problems are fixed from the last couple days. Then again, what should I expect for free?

Reading "Cost-Based Oracle Fundamentals"

I started reading Cost-Based Oracle Fundamentals by Jonathan Lewis last night. In the very beginning he suggests reading the book in its entirety as a first pass and then reading it again for more detail.

"Bah", I thought to myself, "I need to get on to Tom Kyte's book, I don't have time to read it twice."

On page 2 I pulled out the highlighter.

Page 6, eight highlighted passages.

By page 9 I had abandonded the highlighter and proceeded with casual reading. Tom will have to wait.

Interviewing Pet Peeves

Interviewing is one of those areas that you become pretty good at if you're in IT. Over the last couple of years, I have come to interview quite a few candidates for various job. Here are some of my pet peeves of interviewing candidates:

5. When I schedule a time for you to come in, don't arrive 30 minutes early. Go get a cup of cofee or read the newspaper in your car. I specifically scheduled you at that time because I've got a bunch of things I have to do first thing in the morning and I need to make sure everybody is off and running before I take 2 hours out to talk to you.
4. If I ask you to come in, don't ask me for directions. Sure, I'll give you the address, but we're all big girls and boys and we know how to use Mapquest, right?
3. I'll take about 2 minutes to go over the company and what we do, but I expect you to have some background on the company before I talk to you at length. We have this thing called the Internet now.
2. Don't ask me specifics about benefits. Sure we have a 401(k) and a Medical plan, but I don't have the slightest idea about the specifics. That's why we have HR.
1. When I schedule a time to call you, don't give me your cell phone number. I'm calling you about a job, not to go out for a beer. If you don't want to talk at work, I'm cool with that. In fact, I'd rather call you at home where we can let the conversation go where it wants to.

Oracle Backup (aka Reliaty)

From what I can gather from this (starting on pg. 2) and this (pg. 20), Oracle has a product called "Oracle Backup" that used to be a backup product called "Reliaty". I can't find a lick of information on it though. If anybody can point me in the right direction, I'd appreciate it.

Update: 3/14/2006
Straight from the source, I like it: (posted in comments)

Oracle Secure Backup is scheduled to release by end of FY06 (May 06). I apologize for the confusion, but stay tuned to OTN main page over the next few weeks for more information on Oracle's newest data protection product.

Thanks!

Posted by Tammy Bednar

From the slides, it looks like it is a complement to RMAN, which would make sense. Now I'm hoping it can fill a void that NFS has left in my backup strategy. (Hopefully, the "low cost" in the slides will take the same path as the recent products from Oracle and come out as "no cost").

That time of year

It's that time of the year in the US where passionate College Basketball fans glue themselves to the TV all weekend long. The field of 65 has been drawn as we begin scrutinizing the brackets for the office pool. Yes, your employers probably cringe during this time of year as we use company resources to track our picks and scores, but hey, it's March Madness.

I'm a Tarheel at heart, so I can guarantee you they'll be one of my final four picks. All my neighbors will be rooting for the Huskies in my backyard, and the Orangemen are a dangerous team. But lets face it, after the Tarheels, I'm rooting for whoever is playing the Dookies.

Anybody got an E-Size DeskJet to print your brackets out?

I'm Tops on Google!!!

Search for dbms_stats.alter_database_tab_monitoring on Google, and I come on top!

Interesting ORA-00202

I have been getting an interesting ORA-00202 errors thrown to my alert.log during a backup on one of my databases. It's only happened a couple of times, but I am curious about the cause. The text of the ORA-00202 error is:

$ oerr ora 00202
00202, 00000, "controlfile: '%s'"
// *Cause:  This message reports the name file involved in other messages.
// *Action: See associated error messages for a description of the problem.

While the rman level 0 backup is running, my monitoring software encounters the ORA-00202 error when trying to query v$log and dba_data_files. The message in the alert.log is:

Errors in file /oracle/admin/db1/udump/db1_ora_5953.trc:
ORA-00202: controlfile: '/u01/oradata/db1/control01.ctl'

When I look at the trace file, I see:

*** SESSION ID:(11.32) 2006-03-08 17:44:26.211
***
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block -
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.0017641a seq: 0x1 flg: 0x04
consistency value in tail: 0x64191501
check value in block header: 0x24a8, computed block checksum: 0x3
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
*** 2006-03-08 17:44:27.245
ksedmp: internal or fatal error
ORA-00202: controlfile: '/u01/oradata/db1/control01.ctl'
Current SQL statement for this session:
    SELECT file_name,
           tablespace_name
    FROM   dba_data_files
    WHERE  autoextensible='YES'
    AND    maxblocks-blocks <= increment_by                             AND    maxblocks-blocks <> 0

"Ah Ha", I say to myself, "must be a corrupt control file." I shutdown the database immediate and diffed the two control files. Although there were the same size, diff reported that they were different. Sure enough, a corrupt control file. So I copied my good control file over the bad one and restarted my database.

A couple days later, same problem. Same control file. The same control file seems unlikely unless there is an OS related error on that filesystem. I scour the messages file and there's no I/O related errors. I shutdown the database, diffed the control files again, and sure enough, they're different.

I recopied my good control file over my bad one again, started the database, switched logfiles, and shutdown immediate. Then I tested my theory that the control files should be the same and diff reported that they were the same. This time, however, I moved control01.ctl to another filesystem just for kicks to see what would happen.

My theory is that my monitoring software can't get a consistent view of the control file while rman is writing to it. At this point I'm unsure if the control file is corrupt or Oracle is just throwing the wrong error. We'll see what Oracle says...

Check back in 5 days (if I'm lucky)...

Update: I've done some testing and come to some conclusions.

A good year

No matter what their record is at the end of the year, as long as Carolina beats Duke at least once a year, it's a good year.

Andy Katz
Game Recap

Getting Burned

The good thing about getting burned once or twice in the forums is you can learn from the experience. For example, the other day somebody wanted to upgrade a 1TB database from 9i to 10g. At the same time, they were changing platforms.

The initial discussion started along the lines of “You should investigate Transportable Tablespaces. I think they are supported across platforms in 10g.” Decent enough idea, I suppose.

Then this particular user chimes in and says:

Db upgrade accross platforms has Exp/Imp as the only option available.

Ah, here’s my chance. Those were my exact sentiments a couple years ago until somebody pointed out you could setup both databases and copy data with a database link. Another person said you could unload/load comma separated data. OK, some maybe not very practical, but still a possibility. Just for the sake of argument, I posted:

100% UNTRUE. You could unload/reload ASCII files, you could use dblinks, you could use Quest's replication product.You should be careful when dealing with absolutes.

OK, maybe a little harsh, but to the point. So Mr. Exp/Imp pipes up and says:

Sure Jeff
Following are excerpts from Metalink doc 277650.1:
Quote:
The Export and Import utilities are the only method that Oracle supports for moving an existing Oracle database from one hardware platform to another. This includes moving between UNIX and NT systems.

He thinks he as me against the ropes now. If Metalink says it, it must be true. To which I reply:

surprise, surprise, metalink is wrong.

Do I get enjoyment out of proving somebody wrong? Not usually. But there is a lot of bad information out there and I don’t like perpetuating it.

Another Friday

Here I sit, another Friday night in the office.  Tonight’s drama is saying goodbye to the last remaining Oracle 8.1.7.4 database.  

About 18 months ago, we were like firecrackers; an upgrade most every weekend.  We knocked out most of the small (<100G) ones using DBUA.  Then we started on the mission critical stuff; lots of testing and diagnosing query plans that changed for the worse.  Through it all, we learned a lot about 9i and how it was different than 8i.  

Our final database has been a royal pain.  The first time we attempted the upgrade, we ran into OS errors and found out our version of DBUA wasn’t compatible with the OS version.  Instead of upgrading an old machine, we decided to move it to an under utilized machine.  So we attempted again.  Then something went wrong with the upgrade.  So we restored, and researched the problem.  Tonight, (fingers crossed), we’ll be off 8.1.7.4 for good.

On to 10g.

Certification

I live within 5 minutes of my local Borders bookstore. I'm pretty familiar with the computer selection there and have mostly been satisfied with their selection. Looking for some variety, I went to a not-so-local mega Barnes & Noble today to peruse their computer section.

I spent quite a bit of time looking over their selection and noticed something missing, Oracle Certification books. I got to thinking and my local Borders only had a couple books on Oracle Certification and about 3 shelves on Microsoft Certification.

Just a couple of years ago, books for the OCP were plentiful and filled a lot of shelf space. I remember there were two or three publishers with their own series.

Is it just my location of the country? Does it say something about Oracle's certification process? Or has the demand for OCPs weakened over the years?

Lessons the hard way, Part IV

While wrapping up the finishing stages of the Oracle Applications Upgrade from you know where, I discovered the sort command works different on Solaris and Linux.

Take for example, a simple little directory listing:

jake$ cat sortit.txt
prodappl
prodappl/abm
prodappl/APPLSYS.env.tmp
prodappl/abm/11.5.0/admin
prodappl/APPSORA.env
prodappl/PROD.env
prodappl/abm/11.5.0
prodappl/abm/11.5.0/admin/driver
prodappl/APPLSYS.env
prodappl/abm/11.5.0/admin/driver/abmcon.drv

On Linux, I sort it:

jake$ cat sortit.txt | sort
prodappl
prodappl/abm
prodappl/abm/11.5.0
prodappl/abm/11.5.0/admin
prodappl/abm/11.5.0/admin/driver
prodappl/abm/11.5.0/admin/driver/abmcon.drv
prodappl/APPLSYS.env
prodappl/APPLSYS.env.tmp
prodappl/APPSORA.env
prodappl/PROD.env

On Solaris, same command:

woody# cat sortit.txt | sort
prodappl
prodappl/APPLSYS.env
prodappl/APPLSYS.env.tmp
prodappl/APPSORA.env
prodappl/PROD.env
prodappl/abm
prodappl/abm/11.5.0
prodappl/abm/11.5.0/admin
prodappl/abm/11.5.0/admin/driver
prodappl/abm/11.5.0/admin/driver/abmcon.drv

Hmm. I did a "man sort" and found my answer was to force a sort order using the -f flag.

Initial Impressions on Raptor

I'm a PL/SQL Developer guy, no question about it. It gives me the ability to write code pretty quickly and the debugger is second to none. I'm also a Unix guy. I've been running Linux at home for about three years and Solaris is the only desktop I've ever had at work. In order to run PL/SQL Developer I'd either have to use a Windows emulator or a Sun PCI card (PC on a Sun).

Until now.

Oracle's Project Raptor is quite a tool. I installed Project Raptor on my desktop at work this week and I'm hooked. The interface is a little awkward and there's a couple things I don't like about it, but it's definitely a great query tool. I've only looked at the query editor this week (and I haven't totally read the docs yet), but here are my impressions:

• Pointer control isn't quite there yet. Sometimes my pointer turns to a text tool when clicking on a menu item. Sometimes it's an arrow when it was supposed to be a text tool. Sometimes it just wasn't there.
• Lots of java exceptions.
• The explain plan tool is neat once you figure out how to use it.
• When you run the query, it retrieves the whole resultset. It would be nice to retrieve a subset and let you page through the results. Sure, I know about ROWNUM, but I just expect that automatically.
• When you retrieve a resultset, the columns always come back too small. You have to expand them almost every time.
• Most query tools will display a date as a date if the time component is the default time and the entire timestamp if not. In SQL*Plus fashion, the dates show up in NLS_DATE_FORMAT. No biggie, just different.
• I want to see the tables I have permission on under "Tables", not "Other Users>Tables".
• Forget about using OS Authenticated users, it uses JDBC Thin.
  

I know, some pretty nitpicky stuff. Overall, though, I like the query tool and I am using it almost exclusively.

Setting up HTML DB

We're starting to setup HTML DB in some of our databases. We think it will be a good tool to allow our developers to bang out some quick-and-dirty web applications very rapidly.

From a DBA perspective, it's pretty simple to setup and administer. However, I have a philosophical problem with the way workspaces are setup and used.

Traditionally, I'll setup an "owner" schema that holds the tables, packages, objects, etc. that an application may use. Then I'll create one or more "user" schemas that access the "owner's" objects. This method lets me grant the least amount of privileges needed so the application can run. I can also encapsulate business logic and relationships in the application's packages to hide the complexity to the developer. If a developer wants to call my "doit" method of my "stuff" package, he just calls OWNER.STUFF.DOIT().

My first problem with HTML DB is that it expects to have the workspace mapped to the owner schema. While I can place synonyms and views in the "user" schema to appear that the "user" owns the objects, this seems like a kludge. What if I have 50 user schemas, I have to create the same synonym 50 times? [disclaimer]I know about public synonyms, I'm just trying to illustrate a point.[/disclaimer]

My other problem is HTML DB wants to install the demo tables and application into every freaking workspace. I don't want my "user" schemas to have the ability to create tables and I surely don't want DEMO_* as a table in my production system.

I know there are other people out there running HTML DB. Do you map your HTML DB workspaces directly to the owner of the tables?

What kind of person?

What kind of person breaks your car window just for fun?

I went to the gym this evening and the area where I usually park was full. I circled the lot and saw some teenagers pawing through the dumpsters behind the building, but I didn't think anything about it. By the time I got back to my parking area, there were still no spaces, so I parked in a parking deck nearby. I parked under a light just like you're supposed to do and went in and started my workout.

Twenty minutes into my workout, a message comes over the loudspeaker.

"Will the owner of the car with license plate number XYZ-123 please report to the front desk."

Damn, I must have left my lights on.

I grabbed my keys and headed to the front desk. That's where they told me somebody reported my car was broken into. I went out to find a broken driver side window and my glove box gone through. In fact, the radar detector was still hanging on the windshield and there was loose change in the cup holder.

Great.

There was glass everywhere. Inside the car. Outside the car. In fact, there was so much glass down the window, I can't even open the door.

The guys at the gym called the cops and they showed up about 20 minutes later. The cop asked me a couple questions, ran my plates, and gave me a card that had my case number on it. He said I could pick up my Police Report in 4-5 days.

"I'd like to tell you we'll catch the guy that did this, but that'll never happen" was the only thing he could say as he drove away. Maybe I've been watching too much CSI, but no fingerprints? How about a computer simulation of the glass pattern so we can tell how tall the person was? Right. He didn't even get out of the car.

What a hassle. Now I've got to get to a glass shop. On a Saturday. Oh, and it's supposed to rain tonight.

Sure, it was probably those kids. Or maybe some other kids hanging around. What kind of person gets their jollies by breaking car widows?

CSS and XHTML

I got a gift certificate to Borders for Christmas last month. Since I live within walking distance of the nearest Borders bookstore, the gift certificates don't last very long.

I made a bee-line for the back of the store (as usual) and went straight to the Oracle section. We've done some initial prototypes with HTML DB and I was looking for a good book to help expand my knowledge. Come to find out, there's only one book on HTML DB out, but the one I was looking for doesn't come out until the end of the month.

While the wife was perusing the romance novels, I started thumbing through the CSS and XHTML selections thinking I might spruce up the blog. I got about 10 pages into the first book and couldn't follow it. I thought maybe the book was over technical, so I picked up another one. I started reading that one and after a while, just didn't get it either. Maybe I'll just stick with the templates...

State of the IT Job Market, Part II

Well, appearantly MySQL and Oracle DBA's are not as active as the general IT Population. According to my unscientific poll, the majority of you are looking for a new position, just not actively looking.

State of the IT Job Market

Seems like things are heating up in the IT job market here in the Northeast. In the last 30 days, I know of several people that have left stable, secure positions for more lucrative jobs. On the other hand, a Fortune 500 company in the area announced several IT development layoffs of which I personally knew two people who got "downsized".

The Computing Technology Industry Association recently conducted a survey that pointed out 60% of IT workers are looking for new jobs. Of those 60%, a staggering 81% of those job seekers consider their job searches "active".

Wow, nearly 50% of IT workers are actively searching for another job. I used to think thinks like perks and training were the key to keeping employees happy. I have found out that those things are nice, but they don't keep a person loyal to the company. Whether you are an Oracle DBA, MySQL Developer or Network Admin, I believe a person needs to be kept technologically relavent, feel challanged, and most of all, be appreciated. Maybe that's just how I feel, but it seems to be working.

Are you looking for a new position?

Yes, Actively looking for a new position in my company.Yes, Actively looking outside my company.Yes, keeping an eye out, but not active.No, Not happy in my current position but I have high hopes.No way, I'm happy where I am.Unemployed.

Free polls from Pollhost.com

Surviving the bad boss

Everybody has experienced the bad boss once or twice in their life.

My bad boss was on one of my early programming jobs right out of college. She was the classic incompetent manager with a side of egocentric thrown in. I'd get done writing a program or report and verified it worked the way it was supposed to. I would then put the program into production and let the users verify the report worked exactly as expected.

Unbeknown to me, my boss would "fix" my code in the middle of the night and put it into production without even testing it. Of course, I got a call in the morning saying my code dumped core or broke the morning build. I couldn't understand it. That's when I started learning about diff, checksum, and about Sun auditing. I told another manager what I found and sure enough, this wasn't the first time.

I dealt with it mostly with CYA until I could get out.

Send out a search party

We're sending out a search party for my Oracle Support Engineer. I've pinged a SR (aka TAR) for nine days straight without response. Good grief.

How well do you know your US History

Interesting little test. I missed 4 (those darn amendments).

I wanna be like HJR

OK, so it's not as cool as HJR's, but decent enough, eh? Maybe I need some sunglasses.

Cubeville

I had to snicker at an article in Computerworld entitled Private Office or Cubicle: The Debate Goes On. Maybe the word "debate" is a little strong, yet it's still a controversial subject at many companies.

Except for a brief stint as a Project Manager, I've always shared my space with somebody. Mostly cube farms, but occasionally an office with another party. When I work at home, I seem to get more done. Is that because at home I concentrate on tasks that I can do remotely or because I don't have interruptions? I don't know. I do know I like poking my head over the partition and asking the person next to me a question.

Sure, there are times I'd like my own office; getting into a discussion of how Oracle or MySQL works, annual performance reviews, and interviewing. But for those few times I need an office, there are plenty of conference rooms available.

The debate goes on: Cube or Office?

The Definitive Guide to MySQL 5, Third Edition

I went on a buying spree of technical books a couple months ago. One of the MySQL books I picked up was The Definitive Guide to MySQL 5, Third Edition.

Let me start off by saying I'm a Database Administrator. I'm concerned with one thing; the database. I want to know how it works, how to back it up, how to restore it, how the locking works, how to secure it, and how are transactions handled. I don't care how you connect to the database or what tools you use. I'm beginning to understand MySQL, but know I have plenty more to learn.

The book started to get my interest around Chapter 11 - Access Administration and Security. Security on MySQL is a little different than I'm used to, so I got a lot out of this chapter. I also received a lot of information out of Chapter 14 - Administration and Server Configuration. In fact, this chapter has a pretty extensive discussion on logging and administering the different table types that made the book worth purchasing.

The other sections of the book were excerpts on PHP, connecting MS Office to MySQL, using Java and C++ with MySQL, introductory SQL, introductory database design, and a whole bunch of other things I wasn't looking for on a book about MySQL.

Don't get me wrong, the book was well written. I read the whole book (even the stuff I don't care about) and found it very easy reading. Michael Koffler explains the concepts very clearly and supplements with many examples. If you are a developer who doesn't know anything about what databases mean and you've been thrown into a MySQL environment and need to be a jack-of-all-trades, you will probably get a lot out of this book. Maybe I'm just not the target audience.

Glory Road

When I saw the trailers for "Glory Road" a few months ago, I immediately put it on my list of movies to see. Glory Road is the inspirational story of how Coach Don Haskins took the first predominantly African American Division I basketball team to the National Championship game.

In 1966 the basketball powerhouses were Duke, Kentucky, and Kansas (sound familiar?). The virtually unheard of Texas Western (now University of Texas El Paso) had never been to the final four, let alone the title game. The story chronicles the hardships encountered by both the players and coaches on the road to history.

I won't ruin the ending for you, but it's definitely "two thumbs up" for Glory Road.

Goodbye Netgear

You may remember I was having issues with my WAP last fall and I asked for recommendations on a new one. I thought all my problems were solved when Santa brought me a new Netgear router.

Unfortunately, I haven't had very good luck with my Netgear WGT624V3.

The initial setup was a snap, but the troubles started when I tweaked some settings. The first problem was with WEP. I could setup WEP, but neither laptop nor my PDA could connect to it. I certainly wasn't going to run unsecured in a high-density area. This WAP also supports WPA, so I enabled it and got the two laptops talking to it. I'm out of luck with my PDA, but that's something I could live with.

After a couple of days, the laptops started disconnecting periodically. The first fix on the Netgear forums suggested some settings to tweak.

I also found a fix on the Netgear forums suggesting my problem was thermal and I should do this:


Um, I don't think so. I did some more research and found out that people occasionally have problems with the 108Mbps settings, so I turned that off. Wireless connections were somewhat stable after that.

I've noticed over the last week that my desktop computer occasionally says "Network cable unplugged" for about 15 seconds and then it goes away. OK, maybe the cable is bad. I swapped all the ethernet cables out with brand new ones from Staples. After that was done, I still got the message.

Three strikes and you're out. Fortunately I had my old Linksys router around so I hooked it back up and got desktop service back. The Netgear router is going back to the store tonight. If they don't take it, it's going to ebay. If I can't sell it there, it's going to the skeet range.

Maybe because I'm in IT I expect things to always work they way they're supposed to. (Lord knows, I work with Oracle, so that's always the case). What would the normal home consumer do?

Waiting on dbms_job

I've been using dbms_job more and more lately. This weekend I had an interesting problem.

Friday's job queue was running just fine. On Friday evening, I put about 200 jobs into the queue to run immediately and expected them to run for a couple hours. I had job_queue_processes set to 4, so I knew that only four jobs would be running at the same time.

About 75 jobs into the run, no jobs were running. Nothing was in dba_jobs_running. I could see that jobs were still in the queue. I wasn't really sure what was going on, but I couldn't bounce the instance.

I finally decided to set the job_queue_processes to 0 with ALTER SYSTEM and waited until I didn't see the query job coordinator process (cjqN) anymore. I then reset the job_queue_processes to 2. I didn't want to set job_queue_processes to 4 as other stuff was going on by this time. CJQn then restarted and continued processing the jobs in the job queue.

Why I hate Windoz, reason #349 retracted

My number of reasons to hate Windoz stays at #348.

I got suggestions from Nuno Souto and Nial Litchfield that maybe I could install Windoz 2000 directly by just popping in the NT CD when the installer asked for it. So I thought I'd give it a try.


I searched around the internet for articles suggesting how I might do this and came upon an article explaining how to use the makeboot.exe program on the W2K CD. Of course, I could only scrounge up four floppies and two had bad sectors so makeboot.exe couldn't use them.

So it's off to Wal-mart to get some floppies. They had one blister pack with 3 floppies for $3.49. I needed four anyway, so I drove a little further and stopped at Staples. Aha, 25 floppies for $3.89. In all honesty, though, they only had about 10 packs of 25 floppies on the shelf. About 6 feet of CD-Rs, CD-RW, DVDs and 2.5" disks, but only 10 boxes of floppies. Are we seeing the ever populate floppy going away?

Anyway, back at home I run the makeboot.exe and create my four boot floppies and start the install. Nowhere did the install process even ASK me for the NT disk. All in all, it worked out pretty good.

Don't get the wrong idea here. I still hate Windoz, but I only have 348 reasons now. (until I install the 2 year old modem tomorrow, anyway).

Why I hate Windoz, reason #349

I recently installed Red Hat FC4 on my spare PC at home. By the time I installed everything I wanted on it, the performance just wasn't what I expected. Then again, I'm not really sure I expected much out of a 733Mhz Optiplex with a 16M video card and a 20G disk. I thought it was OK with RH9, but with todays computers I knew it could be better. Upgrading it was out of the question, because I don't really want to put that much effort into it. So I decided to switch it back to Windoz and pass it on to a family member.

Problem is, I bought the PC with Windows NT 4.0 on it and then upgraded to W2K a little while later. I searched the internet and found a couple places where I thought I could just install W2K from CD-ROM. Rather quickly I found out that since I was using an upgrade CD, I had to install an upgradable OS first. I searched for my NT disks and re-installed it. Of course with NT I had to create a 2G partition to install on and then an 18G partition for everything else. Then I popped in the W2K CD and ran the upgrade and wiped out the NT partition. Then I want to format the 18G partition and Windoz rolls on for about 60 minutes and then exits with "Unable to format partition".

OK. What's that mean?

So I try to format it again as an NTFS filesystem. "Unable to format partition" again.

OK.

So I try to format as FAT32 and voila, it's formatted. I'm not really interested in having a FAT32 filesystem, so I try to format it again with NTFS and it formats fine. Go figure.

Good training value

If anyone is going to be in the Tri-state area in January, there is a day with Tom Kyte sponsered by the New York Oracle Users Group on January 18th. The talks look like a perfect way to get immersed in 10g. I don't think you could pass it up for the price ($200 non-NYOUG members/$150 members). Alas, I have another engagement that day, but am definitely sending at least one person.