Monday, December 3, 2018

All about HugePages for Oracle Database

4.1.5 Restrictions for HugePages and Transparent HugePages Configurations
Review the following guidelines for HugePages and Transparent HugePages. Oracle recommends that you disable Transparent HugePages, because they may
causes delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database
single instances. Oracle continues to recommend using standard HugePages for Linux. Transparent HugePages memory is enabled by default with Red Hat
Enterprise Linux 6, Red Hat Enterprise Linux 7, SUSE 11, Oracle Linux 6, and Oracle Linux 7 with earlier releases of Oracle Linux Unbreakable
Enterprise Kernel 2 (UEK2) kernels.
Transparent HugePages memory is disabled by default in later releases of UEK2 and later UEK kernels.
Note: Although Transparent HugePages is disable on UEK2 and later UEK kernels, Transparent HugePages may be enabled by default on your Linux system.
Transparent Hugepages are similar to standard HugePages. However, while standard HugePages allocate memory at startup, Transparent Hugepages memory uses the
khugepaged thread in the kernel to allocate memory dynamically during runtime,using swappable HugePages.
HugePages allocates non-swappable memory for large page tables using memorymapped files. HugePages are not enabled by default. If you enable HugePages, then
you should deduct the memory allocated to HugePages from the available RAM before calculating swap space. Refer to your distribution documentation and to Oracle
Technology Network and My Oracle Support for more information.
During Oracle Grid Infrastructure installation, the Grid Infrastructure Management Repository (GIMR) is configured to use HugePages. Because the Grid Infrastructure
Management Repository database starts before all other databases installed on the cluster, if the space allocated to HugePages is insufficient,
then the System Global Area (SGA) of one or more databases may be mapped to regular pages, instead of Hugepages, which can adversely affect performance.
Configure the HugePages memory allocation to a size large enough to accommodate the sum of the SGA sizes of all the databases you intend to install on
the cluster, as well as the Grid Infrastructure Management Repository.
For detailed explanation check the references.

To check transparent HugePages
[oracle@testrac1 dbs]$ egrep 'trans|thp' /proc/vmstat
nr_anon_transparent_hugepages 0
thp_fault_alloc 0
thp_fault_fallback 0
thp_collapse_alloc 0
thp_collapse_alloc_failed 0
thp_split 0
thp_zero_page_alloc 0
thp_zero_page_alloc_failed 0

To check HugePages Size
[oracle@testrac1 ~]$ grep Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

To check HugePage
[oracle@testrac1 dbs]$ grep HugePages /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0

Add value to configuration file and apply it
[root@testrac1 ~]#vi /etc/sysctl.conf
# HugePages
vm.nr_hugepages =2864

[root@testrac1 ~]# sysctl -p

[root@testrac1 ~]# grep HugePages /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:    2864
HugePages_Free:     2864
HugePages_Rsvd:        0
HugePages_Surp:         0

References:
https://www.carajandb.com/en/blog/2016/7-easy-steps-to-configure-hugepages-for-your-oracle-database-server/
https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
https://www.thegeekdiary.com/centos-rhel-67-how-to-configure-hugepages/
https://docs.oracle.com/cd/E37670_01/E37355/html/ol_config_hugepages.html
https://docs.oracle.com/cd/E11882_01/server.112/e10839/appi_vlm.htm#UNXAR393
https://wadhahdaouehi.tn/2016/10/enable-hugepages-rhel-7ol-7-within-oracle-database-12c/
Metalinks :
https://mehmandba.blogspot.com/2018/05/hugepages-on-linux-what-it-is-and-what.html
https://mehmandba.blogspot.com/2018/05/oracle-linux-shell-script-to-calculate.html




Friday, November 30, 2018

ORA-27090 - Unable to Reserve Kernel Resources for Asynchronous Disk I/O (Doc ID 579108.1)

In this Document

Symptoms
Cause
Solution
References


Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12.1.0.2 [Release 10.2 to 12.1]
Linux x86-64
Linux x86
***Checked for relevance on 02-Sep-2016***
Oracle Server Enterprise Edition - Version: 10.2.0.4
10.2.0.4
***Checked for relevance on 26-AUG-2014***



Symptoms

You discover messages of this nature in your alert log:

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA

Additional information: 3
Additional information: 128
Additional information: 65536

- OR -


ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O

Additional information: 3
Additional information: 128
Additional information: 65536

