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.

Funky redo behaviour in 10g

I'll admit it, I don't have that much exposure to 10g just yet. I'm excited about it, I just don't have much experience on it.

We put up a development database on 10.2 a couple months back just so we could start getting experience on 10g. As we add more and more development schemas to this database, we're getting a taste for the new features.

We've been getting a disproportionate number of "Checkpoint not complete" messages lately. Sure, there's pretty heavy write activity going on at the time, but not enough to fill all my redo logs. I have six groups of 128M logfiles with two members each. During the heavy write activity, my logs get switched a few times in a minute. However, I know my log_archive_dest can keep up with the write activity, so there should be no problem archiving them that fast. From the alert.log:

Thu Dec 29 19:05:37 2005
Thread 1 cannot allocate new log, sequence 9945
Private strand flush not complete
Current log# 4 seq# 9944 mem# 0: /u01/oradata/demo1/redo04_01.log
Current log# 4 seq# 9944 mem# 1: /u02/oradata/demo1/redo04_02.log
Thread 1 advanced to log sequence 9945
Current log# 5 seq# 9945 mem# 0: /u01/oradata/demo1/redo05_01.log
Current log# 5 seq# 9945 mem# 1: /u02/oradata/demo1/redo05_02.log
Thu Dec 29 19:06:28 2005
Thread 1 advanced to log sequence 9946
Current log# 1 seq# 9946 mem# 0: /u01/oradata/demo1/redo01_01.log
Current log# 1 seq# 9946 mem# 1: /u02/oradata/demo1/redo01_02.log
Thu Dec 29 19:06:42 2005
Thread 1 advanced to log sequence 9947
Current log# 2 seq# 9947 mem# 0: /u01/oradata/demo1/redo02_01.log
Current log# 2 seq# 9947 mem# 1: /u02/oradata/demo1/redo02_02.log
Thu Dec 29 19:07:00 2005
Thread 1 cannot allocate new log, sequence 9948
Checkpoint not complete
Current log# 2 seq# 9947 mem# 0: /u01/oradata/demo1/redo02_01.log
Current log# 2 seq# 9947 mem# 1: /u02/oradata/demo1/redo02_02.log
Thread 1 advanced to log sequence 9948

After the write activity is over, I look at v$logfile and v$log and see that several groups are still ACTIVE.

I then doubled the size of the logfiles and reran the big job that caused the "Checkpoint not complete" error. While the job didn't cause a "Checkpoint not complete" error, it did make three of the log groups ACTIVE, which I kind of expected. However, 20-30 minutes later, I looked at v$logfile and v$log to see almost all my log groups were either ACTIVE or CURRENT.

system@demo1.us> l
1  select l.group#, lf.member, l.bytes/1024/1024 mb,  l.status, l.archived
2  from v$logfile lf, v$log l
3* where l.group# = lf.group#

GROUP# MEMBER                             MB STATUS     ARC
------ ------------------------------- ----- ---------- ---
   1 /u01/oradata/demo1/redo01_01.log   256 CURRENT    NO
   3 /u01/oradata/demo1/redo03_01.log   256 ACTIVE     YES
   3 /u02/oradata/demo1/redo03_02.log   256 ACTIVE     YES
   4 /u01/oradata/demo1/redo04_01.log   256 ACTIVE     YES
   4 /u02/oradata/demo1/redo04_02.log   256 ACTIVE     YES
   5 /u01/oradata/demo1/redo05_01.log   256 ACTIVE     YES
   5 /u02/oradata/demo1/redo05_02.log   256 ACTIVE     YES
   2 /u01/oradata/demo1/redo02_01.log   256 INACTIVE   YES
   2 /u02/oradata/demo1/redo02_02.log   256 INACTIVE   YES
   1 /u02/oradata/demo1/redo01_02.log   256 CURRENT    NO
   6 /u01/oradata/demo1/redo06_01.log   256 ACTIVE     YES
   6 /u02/oradata/demo1/redo06_02.log   256 ACTIVE     YES
                                      -----
Total                                   3072

12 rows selected.

Elapsed: 00:00:00.05

During light load, I would expect one group CURRENT and maybe one group ACTIVE. The other funny thing is the logs show they are archived, which if I look at the log_archive_dest, they are. I have to do some more research, but I suspect there's an init.ora parameter or something that isn't set correctly...

Router and WAP in one

