在一次数据迁移的时候,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;
查看大字段的大小
![](/i/l/?n=22&i=blog/1339563/202210/1339563-20221023175835518-1362726335.png)
support 说到,当数据库对象(例如包主体)的长度超过 4GB 时,可能会在 DataPump 导出期间出现问题,然而我这个还没有到达4G就已经出问题了。
support 建议的要解决此问题,请使用以下任一替代方法:
升级到 12c
- 或 -
应用补丁 11.2.0.3 或更高版本
- 或 -
如果适用于您的平台和 Oracle 版本,请应用临时补丁 11656163 。
- 或者 -
作为一种可能的解决方法,您可以尝试从导出中排除相关对象
注意:
在客户案例中,实际上是通过添加参数EXCLUDE=STATISTICS解决了问题 --不谋而合,正是自己尝试这种方法处理了。