- OR -

ORA-27090: Message 27090 not found;  product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call
Additional information: 3
Additional information: 128
Additional information: 65536


Strace against DBWR shows:

io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),

which is related to "aio-max-nr" kernel limit.

Cause

The "aio-max-nr" kernel limit is too low.

Solution

The "aio-max-nr" kernel limit should be adjusted according to Oracle recommendations which are available in this document:

Oracle Validated Configuration:
http://linux.oracle.com/pls/apex/f?p=102:2:2007385298933639::NO::P2_VC_ID:426

Also, this parameter should be set as follows:
-------------------------------------
fs.aio-max-nr= 3145728
-------------------------------------


NOTE:
Per bug Bug 13554729 - CORRECT FS.AIO-MAX-NR = 3145728
the published limit in the Oracle Documentation at http://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#BHCCADGD is incorrect.
The Oracle documentation lists the minimum value as the max value.

Oracle Database Related Kernel Parameters

We add kernel parameters to /etc/sysctl.conf  manually or automatically with preinstall before Oracle installation started. Or we can modify these parameters after installation.
[root@rac1 fs]# cat /etc/sysctl.conf 
# oracle-database-server-12cR2-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744
# oracle-database-server-12cR2-preinstall setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 256
# oracle-database-server-12cR2-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096
# oracle-database-server-12cR2-preinstall setting for kernel.shmall is 1073741824 on x86_64
kernel.shmall = 1073741824
# oracle-database-server-12cR2-preinstall setting for kernel.shmmax is 4398046511104 on x86_64
kernel.shmmax = 4398046511104
# oracle-database-server-12cR2-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1
# oracle-database-server-12cR2-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
# oracle-database-server-12cR2-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
# oracle-database-server-12cR2-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
# oracle-database-server-12cR2-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
# oracle-database-server-12cR2-preinstall setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2
# oracle-database-server-12cR2-preinstall setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter = 2
# oracle-database-server-12cR2-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
# oracle-database-server-12cR2-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

Apply kernel parameters without server restart.
/sbin/sysctl -p
or
sysctl -p /etc/sysctl.conf

We can also check the status of parameters in Linux with following commands:
Example: Check aio status - current and max
[oracle@rac2 ~]$ sysctl -a | grep aio

fs.aio-max-nr = 1048576         --> This is the max number of allowable concurrent requests.
fs.aio-nr = 2088704                 --> This is the actual usage

[oracle@rac2 ~]$ cat /proc/sys/fs/aio-nr
2088704

As we can see from outputs actual usage is bigger than the max limit. So we have to optimize the kernel parameter. This is related to ASM and Oracle explained in following links:
 Error related to this parameter: ORA-27090 - Unable to Reserve Kernel Resources for Asynchronous Disk I/O

Wednesday, October 24, 2018

ASMIOSTAT Script to collect iostats for ASM disks (Doc ID 437996.1)

In this Document
Purpose
Requirements
Configuring
Instructions
Script
Sample Output

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.3 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.

PURPOSE

The OS command iostat is normally used to monitoring system input/output device load. This script will provide similar information like iostat  but specific for the ASM disks.
For details about iostat (ie cumulative or not, and so on) please refer to the man of iostat.

REQUIREMENTS

We use v$asm_disk_stat instead of v$asm_disk because the information is exactly the same.
The only difference is v$asm_disk_stat is the information available in memory while v$asm_disk access the disks to re-collect some information. Since the information required doesn't require to "re-collect" it from the disks, v$asm_disk_stat is more appropriate here.

On Solaris, please use :
- the /usr/xpg4/bin/grep utility instead of /usr/bin/grep to avoid the following error:
"grep: illegal option -- q"

- the /usr/xpg4/bin/awk utility instead of /usr/bin/awk to avoid the following error:
"awk: syntax error near line 48"
On other platforms, it could sometime fail due to Shell compatibility issues.
In such case, retry using another Shell  (ie: Bash instead of Ksh)
We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. This script is provided as an example. If it doesn't work, you must adapt it by yourself for your platform 
In 10.1 asmcmd utility is not included:
asmcmd can be used against ASM versions 10gR1 (10.1.0.n) and 10gR2 (10.2.0.n). In ASM version 10.2 asmcmd is provided by default ASM installation.
To use asmcmd in ASM version 10.1 environment we can just copy relevant files from 10.2 installation into the 10.1