Santa brought me a new NetGear router for Christmas. This particular router has an 802.11g WAP built-in so I don't have to give up an ethernet port for it. The basic setup was really easy and when I customized things like the LAN IP and DHCP addresses it was somewhat easy. The only thing that was somewhat difficult was setting up the WEP. I tried to choose my former Passphrase so I wouldn't have to change the wireless devices, but the router just wouldn't take it because it was the wrong length. The WAP has the ability to run WPA, so I set that up instead. Unfortunately, using WPA precludes me from using my Palm, but browsing on the Palm never really worked well anyway.

The old Linksys WAP is on it's way to the local landfill where it belongs. I'm hanging on to the old Linksys router for a backup.

Interesting take on RTFM

I got a kick out of Andrew Gilfster's blog about RTFM. Maybe I'm a little more cynical, cause I think it's perfectly appropriate in certain aggrevated circumstances to give somebody "RTFM" as an answer.

The "New & Improved" Metalink

I jumped on the new metalink last night and was zipping along pretty good. It's definitely different, but I'm not sure different is bad. Today, however, 6 minutes to login. Three minutes to bring up the TAR query screen (or should I say Service Request). Another 4 minutes to get my TAR back. If they're going to make Metalink the primary interface to customer service, can we at least make sure it is somewhat responsive?

Yummy yum

One of the things that pushed me to upgrade to Fedora Core 4 was suspense of the automatic updates from the Red Hat Network a while ago. It seemed as though every time I wanted to install something I needed to search out the correct RPMs and install them before I could intstall the software I originally wanted to install.

FC4 opened my eyes to a totally new updating method; yum. I did a quick search and found The Unoffical Fedora FAQ which walked me through setting up yum and installing software for it. I installed the updated gcc compiler using yum and it only took a couple minutes to download gcc and any dependencies. "Wow, this is easy", I thought to myself, so I used yum to update OpenOffice.org to 2.0. This is definitely a nice tool to keep some of your general software up to date.

I don't think I'll use yum to keep my main database software updated. For example, I'm going to be doing research on having multiple versions of MySQL on the same system. During this research, I'm going to put MySQL 4.1 in one directory and 5.0 in another. I don't want my 4.1 software automatically updated until I want it updated.

