Playing in the Sand

Just got back from a week in Kuwait upgrading our database down there from 9i to 10g and upgrading our ESRI from 9.0 to 9.1.

I must say upgrading Oracle9i to 10g on a Sun Solaris 10 OS is the most easiest and painless install I’ve ever done. I found out on our Dev box that trying to put 10g on Solaris 8 was like trying to put a square peg in a round hole. Luckily, I got our UNIX admin on board with upgrading all of our servers to Solaris 10 before I started my production upgrade festivities.

When I got down there we had one database serving up maps and vehicle tracking data. All the tracking data is OLTP oriented and the maps are nothing but a bunch of blobs. The user has the ability to see maps by themselves and vehicle information (text) by itself. The user also has the ability to see maps and the vehicle data at the same time.

The server has plenty of horsepower and space so I decided to break the database into two. I created another database and put the maps on it. I configured it for bulk stuff .One other thing I did was, we have a particular map that automatically loads up when a user first accesses the webpage, I threw that in to the keep pool. Performance is very nice. It’s so refreshing when you have a database configured correctly for the environment it supports.

Where am I deploying MySQL?

If cost were no object, I'd always deploy Oracle. I'm comfortable with Oracle technology and I think I have a pretty good idea how to implement and administer it.

In the world of corporate IT, however, budgets are king. Projects are measured by their Return on Investment (ROI) and the lower I can get that investment, the better return I can get for my investment. I have a real hard time spending $160K on an application that will occupy 40G of space.

In my opinion, I'd use MySQL for anything but the most mission critical applications. I'm not saying MySQL can't handle the most mission critical applications, but I'm not comfortable betting my business on MySQL at this point.

I think there are about three sweet spots for MySQL. The first is small to medium size OLTP databases (<100 GB) that are fronted by something like a java middle-tier. These applications typically control most of the business logic and authentication/authorization in the middle-tier (right or wrong) and use the database as a big storage bucket. These applications rely on the backend serving data as fast as it can and MySQL can serve data just as fast as the next guy.

Another area where MySQL excels in serving database driven content directly on the webserver. This type of application typically cranks out high numbers of queries and has very little updates to worry about.

Last, but not least, MySQL is suited for data marts ( < 1TB). Stuffing lots of historical data into denormalized relational tables is what "LOAD DATA LOCAL" is all about. These types of applications aren't needed 24x7 but require snappy response times when queried.

No, MySQL doesn't have some of the features that some of the big-box databases have. And it's got plenty of limitations. But when you want an 80% solution, I think it's the right choice. My company is sold on MySQL and as our confidence grows in the software, so will our installed base.

Quick and Dirty MySQL Backup

Until recently, the MySQL databases I work with contain data that can be retrieved from other sources. Most of the data is either batched in from flat files or another database. It would be inconvenient to reload a couple months worth of data, but since these databases are not mission critical, the business could operate without them for a couple days. Lately, we've been implementing some semi-critical systems that rely on a somewhat expedient recovery.

The requirements for the project were that the database must remain up during the backup and losing a day's worth of data was acceptable. All of my regular Oracle readers are cringing at the moment, but hey, that was the rules I was working with.

My first thought was to use mysqlhotcopy because it backed up the actual physical files. However, mysqlhotcopy only allows you to backup MyISAM tables and we extensively use InnoDB.

My next choice was mysqldump. mysqldump basically takes the entire database and dumps a text file containing DDL and DML that will re-create your database. Coming from an Oracle background, I knew there were shortcomings to dumping the entire database, but hopefully I could mitigate them.

The first hurdle was security. I specifically turn off unauthenticated root access on my databases, but I needed to be able to read all the tables to do a backup. I don't want to hard-code my root password or any password in a script as I don't have suicidal tendencies (diagnosed, anyway). So I created a user called backup that could only login from the server machine, but could login unauthenticated.

The next thing I had to figure out was how to get a consistent view of the data. I knew that my developers preferred InnoDB for it's Referential Integrity features and getting inconsistent data would be disasterous. Fortunately, one of the flags to mysql_dump is the --single-transaction which essentially takes a snapshot in time.