CONFIGURING

Not required.

INSTRUCTIONS

Not required

CAUTION

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

SCRIPT

NOTE:     Check the attachments (asmiostat.zip) for the generic script and for platform specific scripts such as AIX/RHEL/HP UX refer (asmiostat_AIX_RHEL_HPUX.zip) attachment

#!/bin/ksh
#
# NAME
#   asmiostat.sh
#
# DESCRIPTION
#   iostat-like output for ASM
#   $ asmiostat.sh [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g Diskgroup]
#                  [-f disk path filter] [<interval>] [<count>]
#
# NOTES
#   Creates persistent SQL*Plus connection to the +ASM instance implemented
#   as a ksh co-process
#
# AUTHOR
#   Doug Utzig
#
# MODIFIED
#   dutzig    08/18/05 - original version
#

ORACLE_SID=+ASM

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

endOfOutput="_EOP$$"

typeset -u diskgroup
typeset diskgroup_string="Disk Group: All diskgroups"
typeset usage="
$0 [-s ASM ORACLE_SID] [-h ASM ORACLE_HOME] [-g diskgroup] [<interval>] [<count>]

Output:
  DiskPath - Path to ASM disk
  DiskName - ASM disk name
  Gr       - ASM disk group number
  Dsk      - ASM disk number
  Reads    - Reads
  Writes   - Writes
  AvRdTm   - Average read time (in msec)
  AvWrTm   - Average write time (in msec)
  KBRd     - Kilobytes read
  KBWr     - Kilobytes written
  AvRdSz   - Average read size (in bytes)
  AvWrSz   - Average write size (in bytes)
  RdEr     - Read errors
  WrEr     - Write errors
"

while getopts ":s:h:g:f" option; do
  case $option in
    s)  ORACLE_SID="$OPTARG" ;;
    h)  ORACLE_HOME="$OPTARG"
        LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH ;;
    g)  diskgroup="$OPTARG"
        diskgroup_string="Disk Group: $diskgroup" ;;
    f)  print '-f option not implemented' ;;
    :)  print "Option $OPTARG needs a value"
        print "$usage"
        exit 1 ;;
    \?) print "Invalid option $OPTARG"
        print "$usage"
        exit 1 ;;
  esac
done
shift OPTIND-1

typeset -i interval=${1:-10} count=${2:-0} index=0

#
# Verify interval and count arguments are valid
#
(( interval <=0 || count<0 )) && {
  print 'Invalid parameter: <interval> must be > 0; <count> must be >= 0'
  print "$usage"
  exit 1
}

#
# Query to run against v$asm_disk_stat
#
if [[ -z $diskgroup ]]; then
  query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number>0 order by group_number, disk_number;"
else
  query="select group_number, disk_number, name, path, reads, writes, read_errs, write_errs, read_time, write_time, bytes_read, bytes_written from v\$asm_disk_stat where group_number=(select group_number from v\$asm_diskgroup_stat where name=regexp_replace('$diskgroup','^\+','')) order by group_number, disk_number;"
fi

#
# Check for version 10.2 or later
#
typeset version minversion=10.2
version=$($ORACLE_HOME/bin/exp </dev/null 2>&1 | grep "Export: " | sed -e 's/^Export: Release \([0-9][0-9]*\.[0-9][0-9]*\).*/\1/')
if ! (print "$version<$minversion" | bc >/dev/null 2>&1); then
  print "$0 requires Oracle Database Release $minversion or later"
  exit 1
fi

#############################################################################
#
# Fatal error
#----------------------------------------------------------------------------
function fatalError {
  print -u2 -- "Error: $1"
  exit 1
}

#############################################################################
#
# Drain all of the sqlplus output - stop when we see our well known string
#----------------------------------------------------------------------------
function drainOutput {
  typeset dispose=${1:-'dispose'} output
  while :; do
    read -p output || fatalError 'Read from co-process failed [$0]'
    if [[ $QUERYDEBUG == ON ]]; then print $output; fi
    if [[ $output == $endOfOutput* ]]; then break; fi
    [[ $dispose != 'dispose' ]] && print -- $output
  done
}

