前两天一非常重要的生产库的alert日志里面一直报如下错误:
NOTE: deferred map free for map id 8
Tue Feb 19 14:08:13 2013
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
NOTE: Deferred communication with ASM instance
Errors in file /home/app/oracle/diag/rdbms/outtrack/outtrack2/trace/outtrack2_pmon_19654.trc:
ORA-15055: 无法连接到 ASM 实例
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^1","kglseshtTable")
Tue Feb 19 14:08:14 2013
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
NOTE: Deferred communication with ASM instance
Errors in file /home/app/oracle/diag/rdbms/outtrack/outtrack2/trace/outtrack2_dbw0_19696.trc:
ORA-15055: 无法连接到 ASM 实例
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^3814","kglseshtTable")
数据库无法归档,导致数据库无法连接。
Tue Feb 19 14:08:13 2013
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
NOTE: Deferred communication with ASM instance
Errors in file /home/app/oracle/diag/rdbms/outtrack/outtrack2/trace/outtrack2_pmon_19654.trc:
ORA-15055: 无法连接到 ASM 实例
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^1","kglseshtTable")
Tue Feb 19 14:08:14 2013
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
WARNING: ASM communication error: op 0 state 0x0 (15055)
ERROR: direct connection failure with ASM
NOTE: Deferred communication with ASM instance
Errors in file /home/app/oracle/diag/rdbms/outtrack/outtrack2/trace/outtrack2_dbw0_19696.trc:
ORA-15055: 无法连接到 ASM 实例
ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^3814","kglseshtTable")
数据库无法归档,导致数据库无法连接。
这个数据库的版本是:11.2.0.3.0,rac环境。目前出问题的是第二个节点,第一个节点目前还可以连接。
一般来看,报4031错误一般是数据库的内存不够。这个数据库目前采用的是oracle内存自动管理的。memory_target设置的值大于0.,要么就是bug所致。
查看SELECT * FROM gv$sga_target_advice;
和SELECT * FROM gv$pga_target_advice;
发现pga的命中率只有62%,sga的值也偏小。查看主机的内存配置:256G,而目前数据库分配的内存只有101G。因此根据 gv$sga_target_advice 的建议将数据库的内存调整至180G:
SQL> alter system set memory_max_target=180g scope=spfile sid='*';
重启数据库实例:
发现数据库实例起不来了,报:ORA-00845 MEMORY_TARGET not supported on this system
详细信息:
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 193273528320 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 134749302784 and used is 562888704 bytes. Ensure that the mount point is /dev/shm for this directory.
memory_target needs larger /dev/shm
看来是oracle SGA的大小超过了系统shm的大小,
memory_target needs larger /dev/shm
看来是oracle SGA的大小超过了系统shm的大小,
有两种解决方案:1.修改shm的大小 .2 修改SGA_MAX_SIZE的大小。
现在只能调整shm的大小了:建议修改shm,shm类似于Windows平台的虚拟内存,shm默认自动调节大小,大约是内存的一半,服务器的内存是256G,因此目前的shm是127G,小于数据库内存180G。
现在只能调整shm的大小了:建议修改shm,shm类似于Windows平台的虚拟内存,shm默认自动调节大小,大约是内存的一半,服务器的内存是256G,因此目前的shm是127G,小于数据库内存180G。
修改/dev/shm的大小方法如下:
vi /etc/fstab
把此文件中:
none /dev/shm tmpfs defaults 0 0
修改为:
none /dev/shm tmpfs defaults,size=190G 0 0
然后保存退出,重新挂载shm
[oracle@oracle11g ~]$ umount /dev/shm
[oracle@oracle11g ~]$ mount /dev/shm
查看shm大小
tmpfs 190G 74G 117G 39% /dev/shm
再重新启动oracle数据库实例。数据库启动正常。
调整memory_target参数:
SQL>alter system set memory_target=180g scope=both sid='XXXXX2';
另外一个节点做类似的修改。
查看alert日志,监控了几日没发现有什么异常。后面再做持续的监控。
另外
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12129601/viewspace-754457/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12129601/viewspace-754457/