impdp因致命錯誤終止 ORA-7445 [kpodpals]

impdp因致命錯誤終止 ORA-7445 [kpodpals]

基本要素
前天好不容易成功給使用者把資料全庫匯出，今天使用者又告知匯出的資料無法匯入，首先就問使用者有什麼錯誤提示，給我的回答是就一個‘作業"SYSTEM"."SYS_IMPORT_FULL_03" 因致命錯誤於 xxxx  elapsed 0 03:01:06 停止’,其他什麼提示都沒有，資訊量太少了，這個處理起來還挺麻煩的。

問題分析

步驟一：首先還是添加跟蹤資訊
還是得靠自己，還好咱們會點跟蹤技巧，具體方法見我之前的文章How to Diagnose Oracle Data Pump-如何給資料泵添加診斷資訊，來吧，看下跟蹤後的結果orcl_dm00_10856.trc，錯誤提示的位置如下：
KUPM:13:38:03.378: Log message received from worker DG,KUPC$C_1_20141208103757,KUPC$A_1_103800203000000,MCP,510,Y
KUPM:13:38:03.378: . . 匯入了 "ZLHIS"."體檢套餐計價"                    576.4 KB  20817 行
KUPM:13:38:03.378: ****OUT DISPATCH, request type=3031, response type =2041
*** 2014-12-08 13:38:04.392
KUPM:13:38:04.392: Client count is: 1
KUPM:13:38:04.392: In check_workers...
KUPM:13:38:04.392: Live worker count is:  1
KUPM:13:38:04.392: In set_longops
KUPM:13:38:04.392: Work so far is: 107805.74195098876953125
KUPM:13:38:04.392: Checking for resumable waits
KUPM:13:39:05.435: Client count is: 1
*** 2014-12-08 13:39:05.435
KUPM:13:39:05.435: In check_workers...
KUPM:13:39:05.435: Live worker count is:  0
KUPM:13:39:05.435: worker id is:
KUPM:13:39:05.435: Worker error is: 0
KUPM:13:39:05.435: Exited main loop...
KUPM:13:39:05.435: Returned to MAIN
KUPV:13:39:05.435: Update request for job: SYSTEM.SYS_IMPORT_FULL_03, func: 1
KUPM:13:39:05.435: Entered state: STOPPING
KUPM:13:39:05.435: keeping master because job is restartable
KUPM:13:39:05.435: Final job_info_flags = 1
KUPM:13:39:05.435: Log message received from MCP
KUPM:13:39:05.435: 作業 "SYSTEM"."SYS_IMPORT_FULL_03" 因致命錯誤於 星期一 12月 8 13:39:05 2014 elapsed 0 03:01:06 停止
KUPM:13:39:05.498: In RESPOND_TO_START
KUPM:13:39:05.498: In check_workers...
KUPM:13:39:05.498: Live worker count is:  0
KUPM:13:39:05.498: worker id is:
KUPM:13:39:05.498: Worker error is: 0
結果非常令人失望，也沒什麼有價值的錯誤提示。
步驟二：查看下alert日誌
這時候想到查看下alert日誌，也許裡面能給一些提示，開啟日誌果然發現了非常有價值的資訊，如下：
Tue Dec 09 02:00:00 2014
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Tue Dec 09 02:00:12 2014
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x14575B408, kpodpals()+5174]
ERROR: Unable to normalize symbol name for the following short stack (at offset 213):
dbgexProcessError()+200<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+2269<-dbkePostKGE_kgsf()+77<-kgeade()+562<-kgerelv()+151<-kgerev()+45<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1862<-sss_xcpt_EvalFilter()+174<-.1.4_5+59<-0000000077C985A8<-0000000077CA9D0D<-0000000077C991AF<-0000000077CD1278<-kpodpals()+5174<-kpodpp()+4946<-opiodr()+1631<-kpoodr()+699<-xupirtrc()+2833<-upirtrc()+117<-kpurcsc()+150<-kpudpxp_ctxPrepare()+19418<-OCIDirPathPrepare()+11<-kupd_initDirPath()+1048<-kupdls()+2863<-spefcifa()+3937<-spefmccallstd()+532<-pextproc()+47<-PGOSF493_peftrusted()+134<-psdexsp()+297<-rpiswu2()+3039<-psdextp()+951<-pefccal()+785<-pefcal()+225<-pevm_FCAL()+164<-pfrinstr_FCAL()+69<-pfrrun_no_tool()+77<-pfrrun()+1241<-plsql_run()+903<-peicnt()+328<-kkxexe()+616<-opiexe()+20959<-kpoal8()+2397<-opiodr()+1631<-kpoodr()+699<-xupirtrc()+2833<-upirtrc()+117<-kpurcsc()+150<-kpuexec()+10984
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_dw00_5304.trc  (incident=8636):
ORA-07445: 出現異常錯誤: 核心轉儲 [kpodpals()+5174] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x14575B408] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_8636\orcl_dw00_5304_i8636.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Dec 09 02:00:17 2014
Dumping diagnostic data in directory=[cdmp_20141209020017], requested by (instance=1, osid=5304 (DW00)), summary=[incident=8636].
Tue Dec 09 02:00:19 2014
Sweep [inc][8636]: completed
Sweep [inc2][8636]: completed
Tue Dec 09 02:00:55 2014