#############################################################################
#
# Ensure the instance is running and it is of type ASM
#----------------------------------------------------------------------------
function verifyASMinstance {
  typeset asmcmdPath=$ORACLE_HOME/bin/asmcmd
  [[ ! -x $asmcmdPath ]] && fatalError "Invalid ORACLE_HOME $ORACLE_HOME: $asmcmdPath does not exist"
  $asmcmdPath pwd 2>/dev/null | grep -q '^\+$' || fatalError "$ORACLE_SID is not an ASM instance"
}

#############################################################################
#
# Start the sqlplus coprocess
#----------------------------------------------------------------------------
function startSqlplus {
  # start sqlplus, setup the env
  $ORACLE_HOME/bin/sqlplus -s '/ as sysdba' |&

  print -p 'whenever sqlerror exit failure' \
  && print -p "set pagesize 9999 linesize 9999 feedback off heading off" \
  && print -p "prompt $endOfOutput" \
  || fatalError 'Write to co-process failed (startSqlplus)'
  drainOutput dispose
}


#############################################################################
#############################################################################
# MAIN
#----------------------------------------------------------------------------
verifyASMinstance
startSqlplus

#
# Loop as many times as requested or forever
#
while :; do
  print -p "$query" \
  && print -p "prompt $endOfOutput" \
  || fatalError 'Write to co-process failed (collectData)'
  stats=$(drainOutput keep)

  print -- "$stats\nEOL"

  index=index+1
  (( count<index && count>0 )) && break

  sleep $interval
done  | \
awk '
BEGIN { firstSample=1
}
/^EOL$/ {
  firstSample=0; firstLine=1
  next
}
{
  path=$4
  if (path ~ /^ *$/) next
  group[path]=$1; disk[path]=$2; name[path]=$3

  reads[path]=$5;      writes[path]=$6
  readErrors[path]=$7; writeErrors[path]=$8
  readTime[path]=$9;   writeTime[path]=$10
  readBytes[path]=$11; writeBytes[path]=$12

  # reads and writes
  readsDiff[path]=reads[path]-readsPrev[path]
  writesDiff[path]=writes[path]-writesPrev[path]

  # read errors and write errors
  readErrorsDiff[path]=readErrors[path]-readErrorsPrev[path]
  writeErrorsDiff[path]=writeErrors[path]-writeErrorsPrev[path]
 
  # read time and write time
  readTimeDiff[path]=readTime[path]-readTimePrev[path]
  writeTimeDiff[path]=writeTime[path]-writeTimePrev[path]

  # average read time and average write time in msec (data provided in csec)
  avgReadTime[path]=0; avgWriteTime[path]=0
  if ( readsDiff[path] ) avgReadTime[path]=(readTimeDiff[path]/readsDiff[path])*1000
  if ( writesDiff[path]) avgWriteTime[path]=(writeTimeDiff[path]/writesDiff[path])*1000

  # bytes and KB read and bytes and KB written
  readBytesDiff[path]=readBytes[path]-readBytesPrev[path]
  writeBytesDiff[path]=writeBytes[path]-writeBytesPrev[path]
  readKb[path]=readBytesDiff[path]/1024
  writeKb[path]=writeBytesDiff[path]/1024

  # average read size and average write size
  avgReadSize[path]=0; avgWriteSize[path]=0
  if ( readsDiff[path] ) avgReadSize[path]=readBytesDiff[path]/readsDiff[path]
  if ( writesDiff[path] ) avgWriteSize[path]=writeBytesDiff[path]/writesDiff[path]
 
  if (!firstSample) {
    if (firstLine) {
      "date" | getline now; close("date")
      printf "\n"
      printf "Date: %s    Interval: %d secs    %s\n\n", now, '"$interval"', "'"$diskgroup_string"'"
      printf "%-40s %2s %3s %8s %8s %6s %6s %8s %8s %7s %7s %4s %4s\n", \
        "DiskPath - DiskName","Gr","Dsk","Reads","Writes","AvRdTm",\
        "AvWrTm","KBRd","KBWr","AvRdSz","AvWrSz", "RdEr", "WrEr"
      firstLine=0
    }
    printf "%-40s %2s %3s %8d %8d %6.1f %6.1f %8d %8d %7d %7d %4d %4d\n", \
      path " - " name[path], group[path], disk[path], \
      readsDiff[path], writesDiff[path], \
      avgReadTime[path], avgWriteTime[path], \
      readKb[path], writeKb[path], \
      avgReadSize[path], avgWriteSize[path], \
      readErrorsDiff[path], writeErrorsDiff[path]
  }

  readsPrev[path]=reads[path];           writesPrev[path]=writes[path]
  readErrorsPrev[path]=readErrors[path]; writeErrorsPrev[path]=writeErrors[path]
  readTimePrev[path]=readTime[path];     writeTimePrev[path]=writeTime[path]
  readBytesPrev[path]=readBytes[path];   writeBytesPrev[path]=writeBytes[path]
}
END {
}
'

