oracle expdp/exp ora-600/ora-39014報錯處理

在一次資料遷移的時候，expdp匯出報錯，錯誤資訊如下：

 

 版本號：11.2.0.1

沒有打PSU，檢視報錯的aler部分紀錄檔如下：

 

 其中的某一些trc紀錄檔檔案截圖：

Trace file d:\oracle\administrator\diag\rdbms\ttfc\ttfc\trace\ttfc_dm00_11104.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.2
CPU : 8 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:4813M/15359M, Ph+PgF:11787M/23551M
Instance name: ttfc
Redo thread mounted by this instance: 1
Oracle process number: 42
Windows thread id: 11104, image: ORACLE.EXE (DM00)

*** 2022-10-23 12:39:28.872
*** SESSION ID:(204.20873) 2022-10-23 12:39:28.872
*** CLIENT ID:() 2022-10-23 12:39:28.872
*** SERVICE NAME:(SYS$USERS) 2022-10-23 12:39:28.872
*** MODULE NAME:(Data Pump Master) 2022-10-23 12:39:28.872
*** ACTION NAME:(SYS_EXPORT_SCHEMA_01) 2022-10-23 12:39:28.872

KUPC: Setting remote flag for this process to FALSE
prvtaqis - Enter
prvtaqis subtab_name upd
prvtaqis sys table upd
KUPP: Initialization complete for master process DM00

*** 2022-10-23 12:39:29.294
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654899 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654757 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block

*** 2022-10-23 13:11:01.717
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:11:02.170
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654757 lascn 22
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst !retval block
kwqberlst rqan->lagno_kwqiia 7
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:11:04.232
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:11:05.576
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:11:06.139
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:11:09.139
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:31.092
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:48.842
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:49.998
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:52.154
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:55.826
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:56.326
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:57.076
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:58.420
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:12:59.170
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:13:01.826
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:13:02.186
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:13:04.295
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:13:05.170
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
Incident 104071 created, dump file: d:\oracle\administrator\diag\rdbms\ttfc\ttfc\incident\incdir_104071\ttfc_dm00_11104_i104071.trc

*** 2022-10-23 13:26:35.249
ORA-31671: Worker 程序 DW00 有未處理的異常錯誤。
ORA-00600: 內部錯誤程式碼, 引數: [kupfiWriteLob_1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.KUPW$WORKER", line 1712
ORA-06512: 在 line 2

KUPP: Exiting kuppqwem
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

*** 2022-10-23 13:26:37.405
kwqberlst rqan->lascn_kwqiia > 0 block
kwqberlst rqan->lascn_kwqiia 7
kwqberlst ascn -1443654893 lascn 22

其他trc檔案

 

 

 

 

首先第一感覺就是BUG，因為oracle版本太舊了，但是我還是嘗試用其他辦法處理，儘量不升級，畢竟不是所有環境都能升級，有些廢棄的環境也完全不需要升級。

我嘗試用exp匯出

 

 匯出失敗，但是我以外的發現有提到統計資訊的匯出失敗，所以我加上 statistics=none,再次用exp 匯出也不行。我覺得不要所有報錯提升都去看support,應該培養自己的正確思維方式，然後我想既然跟統計資訊有關，乾脆試一下用expdp 匯出，互虐統計資訊再試一下，expdp 加上引數 EXCLUDE=STATISTICS  然後神奇的一幕發生了，可以匯出，並且沒有報錯。

 

 

 

 後面用support 檢視到Doc ID 1982596.1

果然是BUG，Bug 11656163 ，是由於有大檔案物件，超過4G，在（11.2.0.3，12.1.0.1）版本修復改BUG。

The bug is a.o. fixed in

12.1.0.1 (Base Release)
11.2.0.3 (Server Patch Set)

檢視表段的大小

SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN
(SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'CLOB')
GROUP BY SEGMENT_NAME
ORDER BY 2 DESC;

 

 

select a.owner,a.segment_name,
sum(bytes/1024/1024/1024) G from dba_segments a where a.owner in ('LIVE','PAYTTFC','RACTTFCTEST') GROUP BY A.owner,a.segment_name
ORDER BY 3 DESC;

 但是我查到

 

 

 所涉及的大欄位的表段

SELECT B.TABLE_NAME,

       B.COLUMN_NAME,

       A.SEGMENT_NAME,

       a.SEGMENT_TYPE,

       ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G

  FROM DBA_SEGMENTS A

  LEFT JOIN DBA_LOBS B

    ON A.OWNER = B.OWNER

   AND A.SEGMENT_NAME = B.SEGMENT_NAME

 WHERE B.SEGMENT_NAME = 'SYS_LOB0000026212C00002$$'

 HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1

 GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;

檢視大欄位的大小