So I wrote a script around mysql_dump and --single-transaction and dumped my entire database to disk. Every now and again, I encountered an "Error 2013: Lost connection to MySQL server during query when dumping table `XYZ` at row: 12345". The row number changed each time, so I figured it had something to do with either activity in the database or memory. I could rerun the command and it usually finished the second or third time.

After the third day straight of my backup failing, I decided to research it a little more. mysql_dump has a flag called --quick which bypasses the cache and writes directly to disk. I put this flag in my backup script and the script started finishing more consistently.

The last hurdle was having enough space on disk to store my backups. Since the backup file is really a text file, I decided to pipe the output through gzip to reduce it's size.

Currently, my quick and dirty backup script is a wrapper around the following command:

mysqldump --all-databases --quick --single-transaction -u backup | gzip > mybackup.sql.gz

We're adopting MySQL at a blistering pace, so I'm sure I'll need to make changes in the future. For right now, though, it gets the job done.

Check this out

I usually employ a logon trigger for most of my Oracle databases so I can grab certain identifying information about the session. Then I save this information in another table for later analysis.

I have started testing 9iR2 on a 64-bit Linux box and have come across a certain peculiarity. v$session is defined as:

SQL> desc v$session
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SADDR                                              RAW(4)
SID                                                NUMBER
...

I then create a table using the same type and try to insert a value:

SQL> create table jh1 (saddr raw(4));

Table created.

SQL> desc jh1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SADDR                                              RAW(4)

SQL> insert into jh1 select saddr from v$session;
insert into jh1 select saddr from v$session
                      *
ERROR at line 1:
ORA-01401: inserted value too large for column

Hmmmf. So I do a CTAS:

SQL> drop table jh1;

Table dropped.

SQL> create table jh1 as select saddr from v$session;

Table created.

SQL> desc jh1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SADDR                                              RAW(8)

...and look what size the column is!

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for Linux: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

Update: 2006/11/01 16:09:
From Support:

I checked my Windows (32bit) database and v$session.saddr is a RAW(4).

OK, that explains it.

The OS wars heat up

You may remember we talked about Oracle's Unbreakable Linux the other day.

Anybody try to get on Metalink yesterday? The Register is reporting on the poor response time yesterday. Maybe Linux will kill Oracle...

Here's an interesting take from Dave Dargo...

Firefox 2.0

I downloaded Firefox 2.0 this weekend to see what it was all about. I thought a couple pages of my regular sites loaded slowly, but that could just be my internet connection. Once of the first features I noticed was it's ability to detect a fraudulent website.

I received an email from a suspected ebay spoof. Sometimes I just click on the links to see how close they are to the real website. To my surprise, this message came up from Firefox:

Will Oracle Kill Linux?

That's right, not will Oracle Kill Red Hat, but will Oracle Kill Linux?

There seems to be some buzz that Oracle's Unbreakable Linux is positioning itself against Red Hat's Linux. Oracle will be offering support on a version of Linux that they have basically ripped off from Red Hat.

There's no doubt in my mind that Oracle won't kill Red Hat. Linux is used for more than just running Oracle software. I should switch my whole enterprise of umpteen hundred Red Hat computers so I can run 20 Oracle servers more efficiently? I don't think so. Can you see a SysAdmin calling in to Oracle for support and having to wait 6 days to talk to Sandeep in some far reaching corner of the globe? Can't see it myself. Besides, Oracle needs Red Hat to continue to develop the platform so they can rip it off again.

The big question in my mind is will Oracle kill Linux? To successfully deploy Unbreakable Linux, Oracle is going to have to snuggle up to the hardware vendors in order to get Unbreakable Linux pushed out on their hardware. I would imagine that is going to tick off some of the proprietary vendors.

Or better yet, will Linux kill Oracle? Will Oracle get so distracted from it's core business of infrastructure software that the products go downhill further?

Only time will answer these questions. Of course, I don't have a yacht and a billion dollars, so what do I know?