exit 0

SAMPLE OUTPUT

For every ASM disk, this is the output provided:

DiskPath    - Path to ASM disk
DiskName    - ASM disk name
Gr          - ASM disk group number
Dsk         - ASM disk number
Reads       - Reads
Writes      - Writes
AvRdTm      - Average read time (in msec)
AvWrTm      - Average write time (in msec)
KBRd        - Kilobytes read
KBWr        - Kilobytes written
AvRdSz      - Average read size (in bytes)
AvWrSz      - Average write size (in bytes)
RdEr        - Read errors
WrEr        - Write errors

Script Output

DiskPath - DiskName            Gr Dsk Reads Writes AvRdTm AvWrTm KBRd KBWr AvRdSz AvWrSz RdEr WrEr /dev/asmdisk14 - DATA_0000 2    0      0           4            0.0            5.0             0         16           0             4096       0            0
/dev/asmdisk4 - DATA_0001   2    1      10         0            17.0          0.0             0         0             0             0             0            0
/dev/asmdisk17 - DATA_0002 2    2      0           1            0.0            0.0             0         0             0            512          0            0
/dev/asmdisk5 - DATA_0003   2    3      0           0            0.0            0.0             0         0             0            0              0            0
/dev/asmdisk16 - DATA_0004 2    4      3           3            6.7            3.3             0         0             0            0              0            0
/dev/asmdisk23 - DATA_0005 2    5      0           0            0.0            0.0             0         0             0            0              0            0

Friday, October 5, 2018

EM 13c : How to Create an EM Administrator with Read Only Access to the Performance Pages of a Database Target? (Doc ID 2180307.1)

In this Document
Goal
Solution


APPLIES TO:

Enterprise Manager for Oracle Database - Version 13.1.1.0.0 and later
Information in this document applies to any platform.

GOAL

Requirement is to create an EM user with Read only access to the database target but the user should be able to view the details in the Database target performance pages. However, the user should not be allowed to perform any performance activities in the database target.
Created a new EM Admin and granted the "Connect Target Read-only" privilege but accessing the Database load map as the new EM admin returns the below error:
Insufficient Privilege

User XXXXXX does not have sufficient privileges to perform the operation.
Operation: Access to Enterprise Manager management Page
Required Privilege: View Database Performance Home Page
Contact Enterprise Manager Administrator to get the privileges required to perform the operation.
What privileges should be granted to the EM Admin to be able to view only the performance pages?
 

SOLUTION

For the EM admin to be able to only view the DB performance pages, grant the privilege: View Database Performance Privilege Group. This includes the "Connect Target Read-only" privilege and the view privilege to access the performance details but will not allow the user to perform any changes.
Follow the below steps:
- Login to the EM console as a super-administrator user.
- Navigate to Setup -> Security -> Administrator.
- Select the EM Admin created and click on Edit button.
- In the 'Target Privileges' page, scroll down and click on the edit icon in the 'Manage Target Privileges Grants' for the specific database target:
edit privs

- In the next page, search for "View Database Performance" in the search field.
- Select the privilege named 'View Database Performance Privilege Group':
perf_privs

- Save the details.
- Login to the EM console as the new EM Admin and verify if the DB target performance pages are accessible.

Monday, October 1, 2018

ASM & Shared Pool (ORA-4031) (Doc ID 437924.1)


Click to add to FavoritesTo BottomTo Bottom

In this Document
Purpose
Scope
Details
ASM Instance Shared Pool Settings:
For ASM release 11.2.0.3/11.2.0.4/12.1 or before upgrade to ASM release 11.2.0.3/11.2.0.4/12.1, please follow the next recommendation:
Database Instance Shared Pool Settings for Use with ASM:
ASM Shared Pool RAC Considerations:
Important Notes:
Community Discussions
References



APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.

PURPOSE

