How to Startup a Misconfigured Database ORA-00837

A misconfigured database is a database which was set with wrong values of initialization parameters in SPFILE, it will prevent the database from startup. Let's see a typical example that can reproduce the problem:

Reproducing ORA-00837

1. We are trying to change MEMORY_TARGET. First of all, we should check the original value.
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 804M
memory_target                        big integer 804M
shared_memory_address                integer     0

2. Change MEMORY_TARGET to a lower value, it succeed.
SQL> alter system set memory_target=800M scope=both sid='primdb1';

System altered.

3. Change MEMORY_TARGET to a higher value with scope=both, but fails.
SQL> alter system set memory_target=900M scope=both sid='primdb1';
alter system set memory_target=900M scope=both sid='primdb1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET


The database prevents you from doing wrong things within memory scope, it's a protection. But you are allowed to change the parameter within spfile scope, because the database will assume that you know what you are going to do.

4. Mistakenly change MEMORY_TARGET to a higher value with SCOPE=SPFILE, it succeeds.
SQL> alter system set memory_target=900M scope=spfile sid='primdb1';

System altered.


This step is a common mistake, and also a dangerous action, because Oracle trusts your decision and it always succeeds with SCOPE=SPFILE.

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

   We usually shutdown the database after a reconfiguration in order to take the new value effect.

6. Database cannot startup
SQL> startup
ORA-01078: failure in processing system parameters
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

The database detect a misconfigured parameter, and won't startup, the root cause is in the error stack, which is ORA-00837, MEMORY_TARGET is higher than MEMORY_MAX_TARGET.

Our goal is to undo the mistake. The saving steps to a misconfigured database are usually the same, no matter which parameter is misconfigured. But you must know which one and what value must be restored. If you have no clue about it, you can follow the error message to trace the source. If you have backup of SPFILE before alteration, you can start it over.

Solutions to ORA-00837

If your database is still running, you can change your parameter back to the original value. If your database refuses to startup, you have some troubles to deal with. I hope you have a backup of SPFILE before alteration. You can restore it and start it over. If you don't have any backup of SPFILE, the following solutions might be helpful.

1. Reconstructing SPFILE from PFILE

If you have not any backup of spfile, it will be more tricky to restore the value. Here are the steps that you may take.

1. Create a PFILE from a misconfigured SPFILE under offline.
SQL> create pfile='/tmp/init-20121025.ora' from spfile='+data/compdb/spfileprimdb.ora';

File created.


You should specify the correct path of SPFILE that is usually for normal startup, and yes, you can create PFILE without the database online.

2. Restore the value of the misconfigured parameter.
$ vi /tmp/init-20121025.ora
...


You can modify the value into acceptable one or remove the line to imply the database to startup with default values. Then save the file.

3. Restore spfile with the modified pfile.
$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create spfile='+data/compdb/spfileprimdb.ora' from pfile='/tmp/init-20121025.ora';

File created.


You'd better to specify the full path of spfile, otherwise the created spfile will go to the default destination of local instance, not the shared destination of RAC. For more about ineffective SPFILE, you may refer to my another post: Why SPFILE Does Not Work in RAC

4. Startup database.
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             792725496 bytes
Database Buffers           41943040 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.

5. Check everything is OK.
SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

2. Restoring SPFILE via RMAN

You can also use RMAN to restore the spfile from AUTOBACKUP under NOMOUNT state of database, if you have autobackup on.

1. Startup nomount with any used pfile.
SQL> startup nomount pfile='/tmp/init-20121025.ora';

2. Restore spfile.
RMAN> restore spfile from autobackup;

3. Bounce the database.
SQL> shutdown immediate; SQL> startup;

For more approaches to restore SPFILE via RMAN, you can check this post: When the SPFILE is Missing

Knowing what value of a parameter that Oracle can accept is crucial before doing any alteration on SPFILE. You may check this post for more: How to Resolve ORA-02097: Parameter Cannot be Modified Because Specified Value is Invalid.