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.2, 3.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
No comments:
Post a Comment