I wonder if Oracle has a yum repository for Oracle Applications? (If you deal with Oracle Apps, you know what I'm talking about).

I think this will be a big time-saver.

FC4 installed

I have the base Fedora Core 4 installed with only a couple issues. When I decided to install the new OS and update my software, I figured I might as well upgrade my Hard Drive. I got an Ultra ATA/133 200G drive and replaced my 20G drive. Of course, I didn't read the package and didn't notice I needed a different I/O card. Back to CompUSA to get the new card.

The second little glitch was I downloaded the ISO images and burned them to CD. Except I didn't realized I had to burn them in a specific way. The second time I figured it out and was able to install right off CD.

The particular distribution I used had Firefox 1.0.4 and OpenOffice 1.9 (2.0 beta) so I'm no further ahead in that respect. Since I was planning on re-installing these packages anyway, it's not a big deal. My first task, however, is to get the MySQL 5.0 source and get Apache configured. More updates to come...

Out with the old, in with the new

I'll have a couple free hours over the next few weeks, so I have decided to upgrade my Linux box at home to RedHat Fedora Core 4. The software on it was really out of date (Firefox 1.0.4, Thunderbird 0.9, MySQL 4.0, OpenOffice 1.0, etc.), so instead of updating everything I figured I'd go the extra step and do everything. Tonight I am spending a couple hours downloading ISO images and burning them to CD. Stay tuned for the progress...

MySQL and icc solved

Thanks to Lenz Grimmer on the MySQL forums. His solution was to use the following variables:

CC=icc \
CFLAGS="-O3 -unroll2 -ip -mp -restrict" \
CXX=icpc \
CXXFLAGS="-O3 -unroll2 -ip -mp -restrict" \
LDFLAGS="-static-libcxa -i-static"

Installing just the MySQL Client using icc

I started out today doing what I thought was a simple task; Install the MySQL client using Intel's compiler. In my environment, we like to install the client software so it is generally available to everyone and the server software on the actual physical box. We feel this gives us the flexibility to upgrade the servers indendently from all the programs.

I've compiled the 5.0.16 software using gcc about a dozen times already and other than a few simple tweaks here and there, it was a no-brainer. When I installed the client, I used gcc instead of the preferred (in my environment) icc.

I started by issuing a "make clean" to remove any builds I did before. I then changed my CC and CXX environment variables to point to the icc compiler and did:

$ configure --without-server --prefix=/usr/local/mysql_client

On my first try I encountered errors that were looking for "__cxa_pure_virtual". I googled and found this which gave me some additional things to do. Sure enough, this solved my "__cxa_pure_virtual" problem, but now I have more errors:

$ export CC=icc
$ export CXX=icc
$ export CFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict"
$ export CXXFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict"

and then I configure with the following options:

$ ./configure --without-server --prefix=/usr/local/mysql_client --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static

I then run "make clean" to clean out any failed compiles and it runs fine.

I then run just "make" and it chugs along for a while, but then exits with errors like:

mkdir .libs
icc -O3 -DDBUG_OFF -O3 -unroll2 -ip -mp -no-gcc -restrict
 -o gen_lex_hash gen_lex_hash.o ../myisam/libmyisam.a
 ../myisammrg/libmyisammrg.a
../heap/libheap.a ../vio/libvio.a ../mysys/libmysys.a ../dbug/libdbug.a
../regex/libregex.a ../strings/libmystrings.a -lz
 -lpthread -lcrypt -lnsl -lm -lpthread
gen_lex_hash.o(.eh_frame+0x12): undefined reference to
 `__gxx_personality_v0'
make[2]: *** [gen_lex_hash] Error 1
make[2]: Leaving directory `/srcpath/5.0.16/mysql-5.0.16/sql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/srcpath/5.0.16/mysql-5.0.16'
make: *** [all] Error 2

Obviously, I'm missing a reference to __gxx_personality_v0, but where do I get that from? Maybe one of my other libaries is out of date? If anybody has any hints, I'd appreciate you sharing them at http://forums.mysql.com/read.php?11,59295,59295#msg-59295.

Update: Solution

Where MySQL and Oracle are the same...

Say you've developed software on Oracle for a while. You know that if you start a query in SQL*Plus and then kill -9 the process that your query is still running on the server. You've got enough calls from your DBA to know that if you do that, you better call and let him know it's still running. Would you think it's any different on MySQL? Nope.

Three wishes...

While looking at something else, I came across 3 Things I wish I'd done by Jeanne Sahadi. While Jeanne writes about money and finance related stuff, I think the third point about valuing your work is right on.

Early in my career, I worked for a very large university in their IT department. Just being out of college, I was very grateful to even have a job. But anybody who works in the public sector knows, the pay ain't that great. In order to make ends meet, I started writing code "on the side" with a friend of a friend. I wrote a system in Paradox (version 3.5 for you techno geeks out there) for $10/hr. I got paid, but found out later that the person I was working for billed the client $50/hr for my time. This person then went on to sell this system to other clients and, of course, I didn't have any rights to it.

I also wish I listened to my gut. I had been working at a regional telecom company in the late 1990's. I had been perfectly happy there for three years and the pay was more than adequate. In 1998, during the rise of the dotcom bubble, I was seduced by a consulting company by the promise of 100% bonuses and four day work weeks. I agonized about it for about a week, and although my gut told me it was too good to be true, I jumped ship. After about two weeks I knew I made a mistake, but told myself it would get better. Fortunately, I had a good management team back at the telecom company and kept in touch with them. After two months I decided I'd had enough of consulting and went back to my old job.

Moving an application

We moved one of our major schemas to a new database this past weekend. This schema had lived in the same database for over six years. Over that time many people worked on the system and brought their experience to the development of it. Some of the things we found during the move:

1. Some Java programs still continue to use a connection URL of host:port:sid.
2. People really like synonyms. There were a couple situations where a synonym pointed to a synonym which pointed to a synonym which pointed to a synonym...
3. Recently, we have been setting up TNS aliases for each application and not each database. Sure, when we have multiple applications on one database there are multiple aliases. But when we move the application and flip the alias, everybody changes at once. Programs that used the application specific aliases moved with no problems.
4. It's 2005 (almost 2006) and we are still fighting people hard-coding username/passwords in their programs.
5. Got a call from a user Sunday, "I can't login with user X to the database." I replied, "User X doesn't live on that database anymore, and where did you get user X's password?"

It may be early...

It is really early in the College Basketball season, but who would have thought the North Carolina Tarheels would give the Illini a strong fight and be 4-1 after seeing Illinois AND Kentucky? Just when everyone counted old Roy out...

Support Survey

https://feedback.oracle.com/pls/feedback/feedback_ossdata.q22006_form

More stupid internet stuff

Sorry folks, I'm tied up with a nasty tuning job right now. Here's some more stupid internet fun...

You Are 35 Years Old

Under 12: You are a kid at heart. You still have an optimistic life view - and you look at the world with awe. 13-19: You are a teenager at heart. You question authority and are still trying to find your place in this world. 20-29: You are a twentysomething at heart. You feel excited about what's to come... love, work, and new experiences. 30-39: You are a thirtysomething at heart. You've had a taste of success and true love, but you want more! 40+: You are a mature adult. You've been through most of the ups and downs of life already. Now you get to sit back and relax.

What Age Do You Act?

8th Grade Math

You Passed 8th Grade Math

Congratulations, you got 10/10 correct!

Could You Pass 8th Grade Math?

My First MySQL "Doh"!

Getting into a support type role with some developers using MySQL. One particular developer wanted to use the "LOAD DATA INFILE..." command to load some data from a text file.

I think to myself, "No problem, just grant the FILE privilege".

He tells me "LOAD DATA INFILE" still doesn't work. After a little head-scratching, I pull out MySQL by Paul DuBois and realize it only works from the server and not from the client. Doh. (Although the "Access denied" error was pointing me to the MySQL maze known as privileges).

First Snow

The first snow of the season always brings hope that the bad things in the year can be purified (or covered up). I'll be cursing the weather after three months of scraping the car windows and shoveling the walk, but for now, it brings hope.

The way a crow
Shook down on me
The dust of snow
From a hemlock tree
Has given my heart
A change of mood
And saved some part
Of a day I had rued.

-- Robert Frost, "Dust of Snow"

Map24.com

I find myself using maps.google.com and mapquest.com to get directions to places I don't typically frequent. If I'm going to 3 or 4 places, I find these mapping sites to be a pain because you have to get directions from point 1 to point 2, point 2 to point 3, point 3 to point 4, etc. It gets pretty tedious. I've often thought to myself, why can't I put in all my addresses and get directions?

Then I came across Map24.com. You can put up to 5 destinations and get your directions all at once.

There are a couple improvements I'd like to see to the site. First, I could only get the map to display with IE, and not every time.

Also, right now you can specify the order in which you want to visit your destinations. However, I think it would be nice to put all my addresses in and let the software calculate the order I should visit them in order to maximize my time.

Check it out, see what you think.

Making IT Exciting Again

I was reading through Computerworld the other day and stumbled upon Making IT Exciting Again, an OpEd piece by Bruce A. Stewart. Back in the heyday of the late 90's you could come up with just about any idea for a web-presence and your company would fund it. Bruce points out that in this age of slashed budgets and trying to more with less, you can focus your efforts on business goals to make your work exciting again. Sure, you'll still be trying to keep your head above water, but you'll have fun doing it.

Mr. Stewart takes two cases in his own business where he examined risk and return to help the C levels recognize people in IT have a little smarts and know more about the business than rebooting a couple computers and make pretty websites.

My own career is a little contradictory to the respective times. In the late 90's I was working for a private telecommunications company that was expanding like wildfire. Every penny was stretched and every request for anything over $500 had to go the COO. Ask for over five grand and you met with the President/Owner of the company. You can imagine the business case we had to present when I asked for $1.2 Million to buy a Sun E5500 and 800G of disk. I got it, but the business case took my manager, a reseller, and myself about six weeks to put together.

My first project at my new company in 2000 was to move one of the two instances off an Ultra 2 with six 9G disks to its own Ultra 2 with six 9G disks. We discussed transaction and data volumes and I said on day one the U2 wouldn't cut it. A month later, we were on a 4 CPU box with dedicated fibre attached storage. Today I have about 20 boxes running about 12TB of storage on a SAN. I've had to justify it, but never had to write more than an email to get it.

Sure, I got lucky. Every time I think things are slowing down, wham, we get hit with a new project. Every day is exciting.

More WAP Worries

It happened again. My WAP lets me connect for about 15 minutes and then all of a sudden goes away. I try rebooting, resetting, and everything else but still no dice. I can't believe I spent another 3 hours on this last night. Time for a new Router/WAP.

Ideally, I'd like a WAP and Router all in one. The WAP would have to support WEP or some other authentication/encryption protocol since I live in a high density area. The router would have to support DHCP, which I think most do. Does anybody have any recommendations?

Periodic Reboots

The sysadmins did maintenance on some common NFS filesystems this weekend. Before they rebooted my workstation they saw:

root@mybox> uptime
  1:20am  up 500 day(s), 13:23,  1 user,  load average: 0.00, 0.00, 0.01

If I had a dollar for every Windows PC that didn't have to be rebooted in 500 days, I'd have...nothing.

Re-examining what you know

Tom Kyte has a very entertaining presentation on "Things You Know" which takes a humourous, yet logical poke at what we think we know and the methods we use to come up with those conclusions. It underlines his philosophy of "trust but verify".

As you may recall, I was searching for a DBA to fill an open slot. As with any new addition, it takes time to assess the individual's abilities and figure out how they will be integrated into the team. I constantly find myself wanting to say "That's the way it works, trust me" but try to take time to explain.

For example, the other day we were recovering a database in the development environment. To do this project we had to recreate the controlfile and do an incomplete recovery. While doing the recovery, Oracle asked for an archived redo log that was months old. We got into a discussion of how the controlfile works with an incomplete recovery, how that relates to each datafile and why Oracle may be asking for a real old archived redo log. I think my DBA learned a little more about Oracle and I learned that he now knows the function of the controlfile.

I want to instill into my team members the ability to question why things are setup as they are. Sometimes I just want something done a particular way because it needs to be done fast. However, I also don't want them to take my word because I don't always know...

Upgrading Oracle Applications

I've been working on a huge project the last few months. We're upgrading to Oracle Applications version 11.5.9 from a much-much-older version of 11.5. One thing that I've never liked about the adpatch program is that it is an interactive session expecting someone to be actively monitoring it's progress. For example, when applying the 11.5.9 monster patch, adpatch goes for about 6 hours and then asks the question "Do you want to continue?". Great. I left the office two hours ago and it will be about 8 more before I get back. Ten wasted hours. At least I'll know I have to stick around for six hours when I apply it to production...

Not Checking out

The blog has kind of taken a backseat the last week or so. Things at work have gotten exceptionally busy with year-end coming around and other business activity. I was the lucky chap (can Americans say that?) "on duty" this weekend and it was just one failure after another. Seventeen hours later and I think I've got almost everything cleaned up. Hang in there, I've got more coming...

Technology at the Police Department

I had to get fingerprinted the other day at my local Police Department and I encountered some interesting technology. Rest assured, it was for recreational purposes only.

I've been fingerprinted for before. My work requires a background check and as part of the investigation your fingerprints get sent to the FBI. The last time I was fingerprinted a police officer came in with his trusty ink pad and cards. He rolled each finger in the appropriate box and you were left with a mess on your hands.

Today, it was a different story. First, the officer put my information into a computer. Instead of pulling out the trusty ink pad, he placed my fingers on a glass surface and then scanned my fingerprints. You still have to go through the same process where the officer rolls each of your fingers over the glass, but it's definitely less messy.

The only thing I was disappointed with is when you're done, the officer runs a fingerprint card through a laser printer and your prints get transferred to a standard form. Once they are sent to the FBI, they get scanned again for analysis. Wouldn't it be much easier to just transfer the images to the FBI directly? Maybe it wouldn't take 6 weeks to get the results back.

HP, EVA's and databases

Getting the next generation disk array from HP and I'm trying to figure out the best way to configure it. The EVA has a new concept called "disk groups" that I just can't get my hands around. The lowest level of storage that the EVA uses is a Redundancy Storage Set (RSS). The EVA basically groups your disks into these RSS's based on the number of disks in your disk group.

The "disk group" basically sits on top of the RSS and is a logical collection of disks. The disk group is stiped across all disks in the disk group. Each "virtual disk" (basically LUN) is then created from a single disk group and assigned a "vRaid" level. You can create as many "vDisks" in your disk group as you need. A single vDisk can not span multiple disk groups unless you use a LVM like vxvm.

The documents I'm reading (here, and here) are conflicting at best. One says that a database server should have one disk group effectively implementing SAME (stripe and mirror everything) for you. The other document says to configure the number of disk groups for the type of I/O you will encounter. Knowing that the redo logs will be sequential access and the datafiles will be a mix of random and sequential access, I think there should be at least two disk groups.

Performance wise, every fiber of my being tells me one filesystem just isn't going to cut it no matter how many physical disks are under it. Do I really want all 48 disks spinning for a 8k write?

One of my fears is that if I lose a disk group, I lose everything. HP, of course, says you will never lose a disk group. Experience on other HP products tells me the first time we have a failure, we'll get some bad advice and wipe something out. Am I just being paranoid about losing a disk group?

Does anybody have practical experience on HP EVA's that can offer some insight?

Skipping a beat

I have one production database left on 8.1.7.4, although it's pretty irrelavent at this point. The plans are in the works to upgrade that particular database to 9.2, so it's time to "decommission" it's 8.1.7.4 development database.

Part of the decommissioning process is to truncate all the tables in the development database, but leave the structure in place just in case we have to do any quick compiles. Although I know it's running on my development database, my pulse still quickens when I see all those tables whirring by knowing they're being truncated. I double and triple checked that I was on development, but still I was a little nervous pressing the "Enter" key...

Giving up on dbms_metadata

My group was assigned a project to create a script that would dump the DDL of a schema to source. We figured dbms_metadata was the way to go and started writing our script. The process was to dump a particular object type, and then run that DDL on an empty schema to make sure it ran.

First thing we hit was good old bug 3832291:

ORA-31603: object "SYS_C0059941" of type CONSTRAINT not found in schema "MYSCHEMA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

OK. We figured out a way around that by generating constraints as ALTER statements. As we're cooking along again, blam, bug 3721907. Sure, we found a way around that one too.

The final nail in the coffin, was the performance with the whole thing. It took 30 minutes to dump a small schema of about 200 tables and a couple dozen packages. Checked metalink and found good old bug 3653586.

Looks like most of these bugs are fixed in 10g, but I'm not upgrading a bunch of instances just so dbms_metadata works. Sigh.

Automating the post office

I thought I had a simple task; mail a letter, get a return receipt, and have the ability to track it. I don't do this every day, so I went to the post office and started reading. Certified mail, Delivery Confirmation, Registered Mail, Priority Mail... it was a lot to take in. I finally decided I wanted Delivery Confirmation with a Return receipt. I filled out both forms and went to wait in line. One line, with one teller.

A friendly postal worker announced "Anybody paying by debit card or credit card can use the automated postal center." Cool, I thought, and I proceeded over to the ATM like thingy.

I must have had a blank stare on my face because the same postal worker asked me, "Do you need help?"

"I just want to mail this letter, get an acknowledgement from the recipient, and be able to track it if it gets lost.", I replied.

"Those aren't the right forms. The machine will print out the right ones."

"OK, cool." I start punching away. Letter. First Class. Done.

"Um, it didn't ask me if I wanted to get a return receipt."

"You did it wrong. You have to add a service."

"O...K..." Cancel. Letter. First Class. Add Service. Return Receipt.

"How do I know my tracking number?"

"You have to add a different service."

At this point, she is getting just as frustrated as I am. There's three Moms behind me patiently waiting to send cookies off to their kids. We go through it together: Cancel. Letter. First Class. Add Service. Certified Mail. Add Return Receipt. Done.

Finally, the stamp prints out. Another form prints the Return Receipt card and another sticker I'm supposed to put on the letter. The form is about half "peel and stick" and half regular paper. I fill out the "peel and stick" part and put it on the back of my letter. I carefully place the other sticker on the front of my envelope and fold it according to the directions on the form. I drop my neatly assembled letter in the big mailbox next to the APC.

"That's for packages", she says and quickly adds on "but it will get there." I'm going to the window next time.

Wicked ORA-27054, Solved

You may recall that I've been having problems getting 10gR2 backed up to NFS mounted disk. After we got bounced around a couple groups of Oracle Support, we finally landed into the capable hands of David Marshall.

After much research on David's part and some testing on our own, we settled on using the following parameters to mount our NFS filesystems for the purpose of backup:

rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsver=3,tcp

So all is well now. Our 9.2 backups complete in the same time and our 10.2 backups complete in an acceptable time. Now if we just knew which one of those parameters did it, we'd complete the education process.