The other day, in seeking inspiration for my first blog entry I went for a walk along the River Thames. Whilst there it struck me that us DBAs are not unlike swans. Now obviously I don’t mean that we are covered in white feathers, have long necks and eat bread, but rather that while the swans look elegant and relaxed on the surface, it is below the water line, out of sight that they are working hard. Similarly the tasks of a DBA can often involve much hidden effort in order to ensure smooth running of a database.

Consider the task of applying an Oracle Applications patch. To you, me, and hundreds of other DBAs this is a fairly routine task that offers little in the way of excitement or reward. Given the casual nature of many requests for patches, this is more than likely how the project manager or functional teams see it too. However, although it’s routine, it would be incorrect to consider it overly basic. Let’s think of the process and the effort required to ensure success, or rather, swan-like grace and composure…

Firstly, it’s necessary to work out the pre- and co-requisite patches. These days the Oracle developers appear to have improved on their readme.txt files so it’s all fairly straightforward. Then any superseded patches need to be accounted for, along with translations where necessary. Typically I end up with an A4-sized sheet of paper covered in scribbled patch numbers and lines joining them up, with arrows indicating the flow of dependency between each of the patches. Once I know the order in which they can be applied, the patches can be downloaded.

Many sites require the DBAs to first download patches via MetaLink to a desktop machine before FTP’ing onto the relevant servers, although it is sometimes possible to FTP directly onto the server from updates.oracle.com using a MetaLink account. I offer no opinion as to the preference of one approach over the other, though there may of course be security considerations when downloading directly to the server.

Assuming that there is enough space to stage the patches (don’t forget that a backup directory is created once the patch is running) and downtime has been agreed with the business, the quiet, unassuming DBA is ready to proceed, and this is where our swan-like grace kicks in.

Running adpatch is pretty straightforward, and the process occupies one terminal window fully until the patch either fails or completes. I like to tail the patch log so that I can see what’s going on. This is done in a second window. For large patches with lots of jobs, the time taken to apply the patch may justify having a number of other terminal windows available - perhaps monitoring individual worker logs. A trade-off here could be the number of workers; I recently worked at a site where we regularly used 24 workers. That’s a lot of logs to monitor, and in that instance would have been too much. Regardless, a third window that is useful is the adctrl utility that allows you to manage those worker processes.

There is a fourth too that may be advisable. Both myself and some of those I have worked with have been caught out by forgetting the database alert log! Funnily enough even at a recent interview when discussing the duration of Apps patches, my interviewer confessed to once wasting a significant amount of time waiting for utlrp.sql to complete before noticing that the redo logs had run out of space at the archive destination.

I also like having another window that is simply free - to monitor file-system space, investigate job failures, etc… So as a minimum I prefer to have five windows open for monitoring the patching process on any one tier. Some of them like the alert log may be duplicate windows but as far as I’m concerned, it’s worth the peace of mind.

It needn’t be a messy, screen-filled approach either - with tabbed support in terminal emulators, it really is possible to present a calm exterior whilst covertly monitoring several shell sessions and not seeing their contents fly past. Tabbing is freely available in Linux , whilst Mac OS X 10.5 will support tabs in its Terminal application, with iTerm doing so already. Windows terminal has WinTabber available, whilst putty can be modified with PuttyTabs.

The observant amongst you will have noticed that I haven’t referred to Enterprise Manager’s patching facilities, nor the merging of patches. The main reason for this is that I personally prefer not to use these tools, relying on the basics and I’m pretty sure I’m not alone. I have only ever encountered one client that used OEM for identifying and managing patches and even then they actually did their own research to ensure the software was reliable! Also, whilst merging patches can undoubtedly save time, I think it can often be a false economy since if anything goes wrong I like to know exactly where and when the fault occurs. There’s a lot to be said for the acronym KISS - keep it simple, stupid.

I chose patching as simple example of a basic task that can actually involve a lot of effort. There are undoubtedly others, and I would be interested in learning which activities other DBAs regard as such, along with recommendations or experiences (either good or bad) of different terminal applications and their features.

This entry was posted on Tuesday, July 31st, 2007 at 7:42 am and is filed under - Global Oracle Contractors Network, Oracle Database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.