Seven Deadly Habits of a DBA

In my travels today I came across the Pythian Group’s Seven deadly habits of a DBA… and how to cure them.

Citing such pitfalls as blind faith in backups, lack of monitoring and finger pointing we’ve all seen these symptoms at one point or another, and they’re all worth some attention. While many of these are largely a product of inexperience I’ve seen my favorite, number 4, The Memory Test at all levels. The “if it happens again I’ll remember how we fixed it” syndrome is often a product of another environmental problem, a lack of documentation procedure. This exact problem is one reason I started blogging in the first place!

Check out the whole list, including their suggested cures for these problems.

dba, database, database administration, oracle

A great FAQ on Oracle NLS_LANG settings

While digging for some details on the NLS language settings in Oracle today I came across a great FAQ on the topic right on Oracle’s site.

The FAQ includes such topics as:
NLS_LANG Parameter Fundamentals
Checking the current NLS_LANG Settings
How to setup the NLS_LANG Property for UNIX
Where to set the NLS_LANG in Windows
A Whole Pile of Other Frequently Asked Questions

Check out the NLS_LANG FAQ if you’re looking to learn about the NLS_LANG and associated settings or to find the answers to many common questions on these parameters.

oracle, dba, database administration, system administration, UNIX, Linux

SQL Date Math and the Leap Year

Ying pointed out on my article about finding the last Saturday of the year that adding a year to a DATE datatype works great with syntax like this:

SQL> select sysdate+numtoyminterval(1, 'YEAR') from dual;

SYSDATE+N
---------
27-NOV-07

It works great right up until you hit February, 29:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtoyminterval(1, 'YEAR') from dual;
*
ERROR at line 1:
ORA-01839: date not valid for month specified

So what can we do about this? Well, as far as I can guess there are only two options. First, you could avoid ever using +numtoyminterval(1, 'YEAR') in your code and instead use +numtodsinterval(365, 'DAY') like this:

SQL> select to_date('02/29/2004', 'MM/DD/YYYY')+numtodsinterval(365, 'DAY') from dual

TO_DATE('
---------
28-FEB-05

This may cause some confusion as 365 days after January 15, 2004 would be January 14, 2005 and 365 days after January 1, 2004 is actually December 31, 2004, but it should never throw an ORA- error.

UPDATE: Ying commented with this solution which is probably better in most scenarios. By using the add_months function we can step forward a number of months and Oracle will automatically truncate to the last day of the month if the resulting month has fewer days than the starting month.

SQL> SELECT Add_Months(to_date('02/29/2004', 'MM/DD/YYYY'), 12) from dual;

ADD_MONTH
---------
28-FEB-05

The other alternative is to handle the exception programmatically. Either avoid inserting a February 29 or avoid using the +numtoyminterval(1, 'YEAR') only when handling a February 29.

I’d say option 1 is the better choice, but both have their drawbacks.

This is the same error you get if you attempt to add a month to, say January 30th since there is never a February 30th.

SQL> select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual;
select to_date('01/30/2006', 'MM/DD/YYYY')+numtoyminterval(1, 'MONTH') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified

I guess this is why most things are good for “30 days” not “1 month”.

Thanks Ying for pointing out this anomaly.

database, oracle, date, to_date, timestamp, time, database administration, database development

Jumping on the Linux Bandwagon

In an interesting chain reaction Oracle and Microsoft have both recently committed to supporting Linux distributions.

Recently Oracle announced they will offer RedHat Linux support at very competitive rates. Beyond offering some healthy competition for RedHat support Oracle’s commitment also makes Oracle on Linux a single vendor solution for software support.

Microsoft was not far behind in announcing a partnership with Novel for Suse Linux sales support.

Only time will tell what this will do to the Unix/Unix-like OS market but it’s sure to shake things up in the short term.

linux, unix, microsoft, redhat, system administration, oracle, database administration, dba, sysadmin

Interview with SearchSecurity.com

Earlier this week I was solicited for an interview with Bill Brenner, Senior News Writer for SearcSecurity.com on the topic of Oracle patches and their new Critical Patch Update bulletins.

Bill interviewed several DBAs and got some interesting opinions on Oracle’s patching procedures.

Jon Emmons, an Oracle database consultant and keeper of a blog called Life After Coffee, which focuses on Oracle security and other topics, said he also found the bulletin changes helpful.

“Perhaps the most valuable new feature in the CPU bulletin is the executive summaries,” Emmons said in an email interview. “These bulleted lists give a great high-level summary. At one point or another we’ve all had to explain to our boss why we need to apply these patches and now Oracle has given us the words to do it with.”

The full article, which came out yesterday, highlights mixed opinions on Oracle’s patching procedure and “improved” CPU bulletin.

oracle, patch, database, dba, database administration, rdbms, security, database security