SPFILE's

When using the new 9i SPFILE facility, changes to parameters in the SPFILE are checked individually for correctness, but not combinations of parameters. Thus, its quite easy to create a scenario where your SPFILE will no longer permit you to restart your database.

For example, in a default 9i database, you can issue:

SQL> create spfile from pfile = '/u01/oracle/admin/db1/pfile/initdb1.ora';

(then shutdown / restart to activate the spfile)

SQL> alter system set db_block_buffers = 1000;

This will work, but (unbeknown to you) you now have both db_cache_size set (assuming a default 9i install) and db_block_buffers - which is not permitted. If you realise the error before the next database restart, then you can resolve the problem with:

SQL> alter system reset db_block_buffers scope=spfile sid='*';

to remove the change you just made. But if you shutdown without fixing the error, when you try to restart, the database will return an error (even if you try a startup nomount)

Resolutions:

1. if you still have the init.ora file, you can remove the spfile so that the database will use the pfile (as opposed to the spfile). Problem is the init.ora may not be up to date.

2. Run strings on the spfile (or open it in Notepad depending on your platform). The spfile is 'binary' but you can see all of the parameters and their values in there in plain text. These can be extracted to make a new init.ora file. You then follow (1) as above

3. In 9.0.1.2 (I dunno about 9.0.1), you can issue

   SQL> create pfile = '/u01/oracle/admin/db1/pfile/initdb1.ora' from spfile;
   

   even if a database and/or instance is down. You then follow (1) above. (Thanks to Pete Sharman for this last one)

Thus, even though we now have the convenience of spfiles, you still need to take care.