The politics of running Oracle on NFS June 29, 2007

Kevin Closson wrote an article making the economic case for running Oracle on NFS. Its a great article, go read it. It shows how much cheaper, more scalable and easier to configure and administer NFS can be compared to SAN solutions, even when taking possible performance degredations into account.

One thing that Kevin failed to mention is that choosing NFS requires a strong backbone and not every DBA would wish to test his or her mettle by attempting to introduce NFS into production enviroment.

You know how the database is always blamed for every application issue? How the DBAs always have to run around and prove that the DB is innocent while everyone else is pointing their fingers at the them? I’m sure everyone reading this blog is familar with “The database is guilty until proven innocent beyond all shadow of doubt” mentality.

Well, its the same with NFS only a bit worse. We’ve been running several large production RAC systems on NFS for the last two years, and for the past two years Netapp and the NFS protocol has been blamed for OS crashes, cluster errors, Oracle bugs, application errors, database misconfiguration, badly written backup scripts, errors that occurd within third party network devices, and the list can go on and on.

Our RAC systems had tons of issues – Netapp and the NFS protocol has been blamed for about 90% of them. I don’t believe we found even one case where it was clearly a storage issue. There used to be a saying “No one was ever fired for deciding to use IBM”. I can’t say the same for deciding to use Oracle on NFS.

The already sensitive situation is made even worse when we were unable to find any consultant – from Oracle, Netapp or 3rd party that was willing to review our storage configuration top to bottom and reassure us (and mostly our twitchy management) that we are doing things fine, everything is configured as it should be and if your Linux servers and Oracle Cluster crashes twice a day, the problem must be somewhere else,

Maybe Kevin should start NFS auditing services.

(I’ve actually planned to write about auditing today, but I got so worked up about storage that I totally forgot what is so interesting about audits anyway. Don’t worry, I’m testing fine grained auditing for a customer these days so some interesting bits about it are sure to come up soon)

Oracle questions answered June 27, 2007

Some people ask google interesting questions, and somehow arrive to this blog. Well, why not answer them? even if it is a bit late for those who originally searched, it may be useful to someone.

Q: copy oracle schema but no data

A: Export/Import is probably the simplest way to copy schemas.

If you use datapump, use “content=metadata_only” parameter when exporting or when importing.

If you use the old export and import, use ROWS=N parameter.

 

 

Q: why is oracle so complex?

A: All databases are complex. They have to do rather complicated things, after all. However, Oracle is the only database that doesn’t make many attempts to hide this complexity (In 10g it changed a bit). Maybe because Oracle recognizes that to get the best performance out of your database, you have to be able to tell it exactly what you want and for that you need to know exactly how the database works, you can’t have good performance if you have to go around guessing how the database will handle your requests. So now as a DBA you are exposed to all this complexity and have to make good decisions about it. If you’d rather let the database guess what you need and hope for the best, you can always use the Other DB.

 

 

5 Unix tools that DBAs don’t use enough June 27, 2007

Oracle runs on an operating system, very often this operating is a variant of Unix. I’m pointing out the obvious because I’ve worked with many excellent DBAs who were not aware of this fact. They were masters of using everything that Oracle provided, usually in the form of V$ and even X$ views, but completely ignored the fact that Oracle runs on Unix, and Unix can also supply lots of information that can be used to monitor, diagnose issues or automate tasks.

Here are few of my favorites, at no particular order:

1) sar and vmstat – reporting current and historical system informatio, including CPU utilization, IO, memory utilization, time spent looking for memory, load average, etc. I begin almost every performance diagnosis session by checking what these tools can tell me. They never tell the whole story, but they give good hints.

2) strace – This gives a system-call level trace of what a process is doing. You usually want to use it when a process hangs and you want to know exactly what it was doing when it hanged. The output looks like this:
open("/proc/25062/environ", O_RDONLY) = -1 EACCES (Permission denied)
stat("/proc/27800", {st_mode=S_IFDIR|0555, st_size=0, ...}) = 0
open("/proc/27800/stat", O_RDONLY) = 6
read(6, "27800 (pickup) S 1534 1534 1534 "..., 1023) = 224
close(6)

Think twice before running this on a busy production system, as this baby will slow the traced process to a crawl and generate tons of IO.

3) gdb -c – run this when you find a core file in an Oracle directory and you want to know who crashed.

4) find – I’ll need a separate post about this wonderful tool. My favorite usage example is for deleting old user traces that usually just take up space. Lets delete those that are over two weeks old:
find /orahome/ora6410g/admin/ITGST/udump/ -name "*.trc" -mtime +14 -exec rm {} \;
This is just an example, find can do much more useful tricks. Its one of those utilities I’ll take when I have to work as a DBA on a deserted island.

