数据泵迁移单个表空间7T大小-pfdb

原创 fanzhuozhuo 2023-12-14

1580

数据泵迁移单个表空间7T大小-pfdb

环境说明
基本检查
源端配置

待迁移表空间read only
待迁移表空间用户查询

目标端配置

创建必要的表空间
创建必要的dblink和directory
目标端创建用户并赋权
表空间导入--TABLESPACES=TBS_OLD_DATA
对象比对
处理无效对象
收集数据库和数据字典统计信息
回收dba 权限赋权普通权限
总结

即前两篇核心库历史表空间数据归档。第三套类似的环境，仍然需要历史数据归档。

环境说明

源端环境：
跟上一套环境基本类似，2节点19c rac。小版本19.13.0.0，归档模式。现在由于存储限制，已经开始存储阈值告警，没有多余空间承载这么大容量。所以经过讨论，把这套库里面的历史数据表空间，8.8T左右，迁移至别的数据库里面，源端删除表空间，达到释放空间的目的。也就是历史数据归档。

[root@qhpfdb1 ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.6 (Maipo)
sqhpfdb1:/home/oracle(qhpfdb1)$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 12 21:22:40 2023
Version 19.13.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
复制

此表空间8T左右，但是相比于上一套库，表空间里面的对象大大减少，根本没在一个数量级上。所以元数据比较小。

TBS_NAME             TYPE                       TOTAL(GB)       USAGE(GB)        FREE(GB) FREE PCT %  EXTENSIBLE(GB)    MAX_SIZE(GB) USED PCT OF MAX % NO_AXF_NUM AXF_NUM
-------------------- -------------------- --------------- --------------- --------------- ---------- --------------- --------------- ----------------- ---------- -------
TBS_OLD_DATA         PERMANENT                  7,320.000       4,913.603       2,406.397      67.13            .000       7,320.000             67.13        225      19

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCHIVEDG
Oldest online log sequence     141042
Next log sequence to archive   141051
Current log sequence           141051

SQL> select count(*) from dba_tables where tablespace_name='TBS_OLD_DATA';

  COUNT(*)
----------
      3821

SQL> select count(*) from dba_indexes where tablespace_name='TBS_OLD_DATA';

  COUNT(*)
----------
      2010

复制

跟前两套相同，里面单独创建一个pdb即可。

贷款仍然是瓶颈，平均才1.3MB/s。那么传输7T的数据文件，需要时间：

光数据文件初始化同步就需要66天。
与前面2套环境不同的是，此库没有多余的磁盘可用中转，也就是数据文件无法落地上下传进行传输，提高效率。所以，它的迁移方案又要重新选择。

目标端环境：
由于源端在湖南，与青海之间的环境带宽有限制，而且目标端也没有多余的磁盘进行落地，所以可选的方案只能是不落地，只有两种可选：一种的impdp+dblink的方式；第二种是xtts的DFT方式。两种都受限于网络带宽，所以重新选择了跟源端处于一个地域，湖南的数据库作为目标端，一举解决网络带宽问题。基于方案的复杂程度，本次采用简单的impdp+dblink的迁移方式，对于前一种的表空间数据的用户，采用remap_user和remap_tablespace即可。
下面就详细说明下整个迁移过程：

基本检查

其实主要就是表空间自包含检查

SQL> set pages 1000 lines 1000
SQL> exec dbms_tts.transport_set_check('TBS_OLD_DATA',true);

-- 查看结果，结果为空，表示为自包含
col violations for a300
select * from transport_set_violations;

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SQL> 
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index OLD_UCR_SFCBASE.PK_SF_B_PARAM_DEFINITION in tablespace TBS_SFCBASE_INDEX enforces primary constraints  of table OLD_UCR_SFCBASE.SF_B_PARAM_DEFINITIONBAK20220510 in tablespace TBS_OLD_DATA.
ORA-39908: Index OLD_UCR_SFCINS1.PK_H_VM_WF_A in tablespace TBS_SFCBASE_INDEX enforces primary constraints  of table OLD_UCR_SFCINS1.H_VM_WF_ATTRBAK20220510 in tablespace TBS_OLD_DATA.
ORA-39908: Index OLD_UCR_SFCINS1.PK_VM_WF_A in tablespace TBS_SFCBASE_INDEX enforces primary constraints  of table OLD_UCR_SFCINS1.VM_WF_ATTRBAK20220510 in tablespace TBS_OLD_DATA.
ORA-39908: Index OLD_UOP_SFCINS1.PK_TD_S_TRADETYPE in tablespace TBS_SFCINS1_INDEX enforces primary constraints  of table OLD_UOP_SFCINS1.TD_S_TRADETYPEBAK20220510 in tablespace TBS_OLD_DATA.
ORA-39908: Index OLD_UCR_NEA1.UQ_TASK_NAME in tablespace TBS_NEA1_INDEX enforces primary constraints  of table OLD_UCR_NEA1.TD_B_TASK_CONFIGBAK20220510 in tablespace TBS_OLD_DATA.
................................................................
ORA-39908: Index OLD_UCR_SFCBASE.PK_VM_EXCEPTION_DESC in tablespace TBS_SFCBASE_INDEX enforces primary constraints  of table OLD_UCR_SFCBASE.VM_EXCEPTION_DESCBAK20220510 in tablespace TBS_OLD_DATA.
ORA-39908: Index OLD_UCR_SFCBASE.PK_VM_QUEUE_SERVER_REGIST in tablespace TBS_SFCBASE_INDEX enforces primary constraints  of table OLD_UCR_SFCBASE.VM_QUEUE_SERVER_REGISTBAK20220510 in tablespace TBS_OLD_DATA.

103 rows selected.

复制

103条违反约束，报错都是：ORA-39908。
参考MOS:How to Fix Transport Set Check Violations (DBMS_TTS.TRANSPORT_SET_CHECK)(ORA-39908, ORA-39910, ORA-39932, ORA-39921, etc.)(Doc ID 1459800.1)
意思违反了约束：要挪到的表空间里面的表，和表上相关索引在两个不同表空间中。有可能迁移走了表，而没有把表上相关索引迁移走。
解决方案：把索引move到和表相同的表空间中。

alter index OLD_UCR_SFCINS1.PK_H_VM_WF_A                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_WF_A                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_SFCINS1.PK_TD_S_TRADETYPE                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.UQ_TASK_NAME                          rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_ID                                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TD_M_MOFFICE                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_C_SERV_BIND                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_C_SERV_MATCH                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_PARAM_MATCH                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_IBCOMPLEX_ESCAPE              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_STATIC_DATA                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_PAGE_MENU                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TD_C_DBINFO                        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TI_C_OLCOMWORK                     rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_M_IFSQLCODE                     rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_FLOW_MATCH                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_M_SFSQLCODE                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_USER                           rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_CFG_SVC_PARAM              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_M_SFCONFIG                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TD_M_SWITCH                     rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_NODE_TEMPLATE              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_M_TIMER                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_PAGE_STATIC_DATA           rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_C_SERV_PARAM_CVT             rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TI_B_IB_TIMER                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_EXCEPTION_RULE               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_CFG_INSTANCE               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_IBBUSI_SIGN                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_M_IFCONFIG                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_ROLE                           rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_C_SERV_PARAM                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_PAGE_DICTIONARY            rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_C_SERV                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_TEMPLATE_VERSION             rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_IBDEFINITION_STRUCTURE        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TL_B_IBPLAT_SYN_LOG                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_QUEUE_CONFIG                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TF_M_AREA                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_IBSIMPLE_ESCAPE               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_SVC_PARAM                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_FLOW_RELATION              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_TAB_ROUTECODE              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_CFG_SVC                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_ERRCODECONVERT                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TD_M_CODEAREA                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_M_SFRELOAD                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TD_M_AREA                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_CFG_INSTANCE                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_FLOW_TEMPLATE              rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_S_PARAMCONVERT                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_ROLE_MENU                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_OSS_CFG_SVC                        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_TD_S_COMMPARA                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_TEMPLATE                     rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_TASK                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_EXCEPTION_CODE               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TD_C_DIVIDE                        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_M_CONVERT                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_H_VM_T_T                        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.SYS_C0070086                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_TF_F_WORKFORM_INFORMATION       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_TF_F_WORKZONULE                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_DEAL_TASK                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_EX_R                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_TA_TS                        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_DYNC_TABLE_SPLIT            rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_ID_GENERATOR_WRAPPER        rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_METHOD_CENTER               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_STATIC_DATA                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_ID_GENERATOR                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_SCHE                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_M_CONVERT                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.SYS_C0070295                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_ALARM_CONFIG                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_CFG_METHOD_CENTER                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TL_B_PLATSYN_AFFIRMLOG             rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TF_B_IBPLAT_SYN_LOG                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TI_C_OLCOMWORK_SERV                rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TI_C_OLCOMWORK_VAR                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TASK_PARAM_VALUE            rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TF                          rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.SYS_C0070423                       rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TF_MAPPING                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TF_THREAD                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_WS_CLIENT_METHOD            rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_H_VM_TEMPLATE                   rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TABLE_SPLIT_MAPPING         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_H_VM_TA                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_TAB_ROUTE                  rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_TD_B_COMMFLAG                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_MOFFICE                    rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_EXCEPTION_CODE_DESC_RELA     rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_H_VM_WF                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_CFG_TABLE_SPLIT                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_SF_B_SERVICE_PF                 rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCINS1.PK_VM_WF                           rebuild tablespace TBS_OLD_DATA;
alter index OLD_UOP_NEA1.PK_TD_C_ROUTE                         rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_MENU                           rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_NEA1.PK_NEA_USER_ROLE                      rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_EXCEPTION_DESC               rebuild tablespace TBS_OLD_DATA;
alter index OLD_UCR_SFCBASE.PK_VM_QUEUE_SERVER_REGIST          rebuild tablespace TBS_OLD_DATA;
复制

经过处理，条件已满足：

SQL> exec dbms_tts.transport_set_check('TBS_OLD_DATA',true);

-- 查看结果，结果为空，表示为自包含
col violations for a300
select * from transport_set_violations;

PL/SQL procedure successfully completed.

SQL> SQL> SQL> SQL> 
no rows selected
复制

虽然本次不采用tts的方式迁移，但是为了把表和表上的相关对象都迁移过去，决定还是按照tts的检查方法，是表空间自包含。

源端配置

待迁移表空间read only

为了避免待迁移表空间有数据变化，手动read only，停止上面的业务。

SQL> alter tablespace TBS_OLD_DATA read only;

Tablespace altered.
复制

待迁移表空间用户查询

SQL> set pages 1000 lines 10000
SQL> col owner for a30
SQL> select distinct owner from dba_tables where tablespace_name='TBS_OLD_DATA'

OWNER
------------------------------
OLD_UCR_OPPF
OLD_UCR_CEN1
UCR_CRMCC
OLD_UCR_SFCINS1
OLD_UOP_OPPF
LC_MADX
YD_CHENXY
LC_JIANGX
UMON
OLD_UOP_SFCBASE
OLD_UCR_SFCBASE
OLD_UOP_OLCOM
OLD_UCR_UIF1
YD_SUNWB
LC_CUIFN
LC_MAYH
YD_XIEZQ
LC_LISG
YD_TENGWEN
YD_WANGL
LC_HOUYQ
LC_LIMS
YD_CHENTQ
LC_LIUC
OLD_UOP_NEA1
OLD_UCR_NEA1
OLD_UCR_PCEN
OLD_UCR_OPPF_SEC
OLD_UOP_TERM
OLD_UCR_LSMS
OLD_UCR_SOA
YD_JIANGHL
LC_YANGJY
YD_CHENL
YD_QIM
LC_CHENYJ
LC_WANGXY
UQRY_LC_ZHANGJIN
OLD_UCR_RES1
LC_DUANJL
YD_HUAJ
YD_XIANLQ
LC_WANGFB
UCR_CTR
OLD_UOP_RES1
OLD_UCR_PF
OLD_UCR_TERM
YD_LIYL
YD_LIS
YD_WANGSD
YD_BASX
OLD_UOP_PF
OLD_UOP_SFCINS1
OLD_UCR_OLCOM
OLD_UOP_UIF1
YD_HUANGQL
UCR_OPS
OLD_UOP_SOA
YD_MAHONGYAN
YD_WANGX
LC_HAODW
YD_ZHUCHM

62 rows selected.
复制

表空间信息：

SQL> select distinct DEFAULT_TABLESPACE from dba_users where username in(select distinct owner from dba_tables where tablespace_name='TBS_OLD_DATA');

DEFAULT_TABLESPACE
------------------------------
USERS
TBS_SFCINS1_DATA
TBS_OLD_DATA
复制

目标端配置

创建必要的表空间

创建必要的dblink和directory

SQL> create public database link to_pf connect to system identified by "Qhyd_2020" using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.230.61.19)(PORT = 1688)))(CONNECT_DATA =(SERVICE_NAME = qhpfdb)))';

