Correcting invalid spfile parameters

From :

tnsManager – Distribute tnsnames the easy way and for free!
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

How annoying! 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> !

oracle@bloo$ vi $ORACLE_HOME/dbs/init${ORACLE_SID}.ora



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


Leave a Reply

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

You are commenting using your 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: