How to fix a problem with the spfile in #Oracle

An invalid entry in the spfile may prevent the instance from starting up:

SQL> alter system set sga_target=500m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00821: Specified value of sga_target 512M is too small, needs to be at least 1392M

The instance doesn’t come up! This is easy to fix without having to restore the spfile from backup:

SQL> create pfile='/home/oracle/init.ora' from spfile;

File created.

SQL> host vi /home/oracle/init.ora

Now correct the value in the text file. I just removed the sga_target parameter from it here. Then

SQL> create spfile from pfile='/home/oracle/init.ora' ;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size		    2924112 bytes
Variable Size		  218104240 bytes
Database Buffers	 1107296256 bytes
Redo Buffers		   13852672 bytes
Database mounted.
Database opened.

Problem solved! That fix works for any invalid entry in the spfile, not only for sga_target. I don’t know how often I had to do that over the years</p />
</p></div>

    	  	<div class=