Database link created.

SQL> select * from dual@to_pf;

D
-
X

SQL> create directory impdp as '/home/oracle/enmo';

Directory created.

SQL> grant read,write on directory impdp to public;

Grant succeeded.
复制

目标端创建用户并赋权

历史数据归档，为了方便，统一使用一个用户，不再单独从源库吧所有用户迁移过来。

SQL> create user old_pf_user20131113 identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;

User created.
复制

为了后面导入元数据报错权限，线都赋予dba权限，后面回收即可

SQL> grant dba to old_pf_user20131113;

Grant succeeded.
复制

表空间导入–TABLESPACES=TBS_OLD_DATA

1、错误1：

qhbossdb2:/dmp(qhbossdb2)$cat pfpar.par 
logfile=pfpar.log
metrics=yes
cluster=N
directory=impdp
include=system_grant,OBJECT_GRANT,ROLE_GRANT
parallel=10
EXCLUDE=STATISTICS 
network_link=to_pf
TABLESPACES=TBS_OLD_DATA
REMAP_TABLESPACE=USERS:TBS_OLD_DATA_PF,TBS_SFCINS1_DATA:TBS_OLD_DATA_PF,TBS_OLD_DATA:TBS_OLD_DATA_PF
REMAP_SCHEMA=OLD_UCR_OPPF:old_pf_user20131113,OLD_UCR_CEN1:old_pf_user20131113,UCR_CRMCC:old_pf_user20131113,OLD_UCR_SFCINS1:old_pf_user20131113,OLD_UOP_OPPF:old_pf_user20131113,LC_MADX:old_pf_user20131113,YD_CHENXY:old_pf_user20131113,LC_JIANGX:old_pf_user20131113,UMON:old_pf_user20131113,OLD_UOP_SFCBASE:old_pf_user20131113,OLD_UCR_SFCBASE:old_pf_user20131113,OLD_UOP_OLCOM:old_pf_user20131113,OLD_UCR_UIF1:old_pf_user20131113,YD_SUNWB:old_pf_user20131113,LC_CUIFN:old_pf_user20131113,LC_MAYH:old_pf_user20131113,YD_XIEZQ:old_pf_user20131113,LC_LISG:old_pf_user20131113,YD_TENGWEN:old_pf_user20131113,YD_WANGL:old_pf_user20131113,LC_HOUYQ:old_pf_user20131113,LC_LIMS:old_pf_user20131113,YD_CHENTQ:old_pf_user20131113,LC_LIUC:old_pf_user20131113,OLD_UOP_NEA1:old_pf_user20131113,OLD_UCR_NEA1:old_pf_user20131113,OLD_UCR_PCEN:old_pf_user20131113,OLD_UCR_OPPF_SEC:old_pf_user20131113,OLD_UOP_TERM:old_pf_user20131113,OLD_UCR_LSMS:old_pf_user20131113,OLD_UCR_SOA:old_pf_user20131113,YD_JIANGHL:old_pf_user20131113,LC_YANGJY:old_pf_user20131113,YD_CHENL:old_pf_user20131113,YD_QIM:old_pf_user20131113,LC_CHENYJ:old_pf_user20131113,LC_WANGXY:old_pf_user20131113,UQRY_LC_ZHANGJIN:old_pf_user20131113,OLD_UCR_RES1:old_pf_user20131113,LC_DUANJL:old_pf_user20131113,YD_HUAJ:old_pf_user20131113,YD_XIANLQ:old_pf_user20131113,LC_WANGFB:old_pf_user20131113,UCR_CTR:old_pf_user20131113,OLD_UOP_RES1:old_pf_user20131113,OLD_UCR_PF:old_pf_user20131113,OLD_UCR_TERM:old_pf_user20131113,YD_LIYL:old_pf_user20131113,YD_LIS:old_pf_user20131113,YD_WANGSD:old_pf_user20131113,YD_BASX:old_pf_user20131113,OLD_UOP_PF:old_pf_user20131113,OLD_UOP_SFCINS1:old_pf_user20131113,OLD_UCR_OLCOM:old_pf_user20131113,OLD_UOP_UIF1:old_pf_user20131113,YD_HUANGQL:old_pf_user20131113,UCR_OPS:old_pf_user20131113,OLD_UOP_SOA:old_pf_user20131113,YD_MAHONGYAN:old_pf_user20131113,YD_WANGX:old_pf_user20131113,LC_HAODW:old_pf_user20131113,YD_ZHUCHM:old_pf_user20131113