At this point, I'd just be happy with a filesystem that doesn't reboot my box every 5 days.

I can see you!

My apologies for not posting anything for awhile but I’ve had some personal issues that I needed to take care of . So….. enough of that I’m back now and hope to contribute on a regular basis.

Let me seeee… We left off with the wonderful world of Spatial and me ranting about ESRI. I want to continue with the Spatial stuff but I wanted to share with you guys a more intriguing (at least in my opinion) turn of events. Even though this is totally out of my job description, in my shop we have to do what it takes to get the job done even if that means doing *shutter* development work.

We recently had a complaint from one of our customers that in certain parts of a country that his equipment couldn’t send or receive a satellite signal due to terrain obstacles and he would like to be able to see on the webmap those areas that are “blacked out” so he can avoid them. Well, since we’re all about keeping the customer happy I was tasked to “Make it happen”.

The first step in the process is getting the elevation data of the desired location, once you have the elevation, you find out which satellite your equipment is using. Once you know the satellite, you find out what the Lat/Log and height (location and distance above the earth) of it is.

When we have all that information we start piecing the puzzle together. We have to create physical objects in the database like a shape file (our satellite) and a Raster image ( our map) and input all the data that we gathered up (elevation, lat/log, height) once all the values are given we use a tool to calculate the black out areas. The example above, I’ve used an observation tower as an example. The red areas are not visible from the observers view but the green areas are. Get it?


This website gives you in real time all satellite names, and locations using a nice little java thingy. http://science.nasa.gov/RealTime/JTrack/3D/JTrack3D.html

Taking the plunge

Within the last six months, I've gotten my enterprise off Oracle 8i and on to 9iR2. We were really behind the 8 ball (no pun intended) since the support window for 8i was quickly running out. We forged ahead and were able to get everything to 9i. Along the way, we upgraded some development systems to 10gR1 and put out a non-critical system in 10gR2. However, something was missing. We hadn't taken advantage of a great deal of the 9i features that didn't come straight out of the box (Optimizer enhancements, PL/SQL fixes, bug fixes, etc).

I didn't want the upgrade to 10gR2 to go the same way. I don't want a hard-and-fast deadline for 10g and I want to be able to take advantage of some of the bells and whistles that come with 10g to ease my management burden. My educational jumping off point is to be certified in 10g.

I'm a firm believer in certification for enhancing the individual's self-worth. That doesn't mean every certified DBA is worth something to a company, nor does it mean somebody certified is worth more than someone who is not certified. It simply means that I view the certification process a valid educational opportunity for the individual to benchmark his knowlege against a standard.

Now, it's not the full-blown-from-the-top certification path that new DBAs are going on. I'm certified in 7, 8, and 8i, so I'll be looking to upgrade from 8i to 10g through 1Z0-045. I know, kind of a cop-out, but I think this will give me an opportunity to try out some of the new stuff before I need it. I bought the book last week, so now I have to start studying.

Don't be surprised if you see interesting (to me, anyway) 10g thingies on the blog in the next few months.

Apex Rocks

Two days ago, I knew very little about Apex (HTMLDB). I knew how to set it up and install it on the database side, but as far as developing something with it, forget it. There are other people using HTMLDB at my company and they have been getting good results with it. Since I didn't know that much about it, I kind of let them have free reign over things.

I am still supporting some reports that I did as a favor for a user a couple years ago. I don't mind since it's a real simple process, but the report relies on me running a query and dumping the results to a CSV file so I can send it to the user.

It's that time a year where I have to go through this process again and this year I decided to hand over the power to the user. From what I heard from other developers, it was a simple tool, so I gave myslef an extra week. I knew I could bang out hte CSV file in about 2 hours if I had to, so I basically had the whole week to work on it.

I started playing with Apex in the morning and in about four hours I had a basic report. In another day, I added some calendar pickers to let them enter a date range and some other dodads to give the user flexibility in how they wanted to filter the report. The best part was the "Spread Sheet" link that automatically downloads the report to a CSV file. Jeff - exit stage left.