有了這個資訊，更進一步的日誌分析我這裡就不再敘述，因為也沒什麼價值資訊，我們這裡就抓住ORA-07445 [kpodpals()+5174]不放，這種核心錯誤，一般99%是Oracle的BUG引起，通過Oracle的官方資訊，果然發現了一篇文檔：
ORA-7445 [kpodpals] During DataPump Import (文檔 ID 1096837.1)
SYMPTOMS

You perform a DataPump import and this breaks with errors:
#> impdp system/password directory=dpu dumpfile=a_table.dmp table_exists_action=replace
Import: Release 10.2.0.1.0 - Production on Wednesday, 21 April, 2010 9:21:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpu
dumpfile=a_table.dmp table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at 09:23:32
CAUSE

This is addressed in Bug 9626756. A no-name column "<space>" is included in the table definition.
The imported table is defined as:
create table a_table
(
    id    number,
    " "  varchar2(10), -- " " means "<one space>"
    text  varchar2(10)
);
SOLUTION

1. Don't use columns like "<space>" in the source database

- OR -

2. If a table has such columns, then exclude the table during import with:
exclude=table:\"IN ('A_TABLE')\"

原因就是有表的欄位是空格，坑爹啊，居然有這麼建立表的，接下來我們就要查詢下我們系統中是否真的存在這樣的表。

解決過程

步驟一：查詢表欄位

通過上門的SQL語句，一查詢果然有有空欄位，表名是TX_診療項目，該表不是我們系統帶的表，是由於之前渠道做系統切換的時候，儲存的使用者舊系統資料的表，真是害死人啊。
步驟二：排除表重新匯入
有兩種方式解決，1.在正式庫中對錶進行調整或者重建，2.匯入的時排除問題表，經過溝通決定採用第二種方法，排除表
impdp system/xxxxx DIRECTORY=dp full=y DUMPFILE=wzyfull20141205b_01.dmp logfile=impdp1209.log trace=4a0300 exclude=TABLE:\"IN \(\'ZLHIS.TX_診療項目\'\)\",SCHEMA:\"IN\(\'SYS\',\'SYSTEM\',\'OUTLN\',\'MGMT_VIEW\',\'FLOWS_FILES\',\'MDSYS\',\'ORDSYS\',\'EXFSYS\',\'DBSNMP\',\'WMSYS\',\'WKSYS\',\'WK_TEST\',\'CTXSYS\',\'ANONYMOUS\',\'SYSMAN\',\'XDB\',\'WKPROXY\',\'ORDPLUGINS\',\'FLOWS_030000\',\'OWBSYS\',\'SI_INFORMTN_SCHEMA\',\'OLAPSYS\',\'SCOTT\',\'ORACLE_OCM\'\)\"
      經過3個多小時的漫長等待，資料成功匯入。
關鍵知識點
資料泵日誌跟蹤：通過在匯出匯入時，添加trace參數，產生追蹤記錄檔檔案
ORA-7445 [kpodpals]： Bug 9626756.在一個表中包含一個沒有名字的全是空格的字

Oracle匯入匯出expdp IMPDP詳解

Oracle 10g expdp匯出報錯ORA-4031的解決方案

Oracle 10gr2 rac expdp 報錯UDE-00008 ORA-31626

Oracle中利用expdp/impdp備份資料庫的使用說明

Oracle備份還原（expdp/impdp）

impdp ORA-39002,ORA-39166,ORA-39164的問題及解決