nohup impdp \'/ as sysdba\' parfile=pfpar.par &
复制

错误过程信息如下：

qhbossdb2:/dmp(qhbossdb2)$cat nohup.out 

Import: Release 19.0.0.0.0 - Production on Tue Nov 14 08:25:58 2023
Version 19.13.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
ORA-39001: invalid argument value
ORA-39038: Object path "ROLE_GRANT" is not supported for TABLESPACE jobs.
ORA-39038: Object path "SYSTEM_GRANT" is not supported for TABLESPACE jobs.
复制

tablespace 和include 不能同时连用。
2、错误2：
修改parfile，重新导入：

qhbossdb2:/dmp(qhbossdb2)$cat pfpar.par  
logfile=pfpar.log
metrics=yes
cluster=N
directory=impdp
parallel=10
EXCLUDE=STATISTICS 
network_link=to_pf
TABLESPACES=TBS_OLD_DATA
REMAP_TABLESPACE=USERS:TBS_OLD_DATA_PF,TBS_SFCINS1_DATA:TBS_OLD_DATA_PF,TBS_OLD_DATA:TBS_OLD_DATA_PF
REMAP_SCHEMA=OLD_UCR_OPPF:old_pf_user20131113,OLD_UCR_CEN1:old_pf_user20131113,UCR_CRMCC:old_pf_user20131113,OLD_UCR_SFCINS1:old_pf_user20131113,OLD_UOP_OPPF:old_pf_user20131113,LC_MADX:old_pf_user20131113,YD_CHENXY:old_pf_user20131113,LC_JIANGX:old_pf_user20131113,UMON:old_pf_user20131113,OLD_UOP_SFCBASE:old_pf_user20131113,OLD_UCR_SFCBASE:old_pf_user20131113,OLD_UOP_OLCOM:old_pf_user20131113,OLD_UCR_UIF1:old_pf_user20131113,YD_SUNWB:old_pf_user20131113,LC_CUIFN:old_pf_user20131113,LC_MAYH:old_pf_user20131113,YD_XIEZQ:old_pf_user20131113,LC_LISG:old_pf_user20131113,YD_TENGWEN:old_pf_user20131113,YD_WANGL:old_pf_user20131113,LC_HOUYQ:old_pf_user20131113,LC_LIMS:old_pf_user20131113,YD_CHENTQ:old_pf_user20131113,LC_LIUC:old_pf_user20131113,OLD_UOP_NEA1:old_pf_user20131113,OLD_UCR_NEA1:old_pf_user20131113,OLD_UCR_PCEN:old_pf_user20131113,OLD_UCR_OPPF_SEC:old_pf_user20131113,OLD_UOP_TERM:old_pf_user20131113,OLD_UCR_LSMS:old_pf_user20131113,OLD_UCR_SOA:old_pf_user20131113,YD_JIANGHL:old_pf_user20131113,LC_YANGJY:old_pf_user20131113,YD_CHENL:old_pf_user20131113,YD_QIM:old_pf_user20131113,LC_CHENYJ:old_pf_user20131113,LC_WANGXY:old_pf_user20131113,UQRY_LC_ZHANGJIN:old_pf_user20131113,OLD_UCR_RES1:old_pf_user20131113,LC_DUANJL:old_pf_user20131113,YD_HUAJ:old_pf_user20131113,YD_XIANLQ:old_pf_user20131113,LC_WANGFB:old_pf_user20131113,UCR_CTR:old_pf_user20131113,OLD_UOP_RES1:old_pf_user20131113,OLD_UCR_PF:old_pf_user20131113,OLD_UCR_TERM:old_pf_user20131113,YD_LIYL:old_pf_user20131113,YD_LIS:old_pf_user20131113,YD_WANGSD:old_pf_user20131113,YD_BASX:old_pf_user20131113,OLD_UOP_PF:old_pf_user20131113,OLD_UOP_SFCINS1:old_pf_user20131113,OLD_UCR_OLCOM:old_pf_user20131113,OLD_UOP_UIF1:old_pf_user20131113,YD_HUANGQL:old_pf_user20131113,UCR_OPS:old_pf_user20131113,OLD_UOP_SOA:old_pf_user20131113,YD_MAHONGYAN:old_pf_user20131113,YD_WANGX:old_pf_user20131113,LC_HAODW:old_pf_user20131113,YD_ZHUCHM:old_pf_user20131113

nohup impdp \'/ as sysdba\' parfile=pfpar.par &
复制

报错部分日志如下：

Import: Release 19.0.0.0.0 - Production on Tue Nov 14 18:47:34 2023
Version 19.13.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
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  "/******** AS SYSDBA" parfile=pfpar.par 
W-1 Startup took 0 seconds
W-1 Estimate in progress using BLOCKS method...
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
W-1      Estimated 32903 TABLE_DATA objects in 485 seconds
W-1 Total estimation using BLOCKS method: 4003. GB
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "OLD_PF_USER20131113"."TI_CH_OLCOMORDERBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLD_PF_USER20131113"."TI_CH_OLCOMWORKBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLD_PF_USER20131113"."TD_B_DISCNTBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLD_PF_USER20131113"."TF_BH_TRADEBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLD_PF_USER20131113"."TL_B_TRANS_LOGBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "OLD_PF_USER20131113"."TI_C_OLCOMCOMMONQUERYBAK20220510" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
........................................
复制

都是报错ORA-39151，然后瞬间进程就结束了。
刚开始以为是remap_user参数写错了，检查了一遍。没有毛病。然后在源库检查了几个报错的对象。

发现报错的表，都属于同名表，但是属于多个用户。但是我们在impdp的时候，统一都把这些对象的用户remap为了一个统一用户，OLD_PF_USER20131113，多个同名表remap为一个用户，不报错才怪。
所以必须把这些重名表的用户，remap到不同的用户下，不能统一到一个用户，要不然就会报错表已存在。
下面在源端查询这些报错的重名表，都是属于哪些用户，把这些用户remap到不同schema即可，而不必全部修改。
从报错日志中批量提取这些报错的表名，然后拼写SQL，查出重复表名对应的schema。

