3-2-1 Initialize

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? :-)

6 Responses to “3-2-1 Initialize”

  1. Doug Burns Says:

    where I still see the following text in their init.ora files:

    Heh, heh … I know that you mean!

    Thanks for this blog – I’ve referenced it myself today to help draw attention to all the unloved pfile/spfiles out there.

  2. APC Says:

    So, when you changed the files, did you break anything ? Did anything improve (beyond the clarity of the files)?

    Cheers, APC

  3. ddelmoli Says:

    Nothing broke. Didn’t see any system behavior changes. There may have been “unnoticeable” improvement :-). Just reducing the number of parameters under consideration helped out in discussions about changes to the file.

  4. Andy C Says:

    I also view the inclusion of template text as bad practice.

    I also used to get fed up with anonymous changes. ‘Err, who changed this parameter ? Why ? When ?’

    To address this, I always placed ‘init.ora’ configuration files under source control. To me, they were no different and just as important from source code.

    A daily checkout (overwriting any local changes) soon made sure the use of CVS was universally adopted :-)

  5. ddelmoli Says:

    Daily overwrite — I like that!

  6. Doug Burns Says:

    APC said …

    “So, when you changed the files, did you break anything ? Did anything improve (beyond the clarity of the files)?”

    I think it’s also important to consider that even if nothing improved, the parameters that were set for some reason, once upon a time should be having a positive effect (i.e. the reason still exists) because, if not, they could well be ‘breaking’ a potential improvement in later versions.

    … and I know you know that, because you were sitting in the same presentations in San Francisco ;-)

    I’ll assume you aren’t suggesting that DBAs would just change parameters for the hell of it and break things. That would never happen.

    Have you been burnt by a DBA or something recently? (Either metaphorically or literally! LOL)

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder