数据泵迁移单个表空间7t大小-pfdb
即前两篇核心库历史表空间数据归档。第三套类似的环境，仍然需要历史数据归档。
源端环境：
跟上一套环境基本类似，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
米乐app官网下载 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)
意思违反了约束：要挪到的表空间里面的表，和表上相关索引在两个不同表空间中。有可能迁移走了表，而没有把表上相关索引迁移走。
m6米乐安卓版下载的解决方案：把索引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 米乐app官网下载 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 米乐app官网下载 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 米乐app官网下载 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 <
经过以上过程，所有迁移表空间中的表和索引，以及元数据均导入成功，具体参考以下日志：
import: release 19.0.0.0.0 - production on tue nov 14 20:57:34 2023
version 19.13.0.0.0
米乐app官网下载 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 &
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」 关注作者 【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。 评论