--由于SQL太长了，只能分成三段来写。
SQL> set pages 1000 lines 1000
SQL> select distinct owner from dba_objects where object_name in('TI_CH_OLCOMORDERBAK20220510','TI_CH_OLCOMWORKBAK20220510','TD_B_DISCNTBAK20220510','TF_BH_TRADEBAK20220510','TL_B_TRANS_LOGBAK20220510','TI_C_OLCOMCOMMONQUERYBAK20220510','TF_F_ACCOUNTBAK20220510','TF_F_CUSTOMERBAK20220510','TF_B_ORDERBAK20220510','TF_F_CUST_GROUPBAK20220510','TF_B_TRACE_TRADEBAK20220510','TF_B_TRADEBAK20220510','TF_F_CUST_GROUP_EXTENDBAK20220510','TF_B_TRADEFEE_DEVICEBAK20220510','TF_F_CUST_MANAGER_STAFFBAK20220510','TF_B_TRADE_ACCESS_ACCTBAK20220510','TF_B_TRADE_ACCOUNTBAK20220510','TF_F_CUST_PERSONBAK20220510','TF_B_TRADE_ADDRBAK20220510','TF_F_INSTANCE_PFBAK20220510','TF_B_TRADE_ATTRBAK20220510','TF_B_TRADE_BLACKWHITEBAK20220510','TF_B_TRADE_BRANDCHANGEBAK20220510','TF_F_RELATION_UUBAK20220510','TF_B_TRADE_CUSTOMERBAK20220510','TF_F_USERBAK20220510','TF_B_TRADE_CUST_PERSONBAK20220510','TF_F_USER_ACCESS_ACCTBAK20220510','TF_F_USER_ADDRBAK20220510','TF_B_TRADE_DATALINE_ATTRBAK20220510','TF_F_USER_ALTSNBAK20220510','TF_B_TRADE_DISCNTBAK20220510','TF_F_USER_ATTRBAK20220510','TF_B_TRADE_ERRBAK20220510','TF_B_TRADE_GRP_MERCHBAK20220510','TF_F_USER_DISCNTBAK20220510','TF_B_TRADE_GRP_MERCHPBAK20220510','TF_B_TRADE_GRP_MERCHP_DISCNTBAK20220510','TF_F_USER_IMPUBAK20220510','TF_B_TRADE_GRP_MERCH_DISCNTBAK20220510','TF_F_USER_INFOCHANGEBAK20220510','TF_B_TRADE_GRP_MERCH_MB_DISBAK20220510','TF_F_USER_NETNPBAK20220510','TF_B_TRADE_GRP_MERCH_MEBBAK20220510','TF_F_USER_OCSBAK20220510','TF_B_TRADE_GRP_MOLISTBAK20220510','TF_F_USER_OTHERBAK20220510','TF_B_TRADE_GRP_PLATSVCBAK20220510');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OLD_UCR_SFCINS1
OLD_UCR_NEA1
OLD_UOP_NEA1
OLD_UCR_PF
OLD_UCR_OLCOM

SQL> select distinct owner from dba_objects where object_name in('TF_F_USER_PRODUCTBAK20220510','TF_B_TRADE_IMPUBAK20220510','TF_F_USER_RATEBAK20220510','TF_B_TRADE_NETNPBAK20220510','TF_F_USER_RESBAK20220510','TF_B_TRADE_OCSBAK20220510','TF_F_USER_SVCBAK20220510','TF_B_TRADE_OTHERBAK20220510','TF_F_USER_SVCSTATEBAK20220510','TF_B_TRADE_PBOSSBAK20220510','TF_B_TRADE_PBOSS_ATTRBAK20220510','TF_F_USER_TELEPHONEBAK20220510','TF_F_USER_VPNBAK20220510','TF_B_TRADE_PLATSVCBAK20220510','TF_F_USER_VPN_MEBBAK20220510','TF_B_TRADE_PRODUCTBAK20220510','TF_F_USER_WIDENETBAK20220510','TF_B_TRADE_RATEBAK20220510','TF_F_USER_WIDENET_ACTBAK20220510','TF_B_TRADE_RELATIONBAK20220510','TF_F_VPMN_MEMBEROUTBAK20220510','TF_B_TRADE_RESBAK20220510','TF_B_TRADE_SVCBAK20220510','TF_B_TRADE_SVCSTATEBAK20220510','TF_B_TRADE_TELEPHONEBAK20220510','TF_B_TRADE_USERBAK20220510','TF_B_TRADE_VPNBAK20220510','TF_R_EMPTYCARD_IDLEBAK20220510','TF_B_TRADE_VPN_MEBBAK20220510','TF_R_EMPTYCARD_USEBAK20220510','TF_B_TRADE_WIDENETBAK20220510','TF_R_SIMCARD_IDLEBAK20220510','TF_B_TRADE_WIDENET_ACTBAK20220510','TF_R_SIMCARD_USEBAK20220510','TI_BH_IBOSS_SVCSTATEBAK20220510','TI_BH_MCAS_UDRBAK20220510','TI_B_IBOSS_SVCSTATEBAK20220510','TI_B_MCAS_DATASYNBAK20220510','TI_B_MCAS_UDRBAK20220510','TI_CH_OLCOMORDERBAK20220510','TI_C_OLCOMORDERBAK20220510','TL_B_IBPLAT_SYN_RSLT_SUBBAK20220510','TL_B_IBPLAT_SYN_SUBBAK20220510','TL_BH_IBPLAT_SYNBAK20220510','TL_B_IBTRADEBAK20220510','TL_BH_IBPLAT_SYN_RSLT_SUBBAK20220510','TL_BH_IBPLAT_SYN_SUBBAK20220510','TL_B_IBERRORBAK20220510','TL_B_IBPLAT_SYNBAK20220510','TL_B_NOTIFYTRADELOGBAK20220510','TL_B_NOTIFYTRADELOGBAK20220510','CFG_DB_RELATBAK20220510','TF_B_PAYLOG_CHK_TMP','TF_R_TEMPOCCUPYBAK20220510','TI_C_OLCOMORDERBAK20220510','TD_C_DBINFOBAK20220510','TF_B_RES_BATCH_TMPBAK20220510','TF_B_PAYLOG_CHK_TMP','CFG_DB_JDBC_PARAMETERBAK20220510','TI_B_IBOSS_SVCSTATEBAK20220510','TI_B_MCAS_DATASYNBAK20220510','TF_R_EQPTBAK20220510','CFG_TABLE_SPLITBAK20220510');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OLD_UCR_CEN1
OLD_UCR_SFCINS1
OLD_UCR_SFCBASE
OLD_UCR_UIF1
OLD_UOP_NEA1
OLD_UCR_NEA1
OLD_UCR_SOA
YD_CHENL
OLD_UCR_RES1
OLD_UCR_PF
OLD_UCR_TERM
YD_WANGSD
OLD_UCR_OLCOM
YD_WANGX

14 rows selected.