I deployed it and gave the user the link in just under two days.

Apex Rocks!

Passwords

One of the things about IT security that really irks me is passwords. As a user I need a password for system X and a different password for system Y. Not only that, but system X requires a password at least 6 characters long with at least one alpha character and one numeric character. System Y requires a password 8 characters long, with two numeric characters and I can never reuse the same password. To add insult to injury, System Y's password expires every 45 days and system X's password expires every 365 days. I just changed my password for system Y to something I know I'll never remember.

Now, I know what you are thinking: LDAP server. Centralize the authentication and authorization and you only need to supply a password once. That's all fine and dandy when I have control over the security, but not when system X is where I do my online banking and system Y is my brokerage account.

Things are changing in the financial world, and not for the better, IMHO. At some sites, I have to answer a personal question every time I login. Others, I have to choose a picture before I even get to enter my password. Others still, I need an RSA key along with another password. I think there should be a standard of authentication practices that your personal trading partners should have to adhere to. I've got so many passwords in my head, I can barely remember how to login to work. In the time I wrote this post, I've forgot system Y's password.

Truth and the DBA

I don’t tolerate lying at all.

I don’t even want you to spin the truth. Give me the whole truth and nothing but the truth and we’ll be fine. An article about conducting business in an ethical manner by Bud Bilanich at Trump University got my attention. It’s worth a good read.

When I first moved up the ranks from a team member to a team leader, I had somebody that worked for me that skated on the edge of the truth quite often.

“How’s project the upgrade project going?” I asked.

“Fine. I’m right on schedule”, she answered.

“Last time I did an upgrade, the JServ configuration gave me problems. How did that go with this upgrade?” I countered.

“No issues.” She replied.

OK, I guess they fixed that.

Two weeks before the big upgrade, I asked again if we were on schedule and she replied “Oh yeah, probably be done in a week.” So I sent a note to the users about the upgrade and how we’ll need people here to test on Sunday to make sure everything is fine. The users got their army ready for Sunday, upper management was notified since they had been breathing down our neck for getting this project done as well.

Monday before the conversion came and I ask for the new URL so I can look at the new software.

“Not quite done yet, definitely this afternoon.”

Hmm, something is sounding fishy here. I looked at the machine and the database wasn’t even up yet. I poked around some logs and saw that certain pieces were failing to come up for various reasons. Did a quick search on Metalink and saw a couple resolutions to the issues so I didn’t think they were too serious.

Tuesday and Wednesday I was out for training, but left explicit instructions that if progress wasn’t being made I was to be notified.

When I got back Thursday, I went to get a quick status.

“JServ doesn’t work, the Concurrent Managers keep dying, and Apache dies when you hit the login URL” was the reply.

Needless to say, the upgrade was cancelled. Upper management was steamed and since I was the project leader, it was my fault. That person no longer works for me.

Granted, it was my fault for not asking the right questions. However, if they had been truthful about their progress and struggles they would have garnered much more respect and I could portray an accurate picture of the progress to upper management. Their spin on the truth (or outright lies) caused my group to lose a lot of respect from the powers that be.

That’s one of the many reasons I manage the way I do today. As Tom Kyte puts it, “Trust, but verify.”

The world of Spatial part I.....

I want to apologize even before I begin because there’s so much information that I feel I need to share with others that my topics may jump around.

Notice how I didn’t say “The world of Oracle Spatial”? That’s because there is an alternative in the land of GIS (Geographic, Information, System), you don’t have to use Oracle’s spatial module. The big dog in GIS is ESRI and if you use ArcSDE (component of ESRI) it has its own way of doing spatial stuff. Before I get down and dirty with the technical aspects of running and maintaining a spatial database, I feel that it’s important that you (the reader) know upfront you do have a choice of how you can mange your spatial storage.

*Steps up on soapbox*

