Frits Hoogland Weblog

In a normal, default ASM situation, the database using ASM has a pfile in $ORACLE_HOME/dbs with one line:

SPFILE='+DISKGROUP/instancename/spfiledbname.ora‘

This means the actual parameter file (the spfile, the binary version of the parameter file) is situated in ASM. This is an understandable default, because in a clustered situation, it’s convenient to have your parameter file on a single location.

But, as stated in several discussions about pfile and spfile (the textversion and the binary version) now many years ago, there is no check if the setting which is done in the spfile makes any sense, or is possible. The only checks which are done when using the ‘alter system set parameter=value scope=spfile’ are if the parameter exists, and if the value set is of the same type as the parameter type (integer, boolean, string, big integer).

This means that it’s possible using ‘alter system’ to set incorrect values to parameters. Incorrect means makes it impossible to start the instance in this context.

If a parameter, say ‘processes’ is set to an invalid value, say ‘0′ (zero) it leads to an interesting situation if that instance is stopped and started:

SQL>
SQL> startup
ORA-03113: end-of-file on communication channel
SQL>

I think we could say this errormessage isn’t very descriptive, but that is another issue.

At this point it is now impossible to start the instance. And, because it is impossible to start the instance even in nomount mode, we can not alter the spfile. (this depends on the parameter changed of course, but processes set to 0 leads to this situation!)

When not using ASM, such a situation was easy solvable by creating a pfile from the spfile on the operating system level:

[oracle@asmtest dbs]$ strings spfilemydatabase.ora > initmydatabase.ora

And edit the incorrect parameter. But it’s not possible to copy files out of ASM in version 10. So we are stuck.

What can be done when encountering such a situation?

1. Restore spfile from backup
Of course one should (and must) backup the database including the spfile with RMAN, so at this point you should restore the spfile with the old and correct settings. (please mind that you can modify the parameters in advance, so if you got a real tight rotation policy, and the parameter is modified long before, you could again be stuck with an incorrect spfile)

It is not unthinkable you are tweaking parameters after you have installed the database, in order to get the database ready for whatever it is supposed to do. Probably when you are tweaking parameters, you do not do a backup in advance. Well, I have to speak for myself, but I do a backup after the database is correctly running for its purpose. See point 3 for what to do to have the possibility to correct it.

2. Copy the spfile out of ASM
With version 11, the possibility exists to use the ‘asmcmd’ commandline tool to copy files between ASM and the local filesystem.
Please mind this article is specifically about version 10, where such a function does not exist. This leaves this option unusable in version 10.

3. (re)Create the spfile from a pfile
The only option left is to recreate the spfile. This means you should make a backup copy of the spfile using the create pfile='/home/oracle/inityourdatabase.ora.date‘ from spfile before making any changes.

This results in a pfile, which should be kept at least until the database is restarted, so in case of incorrectness of the spfile it can be used to start and recreate the spfile.

3.a fetch the spfile directly from the ASM device
Another option is to dump the ASM blocks which contain the spfile (see “Example1″). This can be a lifesaver in case you encounter a customer’s situation which is stuck. (thanks Luca!)