暂无图片
暂无图片
6
暂无图片
暂无图片
暂无图片
首页 / 如何从高版本数据库导出dmp至低版本数据库导入？

如何从高版本数据库导出dmp至低版本数据库导入？

原创 心在梦在 2023-05-09
2318

如何从高版本数据库导出dmp至低版本数据库导入？

 

背景介绍

客户提供一个dmp文件，需要导入到其他数据库中，但是导入过程，抛出如下错误：
图片.png

经检查，是因为dmp文件是从12c环境导出，而目标环境是11g，版本不兼容。

 

思考？

我们知道，考虑了同以前版本的兼容，从低版本数据库EXP数据，导入至高版本数据库是没什么问题的，但是从高本数据库EXP导出数据，导入至低版本数据库会遇到问题，我们该如何解决？我们通过实验验证一下。

实验环境：

低版本: 11.2.0.4

高版本：19.3.0.0
 

一、使用exp、imp

1.1 低版本到高版本

1.1.1 实验

–1) 源端11204版本 创建测试表，并导出

SQL> create table table_11204 as select * from dba_objects;

Table created.

[oracle@ora11204 ~]$ exp sxc/sxc file=table_11204.dmp tables=table_11204 log=table_11204_exp.log
Export: Release 11.2.0.4.0 - Production on Tue May 9 01:25:23 2023
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 tables via Conventional Path ...
. . exporting table                    TABLE_11204      78981 rows exported
Export terminated successfully without warnings.

–2) 将dmp文件拷贝到目标端

[oracle@ora11204 ~]$ scp table_11204.dmp oracle@172.17.0.3:/home/oracle
oracle@172.17.0.3's password: 
table_11204.dmp                                           100% 8920KB   8.7MB/s   00:00

–3)目标端19c版本 导入dmp

[oracle@ora19c ~]$ imp sxc/sxc file=table_11204.dmp tables=table_11204 log=table_11204_imp.log fromuser=sxc touser=sxc
Import: Release 19.0.0.0.0 - Production on Tue May 9 10:20:21 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
IMP-00403: 
Warning: This import generated a separate SQL file "table_11204_imp_sys.sql" which contains DDL that failed due to a privilege issue.
. importing SXC's objects into SXC
. . importing table                  "TABLE_11204"      78981 rows imported
Import terminated successfully with warnings.

结论：从低版本导出，高版本导入，成功。

1.2 高版本到低版本

1.2.1 实验

–1) 源端19c版本 创建测试表，并导出

SQL> create table table_19c as select * from dba_objects;

Table created.

[oracle@ora19c ~]$ exp sxc/sxc file=table_19c.dmp tables=table_19c log=table_19c_exp.log
Export: Release 19.0.0.0.0 - Production on Tue May 9 10:23:32 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                      TABLE_19C      72375 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

–2) 将dmp文件拷贝到目标端

[oracle@ora19c ~]$ scp table_19c.dmp  oracle@172.17.0.2:/home/oracle
oracle@172.17.0.2's password: 
table_19c.dmp                                   100%   11MB 127.2MB/s   00:00

–3)目标端11.2.0.4版本 导入dmp

[oracle@ora11204 ~]$ imp sxc/sxc file=table_19c.dmp tables=table_19c log=table_19c_imp.log fromuser=sxc touser=sxc
Import: Release 11.2.0.4.0 - Production on Tue May 9 02:25:16 2023
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
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

结论：从高版本导出，低版本导入，报错，失败。
 

1.2.2 解决办法

使用低版本客户端，通过tns，连接高版本数据库，然后用EXP导出。

--1) 通过11.2.0.4客户端，通过tns，连接到19c数据库，并导出
[oracle@ora11204 ~]$ exp sxc/sxc@ora19c file=table_19c_2.dmp tables=table_19c log=table_19c_exp2.log
Export: Release 11.2.0.4.0 - Production on Tue May 9 02:36:50 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                      TABLE_19C      72375 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

--2)低版数据库再次导入，成功
[oracle@ora11204 ~]$ imp sxc/sxc file=table_19c_2.dmp tables=table_19c log=table_19c_imp.log fromuser=sxc touser=sxc 
Import: Release 11.2.0.4.0 - Production on Tue May 9 02:39:08 2023
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 file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
. importing SXC's objects into SXC
. . importing table                    "TABLE_19C"      72375 rows imported
Import terminated successfully without warnings.

思考？ 如果使用高版本客户端，通过tns 连接低版本数据库，然后用imp导入，可以吗？ 

-- 这里我们继续使用从19c直接导出的dmp文件，然后通过19c客户端连接到11g数据库，做导入
[oracle@ora19c ~]$ imp sxc/sxc@ora11204 file=table_19c.dmp tables=table_19c log=table_19c_imp.log fromuser=sxc touser=sxc
Import: Release 19.0.0.0.0 - Production on Tue May 9 10:33:17 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, 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 file created by EXPORT:V19.00.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully

结论：导入仍然报错，这种方式不可行。 
 

二、使用expdp、impdp

2.1 低版本到高版本