The ESRI company started out as bunch of engineers who wanted to develop geographic software. The thought was great but, engineers have this idiosyncrasy about delegating work, they think they can do it all and it reflects grossly in their product. The first thing you will become distinctly aware of is that Oracle is kind of an afterthought in the eyes of ESRI, SQL Server is all that is holy with ESRI and the reason for that is because most of it’s customers run SQL Server. Why anyone would want to run an enterprise system with terabytes of data on SQL Server is beyond me (yes, I am Oracle biased). The second thing is Bind variables, they are unheard of, if you have a spatial database and you run ESRI get use to thousands of literal statements plaguing your shared pool . I have done battle for two years with these people and they just don’t get it. There are days where I really want to hop on an airplane, fly to CA and cause bodily harm to the development staff. End of rant.

*Steps off soapbox*

As far as using Oracle spatial vs ESRI “spatial” there are pros and cons of each, it’s up to you to decide which is best for your environment and skill set. I look at using Oracle spatial as kind of like “more moving parts”. The less “stuff” I have to deal with in a database the better, especially if it’s big. I figure if I let ESRI handle ESRI there’s less that can go wrong. I hang out over on ESRI’s forums and the number of Oracle Spatial problems are limited but when there are, they’re usually pretty bad and the question(s) go unanswered. As far as performance gained with Oracle spatial, the jury is still out on that one with me because I’m waiting to see what ESRI’s new release of 9.2 is going to be like. Apparently, they’re going to finally take advantage of the SDO_GEORASTER parameter. Right now they’re only using the SDO_GEOMETRY parameter which handles shape files. It’s like they developed a product, packaged it up, shipped it out, and forgot to put the CD’s in the box. Yeah, if you do use Oracle spatial (now) you get rid of your F and S tables but what’s the sense if you can only use half of the modules ability? It boils down to if you want to use Oracle Spatial when ArcSDE 9.2 comes out you’re going to have to completely drop your rasters and bring them back in (at least that’s the way I see it). I can see it now, managers across the nation giving birth to small farm animals when they find out they’re going to have to drop terabytes of data to take full advantage of Oracle Spatial because of ESRI laziness.

The last thing I want to do is go over anyone’s head when I’m on a roll talking about this stuff so if I mention something that you don’t quite get or want me to elaborate on, please speak up and I’ll be more than happy to pull the reins in sit for a spell.

Job Opportunity

Just learned of a great DBA opportunity in the Jersey City area. Contact Evan Lerman from IJC Partners LLC at (212)626-6920. From Evan:

FINANCIAL EXPERIENCE A MUST ORACLE 9I AND 10G LOCATION JERSEY CITY PAYS UP TO 120K BASE

QUALIFICATIONS:

Minimum of three years experience working as a Database Administrator.

Familiarity with Oracle and Microsoft SQL Server with emphasis on
Oracle. Knowledge of relational database concepts and standards, best practices and procedures relating to database administration.

Experience in financial industry, insurance industry or law firm a plus. Must have excellent technical skills and knowledge of Unix and Windows operating systems. Strong interpersonal, analytical and troubleshooting skills with superior verbal/written skills are required.

JOB Description:
The Senior Database Administrator directs and controls the activities related to data planning and development, and the establishment of policies and procedures pertaining to its management, security, maintenance and utilization. Sets and monitors standards; ensures that database objects, program data access, procedures and facilities are used properly. Advises management on database concepts and functional capabilities. Position is also responsible for installation and ongoing maintenance of enterprise server operating systems and system management products, as well as coordination of installation and upgrades to enterprise servers. Position also provides on-going production system support and performs other duties, as assigned.

Duties & Skills
A. Business/Application Knowledge

• Understands the company's general business functions, and has a conceptual understanding of each unit's activities.
• Has general knowledge of assigned application systems.
• Comprehends the relationships between business activities and application systems. Is able to determine impact of database changes to the application systems, and vice versa.

B. Technical/Programming Skills