5) Perl – Thats the other tool to take to a deserted island. Its a bit large to be called a tool, its an operating system with programing language thrown in. I use it for everything – our backup procedure is written in perl, we automatically generate reports for our customers from perl, disk space usage projections, etc. I’d write a post about this too, but it’ll probably grow to be a book.

60 seconds about ps June 24, 2007

If you use Unix, you’ve probably seen ps once or twice. Its one of the most useful tools in Unix. I use it to check if Oracle is up or not (by looking for smon process), check if a process I killed is dead or not, look for the parent of a process that is taking too much CPU, etc, etc.

I always knew that this tool has more options that just the good old ps -ef, but only today I found the time to really read the man page and see what they do. I found some cool stuff I want to share.

First, the selectors – you can ask ps to show just the processes that belong to a specific user (say, oracle) by running “ps -u oracle” will show all processes that belong to oracle.

There is also an option to select by command name, but it doesn’t work as expected, the command name is not what ps itself will show in the CMD column. So ps -fC oracle will show ora_smon_SID while ps -fC ora_smon_SID will show nothing.

Then, you can ask ps to show you certain columns:
ps -eo pid,ppid,pcpu,pmem,etime,time,comm,args will show you the process id, parent process if, cpu utilization, memory utilization, elapsed time, cpu time, command line and arguments.

And the last cool feature is sorting: you can sort by start time, user time, system time, page fault, user id and other useful parameters.
ps -ef –sort utime will show all the processes sorted by user time.

And combining everything I learned today, I can run:
ps -C oracle –sort cutime -o pid,ppid,pcpu,pmem,etime,time,comm,args
and find out that the oracle process that is taking most user time is ora_lms0_SID, but thats probably because I’m checking this on a rather unloaded system.

Key information is missing from the ps man page – it does not contain the list of parameters that –sort and -o (list of columns) will take. I found the lists here: http://www.ss64.com/bash/ps_keywords.html but I have to assume they exist on some man page, otherwise it is huge neglect by Unix documentation.

DBAs are plumbers June 22, 2007

Data Pump is my favorite feature in Oracle 10g.

Described as Export/Import on steriods, Data Pump is faster and much more manageable way to transfer data between Oracle systems and if you are not using it you don’t know what you are missing.

Many smart people have already written quite a bit about Data Pump and it is even part of the official Oracle 10g Top 20 features.

I found Data Pump beneficial in three scenarios:

1. We take a full export of each data base once a day. I’m aware that export is not recommended as a method to backup the database, however having a full export around already saved our behinds more than once, so I make sure we have one. Just in case.
   The daily export used to take 16 hours on a large database during which the CPU usage and IO waits on the system were too high for comfort. With Data Pump the daily export takes just 3 hours, which can be scheduled during off-business hours with minimal impact.
2. Large part of my routine daily work is to copy schemas from development to testing to production and vice versa. Since we have different tablespaces and different naming conventions on those environments, using export and import for the copy used to be a nightmare.
   We had to create the user in the new system, import the schema into SQL script, replace the schema name and tablespace name in the script, run it to create the schema and only then import the data. The process took around an hour.
   Now with data pump its a 5 minute job. You simply tell the import command to translate the schema name and tablespace name and it will even create the user in the new system for you, with all the correct permissions.
3. We also use Data Pump to create new schema. One of our applications demand that each customer will have its own schema, but all of these schema are identical (same tables, views, permissions, synonyms, etc). To create a new schema we simply use Data Pump to import an empty schema that we exported in the past with a new user name. This is not a huge improvement over running a script for creating new users, but it’s still pretty neat.

When routine maintenance goes bad June 21, 2007

Yesterday we had a routine maintenance to move the database of a large customer to a newer storage system. We knew it was not going to be completely straight forward because there was a security device involved. The database files (data, redo log, archives) are encrypted on the storage device, so if someone gets the data files, they are useless to him, the database access the storage though the security device which decrypts the data for the database.

Yes, if we ever have performance issues there we’ll know what to blame.

Anyway, since we know this is a complex setup we staged and tested the entire move in advance. It worked. Twice. So we were pretty confident that we can get everything done in about 10 minutes. Of course, we scheduled 3 hours maintenance, starting at 12pm.

At 12:30 we were running on the new device. At 12:40 the DB crashed. Short verification revealed that the disks with the data were gone and that we had I/O Errors when trying to access our redo logs and archives.

