Thursday, March 24, 2016

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

In this scenario I am trying to increase the value of parameter memory_max_target. My initial memory_max_target = 804 I want to increase it to 900

SQL> show parameter sga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga     boolean FALSE
pre_page_sga     boolean FALSE
sga_max_size     big integer 804M
sga_target     big integer 0

SQL> show parameter max_target

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target     big integer 804M

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

SQL> alter system set memory_max_target=900 scope=spfile;

System altered.

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

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

SQL> startup;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup mount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Since we can't login into DB to check the value  that was set. Lets create pfile and check the actual value.

SQL> create pfile from spfile;

File created.

[oracle@Linux01 ~]$ cd $ORACLE_HOME/dbs

[oracle@Linux01 dbs]$ ls -ll

[oracle@Linux01 dbs]$ vi initDB11G.ora


Haha .. here is the problem in my case.




*********************************************************************************
In my case the problem is that, I didn't mention the MEMORY_MAX_TARGET in MB
Changing the value to MB did the trick
*********************************************************************************




[oracle@Linux01 dbs]$ sqlplus  /"AS sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 24 23:46:18 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

Below reboot not needed but since I want to use spfile. I did it

SQL> startup pfile='$ORACLE_HOME/dbs/initDB11G.ora';
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

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

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL>


SQL> show parameter memory;

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


2 comments :