The present document provides the required guidelines to set the Shared Pool in the ASM instance and Database instance (when ASM is used as storage option).
This is a very important setting, since the SHARED_POOL is used for standard memory usage (control structures and so on) to manage the instance. The value is also used to store open file extent maps.

SCOPE

DETAILS

ASM Instance Shared Pool Settings:


The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance. The setting for this parameter is also used to determine the amount of space that is allocated for extent storage. The default value for this parameter is suitable for most ASM environments.

Shared Pool in ASM is used for metadata information.

You do not have to set a value for the SHARED_POOL_SIZE initialization parameter if you use Automatic Memory Management (AMM).

Oracle strongly recommends that you use Automatic Memory Management (AMM) for ASM. Automatic Memory Management, automatically manages the memory-related parameters for ASM instances with the MEMORY_TARGET parameter. AMM is enabled by default on ASM instances, even when the MEMORY_TARGET parameter is not explicitly set. The default value used for MEMORY_TARGET (272 MB) is acceptable for most environments. This is the only parameter that you need to set for complete ASM memory management. You can also increase MEMORY_TARGET dynamically, up to the value of the MEMORY_MAX_TARGET parameter, just as you can for a database instance.

Note: For Linux environments, automatic memory management will not work if /dev/shm is not available or is sized smaller than MEMORY_TARGET. For Enterprise Linux Release 5, /dev/shm is configured to be half the size of the system memory by default. You can adjust this by adding a size option to the entry for /dev/shm in /etc/fstab. For more details, see the man page for the mount command.

Note: The minimum MEMORY_TARGET for ASM is 256 MB in the SPFILE. If you set MEMORY_TARGET to a lower value, Oracle Database increases the value to 256 MB automatically.


If you are not using Automatic Memory Management, then the default value for this parameter is suitable for most environments.


=)> For 32-bit environments 32 MB is the default and minimum requirement for an ASM instance, but 128 MB is recommended.

=)> On 64-bit platforms 88 MB are required for an ASM instance, recommended values is 150 MB. 
For ASM release 11.2.0.3/11.2.0.4/12.1 or before upgrade to ASM release 11.2.0.3/11.2.0.4/12.1, please follow the next recommendation:
Log in to ASM:

SQL> show parameter memory_target


If the value is smaller than 1536m, issue the following:


SQL> alter system set memory_max_target=4096m scope=spfile;
SQL> alter system set memory_target=1536m scope=spfile;


The number 1536m has proven to be sufficient for most environment, the change will not be effective until next restart.

Database Instance Shared Pool Settings for Use with ASM:

When you do not use Automatic Memory Management in a database instance, the SGA parameter settings for a database instance may require minor modifications to support ASM. When you use Automatic Memory Management, the sizing data discussed below can be treated as informational only or as supplemental information to help determine the appropriate values that you should use for the SGA. Oracle highly recommends using automatic memory management.


The following are configuration guidelines for Shared Pool sizing on the database instance (when Automatic Memory Management is not used):


SHARED_POOL_SIZE initialization parameter. Aggregate the values from the following queries to obtain the current database storage size that is either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using the aggregated value as input.

SELECT SUM(bytes)/(1024*1024*1024) FROM V$DATAFILE;

SELECT SUM(bytes)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b
WHERE a.group#=b.group#;

SELECT SUM(bytes)/(1024*1024*1024) FROM V$TEMPFILE
WHERE status='ONLINE';


o For disk groups using external redundancy, every 100 GB of space needs 1 MB of extra shared pool plus 2 MB.

o For disk groups using normal redundancy, every 50 GB of space needs 1 MB of extra shared pool plus 4 MB.

o For disk groups using high redundancy, every 33 GB of space needs 1 MB of extra shared pool plus 6 MB.

ASM Shared Pool RAC Considerations:
 When Migrating from single instance to RAC add an additional 15% more shared pool to the Database & ASM instances, since RAC-specific memory is mostly allocated in the shared pool at SGA creation time (that value is heuristic, based on RAC sizing experience).

ORA-04031 Error reported by ASM.


1) Error description:


// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.


2) In a generic description, an ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a contiguous memory chunk large enough to satisfy an allocation request. In ASM, an ORA-4031 error indicates that the ASM instance is running out of shared or large pool memory.

3) If the ASM instance (Standalone or RAC) is reporting shared pool and/or large pool ORA-04031 errors as follow:



