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 🙂

,

  1. #1 by Jan Karremans on August 31, 2016 - 16:52

    So easy and yet so effective!

  2. #2 by Kirill Loifman on 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. #3 by tricantino on September 12, 2016 - 13:45

    What about RAC with spfile stored in ASM?

Leave a Reply

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

WordPress.com Logo

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