Oracle Musings

A while ago I went on a crusade within my organization to review and clean up our init.ora files.  Many of them had been around since versions 7.3 and 8.1 of Oracle and were simply added to over time.  I still like the text-based init.ora files that I can check into source code control and liberally comment.  I’m liking the fact that you can comment on parameters in spfiles too — they even have the comment fields displayable in DB Console and Grid Control.

I’m constantly amazed at the places I go where I still see the following text in their init.ora files:

# Use the following table to approximate the SGA size needed for the
# three scenarios provided in this file:
#
#                     ——-Installation/Database Size——
#                      SMALL           MEDIUM           LARGE
#  Block         2K    4500K            6800K           17000K
#  Size          4K    5500K            8800K           21000K

I’m guessing the init.ora file isn’t being reviewed at those places.

Anyway, I started doing this when I realized that many of the default values for particular parameters were higher / better than the ones we had “set”.  And we didn’t have any documented reasons for setting them.  I ended up with 2 goals:

1. When the default values provided by Oracle are greater than or “better than” the values we had “set”, remove the parameter from the file
2. When we need to set a parameter, we need to include a comment as to why for each and every parameter

End result was a lot more clarity around our settings and why we needed them.  We also were able to basically make an init.ora template for ALL databases, since we made such heavy use of defaults.

What’s your policy for init.ora files?  Do you even have one?

I’d like to direct your attention to Chen Shapira’s latest blog entry, in which she talks about Oracle Streams.  Having been a replication aficionado for years, I’ve always been interested in Streams, but slightly awed by their complexity and flexibility.  I’m looking forward to the follow-up entries, as I’ve recently begun working with them myself.  Perhaps we can all add to the collective knowledge on them.  I can say this, you’ll be learning a lot about things you may not have played with before: Advanced Queuing (especially propagation), LogMiner, and (coolest of all, in my opinion) networked DataPump (in 10g and up).  Just try to keep focused on what you’re trying to do and break Streams down into Capture processing, Propagation processing and Apply processing.  Even though it’s about the older Advanced Replication, you may even want to read my old paper.

I know it’s been a while, and I did get one of the New Year’s “tag you’re it” posts (thanks Doug) — but I cant’ bring myself to “fill it out”.  Seems like odd info to me that wouldn’t be very interesting to you.  Anyway, I’ve got a question related to some recent work I’ve been doing interviewing people.

“Do DBAs need to know SQL?”

Define “know” anyway you want.  I’m curious since many of the people I’ve been interviewing seem to be taken aback by a few simple SQL questions — telling me that DBA’s don’t do SQL — they manage and administer databases.  That SQL is for developers.  On a related note:

“Do DBAs need to be able to tune a database?”

Define “tune” anyway you want.  I’m also surprised at the number of candidates who think that tuning is a COTS vendor or developer responsibility.