10.2.0.1到12.2.0.1数据库迁移expdp+dblink导入失败

当前环境是从10.2.0.1迁移到12.2.0.1pdb中，版本跨度较大，且有较大的lob对象，源库总大小200g+。如果lob对象较小的话，200g的expdp+dblink导入应该是比较快的，估计在2小时以内。但是lob较大的话，在进行expdp+dblink的方式迁移时会非常慢，可能一晚上也完成不了。

对应有较大lob对象的库进行迁移时，可以采用分开导出lob和其他对象。

但是我在进行测试时遇到了ORA-39006，ORA-39113，PLS-00352错误，于是又进行评估重新选择迁移方式。下面是我的测试和分析步骤

--lob段

set pagesize 2000
select a.owner,a.table_name,sum(b.bytes)/1024/1024/1024 
from dba_lobs a,dba_segments b where a.SEGMENT_NAME=b.SEGMENT_NAME and a.owner=b.owner
and b.owner in ('KMOA','LCOA','CXOA','BSOA','YXOA','LJOA','NJOA','DQOA','ZTOA','WSOA','SMOA','QJOA','HLWOA','HHOA','DLOA','DHOA','BNOA')
group by a.owner,a.table_name order by 3 desc,2,1 

--long字段

set lines 200  
   SELECT owner, SUM (bytes) / 1024 / 1024 / 1024,segment_name
       FROM dba_segments
      WHERE  segment_name  IN 
      (select   table_name from dba_tab_columns where  data_type='LONG')  and owner  in
 ('KMOA','LCOA','CXOAv','BSOA','YXOA','LJOA','NJOA','DQOA','ZTOA','WSOA','SMOA','QJOA','HLWOA','HHOA','DLOA','DHOA','BNOA') 
   GROUP BY owner,segment_name
   ORDER BY 2;

clob和long都有，而且都不小

--表空间扩容，目标库表空间不够

alter tablespace YNLJOA  add datafile size 30g autoextend off;    
alter tablespace YNLCOA  add datafile size 30g autoextend off;    
alter tablespace YNKMOA  add datafile size 30g autoextend off;    
alter tablespace YNBNOA  add datafile size 30g autoextend off;    
alter tablespace YNDHOA  add datafile size 30g autoextend off;    
alter tablespace YNHLWOA add datafile size 30g autoextend off;    
alter tablespace YNDQOA  add datafile size 30g autoextend off;    
alter tablespace YNNJOA  add datafile size 30g autoextend off;    
alter tablespace YNBSOA  add datafile size 30g autoextend off;    
alter tablespace YNSMOA  add datafile size 30g autoextend off;    
alter tablespace YNYXOA  add datafile size 30g autoextend off;    
alter tablespace YNWSOA  add datafile size 30g autoextend off;    
alter tablespace YNQJOA  add datafile size 30g autoextend off;    
alter tablespace YNCXOA  add datafile size 30g autoextend off;    
alter tablespace YNDLOA  add datafile size 30g autoextend off;    
alter tablespace YNHHOA  add datafile size 30g autoextend off;    
alter tablespace YNZTOA  add datafile size 30g autoextend off;    

--创建dblink
create public database link oaold connect to system identified by password  using 'oaold';

--因为源库中有较大的lob段，所以找到那些lob的段所在的表，分开导入，以提升导入速度

--迁移xxoa，exclude有大clob的表，注意反斜杠转移括号和version参数

nohup impdp system/password0 cluster=N NETWORK_LINK=oaold schemas=\('KMOA','LCOA','CXOA','BSOA','YXOA','LJOA','NJOA','DQOA','ZTOA','WSOA','SMOA','QJOA','HLWOA','HHOA','DLOA','DHOA','BNOA'\) exclude=KMOA.DOCINFO,LCOA.DOCINFO,CXOA.DOCINFO,BSOA.DOCINFO,YXOA.DOCINFO,LJOA.DOCINFO,NJOA.DOCINFO,DQOA.DOCINFO,ZTOA.DOCINFO,WSOA.DOCINFO,SMOA.DOCINFO,QJOA.DOCINFO,HLWOA.DOCINFO,HHOA.DOCINFO,DLOA.DOCINFO,DHOA.DOCINFO,BNOA.DOCINFO,KMOA.DOCINFONEW TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1 &

