Oracle宕机问题分析

biotwang

昨天数据库突然负载高峰后宕机，之后重启后恢复正常。
先将alert log附上。
----------------------------------
Oracle 11.1.0.7 3 node RAC

download.rar (4.27 MB, 下载次数: 0)

2014-11-19 14:53 上传

点击文件名下载附件
相关日志及trace文件
阅读权限: 60

----------------------------------
其中涉及到3个报错：
-- =====================================================
Tue Nov 18 13:53:39 2014
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x14] [PC:0x3954A28, ksxpmprp()+42]
Errors in file /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/trace/PROD1_
ora_25086.trc  (incident=2304963):
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [ksxpmprp()+42] [SIGSEGV] [ADDR:0x14] [PC:0x3954A28] [Address no
t mapped to object] []
Incident details in: /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/incide
nt/incdir_2304963/PROD1_ora_25086_i2304963.trc

-- =====================================================
Tue Nov 18 14:00:00 2014
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Tue Nov 18 14:00:34 2014
Errors in file /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/trace/PROD1_
arc0_3488.trc  (incident=2303035):
ORA-00240: control file enqueue held for more than 120 seconds
Incident details in: /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/incide
nt/incdir_2303035/PROD1_arc0_3488_i2303035.trc

-- =====================================================
Tue Nov 18 14:27:46 2014
Errors in file /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/trace/PROD1_
diag_3169.trc  (incident=2302750):
ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by 'inst 1, osid 3492'
Incident details in: /u01/PROD/db/tech_st/11.1.0/admin/PROD_ebsracdb1/diag/rdbms/prod/PROD1/incide
nt/incdir_2302750/PROD1_diag_3169_i2302750.trc

biotwang

第一个报错的分析：

ORA-7445 [Ksxpmprp] (Doc ID 857393.1) - Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
The cause of this problem has been identified and verified in unpublished Bug 6196748.

Symptoms:
The trace file would show that there are at least 2 session state objects pointing to the same process state object.

Solution：
1.  Upgrade to the 11gR2 (11.2.0.1) or higher release. OR
2.  Apply any of the following patches:

Apply the 10.2.0.5 patchset Patch 8202632 or higher.
Apply the 11.1.0.7.3 PSU Patch 9352179 or higher.
If available for your platform and version, download and apply Patch 6196748.
For Windows, download and apply depending on your version:
For 10.2.0.4, apply patch 41 or higher (See NOTE:342443.1 for latest 10.2.0.4 patch).
32-Bit Patch:10245343
64-Bit (x64) Patch:10245345
64-Bit (Itanium) Patch:10245344

For 11.1.0.7, download and apply patch 25 or higher (See NOTE:560295.1 for latest 11.1.0.7 patch).
32-Bit Patch:9392331
64-Bit (x64) Patch:9392335

biotwang

第二个报错和第三个报错分析：
ORA-00240: control file enqueue held for more than 120 seconds

Sampled Session History Summary:
  longest_non_idle_wait: 'enq: PV - syncstart'
  [120 samples, 14:00:36 - 14:02:35]
      time_waited: >= 119 sec (still in wait)
-------------------- -------- -------------------- ----------------------------
trace中显示：
skdstdst() <- ksedst1<- kcc_tac_callback <- ksu_dispatch_tac <- ksqcmi <- ksqgtlctx <- ksqgelctx <- ksvcheckpool <- ksvcheckpool <- ksvsubmitwait <- ksvwait <- ksvsubmit <- kfncSlaveSubmit <- kfncFileIdentify <- kfioIdentify <- ksfd_osmopn <- kcropn <- kcroio

从第二个报错来看，当时应该有高LGWR，这造成了大量的ASM control file enqueue写等待，而其ksq服务层一旦发生blocking则会出现此问题，当时ASMLib下的 io读写一定很繁忙。具体需要看AWR报告了解。
由于当时第一个bug导致宕机的时间点未知，所以不能说一定是第一个bug造成的，也可能是io瓶颈引发了第一个bug。

第三个报错时可知，那个时候已经宕机，因此才出现ORA-00494。

Maclean Liu(刘相兵

PV        Synchronizes instance shutdown_slvstart
PV        Synchronizes slave start_shutdown
syncstart