ASM version 10 and the spfile on ASM

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!)

Advertisements
7 comments
  1. Harald van Breederode said:

    How about SQL> create pfile from spfile=’+/’; ?

  2. Good point!

    This would be resolution 4, which is “kind of” simple:

    -get the spfile name in ASM
    a) The default location (of a database created with dbca in version 10.2) of the spfile is +//spfile.ora
    b) As an alternative the asmcmd utility can be used to browse the ASM directories, and find the spfile

    -start sqlplus as sysdba and copy the spfile to a pfile:
    The caveat here is the ‘create pfile from spfile’ command doesn’t need a running instance (whilst ‘alter system set parameter=value scope=spfile’ needs at least nomount)

    sqlplus / as sysdba

    SQL> create pfile=’/tmp/init.ora.copy’ from spfile=’+//spfile.ora’;

    File created.

    SQL> exit

  3. ps: +//spfile.ora means: +diskgroup/dbname/spfiledbname.ora

  4. Klaas-Jan said:

    Or you could re-create the pfile by picking up the instance specific and non-default parameters from the alert log files on your instance. They are printed in the alert log when you start an instance. Then re-create your spfile from this pfile, this helped me on my RAC environment once.

  5. You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

  6. Andy Rivenes said:

    Or you could just add the offending parameter (corrected) to the init.ora file. Oracle will use the last defined “version” of the parameter as the current one. Then once the instance is started you can fix the spfile and remove the parameter definition added to the init.ora.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: