使用数据泵加载数据时遇到ORA-31693、ORA-29913错误
Jan252016
昨天一个朋友在用数据泵迁移分区表的时候遇到了ORA-31693、ORA-29913错误，他的源端是企业版，目标端是标准版，标准版是不支持分区表的。
在告诉他在标准版的数据库中先创建普通的堆表，然后通过CONTENT=DATA_ONLY只加载数据的方法，他那面反馈错误依旧，还是导不进去数据，在使用PARTITION_OPTIONS=MERGE参数后，还是这个错误。我在自己的环境进行模拟测试，源端是11.2.0.4.0版本的企业版，目标端是11.2.0.4.0版本的标准版。下面为实验过程。
1.源端创建分区表并插入数据。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
SQL> create table t_part(id number,name varchar2(10))
2 partition by hash(name)
3 (partition part1,
4 partition part2);
Table created.
SQL> insert into t_part values (1,'a');
1 row created.
SQL> insert into t_part values (2,'b');
1 row created.
SQL> insert into t_part values (3,'c');
1 row created.
SQL> insert into t_part values (4,'d');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_part partition(part1);
ID NAME
---------- ----------
3 c
SQL> select * from t_part partition(part2);
ID NAME
---------- ----------
1 a
2 b
4 d
2.源端创建数据泵需要的DIRECTORY并授权。
SQL> create directory expbk as '/home/oracle'; Directory created. SQL> grant all on directory expbk to dbdream; Grant succeeded.
3.源端使用数据泵导出测试表并发送到目标端主机上。
[oracle@dbserver2 ~]$ expdp dbdream/dbdream directory=expbk dumpfile=t_part.dmp Export: Release 11.2.0.4.0 - Production on Fri Jan 22 17:57:29 2016 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 Starting "DBDREAM"."SYS_EXPORT_SCHEMA_01": dbdream/******** directory=expbk dumpfile=t_part.dmp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 16 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 "DBDREAM"."T_PART":"PART1" 5.414 KB 1 rows . . exported "DBDREAM"."T_PART":"PART2" 5.429 KB 3 rows Master table "DBDREAM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DBDREAM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/t_part.dmp Job "DBDREAM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jan 22 17:57:42 2016 elapsed 0 00:00:12 [oracle@dbserver2 ~]$ scp t_part.dmp 10.9.15.100:/home/oracle/ The authenticity of host '10.9.15.100 (10.9.15.100)' can't be established. RSA key fingerprint is d6:c6:be:ba:84:bf:c4:1f:6a:d1:97:05:06:e5:24:71. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.9.15.100' (RSA) to the list of known hosts. oracle@10.9.15.100's password: t_part.dmp 100% 192KB 192.0KB/s 00:00
4．目标端创建DIRECTORY并授权。
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 – Production SQL> create directory expbk as '/home/oracle'; Directory created. SQL> grant all on directory expbk to dbdream; Grant succeeded.
5.直接使用数据泵导入会报标准版不支持分区表而导致建表失败的错误。
[oracle@dbserver1 ~]$ impdp dbdream/dbdream directory=expbk dumpfile=t_part.dmp
Import: Release 11.2.0.4.0 - Production on Fri Jan 22 18:04:27 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "DBDREAM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DBDREAM"."SYS_IMPORT_FULL_01": dbdream/******** directory=expbk dumpfile=t_part.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"DBDREAM" already exists
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
ORA-39083: Object type TABLE:"DBDREAM"."T_PART" failed to create with error:
ORA-00439: feature not enabled: Partitioning
Failing sql is:
CREATE TABLE "DBDREAM"."T_PART" ("ID" NUMBER, "NAME" VARCHAR2(10 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY HASH ("NAME") (PARTITION "PART1" TABLESPACE "USERS" NOCOMPRESS , PARTITION "PART2" TABLESPACE "USERS" NOCOMPRESS )
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "DBDREAM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Fri Jan 22 18:04:32 2016 elapsed 0 00:00:03
6.在目标端使用先创建表，然后通过只加载数据的方式导入数据。
SQL> create table t_part(id number,name varchar2(10)); Table created. [oracle@dbserver1 ~]$ impdp dbdream/dbdream directory=expbk dumpfile=t_part.dmp CONTENT=DATA_ONLY Import: Release 11.2.0.4.0 - Production on Fri Jan 22 18:06:31 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production Master table "DBDREAM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "DBDREAM"."SYS_IMPORT_FULL_01": dbdream/******** directory=expbk dumpfile=t_part.dmp CONTENT=DATA_ONLY Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "DBDREAM"."T_PART":"PART2" 5.429 KB 3 rows . . imported "DBDREAM"."T_PART":"PART1" 5.414 KB 1 rows Job "DBDREAM"."SYS_IMPORT_FULL_01" successfully completed at Fri Jan 22 18:06:34 2016 elapsed 0 00:00:03
我的环境这种方法是可以的，而且使用分区合并(PARTITION_OPTIONS=MERGE)的方法也是可以的。
[oracle@dbserver1 ~]$ impdp dbdream/dbdream directory=expbk dumpfile=t_part.dmp CONTENT=DATA_ONLY PARTITION_OPTIONS=MERGE
Import: Release 11.2.0.4.0 - Production on Fri Jan 22 18:12:18 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "DBDREAM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DBDREAM"."SYS_IMPORT_FULL_01": dbdream/******** directory=expbk dumpfile=t_part.dmp CONTENT=DATA_ONLY PARTITION_OPTIONS=MERGE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DBDREAM"."T_PART":"PART2" 5.429 KB 3 rows
. . imported "DBDREAM"."T_PART":"PART1" 5.414 KB 1 rows
Job "DBDREAM"."SYS_IMPORT_FULL_01" successfully completed at Fri Jan 22 18:12:20 2016 elapsed 0 00:00:02
SQL> select * from t_part;
ID NAME
---------- ----------
1 a
2 b
4 d
3 c
经过测试，使用分区合并(PARTITION_OPTIONS=MERGE)并不需要自己创建表也是可以的。
SQL> drop table t_part purge; Tbale dropped. [oracle@dbserver1 ~]$ impdp dbdream/dbdream directory=expbk dumpfile=t_part.dmp PARTITION_OPTIONS=MERGE Import: Release 11.2.0.4.0 - Production on Mon Jan 25 10:12:11 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production Master table "DBDREAM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "DBDREAM"."SYS_IMPORT_FULL_01": dbdream/******** directory=expbk dumpfile=t_part.dmp PARTITION_OPTIONS=MERGE Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"DBDREAM" already exists 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/TABLE_DATA . . imported "DBDREAM"."T_PART":"PART1" 5.414 KB 1 rows . . imported "DBDREAM"."T_PART":"PART2" 5.429 KB 3 rows Job "DBDREAM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Mon Jan 25 10:12:13 2016 elapsed 0 00:00:01
可是为什么遇到这个问题的朋友使用这些方法都不可以呢？通过分析发现，他使用的环境是11.2.0.1.0版本的标准版，难道是11.2.0.1.0版本不支持这些功能吗？通过查看数据泵的帮助手册，发现11.2.0.1.0版本也是支持这些的，那是什么原因呢？分析发现他的源端数据库版本是11.2.0.2.0 forlinux 64位企业版，目标端是11.2.1.0 for windows 64位标准版，跨了一个大版本呢，而且他们在源端导出数据的时候并没有把版本指定到11.2.1.0，导致这个问题的原因难道是版本不一致的问题？下面在和他同样的环境进行验证，源端也是11.2.0.2.0 forlinux 64位企业版，目标端也是11.2.1.0 for windows 64位标准版，看看是否会遇到他遇到的问题。
源端：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> create table t_11202(id number,name char(10)) 2 partition by hash(name) 3 ( 4 partition part1, 5 partition part2); Table created. SQL> insert into t_11202 values (1,'a'); 1 row created. SQL> insert into t_11202 values (2,'b'); 1 row created. SQL> insert into t_11202 values (3,'c'); 1 row created. SQL> insert into t_11202 values (4,'d'); 1 row created. SQL> commit; Commit complete. [oracle@dbserver3 ~]$ expdp dbdream/dbdream directory=expbak dumpfile=t_11202 Export: Release 11.2.0.2.0 - Production on Mon Jan 25 14:01:12 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "DBDREAM"."SYS_EXPORT_SCHEMA_01": dbdream/******** directory=expbak dumpfile=t_11202 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 16 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/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "DBDREAM"."T_11202":"PART1" 5.421 KB 1 rows . . exported "DBDREAM"."T_11202":"PART2" 5.453 KB 3 rows Master table "DBDREAM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DBDREAM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/t_11202.dmp Job "DBDREAM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:01:35
目标端使用ftp下载dump文件。
sftp> cd /home/oracle/ sftp> lcd c:/Users/3232/Desktop/ sftp> get t_11202.dmp Downloading t_11202.dmp from /home/oracle/t_11202.dmp 100% 184KB 184KB/s 00:00:00 /home/oracle/t_11202.dmp: 188416 bytes transferred in 0 seconds (184 KB/s)
目标端使用分区合并的方式加载数据。
SQL> select * from v$version; BANNER -------------------------------------------------------------- Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 – Production C:\Users\3232>impdp dbdream/dbdream directory=expbak dumpfile=t_11202 PARTITION_OPTIONS=MERGE Import: Release 11.2.0.1.0 - Production on 星期一 1月 25 14:58:59 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production 已成功加载/卸载了主表 "DBDREAM"."SYS_IMPORT_FULL_01" 启动 "DBDREAM"."SYS_IMPORT_FULL_01": dbdream/******** directory=expbak dumpfile =t_11202 PARTITION_OPTIONS=MERGE 处理对象类型 SCHEMA_EXPORT/USER ORA-31684: 对象类型 USER:"DBDREAM" 已存在 处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT 处理对象类型 SCHEMA_EXPORT/ROLE_GRANT 处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE 处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE 处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA . . 导入了 "DBDREAM"."T_11202":"PART1" 5.421 KB 1 行 . . 导入了 "DBDREAM"."T_11202":"PART2" 5.453 KB 3 行 作业 "DBDREAM"."SYS_IMPORT_FULL_01" 已经完成, 但是有 1 个错误 (于 14:59:04 完成)
成功导入了数据，经过后续测试，先建表再使用只导入数据的方法也可以成功导入数据，那么就说明并不是版本不一致导致的问题，具体什么愿意导致他遇到的问题也不好判断了，怀疑是表结构不一致的问题，毕竟这ORA-01722无效的数字很难定位问题。
使用数据泵加载数据时遇到ORA-31693、ORA-29913错误：等您坐沙发呢！
发表评论
