数据泵(expdp/impdp)跨版本导入，11g导出10g导入

最新推荐文章于 2024-04-25 10:54:02 发布

密斯特羅

最新推荐文章于 2024-04-25 10:54:02 发布

阅读量5k

点赞数

分类专栏： Oracle Oracle Base Oracle Backup&Recovery

本文链接：https://blog.csdn.net/mrluoe/article/details/8225493

版权

Oracle 同时被 3 个专栏收录

65 篇文章 1 订阅

订阅专栏

Oracle Base

29 篇文章 0 订阅

订阅专栏

Oracle Backup&Recovery

8 篇文章 0 订阅

订阅专栏

最近在做数据库升级的事情，开发那边提了个需求说，如果升级并运行几天后怎么把新的数据倒回到旧版本的数据库里面？

我做升级时是使用数据泵做的数据迁移，大家都知道软件的使用“一般”情况下都是向下兼容的，即例如使用10g的expdp工具导出的文件使用11g的impdp导入到11g的数据库里面“几乎”不会出现什么问题，但是如果反过来则可能需要做一些处理，其实也就是在expdp导出时需要使用version参数。于是我把我测试记录如下：

1、先来看看不使用version参数时的情况

从11.2.0.3的数据库中导出：

[oracle@osdbso dumpdir]$ expdp srcb/srcb directory=dumpdir dumpfile=srcb.without.version.dmp tables=stock_quotes,stock_info 

Export: Release 11.2.0.3.0 - Production on Mon Nov 26 13:47:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
Starting "SRCB"."SYS_EXPORT_TABLE_01":  srcb/******** directory=dumpdir dumpfile=srcb.without.version.dmp tables=stock_quotes,stock_info 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 536.3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SRCB"."STOCK_QUOTES"                       461.8 MB 5252837 rows
. . exported "SRCB"."STOCK_INFO"                         284.1 KB    2456 rows
Master table "SRCB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SRCB.SYS_EXPORT_TABLE_01 is:
  /share/dumpdir/srcb.without.version.dmp
Job "SRCB"."SYS_EXPORT_TABLE_01" successfully completed at 13:48:04

在10.2.0.4的数据库中导入，这是会提示版本错误：

[oracle@srccat dumpdp]$ impdp srcb/srcb directory=dumpdir dumpfile=srcb.without.version.dmp logfile=srcb.impdp.without.version.log remap_tablespace=srcb_data:srcb,srcb_indx:srcb

Import: Release 10.2.0.4.0 - Production on Monday, 26 November, 2012 13:41:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 3.1 in dump file "/backup/dumpdp/srcb.without.version.dmp"

2、再来看看使用了version参数的情况

同样的从11.2.0.3的数据库中导出，但是这时带了version选项，注意：version的值必须要小于或等于目标库的基本版本（即所谓的11g、10g、9i等），即version值要小于11.1.0.1，否则同样会报错：

[oracle@osdbso dumpdir]$ expdp srcb/srcb directory=dumpdir dumpfile=srcb.with.version.dmp tables=stock_quotes,stock_info version=10.2.0.4

Export: Release 11.2.0.3.0 - Production on Mon Nov 26 13:48:27 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
Starting "SRCB"."SYS_EXPORT_TABLE_01":  srcb/******** directory=dumpdir dumpfile=srcb.with.version.dmp tables=stock_quotes,stock_info version=10.2.0.4 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 536.3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SRCB"."STOCK_QUOTES"                       461.8 MB 5252837 rows
. . exported "SRCB"."STOCK_INFO"                         283.7 KB    2456 rows
Master table "SRCB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SRCB.SYS_EXPORT_TABLE_01 is:
  /share/dumpdir/srcb.with.version.dmp
Job "SRCB"."SYS_EXPORT_TABLE_01" successfully completed at 13:48:38

同样的还是在10.2.0.4的数据库中导入，此时就可以正常导入了：

[oracle@srccat dumpdp]$ impdp srcb/srcb directory=dumpdir dumpfile=srcb.with.version.dmp logfile=srcb.impdp.with.version.log remap_tablespace=srcb_data:srcb,srcb_indx:srcb

Import: Release 10.2.0.4.0 - Production on Monday, 26 November, 2012 13:44:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SRCB"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SRCB"."SYS_IMPORT_FULL_01":  srcb/******** directory=dumpdir dumpfile=srcb.with.version.dmp logfile=srcb.impdp.with.version.log remap_tablespace=srcb_data:srcb,srcb_indx:srcb 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SRCB"."STOCK_QUOTES"                       461.8 MB 5252837 rows
. . imported "SRCB"."STOCK_INFO"                         283.7 KB    2456 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SRCB"."SYS_IMPORT_FULL_01" successfully completed at 13:47:41

3、其实还有更简单的数据库迁移方式，如果网络可以互通的话，则直接使用network_link=database_link的方式迁移，这同时还减少了数据的落地时间，而且还可以跨版本：

先给10g中的用户授予创建database link的权限，切换至同步用户并创建一个连接源数据库的database link：

[oracle@srccat ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 26 13:21:28 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

13:21:28 srcbtest@SYS> grant create database link to srcb;

Grant succeeded.

13:21:42 srcbtest@SYS> conn srcb
Enter password: 
Connected.
13:21:46 srcbtest@SRCB> create database link to11gdbl connect to srcb identified by srcb using '192.168.220.114:1521/srcbdb';

Database link created.

13:22:16 srcbtest@SRCB> select * from tab@to11gdbl;

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
STOCK_INFO                                                   TABLE
STOCK_INFO2                                                  TABLE
STOCK_QUERY                                                  TABLE
STOCK_QUOTES                                                 TABLE
STOCK_QUOTES2                                                TABLE
TMP1                                                         TABLE
TOAD_PLAN_TABLE                                              TABLE

7 rows selected.

在使用上一步创建的database link进行数据迁移：

[oracle@srccat dumpdp]$ impdp srcb/srcb directory=dumpdir logfile=srcb.impdp.from11g.log network_link=to11gdbl remap_tablespace=srcb_data:srcb,srcb_indx:srcb
 
Import: Release 10.2.0.4.0 - Production on Monday, 26 November, 2012 13:26:00

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SRCB"."SYS_IMPORT_SCHEMA_01":  srcb/******** directory=dumpdir logfile=srcb.impdp.from11g.log network_link=to11gdbl remap_tablespace=srcb_data:srcb,srcb_indx:srcb 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.048 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "SRCB"."TOAD_PLAN_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
. . imported "SRCB"."STOCK_QUOTES"                      5252837 rows
. . imported "SRCB"."STOCK_QUOTES2"                     5245909 rows
. . imported "SRCB"."STOCK_INFO"                           2456 rows
. . imported "SRCB"."STOCK_INFO2"                          2456 rows
. . imported "SRCB"."STOCK_QUERY"                           146 rows
. . imported "SRCB"."TMP1"                                   12 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SRCB"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 13:35:38

至此完！

密斯特羅

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
数据泵(expdp/impdp)跨版本导入，11g导出10g导入

最近在做数据库升级的事情，开发那边提了个需求说，如果升级并运行几天后怎么把新的数据倒回到旧版本的数据库里面？我做升级时是使用数据泵做的数据迁移，大家都知道软件的使用“一般”情况下都是向下兼容的，即例如使用10g的expdp工具导出的文件使用11g的impdp导入到11g的数据库里面“几乎”不会出现什么问题，但是如果反过来则可能需要做一些处理，其实也就是在expdp导出时需要使用version参
复制链接

扫一扫