Posts Tagged ‘Memory_Target’

Recently, we have interesting situation where a development server is upgraded to 11g and it got crashed due to insufficient shared pool size, while running the automatic maintenance task which is part of daily maintenance window. Problem was that by mistake only MEMORY_MAX_TARGET parameter was set to enable the Automatic memory management AMM. MEMORY_TARGET parameter was missed in pfile, while SGA_MAX_SIZE is set with SGA_TARGET is again missed. In summary following was situation:

MEMORY_MAX_TARGET — set to value 6G
MEMORY_TARGET — Missed
SGA_MAX_SIZE — set to value 6G
SGA_TARGET — Missed

Now, to enable the automatic memory management AMM, it’s compulsory to set the MEMORY_TARGET parameter. From 11g onwards, it’s recommended to use the MEMORY* parameters only instead of using SGA* parameters. With above parameters set, when we start the database, then it will start with minimum amount of SGA allocated which is required to start the DB. In case the more memory is required by any component of SGA, automatic increase will not happen because AMM is not enabled. Below are the cases which confirms this behavior. Instance is started with parameters as shown below:

sga_max_size=5G
memory_max_target=6g


SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2230912 bytes
Variable Size            5167383936 bytes
Database Buffers          167772160 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 6G
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 17616076
shared_pool_size                     big integer 336M
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 5G
sga_target                           big integer 0


SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2230912 No
Redo Buffers                        7344128 No
Buffer Cache Size                 167772160 Yes
Shared Pool Size                  352321536 Yes
Large Pool Size                           0 Yes
Java Pool Size                     33554432 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 5344731136 No
Startup overhead in Shared Pool   291714216 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available        4781506560


SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 48M

Now, see the difference in size of components with MEMORY_TARGET set and AMM is automatically enabled. See the components comparison at end of this code example.

memory_max_target=6g
memory_target=5G

PDEV.__java_pool_size=16777216
PDEV.__large_pool_size=16777216
PDEV.__pga_aggregate_target=2147483648
PDEV.__sga_target=3221225472
PDEV.__shared_io_pool_size=0
PDEV.__shared_pool_size=603979776
PDEV.__streams_pool_size=0

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 6G
memory_target                        big integer 5G
shared_memory_address                integer     0
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 30198988
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 0
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2233480 No
Redo Buffers                       19324928 No
Buffer Cache Size                2533359616 Yes
Shared Pool Size                  603979776 Yes
Large Pool Size                    16777216 Yes
Java Pool Size                     16777216 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                       16777216 No
Maximum SGA Size                 6413680640 No
Startup overhead in Shared Pool   328714312 No

NAME                                  BYTES RES
-------------------------------- ---------- ---
Free SGA Memory Available        3221225472

12 rows selected.

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
SQL>

Component_Comp

Advertisements

While starting 11.2.0.3 database on Oracle Linux 5, i got the below error.

[oracle@NODE1 11.2.0.3]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 8 17:24:02 2013

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

SQL> conn sys as sysdba
Enter password: 
Connected to an idle instance.
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> 

Below are few lines from alert.log file for this error:

Thread 1 advanced to log sequence 115 (LGWR switch)
  Current log# 1 seq# 115 mem# 0: /u02/oradata/YYAPRD/redo01a.log
  Current log# 1 seq# 115 mem# 1: /u03/oradata/YYAPRD/redo01b.log
Mon Apr 08 17:24:12 2013
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be 
mounted for at least 2046820352 bytes. /dev/shm is either not mounted or is mounted with available space less 
than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1952489472 and 
used is 169725952 bytes. Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm

Well, for this error below is the explanation from oracle:

The new Automatic Memory Management functionality uses /dev/shm on Linux for SGA and PGA management. The errors occur if either MEMORY_TARGET or MEMORY_MAX_TARGET is configured larger than the configured /dev/shm size, or if /dev/shm is mounted incorrectly.

I followed the metalink doc 465048.1 to resolve the issue. But i found a very nice link explaning, what is /dev/shm and what it is used for. This link also contains the solution as suggested by oracle. I thought of mentioning this link here , so that one can get more details about this /dev/shm.