We got the support for the device on the phone, which escalated to tier 2, tier 3 and finally the development team. Slowly, the situation became painfully clear – all the data files are encrypted and the encryption key is corrupted. What’s worse, our backups are encrypted in exactly the same way, so they were all lost as well. We double and triple checked this – we had no way to access our backups.

This is even worse than the ASM scenario. How can we recover from such a crash? Luckily we discovered that the export files were kept unencrypted, so we could use the last export from 24 hours ago. Major loss of data, but it could be so much worse.

At 10pm our system team with the device development team finally managed to recover the most essential files – data files and redo logs. We could not recover the archived logs.

We started the database and it demanded media recovery, probably because it crashed when the disks disappeared. Since we had no log archives, we ran recovery until cancel, and immediately canceled the recovery. Then we could open the database with resetlogs, and this worked fine. Around 11pm we had a working database.

We did not realize how much risk the encryption adds to our recovery plans, and indeed to any maintenance. A single point of failure, which no one fully understood simply holds our database hostage. Thats as helpless as DBAs get.

What should we do better? Unfortunately, complete encryption was demanded by the customer so there was nothing we can do about it. I suggested encrypting the backups separately, with a different method and a different key, and if we really want to throw money at the issue we can have a separate DB, with separate security box and we can use Oracle Data Guard to move the data to the fail over system. That can be somewhat safe.

The database already crashed June 18, 2007

The is a zen koan that goes like this:

“One day some people came to the master and asked ‘How can you be happy in a world of such impermanence, where you cannot protect your loved ones from harm, illness and death?’ The master held up a glass and said ‘Someone gave me this glass, and I really like this glass. It holds my water admirably and it glistens in the sunlight. I touch it and it rings! One day the wind may blow it off the shelf, or my elbow may knock it from the table. I know this glass is already broken, so I enjoy it incredibly.’”

Indeed, breakage is inevitable. The database will crash, the datafiles will get corrupted, performance issues will occur and the db team will be blamed for all and any project delays.

Since all these issues are inevitable, and we know from the start that they will occur sooner or later, it is obvious that we need to prepare for them the best we can (remember, a backup plan that is not tested is broken by definition), but there is no reason to be surprised or panic when the inevitable happens, because was bound to happen.

And of course, don’t forget to enjoy the database when it working and appreciate those little moments when surprisingly nothing goes wrong.

OS bottle necks June 17, 2007

I’m reading “Oracle Insights” these days, and I simply can’t recommend it enough. The book is a collection of articles written by very smart and experienced DBAs, the articles range over many different topics, but most center around issues with performance and tuning. Every article contains countless insights and ideas, most of them are so practical you can try them at work the next day. But the best thing is that the book is so well written, so readable and so fun that like any good book you can barely put it down, you will find yourself laughing out loud and crying over broken systems. Its really nothing like any Oracle book you ever read.

Gaja Vaidyanatha wrote an article called Compulsive Tuning Disorder about the right and wrong way to tune system, and inside this article was a little gem about identifying OS bottlenecks. For me, this short section alone was worth the price of the book.

Vaidyanatha advocates using two tools for collecting metrics – sar and vmstat, and to look at just two metrics – CPU run queue and memory scan rate. He claims that these are the only two metrics that are important for database tuning – how many processes are waiting for CPU at a given time, and how long the memory manager takes before finding more memory. He then goes on a bit about the exact meaning of these metrics, what the ideal values are and a bit on the important insights you get from the CPU utilization breakdown that sar gives (For example, don’t add processing power if you have lots of iowait).

So simple, and it makes total sense. My life has been changed and improved after reading about 2 pages. Tomorrow I’ll add monitors to track these metrics on our production servers and see if it is as good in practice as it is in theory. I just love finding simple reasonable ideas that are written so well.

Why I’m still not using ASM June 15, 2007

Its simple: ASM scares me.

Every DBA knows that databases cannot be trusted: They hang, they crash, they get corrupted, they can use up all the cpu and generally speaking they can make our life difficult. File systems on the other hand, are quite harmless and most of the time they behave themselves and don’t cause issues.

So putting the file system on the database? Replacing the simple and stable system with a complex and jittery one? It never sounded like a good idea, and nothing Oracle said convinced me otherwise.

Today, I ran into a post that made me happy, because I like having my prejudices validated. Here’s the relevant quote:

” The instances won’t come up again and error ORA-01261 was reported when trying to startup a single instance from sqlplus.

So, i ended up with a bad SPFILE on an ASM diskgroup, that you cannot change unless the db is running! ”