• Builds and maintains all Corporate database environments.
• Builds and maintains test database environments.
• Is responsible for recommending and planning the installation of new releases of database software.
• Ensures the integrity of all physical database objects and established database procedures.
• Creates storage groups, databases, tables and views, reviews SQL, develops and enforces database standards.
• Ensures work is thoroughly tested and smoothly implemented.
• Is responsible for database performance and capacity monitoring and tuning. Prepares regular capacity analyses for management review.
• Assists in determining storage procedures for on- and off-site storage of historical data.
• Assists in establishing backup, recovery and restart procedures.
• Assesses the need for additional hardware or software to assist in monitoring or performance of database applications.
• Communicates availability requirements for database accessibility.
• Coordinates schedules and procedures for the implementation or discontinuance of relational database applications.
• Provides technical support and basic training in the proper use of production databases to database users.
• Assists in troubleshooting application problems where database management is an integral element.
• Mentors junior staff on database management techniques.
• Installs upgrades and fixes to server operating systems (UNIX, NT, etc)
• Analyzes and recommends upgrades and/or new acquisitions of hardware to support new systems or growth of existing systems.
• Coordinates vendor installation of hardware.
• Installs and utilizes third party system management software to monitor overall server performance and capacity utilization.
• Designs and implements backup schedules for critical databases.

C. Analysis Skills

• Provides ongoing research and development activities to investigate new technologies and tools which might be used by Company personnel to more effectively and efficiently perform their jobs.
• Performs functional evaluations of candidate products.
• Prepares time and cost estimates for assigned projects.
• Understands the Company System Lifecycle Methodology, and project development lifecycle.
• Acts as methodology and process mentor for junior staff, as they prepare project deliverables.
• Contributes to database design reviews.
• Develops and maintains a security scheme for the database environments.
• Assists in disaster recovery planning, testing and execution as needed.
• Possesses strong understanding of the system deployment process and correlation with database administration responsibilities.
• Coordinates and conducts database design reviews.
• Possesses keen troubleshooting and creative problem solving skills.
• Possesses the ability to translate user needs and projections into system hardware and/or software requirements.

D. Basic Skills

• Adheres to Company standards and methodology.
• Adheres to company confidentiality and security requirements.
• Communicates effectively.
• Consistently demonstrates a high level of integrity and professionalism.

The Co-operative

Things are changing in the home offices of "So What?". Some of my guest bloggers expressed an interest in continuing to blog about IT goings on and I thought "Why Not?". We're going to concentrate more on IT stuff on So What and I'll leave the personal stuff over at Wilton Diaries. I present to you, the So What Co-operative.

Oracle Spatial & Wildebeests

Before I make an effort to Blog about Oracle Spatial, Rasters, and Shape files is there anyone who reads this Blog that would benefit from me sharing my experiences with it?

Don’t get me wrong, I’m not saying my time is valuable and I have better things to do with it. I’m just trying to get a feel for what interests you (the reader). I’m sure there are those of you who would much rather discuss the migration habits of the West African Wildebeest during the dry season or how histograms for join predicates only work if you stick your tongue out in the right place. Sorry Dave, you know I have to mess with you :)

So Often I read peoples blogs and it looks like they threw up a paragraph or two of gibberish just to take up virtual space and to make it look like there’s activity. I refuse to succumb to that. You take the time out of your day/evening to come and pay this place a visit the least thing that the contributors can do is write decent content that will make your time spent here either enjoyable or knowledge gained. Nuff Said?

What do you do all day?

The conversation started out "What do you do all day?"

I was talking to a fellow IT worker and was trying to explain my job function. I often get this question from non-computer people and I just respond "computers", but this required a more in-depth answer.

I started off with "Basically, I make sure all those databases that the company uses stay up and functional."

"Ah....But what does that mean?"

So I start explaining my day.

My day typically starts with resolving any non-critical problems that happen overnight. I don't have to worry about the critical problems, because they have already been resolved by the person on duty (which is me every other week). I might create a new schema for a user in Europe. Or I might try and find out why userX tried to login yesterday over 300 times using the wrong password. This type of stuff usually lasts from 15 minutes to an hour. In my group, we each have about the same amount of this type of work in the morning. In addition, we'll respond to these type of quickie tasks throughout the day, recording each one.

