[toc]

适用范围

ORACLE 12c、19c

问题概述

impdp 操作使用DISABLE_ARCHIVE_LOGGING:Y 参数后，报ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CHANGE_ARCHIVE_LOGGING 等一系列错误信息。

问题分析

impdp 的参数文件信息如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
more impdp_db2.par
DIRECTORY=DATA_DMP
DUMPFILE=emss2_zs_db2_%u.dmp
LOGFILE=emss2_zs_db2_1.log
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TABLE_EXISTS_ACTION=TRUNCATE
CONTENT=DATA_ONLY
PARALLEL=16
CLUSTER=no
REMAP_SCHEMA=EMSS_BLC:EMSS_DRS_TMP
TABLES=
(
要导入的表清单
)

impdp 后报如下错误:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
=====================
. . imported "schexxx"."tbsxxxx":"P14410"."P14410_202004"  9.818 MB  525822 rows
. . imported "schexxx"."tbsxxxb":"P14410"."P14410_202003"  9.296 MB  515423 rows
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CHANGE_ARCHIVE_LOGGING [TABLE_DATA:"schexxx"."tbsxxxb":"P14410"."P14410_202001"] 
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 12623
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA", line 12174
ORA-06512: at "SYS.UTL_XML", line 400
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12848
ORA-06512: at "SYS.DBMS_METADATA", line 6114
ORA-06512: at "SYS.DBMS_METADATA", line 12165
ORA-06512: at "SYS.KUPW$WORKER", line 8178
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xe59ee6278     33543  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
0xe59ee6278     12651  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
0xe59ee6278      8255  package body SYS.KUPW$WORKER.CHANGE_ARCHIVE_LOGGING
0xe59ee6278      6136  package body SYS.KUPW$WORKER.RESTORE_LOGGING
0xe59ee6278     13904  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
0xe59ee6278      2439  package body SYS.KUPW$WORKER.MAIN
0xe75ed6ab8         2  anonymous block
KUPW: l_valid : 1 - Loading SOURCE_VERSION with type NULL
KUPW: l_valid: -3 - Not Loading DBA with type JOB
KUPW: 
KUPW: In SET_DDL_TRANSFORM with index 3 and object type TABLE
DBMS_METADATA.SET_TRANSFORM_PARAM
DBMS_METADATA.SET_TRANSFORM_PARAM
KUPW: 
DBMS_METADATA.CONVERT
DBMS_METADATA.CONVERT
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'ORA-06512: at "SYS.DBMS_METADATA", line 12174
ORA-06512: at "SYS.UTL_XML", line 400
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12848
ORA-06512: at "SYS.DBMS_METADATA", line 6114
ORA-06512: at "SYS.DBMS_METADATA", line 12165
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CHANGE_ARCHIVE_LOGGING [TABLE_DATA:"schexxx"."INST_RELA_SNAP":"P14410"."P14410_202007"] 
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 12623
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA", line 12174
ORA-06512: at "SYS.UTL_XML", line 400
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12848
ORA-06512: at "SYS.DBMS_METADATA", line 6114
ORA-06512: at "SYS.DBMS_METADATA", line 12165
ORA-06512: at "SYS.KUPW$WORKER", line 8178
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xe59ee6278     33543  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
0xe59ee6278     12651  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
0xe59ee6278      8255  package body SYS.KUPW$WORKER.CHANGE_ARCHIVE_LOGGING
0xe59ee6278      6136  package body SYS.KUPW$WORKER.RESTORE_LOGGING
0xe59ee6278     13904  package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
0xe59ee6278      2439  package body SYS.KUPW$WORKER.MAIN
0xe75ed6ab8         2  anonymous block
KUPW: l_valid : 1 - Loading SOURCE_VERSION with type NULL
KUPW: l_valid: -3 - Not Loading DBA with type JOB
KUPW: 
KUPW: In SET_DDL_TRANSFORM with index 3 and object type TABLE
DBMS_METADATA.SET_TRANSFORM_PARAM
DBMS_METADATA.SET_TRANSFORM_PARAM
KUPW: 
DBMS_METADATA.CONVERT
DBMS_METADATA.CONVERT
KUPW: In procedure DETERMINE_FATAL_ERROR with ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'ORA-06512: at "SYS.DBMS_METADATA", line 12174
ORA-06512: at "SYS.UTL_XML", line 400
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 12848
ORA-06512: at "SYS.DBMS_METADATA", line 6114
ORA-06512: at "SYS.DBMS_METADATA", line 12165
Job "SYSTEM"."SYS_IMPORT_TABLE_01" stopped due to fatal error at Sat May 13 22:54:53 2023 elapsed 0 06:30:27

DISABLE_ARCHIVE_LOGGING:Y表示在导入过程中禁用归档日志。

默认情况下,当我们使用impdp命令导入数据时,ORACLE 会在导入期间产生归档日志,以支持导入数据的恢复。但是在某些情况下,我们并不需要这些归档日志,为了节省空间和提高导入效率,可以选择禁用归档日志。使用这个参数的好处是:

  1. 节省磁盘空间，禁用归档日志可以避免产生大量的归档日志文件,节省磁盘空间。
  2. 提高导入效率，不生成归档日志可以减少 IO 和 CPU 的消耗,提高数据导入的性能。

但是通过搜索 MOS 发现 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y 参数在12c之后是有不少 bug的，当前数据库是 FORCEL LOGGING 模式，因此使用此参数是无效的话。因此既然不生效的话，建议去掉此参。

在去掉此参数之后导入正常，没有报错产生了。

解决方案

在 FORCEL LOGGING 模式，使用 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y 参数是无效，因此去掉次参数。

参考文档

无

原文作者: liups.com

原文链接: http://liups.com/posts/6772d843/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议