一次ORA-3136的处理

最近收到一个告警,用户说数据库无法连接,但是从监控上看,oracle的后台进程已经侦听进程还是在的,没有任何的alert。

登录数据库,已经恢复正常,但是在数据库的alertlog中发现大量的ora-3136的报错:

时间大约是在9点开始,到9点07分结束,历时7分钟,之后就自动恢复了,后续没有报错。

而ora-3136的这个报错,在大部分情况下,我们是可以忽略的,因为这个报错一般是由于客户端由于梅雨正确的密码,连接超时导致。举个很简单的例子,我们用sqlplus user/password@tnsname,但是输入的密码是错误的,oracle提示:ORA-01017: invalid username/password; logon denied,之后,什么都别做,连接挂在那里,等一分钟之后,就可以在alertlog中看到这个报错了。

因此,ora-3136报错的一种可能性是客户端使用率错误的密码登录,但是之后没有退出连接。

但是ora-3136的报错不仅仅是这一种可能,另外还有当收到来自恶意客户端的连接,如Dos攻击,另外,还有当数据库负载比较重的时候,也会有这样的报错。具体可见metalink 《Troubleshooting ORA – 3136 WARNING Inbound Connection Timed Out [ID 465043.1]》里面说的3种可能性:

根据我的理解,总之,在oracle的侦听接受到一个来自客户端的请求,当fork到服务器进程的时候,如果在这个过程中发现意外,如密码错误,如数据库负载太重,都会参数ora-3136的报错。

由于在alertlog中除了ora-3136之外没有别的什么信息,于是拉了一份故障时间点左右的awr report来看,发现了比较严重的问题:
1.shared pool撑的比较大:

2.library cache命中率低:

3.等待事件中library cache的latch严重:

4. SQL的绑定变量使用的很糟糕,几乎没有绑定变量,某些语句类似的可以找到5000多个,仅仅是查询条件中的值不同:

ok,到这里,我们从awrreport中可以暂时的理出一条线索:sql没有很好的绑定变量->需要大量的library cache内存->申请内存的时候,可能机器负载高,导致ora3136的报错。

我们继续结合系统层面的NMON数据来看系统当时的负载情况:
1.八点半到九点多的那段时间CPU中的IO较高:

2. 八点半到九点多那段时间的hdisk0很忙,几乎到100%:

由于hdisk0和hdisk1是属于local disk,hdisk4和hdisk5是san storage。而local disk除了用于本地的一些文件系统的使用,还有用于swap空间。我们继续去看page in和page out的情况。

3. 八点半到九点多那段时间有大量的page in:

因此,我们再次可以进一步的推论:由于需要大量的library cache,数据库向内存申请空间,由于空间不够,或者配置的原因,申请的空间需要向swap空间发生置换,因此发生page in,而在swap空间中的library cache又远远比不上在物理内存内的效率,且hdisk0的繁忙程度为100%。

综上,造成上述的故障:SQL没有很好的绑定变量->需要大量的library cache->申请library cache内存的时候,与swap发生置换,page in增高->hdisk0繁忙100%->整体系统负载高->fork服务器进程失败->ora-3136报错。

因为该机器的物理内存有40G,而我们配置的SGA+PGA还不到20G,有这样大的pipo,我们怀疑是不是有些aix的配置没有正确,同时我们也希望设置lock_sga的参数,把sga锁在物理内存中。检查后,果然发现了些问题:

上述问题,在测试机上修改设置后,进行一星期的测试,在生产系统上修改。

相关文章

10条评论

  1. 整体系统负载高->fork服务器进程失败->ora-3136

    如果这个问题对给oracle support,单就ora-3136的问题,也许不会要awr report吧?而且不能reproduce,恐怕很难找到root cause…

    问个问题,后台进程是谁fork的阿?比如说J003或者S099啥的中途被启动的后台进程。

  2. 看到awr我就想到swap问题,这种案例我处理过,你的结果是因果错乱的,library cache 是属于shared pool的,shared pool是sga里的,sga是固定的内存,就是说不管怎么使用library cache,他都是sga的子集,理论上swap的引起,和它没关系,相反,大量的swap是可能导致library cache latch和shared pool latch的等待很严重。

    最后的方案有几点困惑,

    1.AIX我不知道你那是哪个版本,我记得aix 5以上的默认就是scope=S
    2.pin还是不pin住内存不是引起swap的原因

    所以我觉得你没有找到真正引起swap的原因

    ps:

    综上,造成上述的故障:SQL没有很好的绑定变量->需要大量的library cache->申请library cache内存的时候,与swap发生置换,page in增高->hdisk0繁忙100%->整体系统负载高->fork服务器进程失败->ora-3136报错。

    我觉得这个地方的结论应该改下:

    swap严重

    与swap发生置换,page in增高->hdisk0繁忙100%->整体系统负载高->fork服务器进程失败->ora-3136错误
    与swap发生置换,page in增高->hdisk0繁忙100%->整体系统负载高->library cache latch和shared pool latch

    但是本案例没有找到swap很高的原因

  3. re performance:”library cache 是属于shared pool的,shared pool是sga里的,sga是固定的内存”,这句话我有点异议,SGA不一定是pin在内存里面的。也会同样的被刷到swap空间。

  4. 我不是说sga 是pin在内存里的,我说的固定内存,是指sga的大小的值是固定在sga_max_size的大小,sga被刷到swap,说明内存出现不足了

  5. re performance: 这台机器60G的物理内存,SGA 18G,PGA 3G,进程数大约到90个左右。内存不足的原因,不太可能,所以怀疑是参数设置的问题。

  6. 你这个案例,我们系统也犯过几次, shared pool撑的比较大,library cache latch和shared pool latch 与swap发生置换,page in增高 cpu的wio增高,现象和我们系统的几次情况完全一样,经过几次调优,我们分析主要是这么个流程 :1 ,分析业务连接,主要通过监听日志和交换机防火墙,确定每秒连接次数,确定是哪些业务连接过来,经过确认有业务连接池有问题,在被其他站点爬虫抓取之后,压力非常大,限制连接池,业务连接问题解决; 分析是否无绑定变量,经过分析,系统代码良好,全部使用绑定变量 ; 3,分析shared pool配置,经查,使用自动内存配置,10g的不能解决的问题就是当自动配置后,会有bug造成池不断增大,修改为手动配置, 4,分析机器内存参数配置,发现剩余内存较小,设置内存保留,确保当系统压力大时,系统仍能分配出内存,而不是把内存使用为cache,oracle 反而去从swap中取地址造成系统无响应造成报错ORA-01017,分析os内存分配主要看共享内存和各进程内存,发现耗费内存的主要还是共享池撑的过大,降低之,os的cache耗费过高,设置为directio后,降低cache使用内存,之后此情况没有再出现过。如performance所说,小荷你还得看看机器中内存到底是被什么给消耗掉了,pin住sga不是最关键的,如果内存够用,sga被转到swap的概率很低,而且很快就会回来,所以我觉得还是得分析到底内存被用在哪里了, 个人看法,讨论一下,呵呵。

  7. 请教下,怎么生成一个指定时间段的awr啊?看你awr时间是8:30到9:07呢

  8. 有可能是数据库服务器压力大,也有可能是应用服务器压力大,只要是这60秒内这个认证过程没有完成,就会报这个错误。

  9. re xiayulai: 是的,ora-3136的的问题,可能性有很多,只要在listener将过来的session fork到服务器进程的过程中,有任何意外,都将导致这个报错。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据