Silly DBA Tricks 1: Editing the SPFILE

Here is an article I posted on the Oracle-L mailing list recently. Much to my surprise, people liked it enough that they asked to see it here, too.

This was written in response to the question: “How do you edit an SPFILE?”

—————————————————

There are lots of ways to do this — some supported; most not.

The supported ways basically amount to one of

• CREATE PFILE=… FROM SPFILE
• ALTER SYSTEM SET xxxx = yyyyyyyy SCOPE = SPFILE

I would normally recommend you stick to one of these.

If you insist on editing an SPFILE, you can, (I have done so successfully on a couple occasions) but it is a very bad idea(tm). The first thing to know is this:

• The documentation says SPFILEs are “binary” files.
• The documentation is only half-true.

In actual fact, the SPFILE is really just a simple text file, with binary (ASCII NUL) padding at the beginning and end. On a UNIX(-like) system, you can create a perfectly valid PFILE from any SPFILE with the command:

$ strings spfileMYSID.ora > initMYSID.ora

You *can* also edit it directly, providing you have a binary-capable editor (e.g., “emacs”). Forget editors like ‘vi’ — the standard ‘vi’ editor does bad things to binary files, like replacing ASCII NULs with spaces. Since SPFILEs use ASCII NUL for padding… (note: the Linux version of ‘vi’ — actually “vim” — probably doesn’t do this. Others may not any more, either. I wouldn’t know; I stopped trying to use ‘vi’ to edit binary files about 15 years ago.)

If you insist on editing your SPFILE, I have found that balancing my edits by adding or removing characters from the NUL padding at the end of the file works quite nicely. Of course, I’ve only actually done it about twice. The fact is, there are enough safe (and supported!) alternatives that you should never have to do this.