2.1.1 实验

–1) 源端11204版本导出

SQL> create  directory dir_exp as '/home/oracle/dmp'; 
Directory created.

SQL> grant read,write on directory dir_exp to public;
Grant succeeded.

[oracle@ora11204 dmp]$ expdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204.dmp tables=table_11204 log=expdp_table_11204.log
Export: Release 11.2.0.4.0 - Production on Tue May 9 04:59:27 2023
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=expdp_table_11204.log" Location: Command Line, Replaced with: "logfile=expdp_table_11204.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SXC"."SYS_EXPORT_TABLE_01":  sxc/******** directory=dir_exp dumpfile=expdp_table_11204.dmp tables=table_11204 logfile=expdp_table_11204.log reuse_dumpfiles=true 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TABLE_11204"                         7.657 MB   78981 rows
Master table "SXC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dmp/expdp_table_11204.dmp
Job "SXC"."SYS_EXPORT_TABLE_01" successfully completed at Tue May 9 04:59:38 2023 elapsed 0 00:00:08

–2) 将dmp文件拷贝到目标端

[oracle@ora11204 dmp]$ scp expdp_table_11204.dmp oracle@172.17.0.3:/home/oracle/dmp
oracle@172.17.0.3's password: 
expdp_table_11204.dmp                          100% 7940KB   7.8MB/s   00:00

–3)目标端19c版本 导入dmp

-- 因为目标环境表已经存在，这里加上参数 table_exists_action=replace
[oracle@ora19c dmp]$ impdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204.dmp tables=table_11204 log=impdp_table_11204.log table_exists_action=replace
Import: Release 19.0.0.0.0 - Production on Tue May 9 13:03:10 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=impdp_table_11204.log" Location: Command Line, Replaced with: "logfile=impdp_table_11204.log"
Master table "SXC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
import done in AL32UTF8 character set and UTF8 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SXC"."SYS_IMPORT_TABLE_01":  sxc/******** directory=dir_exp dumpfile=expdp_table_11204.dmp tables=table_11204 logfile=impdp_table_11204.log table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SXC"."TABLE_11204"                         7.657 MB   78981 rows
Job "SXC"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 9 13:03:22 2023 elapsed 0 00:00:07

结论：从低版本导出，高版本导入，成功。
 

2.2 高版本到低版本

2.2 .1 实验

–1) 源端19c版本 导出

[oracle@ora19c dmp]$ expdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204_19c.dmp tables=table_11204 log=expdp_table_11204.log
Export: Release 19.0.0.0.0 - Production on Tue May 9 13:04:26 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=expdp_table_11204.log" Location: Command Line, Replaced with: "logfile=expdp_table_11204.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SXC"."SYS_EXPORT_TABLE_01":  sxc/******** directory=dir_exp dumpfile=expdp_table_11204_19c.dmp tables=table_11204 logfile=expdp_table_11204.log reuse_dumpfiles=true 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TABLE_11204"                         7.657 MB   78981 rows
Master table "SXC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dmp/expdp_table_11204_19c.dmp
Job "SXC"."SYS_EXPORT_TABLE_01" successfully completed at Tue May 9 13:04:53 2023 elapsed 0 00:00:23

–2) 将dmp文件拷贝到目标端

[oracle@ora19c dmp]$ scp expdp_table_11204_19c.dmp oracle@172.17.0.2:/home/oracle/dmp
oracle@172.17.0.2's password: 
expdp_table_11204_19c.dmp                        100% 8028KB 134.8MB/s   00:00

–3)目标端11.2.0.4版本 导入dmp

-- 因为目标环境表已经存在，这里加上参数 table_exists_action=replace
[oracle@ora11204 dmp]$ impdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204_19c.dmp tables=table_11204 log=impdp_table_11204.log table_exists_action=replace
Import: Release 11.2.0.4.0 - Production on Tue May 9 05:06:28 2023
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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/home/oracle/dmp/expdp_table_11204_19c.dmp"

结论：从高版本导出，低版本导入，报错，失败。
 

2.2.2 解决办法

高版本导出时候，加上version参数，version=低版本数据库版本号

