Correcting invalid spfile parameters

Consider the following situation. An alteration is made to the spfile which results in the instance being unable to start. Because the instance will not start, the mistake can not be corrected:

SQL> show parameter sga_max_size
sga_max_size                         big integer 537989896

SQL> alter system set sga_max_size=99999999999 scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-27102: out of memory

SQL> startup nomount
ORA-27102: out of memory

SQL> alter system set sga_max_size=537989896 scope=spfile;
alter system set sga_max_size=537989896 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available

The usual way to fix this problem (apart from being more careful in the first place) is to:

• create pfile from spfile
• edit the pfile
• startup nomount
• create spfile from pfile
• shutdown
• startup
• remove the pfile

There is another way however - and one that I prefer. It relies on the fact that a database can have a spfile and a pfile at the same time, and furthermore parameters specified in the pfile override those in the spfile! The spfile location must be specified in the pfile for this to work. Check out the following trace:

SQL> !
[oradev@testerp] $ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

spfile=/u02/oradata/scr9/spfilescr9.ora
sga_max_size=537989896

:wq

oracle@bloo$ exit

SQL> startup
ORACLE instance started.
Total System Global Area  554767132 bytes
Fixed Size                   451356 bytes
Variable Size             402653184 bytes
Database Buffers          150994944 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> alter system set sga_max_size=537989896 scope=spfile;
System altered.

SQL> !rm $ORACLE_HOME/dbs/init${ORACLE_SID}.ora

SQL>

Sukhwinder Singh

Apps DBA