标签归档:故障诊断

Oracle中诊断阻塞的session

由于锁的机制,当某一条DML或者DDL SQL语句执行被阻塞的时候,需要找出是什么原因导致这条SQL被阻塞了,下面介绍两种常用的诊断方法:
假设有这样一个表:table t(id int primary key,val int),数据为:

id      val
1       1
2       2
[oracle@10g ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 16:48:03 2013
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t (id int primary key,val int); 
Table created.
SQL> insert into t values (1,1);
1 row created.
SQL> insert into t values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
        ID        VAL
---------- ----------
         1          1
         2          2

1.在第一个Session,这里把它叫做Session A,做了如下的update语句,没有提交或者回滚:

SQL> update t set val = 3 where id=1;
1 row updated.

2.在另一个Session里,这里把它叫做Session B,做了如下的update语句,Session B会被阻塞:

SQL> update t set val = 4 where id=1;

一:
有活动事务对对象加锁的时候,在v$locked_object视图中会有记录,如object_id,session_id等,通常被阻塞session的XIDUSN,XIDSLOT,XIDSQN字段都为空,下面查询中session_id为138的是被阻塞的session:

SQL> conn / as sysdba
Connected.
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
------ ------- ------ --------- ---------- --------------- ------------ ------- -----------
     0       0      0     54364        138 SCOTT           oracle       25455             3
     7      24    322     54364        143 SCOTT           oracle       25338             3

通过v$locked_object和dba_objects关联查询出被阻塞session的对象:

SQL> select dbo.*
  2    from v$locked_object lo, dba_objects dbo
  3   where lo.object_id = dbo.object_id
  4     and lo.xidusn = 0;
OWNER OBJECT_NAM SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
----- ---------- -------------- --------- -------------- ----------- --------- --------- ------------------- ------- - - -
SCOTT T                             54364          54364 TABLE       28-AUG-13 28-AUG-13 2013-08-28:16:48:53 VALID   N N N

通过查询v$lock可以看到是哪一个session阻塞了哪一个session:143阻塞了138

SQL> select blkingsess.sid blockingsid, blkedsess.sid blockedsid
  2    from v$lock blkingsess,
  3         (select * from v$lock where request != 0) blkedsess
  4   where blkingsess.id1 = blkedsess.id1
  5     and blkingsess.id2 = blkedsess.id2
  6     and blkingsess.sid != blkedsess.sid;
BLOCKINGSID BLOCKEDSID
----------- ----------
        143        138

在通过v$session可以查到session相关的信息,被阻塞的status一般为ACTIVE,还可以通过sql_address关联v$sql找到被阻塞的SQL语句:

SQL> select sid, serial#, status, sql_address
  2    from v$session
  3   where sid in (143, 138);
SID SERIAL# STATUS   SQL_ADDRESS
--- ------- -------- ----------------
138    5914 ACTIVE   000000008358A478
143    7044 INACTIVE 000000007AA1DA68
SQL> select sql_id,sql_text from v$sql where address='000000008358A478';
SQL_ID        SQL_TEXT
------------- -------------------------------
cgxjac574kd3q update t set val = 4 where id=1

这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session:

SQL> alter system kill session '143,7044';
System altered.

其中143为sid,7044为serial#,session 143会收到如下错误,session 138可以执行后续步骤.

ERROR:
ORA-03114: not connected to ORACLE

法二:
查看等待事件会发现存在锁表:

SQL> select sid, event
  2    from v$session_wait
  3   where event not like 'SQL%'
  4     and event not like 'rdbms%';
       SID EVENT
---------- ----------------------------------------------------------------
       135 jobq slave wait
       138 enq: TX - row lock contention
       140 wait for unread message on broadcast channel
       149 Streams AQ: waiting for time management or cleanup tasks
       150 Streams AQ: qmn slave idle wait
       153 Streams AQ: qmn coordinator idle wait
       164 smon timer
       170 pmon timer
8 rows selected.

查看此锁的持有者与等待着:

SQL> column sess format a15
SQL> SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess,
  2         id1,
  3         id2,
  4         lmode,
  5         request,
  6         type
  7    FROM V$LOCK
  8   WHERE (id1, id2, type) IN
  9         (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
 10   ORDER BY id1, request;
SESS           ID1 ID2 LMODE REQUEST TY
----------- ------ --- ----- ------- --
Holder: 143 458776 322     6       0 TX
Waiter: 138 458776 322     0       6 TX

查出此锁持有者session的session id,机器,程序等信息;

SQL> select s.sid,
  2         s.serial#,
  3         s.program,
  4         s.machine,
  5         s.username,
  6         s.last_call_et,
  7         s.logon_time,
  8         sw.event
  9    from v$session s, v$session_wait sw
 10   where s.sid = sw.sid
 11     and s.sid = 143;
SID SERIAL# PROGRAM                             MACHINE         USERNAME   LAST_CALL_ET LOGON_TIM EVENT
--- ------- ----------------------------------- --------------- ---------- ------------ --------- ------------------------------
143    7044 sqlplus@10g.localdomain (TNS V1-V3) 10g.localdomain SCOTT              3500 28-AUG-13 SQL*Net message from client

这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session:

SQL> alter system kill session '143,7044';
System altered.

其中143为sid,7044为serial#,session 143会收到如下错误,session 138可以执行后续步骤.

ERROR:
ORA-03114: not connected to ORACLE

Oracle RAC导致实例逐出的五大问题

这篇文档在MOS上看到被翻译成了中文,觉得比较实用,可适用于10.2.0.1至11.2.0.3版本的RAC,因此贴出来方便别人查阅,后期会尽量多贴一些MOS上的中文技术资料,希望可以帮到别人,同时也算是自己学习积累的过程。

问题 1:警报日志显示 ora-29740 是实例崩溃/驱逐的原因
症状:
实例崩溃,警报日志显示“ORA-29740:evicted by member …(被成员…驱逐)”错误。
可能的原因:
一个实例将另一个实例从 RAC 数据库驱逐时,出现了 ORA-29740 错误。被驱逐的实例会在警报日志中报告 ora-29740 错误。
此问题的部分原因是集群中的通信错误、向控制文件发送“心跳”失败以及其它原因。
检查所有实例的 lmon 跟踪文件,这对确定实例驱逐的原因代码而言非常重要。查找包含“kjxgrrcfgchk:Initiating reconfig”的行。
这将提供一个原因代码,如“kjxgrrcfgchk:Initiating reconfig, reason 3”。实例驱逐时发生的大多数 ora-29740 错误是由于原因 3(“通信故障”) 造成的。
Document 219361.1 (Troubleshooting ORA-29740 in a RAC Environment) 介绍了以下几种可能造成原因 3的 ora-29740 错误原因:
a) 网络问题。
b) 资源耗尽(CPU、I/O 等)
c) 严重的数据库争用。
d) Oracle bug。
解决方案:
1) 检查网络,确保无网络错误,如 UDP 错误或 IP 数据包丢失或故障错误。
2) 检查网络配置,确保所有节点上的所有网络配置均设置正确。
   例如,所有节点上 MTU 的大小必须相同,并且如果使用巨帧,交换机也能够支持大小为 9000 的 MTU。
