Have you ever hosed up your memory parameters such that you cannot get Oracle to even enter the startup nomount state?

Here I’ll do just that by setting sga_max_size to a value less than sga_target, and then trying to start the database.

First a sanity check so you can see the memory parameters. In this case I am using ASMM (automatic shared memory management), and not the new 11g AMM (automatic memory management) features.

sqlplus / as sysdba
SQL> show parameter memory_max_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memory_max_target                    big integer 0

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1056M

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 1G

SQL> show parameter pga_aggregate_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 350M

OK, let’s have some fun. We’ll set our parameters to something that doesn’t make sense by setting sga_max_size to 0. Then we’ll shutdown immediate.

SQL> alter system set sga_max_size=0 scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now we’ll start our DB back up…and notice that we cant! Panic! We get the ORA-00823 error.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size

Uh oh…not even a nomount works. Makes sense though. Nomount reads the pfile and allocates the memory for the instance, so no wonder it is failing there if we can’t properly allocate memory. I guess we can’t just fire this thing into nomount state and run our alter system commands.

SQL> startup nomount
ORA-01078: failure in processing system parameters
ORA-00823: Specified value of sga_target greater than sga_max_size

OK, no sweat. I’ll create my text based pfile from the spfile, edit this and be on my way.

SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Huh? Where’s my spfile? Can you feel the hairs stand on the back of you neck as the panic sets in that your DB is hosed? No worried, the spfile has to be there somewhere. After all, this was a previously running system. Let’s looks in the alert.log for how my instance booted previously.

cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
vi alert_orcl.log

A quick scan through the alert log reveals that there is indeed an spfile somewhere out in the universe:

spfile                   = "+DATA/orcl/spfileorcl.ora"

Ahh, that’s right. This instance is using ASM (automatic storage management). To create that pfile I need to tell the command exactly where that spfile is:

SQL> create pfile from spfile='+DATA/orcl/spfileorcl.ora';
File created.

Now we’re cooking! There’s the init.ora file we need:

[oracle@localhost dbs]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -lart initorcl.ora
-rw-r----- 1 oracle oinstall 1061 Apr 14 09:20 initorcl.ora

And we happily edit it in vi and change sga_max_size=0 to sga_max_size=1056M
Now our instance starts!

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initorcl.ora';
ORACLE instance started.
Total System Global Area 1102344192 bytes
Fixed Size                  2212576 bytes
Variable Size             335547680 bytes
Database Buffers          754974720 bytes
Redo Buffers                9609216 bytes

Overwrite our spfile with the new values from our pfile:

SQL> create spfile='+DATA/orcl/spfileorcl.ora' from pfile;
File created.

And then shutdown the database and restart. Voila, everything is back to life:

SQL> shutdown;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1102344192 bytes
Fixed Size                  2212576 bytes
Variable Size             335547680 bytes
Database Buffers          754974720 bytes
Redo Buffers                9609216 bytes
Database mounted.
Database opened.