Top 60 Oracle Blogs

Recent comments


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

Getting the database parameters from a spfile

There’s been some debate about how to get the parameters from a spfile. A spfile is a binary version of the parameter file of the Oracle database.

I added to the debate that my experience is that there are is some weirdness with using the strings command on the spfile. The discussion was on twitter, I didn’t add that doing that it most of the time meant it costed more time than I saved from using the “shortcut” of using strings on a spfile.

Let me show you what it means.

I’ve got a database with storage on ASM. Among other options, there are two simple methods to get the spfile from ASM:

You can get the spfile by logging on to the database, and create a pfile from the spfile, and create a spfile again: