How to fix a problem with the spfile in #Oracle

fix

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 🙂

spfile, troubleshooting

Dieser Eintrag wurde erstellt am August 31, 2016, 16:44 und wurde abgelegt unter TOI. Du kannst die Antworten auf diesen Beitrag über RSS 2.0 verfolgen. Du kannst eine Antwort schreiben oder einen Trackback von deiner eigenen Seite schicken.

#1 von Jan Karremans am August 31, 2016 - 16:52

So easy and yet so effective!
#2 von Kirill Loifman am August 31, 2016 - 17:50

great fix!
I also recommend to backup your spfile before any offline spfile manipulations on production systems.
Later in case of issues during the DB restart simply back it out. This can slightly reduce the downtime.
— Kirill Loifman
#3 von tricantino am September 12, 2016 - 13:45

What about RAC with spfile stored in ASM?
#4 von Jonas Ruiz M. Junior (@jonasruiz) am Mai 9, 2017 - 14:34

better fix for the ORA-00821, thank you so much!