SQL> select distinct owner from dba_objects where object_name in('CFG_ID_GENERATORBAK20220510','CFG_ID_GENERATOR_WRAPPERBAK20220510','CFG_SERVICE_CONTROLBAK20220510','TD_B_IBSIMPLE_ESCAPEBAK20220510','TMP_IDXBAK20220510','TMP_INX_NAME1BAK20220510','TD_M_RES_PARABAK20220510','CFG_DB_ACCTBAK20220510','TF_B_RES_PARA_LOGBAK20220510','TI_B_MCAS_UDRBAK20220510','TF_R_ADDRESSBAK20220510','CFG_DYNC_TABLE_SPLITBAK20220510','CFG_DB_ACCT_BFBAK20220510','CFG_DYNC_TABLE_SPLITBAK20220510','TD_B_IBDEFINITION_STRUCTUREBAK20220510','TL_B_PLATSYN_AFFIRMLOGBAK20220510','TI_C_OLCOMWORK_SERVBAK20220510','CFG_TF_MAPPINGBAK20220510','CFG_WS_CLIENT_METHODBAK20220510','TD_B_ERRCODECONVERTBAK20220510','CFG_WSBAK20220510','TL_B_IBTRADE_PLUSBAK20220510','TD_M_RES_CORPBAK20220510','TD_B_IBBUSI_SIGNBAK20220510','TD_M_IFCONFIGBAK20220510','TD_B_COMMFLAGBAK20220510','TMP_KEY_NAME1BAK20220510','TD_S_RESSTATEBAK20220510','CFG_TABLE_SPLITBAK20220510','CFG_TASKBAK20220510','CFG_TFBAK20220510','TI_BH_MCAS_UDRBAK20220510','TF_B_RESINOUT_DETAILBAK20220510','TD_C_ROUTEBAK20220510','TF_F_WORKFORM_INFORMATIONBAK20220510','TD_M_MOFFICEBAK20220510','TI_B_NGPF_RIGHTBAK20220510','TI_B_NGPF_USERBAK20220510','TF_F_UNSATISFYSBAK20220510','CFG_DB_ACCTBAK20220510','CFG_DB_URLBAK20220510','CFG_METHOD_CENTERBAK20220510','TD_M_IFCONFIGBAK20220510','TL_B_IBPLAT_SYN_LOGBAK20220510','TD_S_RESTYPEBAK20220510','CFG_DB_RELATBAK20220510','CFG_TABLE_SPLIT_MAPPINGBAK20220510','TI_R_INTERFACE_RSPBAK20220510','TI_B_NGPF_ROLE_RIGHTBAK20220510','TI_C_OLCOMDIVIDEBAK20220510','TD_M_CODEAREABAK20220510','TD_S_DCLBAK20220510','TF_M_AREABAK20220510','CFG_DB_URLBAK20220510','TD_M_IFSQLCODEBAK20220510','TD_S_PARAMCONVERTBAK20220510','TMP_INX_NAME2BAK20220510','TMP_KEYBAK20220510','TMP_KEY_NAME2BAK20220510','TI_C_OLCOMWORKBAK20220510','CFG_WS_CLIENTBAK20220510','TD_S_RESKINDBAK20220510','TD_S_RES_BRANDBAK20220510','TD_S_RES_MODELBAK20220510','TF_B_RES_PARA_DETAILBAK20220510','TF_B_RES_PREPMG_LOGBAK20220510','TD_M_RESTRADEBAK20220510','CFG_ID_GENERATOR_WRAPPERBAK20220510','CFG_TASK_PARAM_VALUEBAK20220510','CFG_TF_THREADBAK20220510','TI_BH_IBOSS_SVCSTATEBAK20220510','TF_B_RESINOUT_LOGBAK20220510','TD_M_IFSQLCODEBAK20220510','TD_M_CONVERTBAK20220510','TD_M_SWITCHBAK20220510','TD_S_COMMPARABAK20220510','TF_F_WORKZONULEBAK20220510','TI_B_NGPF_ROLEBAK20220510','CFG_ID_GENERATORBAK20220510','CFG_TABLE_SPLIT_MAPPINGBAK20220510','TD_M_AREABAK20220510','CFG_DB_JDBC_PARAMETERBAK20220510','TD_B_IBCOMPLEX_ESCAPEBAK20220510','TI_C_OLCOMWORK_VARBAK20220510','TD_C_DIVIDEBAK20220510','TMP_TRADE_ERR','TD_B_IBBUSI_SIGNBAK20220510','CFG_METHOD_CENTERBAK20220510','CFG_TF_DTLBAK20220510','CFG_WS_MAPPINGBAK20220510','TD_C_DIVIDEBAK20220510','TMP_TRADE_ERR','TD_B_IBBUSI_SIGNBAK20220510','CFG_METHOD_CENTERBAK20220510','CFG_TF_DTLBAK20220510','CFG_WS_MAPPINGBAK20220510');

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OLD_UCR_SFCINS1
OLD_UCR_CEN1
OLD_UCR_UIF1
OLD_UCR_SFCBASE
LC_HOUYQ
OLD_UCR_NEA1
OLD_UCR_PCEN
OLD_UCR_SOA
OLD_UOP_NEA1
LC_WANGXY
OLD_UCR_RES1
OLD_UCR_TERM
OLD_UCR_PF
OLD_UCR_OLCOM

14 rows selected.
复制

去重后，共17个用户，我们必须在目标端单独创建新的17个用户，remap从源端对应过来的这17个包含有同名的表schema。为啥不创建和源端同名的schema？因为目标端和源端一样，都是生产核心库，上面有些是个人用户，用户名都是相同的，导入目标端可能会导入到同名schema下，所以为了降低对目标端库的影响，必须重新创建新的schema，用以区分。

删除当前remap schema，重新创建18个用户（另外一个remap剩余的shema），为了和源端区分，新建用户统一添加前缀"PF_"：

SQL> drop user OLD_PF_USER20131113 cascade;

User dropped.

create user PF_OLD_OTHER       identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_SFCINS1 identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_CEN1    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_SFCBASE identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_UIF1    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_LC_HOUYQ        identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_NEA1    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UOP_NEA1    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_SOA     identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_YD_CHENL        identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_PCEN    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_LC_WANGXY       identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_RES1    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_PF      identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_TERM    identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_YD_WANGSD       identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_OLD_UCR_OLCOM   identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;
create user PF_YD_WANGX        identified by "Qhyd_2020" default tablespace TBS_OLD_DATA_PF;

grant dba to PF_OLD_OTHER      ;
grant dba to PF_OLD_UCR_SFCINS1;
grant dba to PF_OLD_UCR_CEN1   ;
grant dba to PF_OLD_UCR_SFCBASE;
grant dba to PF_OLD_UCR_UIF1   ;
grant dba to PF_LC_HOUYQ       ;
grant dba to PF_OLD_UCR_NEA1   ;
grant dba to PF_OLD_UOP_NEA1   ;
grant dba to PF_OLD_UCR_SOA    ;
grant dba to PF_YD_CHENL       ;
grant dba to PF_OLD_UCR_PCEN   ;
grant dba to PF_LC_WANGXY      ;
grant dba to PF_OLD_UCR_RES1   ;
grant dba to PF_OLD_UCR_PF     ;
grant dba to PF_OLD_UCR_TERM   ;
grant dba to PF_YD_WANGSD      ;
grant dba to PF_OLD_UCR_OLCOM  ;
grant dba to PF_YD_WANGX       ;
复制

修改parfile，重新导入

qhbossdb2:/dmp(qhbossdb2)$cat pfpar.par 
logfile=pfpar.log
metrics=yes
cluster=N
directory=impdp
parallel=10
EXCLUDE=STATISTICS 
network_link=to_pf
TABLESPACES=TBS_OLD_DATA
REMAP_TABLESPACE=USERS:TBS_OLD_DATA_PF,TBS_SFCINS1_DATA:TBS_OLD_DATA_PF,TBS_OLD_DATA:TBS_OLD_DATA_PF
REMAP_SCHEMA=OLD_UCR_OPPF:PF_OLD_OTHER,OLD_UCR_CEN1:PF_OLD_UCR_CEN1,UCR_CRMCC:PF_OLD_OTHER,OLD_UCR_SFCINS1:PF_OLD_UCR_SFCINS1,OLD_UOP_OPPF:PF_OLD_OTHER,LC_MADX:PF_OLD_OTHER,YD_CHENXY:PF_OLD_OTHER,LC_JIANGX:PF_OLD_OTHER,UMON:PF_OLD_OTHER,OLD_UOP_SFCBASE:PF_OLD_OTHER,OLD_UCR_SFCBASE:PF_OLD_UCR_SFCBASE,OLD_UOP_OLCOM:PF_OLD_OTHER,OLD_UCR_UIF1:PF_OLD_UCR_UIF1,YD_SUNWB:PF_OLD_OTHER,LC_CUIFN:PF_OLD_OTHER,LC_MAYH:PF_OLD_OTHER,YD_XIEZQ:PF_OLD_OTHER,LC_LISG:PF_OLD_OTHER,YD_TENGWEN:PF_OLD_OTHER,YD_WANGL:PF_OLD_OTHER,LC_HOUYQ:PF_LC_HOUYQ,LC_LIMS:PF_OLD_OTHER,YD_CHENTQ:PF_OLD_OTHER,LC_LIUC:PF_OLD_OTHER,OLD_UOP_NEA1:PF_OLD_UOP_NEA1,OLD_UCR_NEA1:PF_OLD_UCR_NEA1,OLD_UCR_PCEN:PF_OLD_UCR_PCEN,OLD_UCR_OPPF_SEC:PF_OLD_OTHER,OLD_UOP_TERM:PF_OLD_OTHER,OLD_UCR_LSMS:PF_OLD_OTHER,OLD_UCR_SOA:PF_OLD_UCR_SOA,YD_JIANGHL:PF_OLD_OTHER,LC_YANGJY:PF_OLD_OTHER,YD_CHENL:PF_YD_CHENL,YD_QIM:PF_OLD_OTHER,LC_CHENYJ:PF_OLD_OTHER,LC_WANGXY:PF_LC_WANGXY,UQRY_LC_ZHANGJIN:PF_OLD_OTHER,OLD_UCR_RES1:PF_OLD_UCR_RES1,LC_DUANJL:PF_OLD_OTHER,YD_HUAJ:PF_OLD_OTHER,YD_XIANLQ:PF_OLD_OTHER,LC_WANGFB:PF_OLD_OTHER,UCR_CTR:PF_OLD_OTHER,OLD_UOP_RES1:PF_OLD_OTHER,OLD_UCR_PF:PF_OLD_UCR_PF,OLD_UCR_TERM:PF_OLD_UCR_TERM,YD_LIYL:PF_OLD_OTHER,YD_LIS:PF_OLD_OTHER,YD_WANGSD:PF_YD_WANGSD,YD_BASX:PF_OLD_OTHER,OLD_UOP_PF:PF_OLD_OTHER,OLD_UOP_SFCINS1:PF_OLD_OTHER,OLD_UCR_OLCOM:PF_OLD_UCR_OLCOM,OLD_UOP_UIF1:PF_OLD_OTHER,YD_HUANGQL:PF_OLD_OTHER,UCR_OPS:PF_OLD_OTHER,OLD_UOP_SOA:PF_OLD_OTHER,YD_MAHONGYAN:PF_OLD_OTHER,YD_WANGX:PF_YD_WANGX,LC_HAODW:PF_OLD_OTHER,YD_ZHUCHM:PF_OLD_OTHER