--1) 19c 导出时，加上version=11.2.0.4参数
[oracle@ora19c dmp]$ expdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204_19c_2.dmp tables=table_11204 log=expdp_table_11204.log version=11.2.0.4
Export: Release 19.0.0.0.0 - Production on Tue May 9 13:07:58 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=expdp_table_11204.log" Location: Command Line, Replaced with: "logfile=expdp_table_11204.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.
Starting "SXC"."SYS_EXPORT_TABLE_01":  sxc/******** directory=dir_exp dumpfile=expdp_table_11204_19c_2.dmp tables=table_11204 logfile=expdp_table_11204.log version=11.2.0.4 reuse_dumpfiles=true 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SXC"."TABLE_11204"                         7.657 MB   78981 rows
Master table "SXC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SXC.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dmp/expdp_table_11204_19c_2.dmp
Job "SXC"."SYS_EXPORT_TABLE_01" successfully completed at Tue May 9 13:08:14 2023 elapsed 0 00:00:13

--2) 拷贝dmp文件至11g环境
[oracle@ora19c dmp]$ scp expdp_table_11204_19c_2.dmp oracle@172.17.0.2:/home/oracle/dmp
oracle@172.17.0.2's password: 
expdp_table_11204_19c_2.dmp                                                        100% 7996KB 101.9MB/s   00:00    
--3) 11g环境再次导入dmp，成功
[oracle@ora11204 dmp]$ impdp sxc/sxc directory=dir_exp dumpfile=expdp_table_11204_19c_2.dmp tables=table_11204 log=impdp_table_11204.log table_exists_action=replace
Import: Release 11.2.0.4.0 - Production on Tue May 9 05:09:46 2023
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=impdp_table_11204.log" Location: Command Line, Replaced with: "logfile=impdp_table_11204.log"
Master table "SXC"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded
Starting "SXC"."SYS_IMPORT_TABLE_04":  sxc/******** directory=dir_exp dumpfile=expdp_table_11204_19c_2.dmp tables=table_11204 logfile=impdp_table_11204.log table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SXC"."TABLE_11204"                         7.657 MB   78981 rows
Job "SXC"."SYS_IMPORT_TABLE_04" successfully completed at Tue May 9 05:09:50 2023 elapsed 0 00:00:03

三、判断dmp文件是exp 还是expdp

 

方法1: 使用dbms_datapump.get_dumpfile_info()存储过程

10g 之后，我们可以通过dbms_datapump.get_dumpfile_info()存储过程来判断dmp文件是通过exp导出还是expdp导出。

dbms_datapump.get_dumpfile_info()存储过程可以从dump文件中获取信息Filetype返回以下文件类型：

  • 0：Unknown
  • 1：Data Pump dump file
  • 2：Original Export dump file

这里，我们知道table_19c.dmp 是我们通过exp方式导出的，expdp_table_11204.dmp 是通过expdp方式导出的。那么如何通过上面的存储过程判断？
 
1）首先创建一个directory目录用于保存dmp文件

SQL> create  directory dir_exp as '/home/oracle/dmp'; 

Directory created.

2）然后在sqlplus当中执行如下代码

SQL> set serveroutput on
SQL>  declare
  2    v_filetype   NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
  3    v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
  4  begin
  5    dbms_datapump.get_dumpfile_info(filename   => 'table_19c.dmp',
  6                                    directory  => upper('dir_exp'),
  7                                    info_table => v_info_table,
  8                                    filetype   => v_filetype);
  9    dbms_output.put_line('Filetype : ' || v_filetype);
 10  end;
 11  /
Filetype : 2

PL/SQL procedure successfully completed.


SQL> declare
  2    v_filetype   NUMBER; -- 0=unknown 1=expdp 2=exp 3=ext
  3    v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
  4  begin
  5    dbms_datapump.get_dumpfile_info(filename   => 'expdp_table_11204.dmp',
  6                                    directory  => upper('dir_exp'),
  7                                    info_table => v_info_table,
  8                                    filetype   => v_filetype);
  9    dbms_output.put_line('Filetype : ' || v_filetype);
 10  end;
 11  /
Filetype : 1

PL/SQL procedure successfully completed.

结论：可以看到，table_19c.dmp 返回 Filetype : 2 ；expdp_table_11204.dmp 返回 Filetype : 1。

注意：保证dmp文件的权限是正确，否则会抛出如下错误：

ERROR at line 1:
ORA-39211: unable to retrieve dumpfile information as specified
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5906
ORA-31640: unable to open dump file "/home/oracle/dmp/expdp_table_11204_19c_2.dmp" for read
ORA-06512: at "SYS.KUPF$FILE", line 5956
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-06512: at "SYS.KUPF$FILE_INT", line 287
ORA-06512: at "SYS.KUPF$FILE", line 5642
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5898
ORA-06512: at line 5

方法2：使用strings查看dmp文件信息

如果觉得上面的方法，比较麻烦，可以直接通过strings命令，查看dmp文件头信息来判断，但是上面的方法更加官方一点。

--1) 查看exp的dmp文件
[oracle@ora19c dmp]$ strings table_19c.dmp|more
TEXPORT:V19.00.00
DSXC
RTABLES
8192
                                         Tue May 9 10:23:33 2023table_19c.dmp
#G#G
#G#G
+00:00

--2) 查看expdp的dmp文件
[oracle@ora19c dmp]$ strings expdp_table_11204.dmp |more
"SXC"."SYS_EXPORT_TABLE_01"
x86_64/Linux 2.4.xx
LHR11G
AL32UTF8
11.02.00.04.00
001:001:000001:000001

结论：expdp的dmp文件，可以看到"SYS_EXPORT_TABLE_01" 关键信息，我们知道SYS_EXPORT_TABLE_01表是expdp导出过程中自动生成的，所以expdp_table_11204.dmp 可以判断是expdp导出的dmp文件。

墨力计划impdp expdp 数据泵
最后修改时间：2023-05-09 14:14:13
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论