EXP-00006: 出现内部不一致的错误，导出失败原因排查？

原创心在梦在²º²º 2022-09-13

4718

一、背景介绍

Oracle针对一些超大表，我们通常都会使用分区表来存放数据，便于优化、归档等，但是分区表也需要定期维护，增加新的分区，否则可能会发生如下错误:

ORA-14400: 插入的分区关键字未映射到任何分区

在oracle11g出现之前，实际工作中经常会遇到这种情况，而处理这种情况，通常是由DBA手动进行分区或者直接定义maxvalue。

11g之后推出新增特性Interval分区可以解决这个问题，它是针对Range类型分区的一种功能拓展，支持自动按年、月、日分区。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。

二、问题描述

客户反馈每日exp备份任务，执行报错，检查导出log，发现在导出一部分表之后，会抛出EXP-00006: 出现内部不一致的错误，然后导出结束。备份log如下：

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 XXXX 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 XXXX 的对象类型定义
即将导出 XXXX 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 XXXX 的表通过常规路径...
.................
................
. . 正在导出表                         WCXSSCB导出了           0 行
. . 正在导出表                        WEBCDGNB导出了          89 行
EXP-00006: 出现内部不一致的错误
EXP-00000: 导出终止失败

通过导出log只能看到exp-6错误信息，不够清晰，无法判断是哪个具体的表导出失败的，经查询mos，看到文档Doc ID 762774.1描述，11g之后，exp不支持Interval-Partition导出。
图片.png

三、问题复现

1) 没有自动分区表情况下导出

[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:42:19 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

结论：exp导出正常，无任何报错。

2) 有自动分区表情况下导出

SQL> conn sxc/sxc
Connected.
SQL> 
SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  5    create_time DATE
  6  )
  7  PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
  8  (partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

Table created.

SQL> insert into test values(1,'test1',sysdate);

1 row created.

SQL> commit;

Commit complete.

[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:43:29 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

结论：exp导出报错，抛出EXP-6 和 EXP-0错误信息，和客户反馈问题一致。

四、解决办法

方法1：使用数据泵导出

[oracle@ora11204 ~]$ expdp sxc/sxc directory=SOURCEDIR dumpfile=test_expdp.dmp logfile=test_expdp.log   
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:44:30 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SXC"."SYS_EXPORT_SCHEMA_01":  sxc/******** directory=SOURCEDIR dumpfile=test_expdp.dmp logfile=test_expdp.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SXC"."T"                                   7.651 MB   78920 rows
. . exported "SXC"."TEST":"SYS_P101"                     5.867 KB       1 rows
. . exported "SXC"."TEST":"PART_T01"                         0 KB       0 rows
Master table "SXC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/oradata/test_expdp.dmp
Job "SXC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Sep 14 10:45:03 2022 elapsed 0 00:00:33

结论：使用expdp导出正常，无任何报错。

方法2：使用普通分区表

-- 1）转化为普通分区表
SQL> alter table test rename to test_bak;

Table altered.

SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  create_time DATE
)
partition by range (create_time)
(
  partition part_t01 values less than (TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition part_tmax values less than (MAXVALUE)                         
 11  );

Table created.

SQL> insert into test select * from test_bak;

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_bak;

Table dropped.


-- 2）再次使用exp导出：正常，无任何报错
[oracle@ora11204 ~]$  exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 11:26:24 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
. . exporting table                           TEST
. . exporting partition                       PART_T01          0 rows exported
. . exporting partition                      PART_TMAX          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

五、延申测试

1) 新建自动分区表，不插入数据

SQL> conn sxc/sxc
Connected.
SQL> drop table test;
SQL> create table test
  2  (
  3    ID NUMBER(20) not null,
  4    REMARK VARCHAR2(1000),
  5    create_time DATE
  6  )
  7  PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
  8  (partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));

Table created.

-- 新建自动分区表之后，直接导出
[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 10:51:23 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00113: Feature New Composite Partitioning Method is unsupported. Table SXC.TEST could not be exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

结论：exp导出同样报错，但是抛出了 EXP-00113: Feature New Composite Partitioning Method is unsupported. Table SXC.TEST could not be exported错误信息，提示了是 SXC.TEST 表导出失败，比之前的错误信息详细。

2) 自动分区表插入数据

SQL> insert into test values(1,'test1',sysdate);

1 row created.

SQL> commit;

Commit complete.


-- 插入数据后，再导出
[oracle@ora11204 ~]$ exp sxc/sxc file=test_exp.dmp log=test_exp.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Sep 14 11:09:02 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SXC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SXC 
About to export SXC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SXC's tables via Conventional Path ...
. . exporting table                              T      78920 rows exported
EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

结论：exp导出同样报错，但是并没有抛出了 EXP-00113错误信息，只抛出了EXP-6 和 EXP-0错误信息，不够清晰。

墨力计划故障处理

最后修改时间：2022-09-13 19:18:57

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

EXP-00006: 出现内部不一致的错误 ，导出失败原因排查？