nohup impdp \'/ as sysdba\' parfile=pfpar.par &
复制

从日志可以看出，问题已经解决,表的元数据已经导入成功了。

qhbossdb2:/dmp(qhbossdb2)$tail -f nohup.out 

Import: Release 19.0.0.0.0 - Production on Tue Nov 14 20:57:34 2023
Version 19.13.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
Starting "SYS"."SYS_IMPORT_TABLESPACE_02":  "/******** AS SYSDBA" parfile=pfpar.par 
W-1 Startup took 0 seconds
W-1 Estimate in progress using BLOCKS method...
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

W-1      Estimated 32903 TABLE_DATA objects in 484 seconds
W-1 Total estimation using BLOCKS method: 4003. GB
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE
复制

然后就进入到无穷无尽的等待中，但是一定要注意，大批量的导数，会给归档日志造成压力，此时一定要注意归档空间的剩余，归档空间满了，数据库就会不可用，从而影响业务。
自己动手，丰衣足食，及时部署归档删除脚本，如果有ADG，还是考虑备库对于归档日志的应用情况：

qhbossdb2:/home/oracle/enmo(qhbossdb2)$cat dele_arch.sh 
#!/bin/bash
source ~/.bash_profile
rman target /  log=/home/oracle/enmo/dele_arch.log <<EOF
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-30/1440';
exit;
EOF
qhbossdb2:/home/oracle/enmo(qhbossdb2)$crontab -l
*/14 * * * * sh /home/oracle/enmo/dele_arch.sh
复制

经过以上过程，所有迁移表空间中的表和索引，以及元数据均导入成功，具体参考以下日志：

Import: Release 19.0.0.0.0 - Production on Tue Nov 14 20:57:34 2023
Version 19.13.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
Starting "SYS"."SYS_IMPORT_TABLESPACE_02":  "/******** AS SYSDBA" parfile=pfpar.par
W-1 Startup took 0 seconds
W-1 Estimate in progress using BLOCKS method...
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
W-1      Estimated 32903 TABLE_DATA objects in 484 seconds
W-1 Total estimation using BLOCKS method: 4003. GB
W-1 Processing object type TABLE_EXPORT/TABLE/TABLE
W-1      Completed 4947 TABLE objects in 1219 seconds
W-2 Startup took 0 seconds
W-3 Startup took 0 seconds
W-4 Startup took 0 seconds
W-6 Startup took 0 seconds
W-5 Startup took 0 seconds
W-7 Startup took 0 seconds
W-8 Startup took 0 seconds
W-9 Startup took 0 seconds
W-10 Startup took 0 seconds
W-1 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_10" 126509468 rows in 594 seconds using direct_path
W-10 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_9" 130477413 rows in 620 seconds using direct_path
W-8 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_8" 132961362 rows in 625 seconds using direct_path
W-9 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_12" 129771210 rows in 643 seconds using direct_path
W-4 . . imported "PF_OLD_UCR_OLCOM"."TI_CH_OLCOMWORKBAK20220510":"P12" 32646176 rows in 672 seconds using direct_path
W-3 . . imported "PF_OLD_UCR_OLCOM"."TI_CH_OLCOMWORKBAK20220510":"P11" 43373301 rows in 890 seconds using direct_path
W-1 . . imported "PF_OLD_UCR_OLCOM"."TI_CH_OLCOMWORKBAK20220510":"P01" 22552276 rows in 465 seconds using direct_path
W-8 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_7" 124243947 rows in 537 seconds using direct_path
W-10 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_11" 126000562 rows in 573 seconds using direct_path
W-4 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_4" 122402272 rows in 529 seconds using direct_path
W-1 . . imported "PF_OLD_UCR_PF"."TI_CH_PARAMBAK20220510":"M09" 92869253 rows in 443 seconds using direct_path
W-10 . . imported "PF_OLD_UCR_OLCOM"."TI_CH_OLCOMWORKBAK20220510":"P09" 22091157 rows in 405 seconds using direct_path
W-4 . . imported "PF_OLD_UCR_PF"."TI_CH_PARAMBAK20220510":"M11" 87175337 rows in 435 seconds using direct_path
W-2 . . imported "PF_OLD_OTHER"."TMP_EOM_MSG_NOTRADE"       27501281 rows in 1654 seconds using direct_path
W-8 . . imported "PF_OLD_UCR_UIF1"."TL_BH_IBTRADE":"PAR_TL_BH_IBTRADE_3" 113515573 rows in 494 seconds using direct_path
...........................
W-1 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_03203"                0 rows in 0 seconds using direct_path
W-7 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_03204"                0 rows in 0 seconds using direct_path
W-9 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_03205"                0 rows in 0 seconds using direct_path
W-6 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_03206"                0 rows in 0 seconds using direct_path
W-3 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_03207"                0 rows in 0 seconds using direct_path
W-8 . . imported "PF_OLD_OTHER"."ZHUCM_TEMP_ARCH"              5000 rows in 0 seconds using direct_path
W-1 Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'UOP_OLCOM1' does not exist

Failing sql is:
GRANT ALTER ON "PF_OLD_OTHER"."TMP_TSYH_BAK" TO "UOP_OLCOM1"

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'UOP_OLCOM1' does not exist

Failing sql is:
GRANT FLASHBACK ON "PF_OLD_OTHER"."TMP_TSYH_BAK" TO "UOP_OLCOM1"
............................
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'UOP_UIF1' does not exist

Failing sql is: 
GRANT SELECT ON "PF_OLD_OTHER"."NEWHN_OLD2NEW_TRANS_LOG" TO "UOP_UIF1"

W-1      Completed 20295 OBJECT_GRANT objects in 977 seconds 
W-1 Processing object type TABLE_EXPORT/TABLE/COMMENT
W-1      Completed 25241 COMMENT objects in 1467 seconds 
W-1 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"PF_OLD_UCR_SFCINS1"."IDX_W_A_WID" failed to create with error:
ORA-00959: tablespace 'TBS_SFCBASE_INDEX' does not exist

Failing sql is: 
CREATE INDEX "PF_OLD_UCR_SFCINS1"."IDX_W_A_WID" ON "PF_OLD_UCR_SFCINS1"."VM_WF_ATTRBAK20220510" ("WORKFLOW_ID") PCTFREE 10 INITRANS 20 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 M
INEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS_SFCBASE_INDEX" PARALLEL 1
...........................
ORA-39083: Object type CONSTRAINT:"PF_OLD_UCR_SFCINS1"."PK_SF_O_PARAM_GREEN" failed to create with error:
ORA-00959: tablespace 'TBS_SFCBASE_INDEX' does not exist

