建立物化檢視導致資料庫例項崩潰

這個BUG是在9204上碰到的最嚴重的一個bug，在建立物化檢視的時候，直接導致了例項的崩潰。

物化檢視的建立指令碼就不貼出來了，語句比較長，其中大部分表是透過資料庫鏈訪問，而且還包括了樹狀查詢，但是，物化檢視功能並不複雜，既不是REFRESH FAST也不是ON COMMIT，更不是基於查詢重寫的。

使用者程式包括如下的錯誤資訊：

ORA-07445: exception encountered: core dump [00000001009FC6B0] [SIGSEGV] [Address not mapped to object] [0x000000008] [] []
Current SQL statement for this session:
CREATE SUMMARY "MIS2"."MIS2_GPO_ORDER_ITEM" COMPILE

而從後臺PMON程式的日誌檔案可以看到下面的錯誤：

Oracle process number: 2
Unix process pid: 7920, image: oracle@newreport (PMON)

ORA-07445: exception encountered: core dump [0000000100AE6CF4] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []

.

.

.

ORA-07445: exception encountered: core dump [0000000100AE6640] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [0000000100AE6CF4] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []

.

.

.

ORA-07445: exception encountered: core dump [0000000100AE6640] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [0000000100AE6640] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [0000000100AE6CF4] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFF7CCF32C8] [] []

使用者程式7445錯誤的第一個錯誤引數是qsmkzii_init_qsmksinline。而PMON程式後臺檔案的錯誤函式分別是kksheqd和kkshlcu。

根據這些資訊在METALINK上查詢發現，最接近的bug為：Bug 3004764。

Bug 3004764 PMON may crash the instance with ORA-7445[KKSHEQD] / ORA-7445[KKSHLCU]

This note gives a brief overview of bug 3004764.

Affects:

Product (Component)	(Rdbms)
Range of versions believed to be affected	Versions >= 9.0.1.4 but < 10G
Versions confirmed as being affected	(None Specified)
Platforms affected	Generic (all / most platforms affected)

It is believed to be a regression in default behaviour thus:

· Regression introduced in 9.0.1.4

 Regression introduced in 9.2.0.2

Fixed:

This issue is fixed in

9.2.0.5 (Server Patch Set) 10g Production Base Release

Symptoms:

• Dump in or under kkshlcu

Related To:

• (None Specified)

Description

PMON may crash the instance by dumping with ORA-7445[KKSHEQD] or ORA-7445[KKSHLCU] when cleaning up a dead process if the process being cleaned up died (or was killed) at a specific point in the code.

The problem only occurs for specific scenarios and is most likely to occur if the aborted session was running DDL at the time it died.

這還是第一次碰到可以導致例項崩潰的BUG，不過想要避免這個bug也不算太過於困難。實踐再一次證明，越複雜的功能越容易導致bug的產生。