当前位置:Gxlcms > mysql > ORA-04031报错导致的数据库重启

ORA-04031报错导致的数据库重启

时间:2021-07-01 10:21:17 帮助过:85人阅读

Linux公社(www.linuxidc.com)是专业的Linux系统门户网站,实时发布最新Linux资讯,包括Linux、Ubuntu、Fedora、RedHat、红旗Linux、Linux教程、Linux认证、SUSE

环境:

OSAIX Version 6.1

Oracle : 10.2.0.5 rac(节点rac2

一、问题描述

晚上22:40 收到一条短信,数据库挂了,立马连上数据库,在日志里面发现:

从日志里面看问题很明显:是由于ora04031导致数据库重启。

二、分析与解决问题思路:

ora04031错误导致后台进程LCK0挂了,进而导致数据库重启:

查看当前数据库相关参数:

查看awr:

这里可以看出shared pool size 8G buffer cache 160G,有7个子池

查看dump文件/oracle/product/admin/oss139/bdump/oss1392_lck0_6685174.trc

Memory Utilization of Subpool 1

================================

Allocation Name Size

_________________________ __________

"free memory " 19127320 19M

Memory Utilization of Subpool 2

================================

Allocation Name Size

_________________________ __________

"free memory " 131103400 130M

Memory Utilization of Subpool 3

================================

Allocation Name Size

_________________________ __________

"free memory " 19409776 19M

Memory Utilization of Subpool 4

================================

Allocation Name Size

_________________________ __________

"free memory " 19172224 19M

emory Utilization of Subpool 5

================================

Allocation Name Size

_________________________ __________

"free memory " 18623928 18M

Memory Utilization of Subpool 6

================================

Allocation Name Size

_________________________ __________

"free memory " 18026416 18M

Memory Utilization of Subpool 7

================================

Allocation Name Size

_________________________ __________

"free memory " 12162296 12M

相比前一天总free memory下降到只有200多M,,对应的gcs resources,gcs shadows却占用了5G多空间,gcs resourcesgcs shadow资源均是Oracle RAC中特有的全局缓存服务资源,这些资源负责处理RAC中的全局buffer cache

然后查询shared_pool当前的剩余内存:

只有16M了,也就是昨天发生ora 04031并不是偶然。

metalink搜索gcs resource:

Gcs 这两位兄弟会导致ora 04031错误:

那么如何评估gcs的大小?

从上图红线可以看出他的大小依赖于db buffer的大小。

当实例高速缓存buffer cache增加的时候,gcs资源所占用的空间也相应增长,具体算法如下:Example with Linux x86-64 / 10.2.0.4

o v$resource_limit


Resource Name Current Max Initial Limit
-------------- ------- ------- ------- -------
gcs_resources 585758 1110251 1113203 1113203
gcs_shadows 909888 1111054 1113203 1113203

o Initial shared memory in theory,
gcs resources = 1113203 * 120(+alpha) bytes = 133,584,360 (+alpha) bytes
gcs shadows = 1113203 * 72(+alpha) bytes = 80,150,616 (+alpha) bytes

o Actual size in shared pool
gcs resources = 185,766,864 bytes
gcs shadows = 107,993,760 bytes
Practically, a little bigger memory is used because gcs resources/shadows
structure sizes are different depending on Oracle versions and platforms.

也就是加大shared_pool

linux

人气教程排行