Failing sql is:
ALTER TABLE "PF_OLD_UCR_SFCINS1"."SF_O_PARAM_GREENBAK20220510" ADD CONSTRAINT "PK_SF_O_PARAM_GREEN" PRIMARY KEY ("IBSYSID", "TAG", "SEQ", "EXEC_DAY") USING INDEX PCTFREE 10 INITRANS 20
MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS_SFCBASE_INDEX"  LOC
AL (PARTITION "D01" NOCOMPRESS PCTFREE 10 INITRANS 20 MAXTRANS 255 LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROU
PS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS_SFCBASE_INDEX" , PARTITION "D02" NOCOMPRESS PCTFREE 10 INITRANS 20 MAXTRANS 255 LOGGING  STORAGE(I
NITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TB
S_SFCBASE_INDEX" , PARTITION "D03" NOCOMPRESS PCTFREE 10 INITRANS 20 MAXTRANS 255 LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS
1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBS_SFCBASE_INDEX" , PARTITION "D04" NOCOMPRESS PCTFREE 10 INITRANS 20 MAXTRANS 255 LOG
GING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "TBS_SFC

W-1      Completed 2786 CONSTRAINT objects in 1215 seconds
W-2      Completed 32903 TABLE_EXPORT/TABLE/TABLE_DATA objects in 27568 seconds
Job "SYS"."SYS_IMPORT_TABLESPACE_02" completed with 13783 error(s) at Wed Nov 15 23:12:46 2023 elapsed 1 02:15:10
复制

有2个报错：
1、OBJECT_GRANT赋权，原因是role角色不存在。下一步导入，再进一步完善。

W-1 Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'UOP_OLCOM1' does not exist

Failing sql is:
GRANT ALTER ON "PF_OLD_OTHER"."TMP_TSYH_BAK" TO "UOP_OLCOM1"
复制

2、创建874个索引失败，由于表空间不存在。排查后，源库这些索引都在其他表空间，看来remap参数的完善。

qhbossdb2:/dmp(qhbossdb2)$cat pfindex.par 
logfile=index.log
metrics=yes
cluster=N
directory=impdp
parallel=10
include=index
network_link=to_pf
TABLESPACES=TBS_OLD_DATA
REMAP_TABLESPACE=USERS:TBS_OLD_DATA_PF,TBS_SFCINS1_DATA:TBS_OLD_DATA_PF,TBS_OLD_DATA:TBS_OLD_DATA_PF,TBS_NEA1_DATA:TBS_OLD_DATA_PF,TBS_NEA1_HINDEX:TBS_OLD_DATA_PF,TBS_NEA1_INDEX:TBS_OLD_DATA_PF,TBS_SFCBASE_INDEX:TBS_OLD_DATA_PF
REMAP_SCHEMA=OLD_UCR_OPPF:PF_OLD_OTHER,OLD_UCR_CEN1:PF_OLD_UCR_CEN1,UCR_CRMCC:PF_OLD_OTHER,OLD_UCR_SFCINS1:PF_OLD_UCR_SFCINS1,OLD_UOP_OPPF:PF_OLD_OTHER,LC_MADX:PF_OLD_OTHER,YD_CHENXY:PF_OLD_OTHER,LC_JIANGX:PF_OLD_OTHER,UMON:PF_OLD_OTHER,OLD_UOP_SFCBASE:PF_OLD_OTHER,OLD_UCR_SFCBASE:PF_OLD_UCR_SFCBASE,OLD_UOP_OLCOM:PF_OLD_OTHER,OLD_UCR_UIF1:PF_OLD_UCR_UIF1,YD_SUNWB:PF_OLD_OTHER,LC_CUIFN:PF_OLD_OTHER,LC_MAYH:PF_OLD_OTHER,YD_XIEZQ:PF_OLD_OTHER,LC_LISG:PF_OLD_OTHER,YD_TENGWEN:PF_OLD_OTHER,YD_WANGL:PF_OLD_OTHER,LC_HOUYQ:PF_LC_HOUYQ,LC_LIMS:PF_OLD_OTHER,YD_CHENTQ:PF_OLD_OTHER,LC_LIUC:PF_OLD_OTHER,OLD_UOP_NEA1:PF_OLD_UOP_NEA1,OLD_UCR_NEA1:PF_OLD_UCR_NEA1,OLD_UCR_PCEN:PF_OLD_UCR_PCEN,OLD_UCR_OPPF_SEC:PF_OLD_OTHER,OLD_UOP_TERM:PF_OLD_OTHER,OLD_UCR_LSMS:PF_OLD_OTHER,OLD_UCR_SOA:PF_OLD_UCR_SOA,YD_JIANGHL:PF_OLD_OTHER,LC_YANGJY:PF_OLD_OTHER,YD_CHENL:PF_YD_CHENL,YD_QIM:PF_OLD_OTHER,LC_CHENYJ:PF_OLD_OTHER,LC_WANGXY:PF_LC_WANGXY,UQRY_LC_ZHANGJIN:PF_OLD_OTHER,OLD_UCR_RES1:PF_OLD_UCR_RES1,LC_DUANJL:PF_OLD_OTHER,YD_HUAJ:PF_OLD_OTHER,YD_XIANLQ:PF_OLD_OTHER,LC_WANGFB:PF_OLD_OTHER,UCR_CTR:PF_OLD_OTHER,OLD_UOP_RES1:PF_OLD_OTHER,OLD_UCR_PF:PF_OLD_UCR_PF,OLD_UCR_TERM:PF_OLD_UCR_TERM,YD_LIYL:PF_OLD_OTHER,YD_LIS:PF_OLD_OTHER,YD_WANGSD:PF_YD_WANGSD,YD_BASX:PF_OLD_OTHER,OLD_UOP_PF:PF_OLD_OTHER,OLD_UOP_SFCINS1:PF_OLD_OTHER,OLD_UCR_OLCOM:PF_OLD_UCR_OLCOM,OLD_UOP_UIF1:PF_OLD_OTHER,YD_HUANGQL:PF_OLD_OTHER,UCR_OPS:PF_OLD_OTHER,OLD_UOP_SOA:PF_OLD_OTHER,YD_MAHONGYAN:PF_OLD_OTHER,YD_WANGX:PF_YD_WANGX,LC_HAODW:PF_OLD_OTHER,YD_ZHUCHM:PF_OLD_OTHER
nohup impdp \'/ as sysdba\' parfile=pfindex.par &
复制

部分日志如下：

..............
ORA-31684: Object type INDEX:"PF_OLD_UCR_PF"."PK_TL_B_IBPLAT_SYN_RSLT_SUB" already exists
W-1      Completed 2404 INDEX objects in 623 seconds
W-1 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1      Completed 3739 INDEX_STATISTICS objects in 28 seconds
Job "SYS"."SYS_IMPORT_TABLESPACE_02" completed with 1740 error(s) at Thu Nov 16 09:41:10 2023 elapsed 0 00:11:15   
复制

以上两个错误都容易处理。主要能完整的把表数据迁移过来即可。

对象比对

对象信息：

SQL>  select owner,table_name from dba_tables where tablespace_name='TBS_OLD_DATA'
  2   minus
  3   select owner,table_name from dba_tables@to_pf where tablespace_name='TBS_OLD_DATA';

no rows selected

SQL> select owner,index_name from dba_indexes where tablespace_name='TBS_OLD_DATA'
  2  minus
  3  select owner,index_name from dba_indexes@to_pf  where tablespace_name='TBS_OLD_DATA';

no rows selected
复制

表空间中所有对象都迁移过来了

处理无效对象

exec utl_recomp.recomp_parallel(60);

收集数据库和数据字典统计信息

set serveroutput on
set timing on
exec dbms_stats.set_param('DEGREE','64');
select dbms_stats.get_param('DEGREE') from dual; 
exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.set_param('DEGREE','0');
commit;
复制

回收dba 权限赋权普通权限

revoke dba from  PF_OLD_OTHER      ;
revoke dba from  PF_OLD_UCR_SFCINS1;
revoke dba from  PF_OLD_UCR_CEN1   ;
revoke dba from  PF_OLD_UCR_SFCBASE;
revoke dba from  PF_OLD_UCR_UIF1   ;
revoke dba from  PF_LC_HOUYQ       ;
revoke dba from  PF_OLD_UCR_NEA1   ;
revoke dba from  PF_OLD_UOP_NEA1   ;
revoke dba from  PF_OLD_UCR_SOA    ;
revoke dba from  PF_YD_CHENL       ;
revoke dba from  PF_OLD_UCR_PCEN   ;
revoke dba from  PF_LC_WANGXY      ;
revoke dba from  PF_OLD_UCR_RES1   ;
revoke dba from  PF_OLD_UCR_PF     ;
revoke dba from  PF_OLD_UCR_TERM   ;
revoke dba from  PF_YD_WANGSD      ;
revoke dba from  PF_OLD_UCR_OLCOM  ;
revoke dba from  PF_YD_WANGX       ;
复制

由于是历史数据归档，授普通权限即可。

grant connect,resource to PF_OLD_OTHER      ;
grant connect,resource to PF_OLD_UCR_SFCINS1;
grant connect,resource to PF_OLD_UCR_CEN1   ;
grant connect,resource to PF_OLD_UCR_SFCBASE;
grant connect,resource to PF_OLD_UCR_UIF1   ;
grant connect,resource to PF_LC_HOUYQ       ;
grant connect,resource to PF_OLD_UCR_NEA1   ;
grant connect,resource to PF_OLD_UOP_NEA1   ;
grant connect,resource to PF_OLD_UCR_SOA    ;
grant connect,resource to PF_YD_CHENL       ;
grant connect,resource to PF_OLD_UCR_PCEN   ;
grant connect,resource to PF_LC_WANGXY      ;
grant connect,resource to PF_OLD_UCR_RES1   ;
grant connect,resource to PF_OLD_UCR_PF     ;
grant connect,resource to PF_OLD_UCR_TERM   ;
grant connect,resource to PF_YD_WANGSD      ;
grant connect,resource to PF_OLD_UCR_OLCOM  ;
grant connect,resource to PF_YD_WANGX       ;

alter user PF_OLD_OTHER       quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_SFCINS1 quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_CEN1    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_SFCBASE quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_UIF1    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_LC_HOUYQ        quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_NEA1    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UOP_NEA1    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_SOA     quota unlimited on TBS_OLD_DATA_PF;
alter user PF_YD_CHENL        quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_PCEN    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_LC_WANGXY       quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_RES1    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_PF      quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_TERM    quota unlimited on TBS_OLD_DATA_PF;
alter user PF_YD_WANGSD       quota unlimited on TBS_OLD_DATA_PF;
alter user PF_OLD_UCR_OLCOM   quota unlimited on TBS_OLD_DATA_PF;
alter user PF_YD_WANGX        quota unlimited on TBS_OLD_DATA_PF;
复制

经历了1天2小时，整个迁移过程就算结束了。

总结

数据泵导入的时候，一定要注意目标端的归档空间，迁移期间归档量会暴增，避免归档空间慢，造成业务中断。
impdp+network_link_tablespace，是可以进行整个表空间迁移的，为了避免对象自包含，前提和xtts一样，需要检查。
remap参数要仔细用好，在迁移过程中，遇到重复对象，remap_user一定不能是同一个，单独创建新用户。
看似上面步骤很多，其实很多都是排错步骤，最终parfile文件如下，一个步骤就会全部导入完成。

[root@qhbossdb2 dmp]# cat pfzuizhong.par 
logfile=pfpar.log
metrics=yes
cluster=N
directory=impdp
parallel=10
EXCLUDE=STATISTICS 
network_link=to_pf
TABLESPACES=TBS_OLD_DATA
REMAP_TABLESPACE=USERS:TBS_OLD_DATA_PF,TBS_SFCINS1_DATA:TBS_OLD_DATA_PF,TBS_OLD_DATA:TBS_OLD_DATA_PF,TBS_NEA1_DATA:TBS_OLD_DATA_PF,TBS_NEA1_HINDEX:TBS_OLD_DATA_PF,TBS_NEA1_INDEX:TBS_OLD_DATA_PF,TBS_SFCBASE_INDEX:TBS_OLD_DATA_PF
REMAP_SCHEMA=OLD_UCR_OPPF:PF_OLD_OTHER,OLD_UCR_CEN1:PF_OLD_UCR_CEN1,UCR_CRMCC:PF_OLD_OTHER,OLD_UCR_SFCINS1:PF_OLD_UCR_SFCINS1,OLD_UOP_OPPF:PF_OLD_OTHER,LC_MADX:PF_OLD_OTHER,YD_CHENXY:PF_OLD_OTHER,LC_JIANGX:PF_OLD_OTHER,UMON:PF_OLD_OTHER,OLD_UOP_SFCBASE:PF_OLD_OTHER,OLD_UCR_SFCBASE:PF_OLD_UCR_SFCBASE,OLD_UOP_OLCOM:PF_OLD_OTHER,OLD_UCR_UIF1:PF_OLD_UCR_UIF1,YD_SUNWB:PF_OLD_OTHER,LC_CUIFN:PF_OLD_OTHER,LC_MAYH:PF_OLD_OTHER,YD_XIEZQ:PF_OLD_OTHER,LC_LISG:PF_OLD_OTHER,YD_TENGWEN:PF_OLD_OTHER,YD_WANGL:PF_OLD_OTHER,LC_HOUYQ:PF_LC_HOUYQ,LC_LIMS:PF_OLD_OTHER,YD_CHENTQ:PF_OLD_OTHER,LC_LIUC:PF_OLD_OTHER,OLD_UOP_NEA1:PF_OLD_UOP_NEA1,OLD_UCR_NEA1:PF_OLD_UCR_NEA1,OLD_UCR_PCEN:PF_OLD_UCR_PCEN,OLD_UCR_OPPF_SEC:PF_OLD_OTHER,OLD_UOP_TERM:PF_OLD_OTHER,OLD_UCR_LSMS:PF_OLD_OTHER,OLD_UCR_SOA:PF_OLD_UCR_SOA,YD_JIANGHL:PF_OLD_OTHER,LC_YANGJY:PF_OLD_OTHER,YD_CHENL:PF_YD_CHENL,YD_QIM:PF_OLD_OTHER,LC_CHENYJ:PF_OLD_OTHER,LC_WANGXY:PF_LC_WANGXY,UQRY_LC_ZHANGJIN:PF_OLD_OTHER,OLD_UCR_RES1:PF_OLD_UCR_RES1,LC_DUANJL:PF_OLD_OTHER,YD_HUAJ:PF_OLD_OTHER,YD_XIANLQ:PF_OLD_OTHER,LC_WANGFB:PF_OLD_OTHER,UCR_CTR:PF_OLD_OTHER,OLD_UOP_RES1:PF_OLD_OTHER,OLD_UCR_PF:PF_OLD_UCR_PF,OLD_UCR_TERM:PF_OLD_UCR_TERM,YD_LIYL:PF_OLD_OTHER,YD_LIS:PF_OLD_OTHER,YD_WANGSD:PF_YD_WANGSD,YD_BASX:PF_OLD_OTHER,OLD_UOP_PF:PF_OLD_OTHER,OLD_UOP_SFCINS1:PF_OLD_OTHER,OLD_UCR_OLCOM:PF_OLD_UCR_OLCOM,OLD_UOP_UIF1:PF_OLD_OTHER,YD_HUANGQL:PF_OLD_OTHER,UCR_OPS:PF_OLD_OTHER,OLD_UOP_SOA:PF_OLD_OTHER,YD_MAHONGYAN:PF_OLD_OTHER,YD_WANGX:PF_YD_WANGX,LC_HAODW:PF_OLD_OTHER,YD_ZHUCHM:PF_OLD_OTHER

nohup impdp \'/ as sysdba\' parfile=pfzuizhong.par &
复制

墨力计划 oracle 数据库迁移数据泵

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

数据泵迁移单个表空间7T大小-pfdb