3) 检查服务器是否存在 CPU 负载问题或可用内存不足。
4) 检查数据库在实例驱逐之前是否正处于挂起状态或存在严重的性能问题。
5) 检查 CHM (Cluster Health Monitor) 输出,以查看服务器是否存在 CPU 或内存负载问题、网络问题或者 lmd 或 lms 进程出现死循环。CHM 输出只能在特定平台和版本中使用,因此请参阅 CHM 常见问题 Document 1328466.1
6) 如果 OSWatcher 尚未设置,请按照 Document 301137.1 中的说明进行设置以运行 OSWatcher。
CHM 输出不可用时,使用 OSWatcher 输出将有所帮助。

问题 2:警报日志在实例崩溃或驱逐前显示“ipc send timeout”错误
症状:
实例驱逐时,警报日志显示许多“IPC send timeout”错误。此消息通常伴随数据库性能问题。
可能的原因:
在 RAC 中,数据库进程,例如 lmon、lmd 和 lms 会不断地和其他实例的进程通信。lmd0 进程负责管理 enqueue,而 lms 进程负责管理数据块资源并传输数据块以支持 Cache Fusion。如果这些进程中的一个或多个受阻、死循环或异常繁忙,则可能导致“IPC send timeout(IPC 发送超时)”错误。
lmon、lms 和 lmd 进程报告“IPC send timeout”错误的另一个原因是网络问题或服务器资源(CPU 和内存)问题。这些进程可能无法获得 CPU 运行调度或这些进程发送的网络数据包丢失。
涉及 lmon、lmd 和 lms 进程的通信问题导致实例驱逐。被驱逐实例的警报日志显示的信息类似于如下示例
IPC Send timeout detected.Sender: ospid 1519
Receiver: inst 8 binc 997466802 ospid 23309
如果某实例被驱逐,警报日志中的“IPC Send timeout detected(检测到 IPC 发送超时)”通常伴随着其它问题,如 ora-29740 和“Waiting for clusterware split-brain resolution(等待集群件“脑裂”解决方案)”
解决方案:
此处的解决方案与问题 1 相似。
1) 检查网络,确保无网络错误,如 UDP 错误或 IP 数据包丢失或故障错误。
2) 检查网络配置,确保所有节点上的所有网络配置均设置正确。
   例如,所有节点上 MTU 的大小必须相同,并且如果使用巨帧,交换机也能够支持大小为 9000 的 MTU。
