Fixed – ORA-00844: Parameter not taking MEMORY_TARGET into account

In our previous article you have seen how to resolve ORA-19809: limit exceeded for recovery files error. In this article we will show you step by step guide to resolve "ORA-00844: Parameter not taking MEMORY_TARGET into account" error.

Error Message:

ORA-01078: failure in processing system parameters
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 9445460234 cannot be set to more than MEMORY_TARGET 4547687648.

Root Cause:

  • The SGA_MAX_SIZE parameter is larger than MEMORY_TARGET parameter.
  • Either you forgot to set the MEMORY_TARGET in the spfile after setting SGA_MAX_SIZE parameter or you have already set the MEMORY_TARGET parameter but forgot to set SGA_MAX_SIZE to 0.

Resolution:

First you need to remove the SGA_MAX_SIZE parameter from pfile and spfile. If you are running a spfile, you need to create a pfile from spfile. Connect with sys user using following command:


C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:17:53 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/sys@xe as sysdba
Connected.
SQL>

If you are unable to connect with SYS user, follow below steps:

C:\>set oracle_sid=xe

C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 16 11:19:37 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/sys as sysdba
Connected.
SQL>

Once you are connected with database, create pfile from spfile:

SQL> create pfile from spfile;
File created.

Or

SQL> create pfile = 'D:\oraclexe\app\oracle\product\11.2.0\server\dbs\init.ora' from spfile='D:\oraclexe\app\oracle\product\11.2.0\server\dbs\SPFILEXE.ORA';
File created.

SQL> exit
Disconnected

Open the dbs directory where newly created pfile is located. Remove following lines of code from pfile (this tutorial is based on Oracle 11g express):

Location: D:\oraclexe\app\oracle\product\11.2.0\server\dbs\init.ora

sga_max_size=2238535456
sga_target=0

Now recreate spfile from pfile:

SQL> create spfile from pfile;
File created.

Finally start your database with startup command.

SQL> startup
ORACLE instance started.
Total System Global Area  376635392 bytes
Fixed Size                  1383692 bytes
Variable Size             281019124 bytes
Database Buffers           88080384 bytes
Redo Buffers                6152192 bytes
Database mounted.
Database opened.


Subscribe to get free tech advice, tips and in-depth articles.


SiteGround Web Hosting Promo Discount Banner

Leave a Reply

Your email address will not be published. Required fields are marked *