When you start your database and it throws following error:
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.
Cause: The SGA_MAX_SIZE parameter is larger than MEMORY_TARGET parameter.
May be you forgot to set the MEMORY_TARGET in the spfile after setting SGA_MAX_SIZE parameter or already set the MEMORY_TARGET parameter but forgot to set SGA_MAX_SIZE to 0.
Now to start your database, you need to remove the SGA_MAX_SIZE parameter from pfile and spfile.
If you are running a spfile, you must need to create a pfile from spfile. First connect with sys user using following command:
C:\>sqlplus /nologSQL*Plus: Release 184.108.40.206.0 Production on Sat Nov 16 11:17:53 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.SQL> conn sys/sys@xe as sysdbaConnected.SQL>
If you are still unable to connect with SYS user, follow below steps:
C:\>set oracle_sid=xeC:\>sqlplus /nologSQL*Plus: Release 220.127.116.11.0 Production on Sat Nov 16 11:19:37 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.SQL> conn sys/sys as sysdbaConnected.SQL>
Once you are connected with database, create pfile from spfile:
SQL> create pfile from spfile;File created.OrSQL> create pfile = 'D:\oraclexe\app\oracle\product.2.0\server\dbs\init.ora' from spfile='D:\oraclexe\app\oracle\product.2.0\server\dbs\SPFILEXE.ORA';File created.SQL> exitDisconnected
Now, open the dbs directory which contain the newly created pfile and remove following lines from pfile (example based on Oracle 11g express):
Now recreate spfile from pfile:
SQL> create spfile from pfile;File created.
Now startup the database will be successful:
SQL> startupORACLE instance started.Total System Global Area 376635392 bytesFixed Size 1383692 bytesVariable Size 281019124 bytesDatabase Buffers 88080384 bytesRedo Buffers 6152192 bytesDatabase mounted.Database opened.
If this post helps you to resolve your problem then don’t forget to like and share with your friends as well.