3) 检查服务器是否存在 CPU 负载问题或可用内存不足。
4) 检查数据库在实例驱逐之前是否正处于挂起状态或存在严重的性能问题。
5) 检查 CHM (Cluster Health Monitor) 输出,以查看服务器是否存在 CPU 或内存负载问题、网络问题或者 lmd 或 lms 进程出现死循环。CHM 输出只能在特定平台和版本中使用,因此请参阅 CHM 常见问题 Document 1328466.1
6) 如果 OSWatcher 尚未设置,请按照 Document 301137.1 中的说明进行设置以运行 OSWatcher。
CHM 输出不可用时,使用 OSWatcher 输出将有所帮助。

问题 3:在实例崩溃或驱逐前,问题实例处于挂起状态
症状:
在实例崩溃/驱逐前,该实例或数据库正处于挂起状态。当然,也可能是节点挂起。
可能的原因:
由于 lmon、lmd 和 lms 等不同进程与其它实例上对应的进程通信,因此当实例和数据库挂起时,这些进程可能正在等待某个资源,如 latch、enqueue 或数据块。这些等待中的进程得不到网络响应,或无法通过网络向远程实例发送任何通信。因此,其它实例将驱逐问题实例。
在执行驱逐其他实例动作的实例警报日志中,您可能会看到与以下消息类似的消息:
Remote instance kill is issued [112:1]:8
或者
Evicting instance 2 from cluster
解决方案:
1) 查找数据库或实例挂起的原因。对数据库或实例挂起问题进行故障排除时,获取全局 systemstate 转储和全局hang analyze 转储是关键。如果无法获取全局 systemstate 转储,则应获取在大致相同时间所有实例的本地 systemstate 转储。
2) 检查 CHM (Cluster Health Monitor) 输出,以查看服务器是否存在 CPU 或内存负载问题、网络问题或者 lmd 或 lms 进程出现死循环。CHM 输出只能在某些平台和版本中使用,因此请参阅 CHM 常见问题 Document 1328466.1
3) 如果 OSWatcher 尚未设置,请按照 Document 301137.1 中的说明进行设置以运行 OSWatcher。
CHM 输出不可用时,使用 OSWatcher 输出将有所帮助。

