使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g，除了使用RMAN方式之外，我们可以使用带dblink的datapump方式来实现基于逻辑上的迁移。其步骤也相对简单，而且不会产生中间过程生成的dump文件。本文即针对如何使用该方法给出了示例，供大家参考。

 

1、确保源数据库和目标数据库处于可用状态--环境描述--源库:   mftst  Oracle 10.2.0.3 + Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)   32bit--目标库: usbo   Oracle 11.2.0.1 + Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l). 64bitrobin@SZDB:~> tnsping usbo[oracle@linux1 ~]$ tnsping gomftstsys@MFTST> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production2、源数据库端的相关参数sys@MFTST> select instance_name,host_name from v$instance;INSTANCE_NAME    HOST_NAME---------------- ----------------------------------------------------------------MFTST            SZDB--确保参数parallel_max的值为0，以避免源库端导出性能慢(Bug 5453502/Note: 392689.1)sys@MFTST> show parameter parallel_maxNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------parallel_max_servers                 integer     03、目标数据库段端相关参数sys@USBO> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Productionsys@USBO> select instance_name,host_name from v$instance;INSTANCE_NAME    HOST_NAME---------------- ----------------------------------------------------------------usbo             linux1.orasrv.com--确保参数cursor_sharing的值为EXACT，以避免性能问题(Bug 4235941/Note: 416238.1)sys@USBO> show parameter cursor_shaNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cursor_sharing                       string      EXACT--目标数据库端的导入导出路径sys@USBO> select * from dba_directories where directory_name like '%PUMP%';OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH------------------------------ ------------------------------ ------------------------------------------------------------SYS                            DATA_PUMP_DIR                  /u01/app/oracle/admin/usbo/dpdump/4、创建从目标数据库到源数据库的dblinksys@USBO> create database link gomftst connect to goex_admin identified by goex_admin using 'GOMFTST';Database link created.sys@USBO> select * from dual@gomftst;DUM---X-- Author   : Robinson Cheng-- Blog     : http://blog.csdn.net/robinson_06125、创建相应的表空间--由于源数据库的表空间不存在于目标数据库，导入时创建对象会报错，因此我们需要在目标数据库创建表空间--对于Oracle 11g，我们可以使用remap_datafile以及remap_tablespace参数来指定，不过直接创建相应的表空间貌似更加简便sys@USBO> SELECT 'create tablespace ' || tablespace_name || ' datafile ''' || REPLACE (file_name, 'MFTST', 'usbo') || ''' size '  2     || TO_CHAR (CEIL (size_mb)) || ' M autoextend on;' as cmd  3    FROM (SELECT tablespace_name, file_name, (bytes / 1024 / 1024) size_mb  4            FROM dba_data_files@gomftst  5           WHERE tablespace_name IN (SELECT DISTINCT tablespace_name  6                                       FROM dba_segments@gomftst  7                                      WHERE owner IN ('GOEX_ADMIN', 'FIX_ADMIN')));CMD-----------------------------------------------------------------------------------------------------------------------------create tablespace GOEX_ACCOUNT_TBL datafile '/u02/database/usbo/oradata/usbo_account_tbl.dbf' size 329 M autoextend on;create tablespace GOEX_STOCK_TBL datafile '/u02/database/usbo/oradata/usbo_stock_tbl.dbf' size 161 M autoextend on;create tablespace GOEX_STOCK_L_TBL datafile '/u02/database/usbo/oradata/usbo_stock_l_tbl.dbf' size 2 M autoextend on;create tablespace GOEX_TX_TBL datafile '/u02/database/usbo/oradata/usbo_tx_tbl.dbf' size 825 M autoextend on;create tablespace GOEX_USERS_TBL datafile '/u02/database/usbo/oradata/usbo_users_tbl.dbf' size 315 M autoextend on;create tablespace GOEX_ACCOUNT_IDX datafile '/u02/database/usbo/oradata/usbo_account_idx.dbf' size 51 M autoextend on;create tablespace GOEX_STOCK_IDX datafile '/u02/database/usbo/oradata/usbo_stock_idx.dbf' size 40 M autoextend on;create tablespace GOEX_STOCK_L_IDX datafile '/u02/database/usbo/oradata/usbo_stock_l_idx.dbf' size 3 M autoextend on;create tablespace GOEX_TX_IDX datafile '/u02/database/usbo/oradata/usbo_tx_idx.dbf' size 68 M autoextend on;create tablespace GOEX_USERS_IDX datafile '/u02/database/usbo/oradata/usbo_users_idx.dbf' size 60 M autoextend on;create tablespace GOEX_IMPORT_TBL datafile '/u02/database/usbo/oradata/usbo_import_tbl.dbf' size 608 M autoextend on;create tablespace GOEX_IMPORT_IDX datafile '/u02/database/usbo/oradata/usbo_import_idx.dbf' size 11 M autoextend on;create tablespace IES_IDX datafile '/u02/database/usbo/oradata/usbo_IES_IDX.DBF' size 20 M autoextend on;create tablespace IES_TBL datafile '/u02/database/usbo/oradata/usbo_IES_TBL.DBF' size 105 M autoextend on;create tablespace FIX_TX_IDX datafile '/u02/database/usbo/oradata/usbo_fix_tx_idx.dbf' size 3 M autoextend on;create tablespace FIX_TX_TBL datafile '/u02/database/usbo/oradata/usbo_fix_tx_tbl.dbf' size 24 M autoextend on;create tablespace FIX_USERS_IDX datafile '/u02/database/usbo/oradata/usbo_fix_users_idx.dbf' size 1 M autoextend on;create tablespace FIX_USERS_TBL datafile '/u02/database/usbo/oradata/usbo_fix_users_tbl.dbf' size 3 M autoextend on;create tablespace GOEX_IPO_IDX datafile '/u02/database/usbo/oradata/usbo_ipo_idx.dbf' size 2 M autoextend on;create tablespace GOEX_IPO_TBL datafile '/u02/database/usbo/oradata/usbo_ipo_tbl.dbf' size 3 M autoextend on;--把上面的语句直接复制到SQL提示符下执行即可，如果有需要也可以将其封装到脚本来执行--创建相应的临时表空间，这是由于被导入的schema可能缺省的临时表空间并非temp的问题sys@USBO> SELECT    'create temporary tablespace '  2         || tablespace_name  3         || ' tempfile '''  4         || REPLACE (file_name, 'MFTST', 'usbo')  5         || ''' size '  6         || TO_CHAR (CEIL (bytes / 1024 / 1024))  7         || ' M autoextend on;' as cmd  8    FROM dba_temp_files@gomftst  9   WHERE tablespace_name IN (SELECT temporary_tablespace 10                               FROM dba_users@gomftst 11                              WHERE username IN ('GOEX_ADMIN', 'FIX_ADMIN') AND temporary_tablespace <> 'TEMP');CMD--------------------------------------------------------------------------------------------------------------------------create temporary tablespace GOEX_TEMP tempfile '/u02/database/usbo/oradata/usbo_tempusbo.dbf' size 149 M autoextend on;--下面是创建相应的临时表空间sys@USBO> create temporary tablespace GOEX_TEMP tempfile '/u02/database/usbo/oradata/usbo_tempusbo.dbf' size 149 M autoextend on;6、实施数据迁移$ export ORACLE_SID=usbo$ impdp \'\/ as sysdba \' directory=DATA_PUMP_DIR network_link=gomftst logfile=imp_mftst.log version=10.2.0.3 \parallel=2 schemas='goex_admin,fix_admin'[oracle@linux1 ~]$ impdp \'\/ as sysdba \' directory=DATA_PUMP_DIR network_link=gomftst logfile=imp_mftst.log version=10.2.0.3 \> parallel=2 schemas='goex_admin,fix_admin'Import: Release 11.2.0.1.0 - Production on Tue Sep 3 15:12:19 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing optionsStarting "SYS"."SYS_IMPORT_SCHEMA_13":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR network_link=gomftst logfile=imp_mftst.log version=10.2.0.3 parallel=2 schemas=goex_admin,fix_admin Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 1.557 GBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SYNONYM/SYNONYMProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/TABLE. . imported "GOEX_ADMIN"."BO2_OUT_TRADE_CHRG_DTL_TBL"     4359 rows. . imported "GOEX_ADMIN"."GO_GA_ACC_HIST_TBL"              117 rows. . imported "GOEX_ADMIN"."GO_GA_TRANS_SUMMARY_TBL"          16 rows. . imported "GOEX_ADMIN"."BO2_OUT_TRADE_HIST_DTL_TBL"      752 rows. . imported "GOEX_ADMIN"."BO2_OUT_STKINFO_DTL_TBL"      733481 rows. . imported "GOEX_ADMIN"."GO_GA_STOCK_OVERSEA_TBL"      668255 rows. . imported "GOEX_ADMIN"."BO2_OUT_ACC_HIST_DTL_TBL"         19 rows............................7、需要注意的如果在导入的过程中有角色或其他非相关的用户报错，可以考虑从源库提取创建脚本在目标数据库先创建再导入

 

     

相关参考：
  数据泵 EXPDP 导出工具的使用  
  数据泵IMPDP 导入工具的使用
  SQL*Loader使用方法
  导入导出 Oracle 分区表数据
  expdp impdp中 exclude/include 的使用
  使用 Oracle Datapump API 实现数据导出
  Oracle datapump expdp/impdp 导入导出数据库时hang住
  使用datapump 导出导入同义词(export and import synonym using datapump)
  传统路径导出 VS 直接路径导出(oracle exp direct=y)

  oracle imp导入时出现skipping table