（ps ：带\插入脚本会有乱码）

--迁移有大clob的表
nohup impdp system/password@oa parallel=10 cluster=N NETWORK_LINK=oaold2 TABLES=KMOA.DOCINFO,LCOA.DOCINFO,CXOA.DOCINFO,BSOA.DOCINFO,YXOA.DOCINFO,LJOA.DOCINFO,NJOA.DOCINFO,DQOA.DOCINFO,ZTOA.DOCINFO,WSOA.DOCINFO,SMOA.DOCINFO,QJOA.DOCINFO,HLWOA.DOCINFO,HHOA.DOCINFO,DLOA.DOCINFO,DHOA.DOCINFO,BNOA.DOCINFO,KMOA.DOCINFONEW TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1  &

--导入报错

Import: Release 12.1.0.2.0 - Production on Tue May 22 00:57:54 2018
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORA-39006: internal error
ORA-39113: Unable to determine database version
ORA-06550: line 1, column 7:
PLS-00352: Unable to access another database 'OAOLD'
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS@OAOLD' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
ORA-39097: Data Pump job encountered unexpected error -6550

mos上找到一篇文章

ORA-39113 When Running A DataPump Job Through NETWORK_LINK (文档ID 2100177.1)

报错信息跟我们这里很像，主要讲了2种情况

1.Private dblink 会遇到bug。（-In the unpublished BUG 22505732 - TRANSPORTABLE TABLESPACE NETWORK_LINK IMPORT FAILS WITH ORA-04054, Development has confirmed ORA-39113 is an expected error and closed the internal bug as not a bug with the comments: "private db links would not work in expdp/impdp".）

2.当GLOBAL_NAMES为true时，dblink需要设置为GLOBAL_NAMES相同的名字，或者把GLOBAL_NAMES设置为false。

我检查我的dblink为public，GLOBAL_NAMES为false，所以也不符合上面的情况

考虑到10.2.0.1到12.2.0.1版本跨度很大，且两边都是.1的基础版本，不排除遇到bug的可能

且

在mos

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (文档 ID 553337.1)中找到如下内容：

1. Tables with a LONG column are not supported in an Export Data Pump job with NETWORK_LINK parameter. An ORA-31679 error will be generated and the export will move on to the next table. See also section 8.11. "ORA-31679 (Table data object "aaa"."bbb" has long columns, and longs can not be loaded/unloaded using a network link)" below.

2. Tables with object_type columns are not supported in an Export Data Pump job with NETWORK_LINK parameter. An ORA-22804 error will be generated and the export will move on to the next table. See also section 8.12. "ORA-22804 (remote operations not permitted on object tables or user-defined type columns)" below.

3. When both the NETWORK_LINK and the TABLES parameters are used, then only whole tables can be exported (not partitions of tables). Trying to export a partition of a table over a database link will fail with ORA-39203 (Partition selection is not supported over a network link) or in older releases with ORA-14100 (partition extended table name cannot refer to a remote object). See also section 8.13. "ORA-39203 (Partition selection is not supported over a network link)" below.

expdp+dblink限制了long，object_type，所有这里没有办法进行此方式的迁移

亲测clob不能通过dblink+ctas进行传输。

此时考虑expdp+ftp的方式进行迁移，先做一个小表的测试

Ftp进行文件传输

--目录

SQL> create directory imp_dir as '/oracle/soft'
 
Directory created.
 
 
SQL>  grant read,write on  directory imp_dir to system;
 
Grant succeeded.

--导入，使用了remap_tablespace参数，因为flowtrace的owner是kmoa，但是由于源库规划的历史问题存储在users表空间中，故将其迁到kmoa的default tablespace中

 impdp system/password@oa parallel=2 tables=kmoa.FLOWTRACE dumpfile=FLOWTRACE_01.DMP,FLOWTRACE_02.DMP logfile=flowtrace.log directory=imp_dir remap_tablespace=users:ynkmoa version=10.2.0.1 TABLE_EXISTS_ACTION=REPLACE 

目前看来expdp+ftp的模式可行

expdp+ftp需要源库和目标库的目录至少要250g的空间用于存放dmp文件