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
*********************************************************************************
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
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
Thankyou sir for your exact answer.
ReplyDeleteGod bless you real good sir.
ReplyDelete