As Oracle consultants we often get caught up in the esoteric areas of performance, such as contention, complex execution plans, and obscure parameters.  But we forget that sometimes it’s the little things that count, many of which we learned in the DBA101 phase of our careers.

I was reminded of this recently at one of our remote DBA clients.  The customer was having issues with flashback queries in a RAC cluster.  We discovered that the undo_retention setting on one instance was much less than on the others.  At first we couldn’t figure out how this was happening; the settings were correct in the spfile.  Then we looked in the $ORACLE_HOME/dbs directory of the instance and saw that there was a pfile (aka, an init.ora file) in the directory, with different settings for undo_retention.

As is typical in RAC installations, there was supposed to be a pfile whose only contents were a pointer to the spfile (because the spfile was in ASM).  But what had happened was that someone had executed the command “create pfile from spfile” in order to read or copy the spfile settings.  Of course, this overwrote the pfile which pointed to the spfile.  It didn’t cause an issue until the next time the instance was restarted; when that was done the instance then had a setting for undo_retention from an earlier time.

For this reason, my personal best practice for creating an editable pfile from an spfile is to put it into the /tmp directory–

 create pfile='/tmp/something.ora' from spfile;

That way I can read it, edit it for whetever use (such as making a clone), or whatever I want without risking having it used unexpectedly at some time in the future.  Others use

 create pfile='?/dbs/init_<not_the_sid>.ora' from spfile; 

There are many options, but the point is to not use the default which results when you don’t specify a pfile name.

Of course, one could say “the DBA should always check and clean up everything after their tasks”, which is true.  But avoiding the need for the cleanup by following a standard practice is a more thorough solution, in my opinion.

Not everything we do as DBAs is complex, convoluted, or esoteric.  Taking care of the little things can be as important as being able to optimize a 300 line query.