Fri Dec 16 02:54:29 2011
Errors in file /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/trace/+ASM1_ora_1510.trc (incident=94313):
ORA-04031: unable to allocate 3656 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ASM file")
.
.
.
"SQL> alter diskgroup NUCLEUS_DG mount;
alter diskgroup NUCLEUS_DG mount
*
ERROR at line 1:
ORA-04031: unable to allocate 1061464 bytes of shared memory ("large pool","unknown object","large pool","kfr redo buffer")

 then please collect the next information for Oracle Support.

3.1) Please connect to each ASM instance and provide the output of the next script from all the ASM instances:
spool asm<#>_4031_shared_pool.html
SET MARKUP HTML ON
set echo on

set pagesize 200

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select 'THIS ASM REPORT WAS GENERATED AT: ==)> ' , sysdate " " from dual;


select 'HOSTNAME ASSOCIATED WITH THIS ASM INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';


select bytes/1024/1024 MB
from v$sgastat
where pool = 'shared pool' and name = 'free memory';

select bytes/1024/1024 MB
from v$sgastat
where pool = 'large pool' and name = 'free memory';


select * from V$SHARED_POOL_ADVICE;

select * from v$version;


show parameter asm
show parameter cluster
show parameter instance_type
show parameter instance_name


show sga

show parameter

spool off

exit

3.2) Please upload the alert log from the ORACLE_HOME corresponding to the instance in which you got the ORA-4031 error.

3.3) For every ORA-4031 error, the database generates a trace file which contains details about the error. This file exists in the ORACLE_HOME/trace directory (please provide them).

3.4) Please provide an IPS package that contains a text-based (non-XML) Alert log and default (non-incident) trace file.

Example:

In the ASM alert.log you will see a message like this:

Fri Dec 16 02:19:42 2011
Errors in file /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/trace/+ASM1_ora_16890.trc (incident=94321):
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim object batch")
Incident details in: /u01/app/11.2.0/grid/log/diag/asm/+asm/+ASM1/incident/incdir_94321/+ASM1_ora_16890_i94321.trc
Fri Dec 16 02:19:44 2011
Dumping diagnostic data in directory=[cdmp_20111216021944], requested by (instance=1, osid=16890), summary=[incident=94321].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Dec 16 02:19:45 2011

Where: "summary=[incident=94321]" is the incident number.

Therefore please package and provide that incident number following the steps described in the next note:

=)> ADR Different Methods to Create IPS Package Document 411.1

Note 1: Please compress those files in just one file (*.zip or *.tar) and upload it.

Note 2: Trace information from point 3.23.3 3.4 are required as input for the ORA-4031 Tool Document 559339.1


Important Notes:


Note: In 11.2.0.3/11.2.0.4, we increase the default PROCESSES based on the number of CPU cores, but the default MEMORY_TARGET value is not increased.   If in 11.2.0.2, customers explicitly set MEMORY_TARGET to some value that may not be big enough for 11.2.0.3/11.2.0.4, when they upgrade to 11.2.0.3/11.2.0.4, ASM will fail to start with error "memory_target is too small".    We should add additional check for MEMORY_TARGET during the upgrade prerequisite check.

You can unset MEMORY_TARGET so that ASM can use the default value, but if MEMORY_TARGET is explicitly set, please make sure it's large enough, following the next rules:

1) If PROCESSES parameter is explicitly set:

The MEMORY_TARGET should be set to no less than:

      256M + PROCESSES  * 132K (64bit)

 or

      256M + PROCESSES  * 120K (32bit)

2) If PROCESSES parameter is not set:

The MEMORY_TARGET should be set to no less than:

      256M + (available_cpu_cores * 80 + 40) * 132K  (64bit)

or
      256M + (available_cpu_cores * 80 + 40) * 120K  (32bit)



REFERENCES

NOTE:1625886.1 - ORA-4031 Errors On ASM Instance When Huge Pages Are Enabled On The System
NOTE:1373255.1 - 11.2.0.1/11.2.0.2 to 11.2.0.3 Grid Infrastructure and Database Upgrade on Exadata Database Machine
NOTE:1416083.1 - Unable To Start ASM (ORA-00838 ORA-04031) On 11.2.0.3/11.2.0.4 If OS CPUs # > 64.
NOTE:1982132.1 - Default and Minimum MEMORY_TARGET & MEMORY_MAX_TARGET Value for ASM 11.2.0.4 and Onwards