Then I spend about 15 minutes catching up on how my people are doing with their projects. Sometimes more, sometimes less, but I generally like to get a feel of how things are going before I start doing my heavy duty work.

Most of my day is spend doing what I call "project work". Project Work are those tasks that can't be done in less than a day. A project might be as short as a day, or may be as long as 18 months. An example of a project might be as complex as upgrading Oracle Applications to 11.5.10 or might be as simple as setting up a connection manager for a particular sub-net. I typically schedule my work so I can work on two projects at the same time (ie. while Oracle Applications is applying patch XYZ, I write code for my monitoring software).

Occasionally, I'll have to respond to a critical situation. I have monitoring software running all the time and when it encounters something that it thinks I should know about, the software sends us a message. If a process is running over X minutes, I get a message indicating that maybe I should investigate more. If the software encounters a condition that could potentially stop business, I get notified right away using a text message. If my backups fail at 02:00, I get notified. If the log_archive_dest gets over 90% full, I get notified. On average, I get about three after-hours messages a week when I'm on duty.

I don't worry about backups, they're automated. I don't worry about my alert.log, it's being monitored. I don't worry about the database being up, it's monitored.

The other person usually wakes up from their coma at that point and says "Oh."

Weathervanes indeed

You may recall Steve's entry about weathervane theft in New England. At these prices, I can understand why.

On top of the world

I’ve never climbed a mountain, but seeing a sunset at 14,000 feet is a breathtaking experience. We took the Mauna Kea (pronounced Mona Kaya) Sunset and Stargazing tour by Hawaii Forest and Trail one evening. They take you from your hotel (sea level, temperature 93) on a 90 minute ride to the summit of Mauna Kea (14,000 feet, 38 degrees) to watch the sunset. The view did not disappoint as an unobstructed sunset was one of the most incredible things I have ever seen.

Mauna Kea is home to some of the world’s most powerful observatories. The stargazing portion of the tour was a 90 minute talk on the constellations and how ancient Hawaiians used these stars to navigate. Then the guide pulled out an 8” telescope and let us see some of the stars up close. The view of Jupiter was striking and we saw details on the moon almost like it was coming from Google Maps.

I’ll let the pictures do the talking, but if you’re ever on the big island, you won’t be disappointed with this tour.

Choosing your platform based on TCA?

I giggled at "The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g" over on ComputerWorld.com.

I've been in this business 17 years working in countless companies. I've implemented everything from cancer protocol databases to telecom billing systems and I can tell you management has never looked at the Annual TCA per database user as a deciding factor.

I can't believe people are actually paid to come up with these numbers. Lets see an apples to apples comparison. For example, put Oracle and MS SQL on the same hardware on Windows XP (yes, I'm going against my "Never Windoz" philosophy, but last I checked, MS SQL wasn't available on Linux). Then run ApplicationX against both platforms and measure number of customer complaints/hour for each system. Now measure how long it takes to solve those problems until customer is satisfied. That's a real number.

Airline Travel

Airline travel has changed a little bit since I last flew.  Granted, I don’t travel on business and fly maybe twice a year on average, so things could change without me taking notice.  Sure, we all know about taking liquids and gels on the plane is a big no-no now, but other changes abound as well.

First thing we noticed was there is a not-so-generous weight limit on baggage.  Our particular carrier had a 50 pound weight limit per checked bag, two bags per passenger.  Otherwise, they hit you with a $80 charge for “overweight” baggage.  We each packed one bag not wanting to lug our whole wardrobe to Hawaii.  No problem, we thought, until we stepped on the bathroom scale with the bags the day before we left.  One bag was OK at 46 pounds, but the bigger one weighed in at 58.  Problem was, the bag itself was 15 pounds (one of those super large 29” bags).  Valerie took out enough stuff to get it under 50 pounds, but that left the bag about 1/3 empty, which seemed like a waste.  Also, that gave us enough room to buy stuff and pack the bag again and go over the limit on the return trip. We made a mad dash to the store and purchased another 26” bag, stuffed all the stuff in and it tipped the scales at 48 pounds.  First hurdle passed.

