一套HP-UX上的11.1.0.7上的系统，在使用11g自带的新特性dictionary health check(数据字典健康检查)功能时发现FILE$基表存在讹误，并且告警日志中伴随出现ORA-00600：[qksrcBuildRwo]内部错误，具体错误信息如下:
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
91337 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91334 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91331 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91328 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed
91325 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed
91322 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed
ALERT LOG
-----------------------
Display of database log file :
Tue Jun 30 10:32:27 2009
Errors in file /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_18678.trc (incident=20162):
ORA-00600: sis?inen virhekoodi, argumentit: [qksrcBuildRwo], [], [], [], [], [], [], []
Incident details in: /oracle/11.1.0/diag/rdbms/bect/BECT/incident/incdir_20162/BECT_j000_18678_i20162.trc
Tue Jun 30 10:32:57 2009
Trace dumping is performing id=[cdmp_20090630103257]
Tue Jun 30 10:32:59 2009
Sweep Incident[20162]: completed
Tue Jun 30 10:44:15 2009
ORA-1652: unable to extend temp segment by 128 in tablespace FENIX_SECURE
ORA-1652: unable to extend temp segment by 1024 in tablespace FENIX_SECURE
Errors in file /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_21609.trc:
ORA-12012: virhe ty?n 58702 automaattisen suorituksen yhteydess?
ORA-01652: v?liaikaisen segmentin laajennus 1024:lla taulualueeessa FENIX_SECURE ei onnistu
TRACE FILE
----------------------
Display of relevant trace file :
Dump file /oracle/11.1.0/diag/rdbms/bect/BECT/incident/incdir_20162/BECT_j000_18678_i20162.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
ORACLE_HOME = /oracle/11.1.0
System name: HP-UX
Node name: hellu
Release: B.11.23
Version: U
Machine: ia64
Instance name: BECT
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 18678, image: oracle@hellu (J000)
*** 2009-06-30 10:32:27.211
*** SESSION ID:(520.12850) 2009-06-30 10:32:27.211
*** CLIENT ID:([email protected]@Mozilla/5.0 (Windows; U; Windows NT 5.0; f) 2009-06-30 10:32:27.211
*** SERVICE NAME:(SYS$USERS) 2009-06-30 10:32:27.211
*** MODULE NAME:(DBMS_SCHEDULER) 2009-06-30 10:32:27.211
*** ACTION NAME:(ADV_SQL_TUNING_1246346508268) 2009-06-30 10:32:27.211
Dump continued from file: /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_18678.trc
ORA-00600: sis?inen virhekoodi, argumentit: [qksrcBuildRwo], [], [], [], [], [], [], []
========= Dump for incident 20162 (ORA 600 [qksrcBuildRwo]) ========
*** 2009-06-30 10:32:27.219
----- Current SQL Statement for this session (sql_id=06y1876p6cr8a) -----
/* SQL Analyze(520,1) */
WITH TARGETS AS
(SELECT COLUMN_VALUE TARGET_GUID
FROM TABLE(CAST(:B1 AS MGMT_TARGET_GUID_ARRAY)))
SELECT /*+ ORDERED USE_NL(assoc) USE_NL(cfg)
NO_INDEX_FFS(assoc MGMT_POLICY_ASSOC_PK)
INDEX_ASC(assoc MGMT_POLICY_ASSOC_PK)
NO_INDEX_FFS(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX)
INDEX_ASC(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) */
ASSOC.OBJECT_GUID TARGET_GUID, LEAD(ASSOC.OBJECT_GUID, 1) OVER(
ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER) NEXT_TARGET_GUID,
POLICY.POLICY_GUID POLICY_GUID, LEAD(POLICY.POLICY_GUID, 1) OVER(
ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER) NEXT_POLICY_GUID,
POLICY.POLICY_NAME, POLICY.POLICY_TYPE, DECODE(POLICY.POLICY_TYPE, :B5,
NVL(CFG.MESSAGE, POLICY.MESSAGE), :B10, CFG.MESSAGE, NULL) MESSAGE,
DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.MESSAGE_NLSID, POLICY.MESSAGE_NLSID), :B10,
CFG.MESSAGE_NLSID, NULL) MESSAGE_NLSID, DECODE(POLICY.POLICY_TYPE, :B5,
NVL(CFG.CLEAR_MESSAGE, POLICY.CLEAR_MESSAGE), :B10, CFG.CLEAR_MESSAGE, NULL)
CLEAR_MESSAGE, DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.CLEAR_MESSAGE_NLSID, POLICY.CLEAR_MESSAGE_NLSID), :B10,
CFG.CLEAR_MESSAGE_NLSID, NULL)
CLEAR_MESSAGE_NLSID, POLICY.REPO_TIMING_ENABLED, :B4,
POLICY.VIOLATION_LEVEL,
DECODE(POLICY.POLICY_TYPE, :B5, :B11, 0) VIOLATION_TYPE, POLICY.CONDITION_TYPE,
POLICY.CONDITION, DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.CONDITION_OPERATOR, POLICY.CONDITION_OPERATOR), :B10,
CFG.CONDITION_OPERATOR, 0) CONDITION_OPERATOR,
CFG.KEY_VALUE, CFG.KEY_OPERATOR, CFG.IS_EXCEPTION, CFG.NUM_OCCURRENCES,
NULL EVALUATION_DATE, CAST(MULTISET(
SELECT MGMT_POLICY_PARAM_VAL(PARAM_NAME, CRIT_THRESHOLD, WARN_THRESHOLD, INFO_THRESHOLD)
FROM MGMT_POLICY_ASSOC_CFG_PARAMS PARAM
WHERE PARAM.OBJECT_GUID = CFG.OBJECT_GUID AND PARAM.POLICY_GUID = CFG.POLICY_GUID
AND PARAM.COLL_NAME = CFG.COLL_NAME AND PARAM.KEY_VALUE = CFG.KEY_VALUE AND
PARAM.KEY_OPERATOR = CFG.KEY_OPERATOR) AS MGMT_POLICY_PARAM_VAL_ARRAY) PARAMS, DECODE(POLICY.CONDITION_TYPE, :B9, CAST(MULTISET(
SELECT MGMT_NAMEVALUE_OBJ.NEW(BIND_COLUMN_NAME, BIND_COLUMN_TYPE)
FROM MGMT_POLICY_BIND_VARS BINDS
WHERE BINDS.POLICY_GUID = POLICY.POLICY_GUID) AS MGMT_NAMEVALUE_ARRAY),
MGMT_NAMEVALUE_ARRAY()) BINDS, DECODE(:B8, 0, MGMT_MEDIUM_STRING_ARRAY(), 1, MGMT_MEDIUM_STRING_ARRAY(CFG.KEY_VALUE), CAST((
SELECT MGMT_MEDIUM_STRING_ARRAY(KEY_PART1_VALUE, KEY_PART2_VALUE, KEY_PART3_VALUE,
KEY_PART4_VALUE, KEY_PART5_VALUE)
FROM MGMT_METRICS_COMPOSITE_KEYS COMP_KEYS
WHERE COMP_KEYS.COMPOSITE_KEY = CFG.KEY_VALUE AND COMP_KEYS.TARGET_GUID = CFG.OBJECT_GUID) AS MGMT_MEDIUM_STRING_ARRAY)) KEY_VALUES
FROM TARGETS, MGMT_POLICIES POLICY, MGMT_POLICY_ASSOC ASSOC, MGMT_POLICY_ASSOC_CFG CFG
WHERE POLICY.METRIC_GUID = :B7 AND ASSOC.OBJECT_GUID = TARGETS.TARGET_GUID
AND ASSOC.POLICY_GUID = POLICY.POLICY_GUID AND POLICY.POLICY_TYPE != :B6 AND
(POLICY.POLICY_TYPE = :B5 OR ASSOC.COLL_NAME = :B4) AND ASSOC.OBJECT_TYPE = :B3
AND ASSOC.IS_ENABLED = :B2 AND CFG.OBJECT_GUID = ASSOC.OBJECT_GUID AND CFG.POLICY_GUID = ASSOC.POLICY_GUID
AND
CFG.COLL_NAME = ASSOC.COLL_NAME
ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER, CFG.KEY_VALUE DESC
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
c0000000bf9ee2e8 7294 package body SYS.DBMS_SQLTUNE_INTERNAL
c0000000bfbe76d0 8 SYS.WRI$_ADV_SQLTUNE
c0000000d513f948 545 package body SYS.PRVT_ADVISOR
c0000000d513f948 2597 package body SYS.PRVT_ADVISOR
c0000000cef08358 241 package body SYS.DBMS_ADVISOR
c0000000bc1e9ff8 718 package body SYS.DBMS_SQLTUNE
c0000000bc31c3a0 1 anonymous block
----- Call Stack Trace -----
Function List (to Full stack) (to Summary stack)
skdstdst
以上trace中值得注意的是stack trace记录:dbgeExecuteForError <- dbgePostErrorKGE <- dbkePostKGE_kgsf，通过匹配该stack trace point在MOS上可以找到2个相关的Bug记录:
Bug 8340928: XF11.2PREPEND_DML - TRC - QKSRCBUILDRWO:
When a column in the select list references to a view column which produces
a temp LOB, such as a TO_CLOB() operator, then an ORA-600 can occur
when using the result-cache.
Workaround
Disable the result-cache
Bug 7314587: STARETL ORA-00600 INTERNAL ERROR CODE, ARGUMENTS [QERNCROWP1], [0], [2] RDBMS:
Ora-600 [Qksrcbuildrwo]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 - Release: 11.1 to 11.1
Symptoms
Getting ORA-00600 [qksrcBuildRwo] in the alert log file when trying to execute select statement.
ERROR:
--------
ORA-00600: internal error code, arguments: [qksrcBuildRwo], [], [], [], [], [], [], [], [], [],
[], []
----- Call Stack Trace -----
dbgexProcessError dbgeExecuteForError dbgePostErrorKGE dbkePostKGE_kgsf
kgeadse kgerinv_internal kgerinv kgeasnmierr qksrcBuildRwo
qknrcAllocate qkadrv qkadrv qkadrv opitca kksLoadChild
kxsGetRuntimeLock kksfbc kkspsc0 kksParseCursor opiosq0
opiall0 opikpr opiodr rpidrus skgmstack rpidru rpiswu2
kprball kprbprsu kkxs_parse kkxsprsclb pevm_icd_call_common
pfrinstr_ICAL pfrrun_no_tool pfrrun plsql_run peicnt
kkxexe opiexe opiall0 opikpr opiodr rpidrus skgmstack
rpidru rpiswu2 kprball kzftExHandler kzftAuditExe kzftChkAudit
Changes
Result cache is enabled (result cache is a new feature in 11g).
From the alert log file
-----------------
result_cache_mode = "AUTO"
Cause
The ORA-00600 [Qksrcbuildrwo] is caused by
unpublished Bug 8340928 XF11.2PREPEND_DML - TRC - QKSRCBUILDRWO
Solution
1. Disable result_cache_mode in the spfile/pfile (remove it) or set it to MANUAL
At session level
------------
SQL> alter session set result_cache_mode='MANUAL';
-- Or
At system level
------------
SQL> alter system set result_cache_mode='MANUAL';
-- Or
2. Apply one off Patch 8340928 if available on My Oracle Support for your Oracle Version and Platform.
-- Or
3. Upgrade to 11.2 where unpublished Bug 8340928 is fixed.
可以确定该qksrcBuildRwo内部错误与字典表FILE$的讹误无关，而是由于11g release1中result cache的相关bug引起的；MOS建议通过不适用结果集缓存(result cache)特性来workaround这个错误，或者干脆升级到11g release(11.2.0.1以上，目前最新为11.2.0.2)。