客户的测试环境碰到了这个错误。
告警日志中错误如下：
Sat Oct 15 01:21:40 GMT+08:00 2011ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20111015004332.DB','db' SCOPE=MEMORY SID='db1'; Sat Oct 15 01:21:40 GMT+08:00 2011ALTER SYSTEM SET service_names='db' SCOPE=MEMORY SID='db1'; Sat Oct 15 01:39:03 GMT+08:00 2011Errors IN file /u01/app/oracle/admin/db/udump/db11_ora_4325842.trc: ORA-00600: 内部错误代码, 参数: [6928], [886], [], [], [], [], [], [] Sat Oct 15 01:39:45 GMT+08:00 2011Trace dumping IS performing id=[cdmp_20111015013945] Sat Oct 15 02:16:12 GMT+08:00 2011The VALUE (30) OF MAXTRANS parameter ignored. Sat Oct 15 02:16:14 GMT+08:00 2011ALTER SYSTEM SET service_names='db','SYS$SYS.KUPC$C_1_20111015021613.DB' SCOPE=MEMORY SID='db'; Sat Oct 15 02:16:14 GMT+08:00 2011ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20111015021613.DB','db','SYS$SYS.KUPC$S_1_20111015021613.DB' SCOPE=MEMORY SID='db1'; kupprdp: master process DM00 started WITH pid=792, OS id=60686622 TO EXECUTE - SYS.KUPM$MCP.MAIN('SYS_IMPORT_TABLE_01', 'U1', 'KUPC$C_1_20111015021613', 'KUPC$S_1_20111015021613', 0); kupprdp: worker process DW01 started WITH worker id=1, pid=794, OS id=54002170 TO EXECUTE - SYS.KUPW$WORKER.MAIN('SYS_IMPORT_TABLE_01', 'U1'); |
从上面的完整的事件信息判断，问题似乎发生在数据泵导入的过程中，进一步检查对应的详细信息：
/u01/app/oracle/admin/db/udump/db1_ora_4325842.trc Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production WITH the Partitioning, REAL Application Clusters, OLAP, DATA Mining AND REAL Application Testing options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db System name: AIX Node name: db1 Release: 1 Version: 6 Machine: 00C73FB64C00 Instance name: db1 Redo thread mounted BY this instance: 1 Oracle process NUMBER: 39 Unix process pid: 4325842, image: oracle@db *** 2011-10-15 01:38:43.021 *** ACTION NAME:(..\Control\T.ctl) 2011-10-15 01:38:43.017 *** MODULE NAME:(SQL Loader Direct Path LOAD) 2011-10-15 01:38:43.017 *** SERVICE NAME:(db) 2011-10-15 01:38:43.017 *** SESSION ID:(3092.196) 2011-10-15 01:38:43.017 col 0; len 5; c4 15 0c 08 13 col 1; len 18; 32 30 31 31 30 37 31 38 30 30 30 30 34 30 30 30 30 30 col 0; len 5; c4 15 0c 08 13 col 1; len 18; 32 30 31 31 30 37 31 38 30 30 30 30 34 30 30 30 30 30 *** 2011-10-15 01:39:03.971 ksedmp: internal OR fatal error ORA-00600: 内部错误代码, 参数: [6928], [886], [], [], [], [], [], [] CURRENT SQL statement FOR this SESSION: INSERT /*+ SYS_DL_CURSOR */ INTO U1.T ("BEGIN_DATE","NO","CURRENT_DATE") VALUES (NULL,NULL,NULL) ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ksedst+001c bl ksedst1 700000010003520 ? 11019C008 ? ksedmp+0290 bl ksedst 104C20490 ? ksfdmp+02d8 bl 03F34EF4 kgeriv+0108 bl _ptrgl kgeasi+0118 bl kgeriv 000000001 ? 000000000 ? 1101C0848 ? 1108ECC98 ? 1108EE1F0 ? kdblfl+0734 bl 01FC4904 klafin+010c bl kdblfl 000000081 ? 110455A58 ? 7000000FDC56DA8 ? 1101C0848 ? kpodpfin+0550 bl klafin 104D0F250 ? kpodpmop+0364 bl kpodpfin 110455180 ? opiodr+0b2c bl _ptrgl ttcpip+1020 bl _ptrgl opitsk+117c bl 01FC70C8 opiino+09d0 bl opitsk 0FFFFD2F0 ? 000000000 ? opiodr+0b2c bl _ptrgl opidrv+04a4 bl opiodr 3C1028E398 ? 404C734B0 ? FFFFFFFFFFFF2B0 ? 01028E390 ? sou2o+0090 bl opidrv 3C02A2911C ? 4A0071E60 ? FFFFFFFFFFFF2B0 ? opimai_real+01bc bl 01FC3934 main+0098 bl opimai_real 000000000 ? 000000000 ? __start+0070 bl main 000000000 ? 000000000 ? --------------------- Binary Stack Dump --------------------- |
而从详细TRACE分析，显然这是一个SQLLDR进程，不过考虑到数据泵也是采用直接路径装载，那么很有可能这个直接路径是由数据泵所触发的。
这个问题在MOS上的描述为：Bug 5035742 – SQL*loader direct path sets all local indexes UNUSABLE when an error occurs [ID 5035742.8]，显然是由于直接路径的处理过程中存在不完善的地方，导致索引状态不正常所致。
Oracle给出的方法是采用常规路径方式导入，而Oracle目前并没有专门的PATCH来解决这个问题。所幸这个问题对于系统的影响并不大，一般而言进行批量装载的表，在装载的时刻都没有什么并发的访问，除了改变数据泵导入模式外，也可以采用手工修复的方式来解决这个问题。