Oracle 11G 引入Memory_max_target和Memory_target参数用于数据库的内存自动管理(AMM),本意是修改该两个参数超过物理内存的大小,视图看看数据库报什么错误。
备份spfile文件
$cp spfileorcl.ora spfileorcl.ora.bak
直接手动修改spfile中的两个参数值:
两个值得大小分别修改为6,815,744,000,000≈6.7T 实际物理内存为16G左右
$ free -g
启动数据库出现如下报错:
SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.ora'
[oracle@021Y-SH-BKAP dbs]$ oerr ora 1078
01078, 00000, "failure in processing system parameters"
// *Cause: Failure during processing of INIT.ORA parameters during system startup.
// *Action: Further diagnostic information should be in the error stack.
[oracle@021Y-SH-BKAP dbs]$ oerr lrm 109
109, 0, "could not open parameter file '%.*s'"
// *Cause: The parameter file does not exist.
// *Action: Create an appropriate parameter file.
理论上我修改memory_target参数应该报错memory_target值不符合物理内存的配置,但实际报的却是找不到pfile。是不是说明手动修改spfile会导致spfile无法正常读取呢?
手动将这两个参数的值修改成原值,发现启动时依旧同样的错误。可以判断手动修改spfile确实造成异常。通过file命令查看spfile.ora可以知道该文件的类型是二进制的数据文件类型。
$ file spfile.ora
spfile.ora: data
还原正常了spfile备份文件
SQL> show parameter spfile;
NAME TYPE VALUE
-------- ---------- ------------------------------
spfile string /DBBK/oracle/product/11.2.0.1.0/dbs/spfileorcl.ora
通过DDL语句我尝试了多次修改这两个参数超出物理内存值,发现一些很有趣的现象:
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter system set memory_max_target=6800G scope=spfile;
SQL> alter system set memory_target=6500G scope=spfile;
System altered.
大多数情况下调整后,数据库都能正常加载spfile文件中的配置,但偶尔也出现如下报错。先记录下来,再逐一分析
SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system
SQL> startup nomount;
ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","unknown object","sga heap(2,1)","fixed allocation callback")
SQL> startup nomount;
ORA-04031: unable to allocate 10272 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KGLSG")
观察第一个报错的情况:
$ oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.
可以看到错误告警的解释是memory_target参数在该系统不支持,或者是/dev/shm分配的大小不足,查看告警日志获取启动信息:
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 536870912000 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 8363978752 and used is 0 bytes. Ensure that the mount point is /dev/shm for this directory.
同样说明了情况,shm内存分配不足,这是linux系统的共享内存形式,根据linux本质一切皆文件的定义。linux的共享内存也被定为一个可挂在的分区系统文件tmpfs。当memory_target超过这一限制时,可能会导致类似的报错的产生。
$ df -Ph
Filesystem Size Used Avail Use% Mounted on
tmpfs 7.8G 1017M 6.8G 13% /dev/shm
但这样的报错并不单单是由Memory_target参数影响的。无论如何修改memory_target和memory_max_target的值超过实际内存大小。该报错都不能保证发生,当我将sga_max_size和sga_target设置的值超过实际物理内存时,反而触发了该报错。为什么呢?
观察启动可以看到,数据库加载spfile时候是根据sga_max_size和sga_target来进行SGA的分配的,只要该参数设置合理。即使Memory_target设置的再不合理,数据库也能正常启动。那么我是不是可以理解为当应用请求更多的内存并超过了sga_max_size的值或者sga_max_size和sga_target值均未分配的时候,数据库的内存管理才真正由memory_target和memory_max_target值来决定?
SQL> alter system set sga_max_size=0 scope=spfile;
SQL> alter system set sga_target=0 scope=spfile;
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1068994560 bytes
Fixed Size 2220072 bytes
Variable Size 671092696 bytes
Database Buffers 390070272 bytes
Redo Buffers 5611520 bytes
当我修改后,发现实际SGA自动分配了1G左右的内存。这个是由Memory_target来决定的吗?答案也不是。查看隐参。发现SGA默认大小实际是由__sga_target这个隐参决定的
那么得出一个结论:SGA的大小首先由与SGA有关的参数来决定。同理可以判断PGA亦是如此。
现在我们在来看看这个报错
ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","unknown object","sga heap(2,1)","fixed allocation callback")
很明显提示说不能再分配给shared memory 56bytes的空间,后面的细节也说明是分配的对象是shared pool
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *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.
可以看到给出的解决方式要么用DBMS_SHARED_POOL去PIN住较大的内存从而减少共享内存的分配,或者通过修改SHARED_POOL_RESERVED_SIZE和SHARED_POOL_SIZE来增大内存的分配。
而其中提示sga heap(2,1)的信息来自哪?
尝试转储shared pool的信息:
alter session set events 'immediate trace name heapdump level 2';
总结:
11G以后引入了memory_max_target和memory_target参数进行内存的自动化管理(Automatic Memory Management),但实际上SGA和PGA的分配还是由各自的参数先行决定,比如sga_target、sga_max_size、pge_aggregate_target。
PGA和SGA默认值实际分别有__pga_aggregate_target和__sga_target隐参来决定。
AMM管理的是SGA和PGA的分配关系,ASMM(Automatic Shared Memory Management)则管理的是SGA的各组件的分配关系,ASEMM(Automated SQL Execution Memory Management)则是管理PGA的自动分配关系。
转载于:https://blog.51cto.com/onlinekof2001/1720868