问题 4:在一个或多个实例崩溃或驱逐前,警报日志显示“Waiting for clusterware split-brain resolution(等待集群“脑裂”解决方案)”
症状:
在一个或多个实例崩溃之前,警报日志显示“Waiting for clusterware split-brain resolution(等待集群件“脑裂”解决方案)”。这通常伴随着“Evicting instance n from cluster(从集群驱逐实例 n)”,其中 n 是指被驱逐的实例编号。
可能的原因
lmon 进程向远程实例发送一个网络 ping,如果远程实例上的 lmon 进程不响应,则出现实例级别的“脑裂”。因此,查找 lmon 不能相互通信的原因对解决此问题而言非常重要。
常见原因有:
1) 实例级别的“脑裂”通常由网络问题导致,因此检查网络设置和连接非常重要。但是,因为如果网络已关闭,集群件 (CRS) 就会出现故障,所以只要 CRS 和数据库使用同一网络,则网络不太可能会关闭。  
2) 服务器非常繁忙和/或可用内存量低(频繁的交换和内存扫描),将阻止 lmon 进程被调度。
3) 数据库或实例正处于挂起状态,并且 lmon 进程受阻。
4) Oracle bug
以上原因与问题 1的原因相似(警报日志显示 ora-29740 是实例崩溃/驱逐的原因)。
解决方案:
此处的解决方案与问题 1 相似。
1) 检查网络,确保无网络错误,如 UDP 错误或 IP 数据包丢失或故障错误。
2) 检查网络配置,确保所有节点上的所有网络配置均设置正确。
   例如,所有节点上 MTU 的大小必须相同,并且如果使用巨帧,交换机也能够支持大小为 9000 的 MTU。
3) 检查服务器是否存在 CPU 负载问题或可用内存不足。
4) 检查数据库在实例驱逐之前是否正处于挂起状态或存在严重的性能问题。
5) 检查 CHM (Cluster Health Monitor) 输出,以查看服务器是否存在 CPU 或内存负载问题、网络问题或者 lmd 或 lms 进程出现死循环。CHM 输出只能在特定平台和版本中使用,因此请参阅 CHM 常见问题 Document 1328466.1
6) 如果 OSWatcher 尚未设置,请按照 Document 301137.1 中的说明进行设置以运行 OSWatcher。
CHM 输出不可用时,使用 OSWatcher 输出将有所帮助。

问题 5:另一个实例尝试驱逐问题实例,但由于一些原因未能成功驱逐,最终CRS会终止该问题实例。
症状:
一个实例驱逐其他实例时,在问题实例自己关闭之前,所有实例都处于等待状态,但是如果问题实例因为某些原因不能终止自己,发起驱逐的实例将发出 Member Kill 请求。Member Kill 请求会要求 CRS 终止问题实例。此功能适用于 11.1 及更高版本。
可能的原因:
要求 CRS 终止问题实例的实例警报日志显示
Remote instance kill is issued [112:1]:8
例如,以上消息表示终止实例 8 的 Member Kill 请求已发送至 CRS。
问题实例由于某种原因正处于挂起状态且无响应。这可能是由于节点存在 CPU 和内存问题,并且问题实例的进程无法获得 CPU 运行调度。
第二个常见原因是数据库资源争用严重,导致问题实例无法完成远程实例驱逐该实例的请求。
另一个原因可能是由于实例尝试中止自己时,一个或多个进程“幸存”了下来。除非实例的所有进程全部终止,否则 CRS 不认为该实例已终止,而且不会通知其它实例该问题实例已经被终止。这种情况下的一个常见问题是一个或多个进程变成僵尸进程且未终止。
并导致CRS通过节点重启或 rebootless restart( CRS 重新启动但节点不重启)进行重新启动。这种情况下,问题实例的警报日志显示
Instance termination failed to kill one or more processes
Instance terminated by LMON, pid = 23305
(实例终止未能终止一个或多个进程
实例被 LMON, pid = 23305 终止)
解决方案:
此问题的解决方案与问题 3 相似
1) 查找数据库或实例挂起的原因。对数据库或实例挂起问题进行故障排除时,获取全局 systemstate 转储和全局hang analyze 转储是关键。如果无法获取全局 systemstate 转储,则应获取在大致相同时间所有实例的本地 systemstate 转储。
2) 检查 CHM (Cluster Health Monitor) 输出,以查看服务器是否存在 CPU 或内存负载问题、网络问题或者 lmd 或 lms 进程出现死循环。CHM 输出只能在某些平台和版本中使用,因此请参阅 CHM 常见问题 Document 1328466.1
3) 如果 OSWatcher 尚未设置,请按照 Document 301137.1 中的说明进行设置以运行 OSWatcher。
CHM 输出不可用时,使用 OSWatcher 输出将有所帮助.