Oracle pfile, spfile and parameters, as well as AMM

Jephe Wu -

Objective: Checking the current running parameters for Oracle database and configuring AMM (automatic memory managgement)
Environment: Oracle 11g 64bit, RHEL 5

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.

(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 -

2.  Configure Automatic Memory Management(AMM) on 11g [ID 443746.1]
Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

Add the values of pga_aggregate_target and sga_target. In our case it is 12g

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

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

2. does -/+ buffers/cache used column include SGA Oracle shared memory

testing below:

[oracle@oratest ~]$ sqlplus  / as sysdba

SQL*Plus: Release 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 - 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
                       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
                       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

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

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.