Jephe Wu - http://linuxtechres.blogspot.com
Objective: Checking the current running parameters for Oracle database and configuring AMM (automatic memory managgement)
Environment: Oracle 11g 64bit, RHEL 5
Commands:
1. Checking if the database is using pfile or spfile
sqlplus / as sysdba
select name,value from v$parameter where name='pfile';
select name,value from v$parameter where name='spfile';
2. generate pfile from spfile or memory
sqlplus / as sysdba
create pfile from spfile;
create pfile from memory;
create spfile from pfile; (if the database starts up with pfile initially, then need to change to spfile)
3. checking pfile,spfile and memory
sqlplus / as sysdba
show parameter memory_target;
show parameter pga;
show parameter sga;
show sga;
AMM configuration:
1. Configure tmpfs size
error message:
a. ORA-845: MEMORY_TARGET not supported on this system
b. 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 <size> bytes.The /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 <size> and used is <size> bytes.memory_target needs larger /dev/shm
If ORA-04031 is seen in the alert log, sometimes you can not establish new connections due to this problem.
Solutions:
(ORA-00845 When Starting Up An 11g Instance With AMM Configured. [ID 460506.1])
1. If you are installing Oracle 11g on a Linux system, note that Memory Size (SGA and PGA), which sets
the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory filesystem (/dev/shm) on your operating system. To resolve the current error, increase the /dev/shm file size. For example:
# mount -t tmpfs tmpfs -o size=12g /dev/shm
Also, to make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:
tmpfs /dev/shm tmpfs size=12g 0
note: tmpfs is previously called shmfs - http://en.wikipedia.org/wiki/Tmpfs
2. Configure Automatic Memory Management(AMM) on 11g [ID 443746.1]
Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.
SQL>SHOW PARAMETER TARGET
3.Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 12g
4. apply changes to spfile
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 12g SCOPE = SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET = 12g SCOPE = SPFILE;
SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;5. restart database
SQL> shutdown immediate;sql> startup (or startup mount, then alter database open)
sql> show parameter target;
-------------
Updated on 29 Nov 2012
1. How to look at Linux free command output
https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/5/html/Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases/chap-Oracle_9i_and_10g_Tuning_Guide-Memory_Usage_and_Page_Cache.html
2. does -/+ buffers/cache used column include SGA Oracle shared memory
testing below:
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 29 20:35:45 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> !free
total used free shared buffers cached
Mem: 2050676 1938452 112224 0 32112 869344
-/+ buffers/cache: 1036996 1013680
Swap: 5144560 49032 5095528
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !free
total used free shared buffers cached
Mem: 2050676 1265780 784896 0 32144 339040
-/+ buffers/cache: 894596 1156080
Swap: 5144560 49032 5095528
SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
48G 30G 16G 65% /
/dev/sda1 99M 23M 71M 25% /boot
tmpfs 1002M 0 1002M 0% /dev/shm
SQL> startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 637536288 bytes
Database Buffers 192937984 bytes
Redo Buffers 2412544 bytes
Database mounted.
Database opened.
SQL> !free
total used free shared buffers cached
Mem: 2050676 1872012 178664 0 32200 870120
-/+ buffers/cache: 969692 1080984
Swap: 5144560 49032 5095528
SQL> !df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
48G 30G 16G 65% /
/dev/sda1 99M 23M 71M 25% /boot
tmpfs 1002M 520M 482M 52% /dev/shm
[oracle@oratest ~]$ echo "(870120-339040)/1024" | bc
518
Note: When using Oracle, shared memory, which is used by Oracle process, (ipcs), it's caculated by cached column of 'free' command.
3. ipcs or /dev/shm for Oracle shared memory
Refer to http://www.toadworld.com/Newsletter/TWPIPELINEMay2009/PIPEMay09Oracle/tabid/575/Default.aspx
In Oracle 11g, When you use Automatic Memory Management (AMM) by memory_target or memory_max_target parameters. ipcs -m doesn't show any shared memory. It actually used /dev/shm for small files.
Posts