We get to the gate to check in and there are kiosks instead of agents at the gate.  I’m no stranger to using the electronic check in when I only have carry on luggage.  But when you have bags to check how do they get on the plane?  Well, you wait.  You punch in all your information, put the bag on the scale and wait for an attendant to take you bag and put it on the belt.  Then the attendant goes to the next kiosk while you put your second bag on the scale and you wait again until he comes back around.  Sure, I understand they’re not paying someone to do that work, but now I have to wait longer.  And what about the 70ish woman ahead of me who had no clue as to what she needed to do?  She needed a ticket agent.  To me, that’s the sign of a company that uses automation to cut costs without regard to the customer’s time.

Anybody who knows me, knows I’m a big guy.  I get along in a normal seat, but it’s not the most comfortable experience for me.  Used to be that the exit row seats weren’t assigned until the gate agent saw you were capable to operate the emergency doors in an emergency.  I used to take advantage of that by getting to the airport two hours ahead of time and snagging an exit row seat about 80% of the time.  Got to the airport two hours early as usual, and requested exit row from the kiosk (see above).  “No problem”, the kiosk says, “I’ll just charge your credit card $41 per seat per leg of your trip.”  WTF?  Thanks, but no.  We board the plane and one seat out of 19 has a passenger who paid the extra $41.  Nice.  Luckily, we encountered one of the nicest flight attendants who saw I was “seat challenged” and “made” us change to an unoccupied exit row.  “I’ll get in trouble for this, but you’re going to die back there.” he said.  Nice to see a person who knows how they used to treat customers in a company that doesn’t care about the customer anymore.

Last, but not least, Airline food.  Does anybody really expect a decent meal on an airline?  Me neither.  In fact, I made conscious decision about 10 years ago that I’d rather have nothing than eat the half not-so-hot half ice-cold apple pancakes on a plane.   More and more airlines are going the way of Southwest in not even offering meals on their flights and opting instead for the obligatory snack of 6 pretzels.  Anyway, this airline has gone the same way and don’t provide meals for their economy passengers.  But you can purchase one onboard; for $5.  Personally, I’ve got no problems with this, but is it another way to extract $5 from the customer?  Perhaps.  Maybe when people pay for their food they will expect more quality.

Thanks

Thanks to my guest bloggers the last couple of weeks. I hope they gave you something a little different to read and maybe encouraged them to start blogging on their own. A couple observations over vacation will follow and progress to the normal stuff after that.

The Ultimate job....

What if you were a gamer and an Oracle DBA, what would be the ultimate job for you? You know a job where you couldn't wait to get to every morning!

I think I found the Holy Grail of jobs (at least in my opinion) and it pains me to see that I have to let this slip through my fingers knowing that if I applied I could get it. I keep convincing myself "No, I don't want to move to California". Don't get me wrong, I really enjoy what I'm doing now but... it's Blizzard *droool*.

Gaming has always been a hobby of mine (when I can find the time). I just recently acquired an old P233 and I slapped a hundred megs of ram with Windoz95 on it so I could play all those old games like MecWarriorII. Here’s the kicker I had an extra Nvidia PIC 125meg video card sitting around and I went to their website to see if they had the drivers for Win95 low and behold they did! So you’ve got 100megs of RAM and 125meg video card with plenty CPU power. The games play great the only thing is you can’t get that high resolution like 1024 X 786 only 800 X 600.

Now for something completely different….

For those of you who are upgrading from 9i to 10g Oracle has this nifty little validation checker that runs a script against your server (Solaris) to ensure all the settings, patches, bla, bla are correct. Well as of Aug 25th it’s no longer valid. Last week I was prepping a server for an upgrade ran the script and everything came back all nice, then on Saturday I was doing the actual install, it’s running its own checks and bam! It tells me I’m all messed up and not to even try installing 10g. Ohhh I was soooo mad! Sure would have been nice to know Oracle de-supported it. Only good thing that came from it all was that I was able to get back home and spend the weekend gaming *wink* hee hee.

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.