expdp导出dblink impdp导入dblink
一、若创建的是public的dblink，默认是不导出的，解决方案见DataPump SCHEMA Level Export (EXPDP) Does Not Export Public Database Links (Doc ID 837839.1)
方案一： 手工查出原库的dblink，在目标库上再手工创建。
方案二： 创建一个parfile.par，里面添加如下内容： INCLUDE=DB_LINK:“LIKE ‘LINK_%’” -----%是匹配dblink name（查询select * from DBA_DB_LINKS;中的host列）
操作如下： 只导出dblink：
[oracle@rhel75 backup]$ expdp u1/u1 directory=DATA_DIR dumpfile=dump_link.dmp content=metadata_only full=y parfile=parfile.par logfile=dump_expdp20200701_link5.log & [1] 67473 [oracle@rhel75 backup]$ Export: Release 12.2.0.1.0 - Production on Thu Jul 2 23:18:07 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "U1"."SYS_EXPORT_FULL_01": u1/******** directory=DATA_DIR dumpfile=dump_link.dmp content=metadata_only full=y parfile=parfile.par logfile=dump_expdp20200701_link5.log Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Master table "U1"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for U1.SYS_EXPORT_FULL_01 is: /oracle/backup/dump_link.dmp Job "U1"."SYS_EXPORT_FULL_01" successfully completed at Thu Jul 2 23:18:17 2020 elapsed 0 00:00:09 [1]+ Done expdp u1/u1 directory=DATA_DIR dumpfile=dump_link.dmp content=metadata_only full=y parfile=parfile.par parallel=8 logfile=dump_expdp20200701_link5.log [oracle@rhel75 backup]$导入dblink：
[oracle@rhel75 ~]$ impdp u1/u1 directory=DATA_DIR dumpfile=dump_link.dmp logfile=link.log full=y Import: Release 12.2.0.1.0 - Production on Thu Jul 2 23:20:28 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ORA-39146: schema "U1" does not exist Master table "U3"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "U3"."SYS_IMPORT_FULL_01": u3/******** directory=DATA_DIR dumpfile=dump_link.dmp REMAP_SCHEMA=u1:u3 logfile=link.log full=y Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Job "U3"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Jul 2 23:20:32 2020 elapsed 0 00:00:03[oracle@rhel75 ~]$ 查询：
set pagesize 230 set line 400 col owner for a14 col DB_LINK for a20 col USERNAME for a15 col CREATED for a10 col HOST for a40 select * from DBA_DB_LINKS; OWNER DB_LINK USERNAME HOST CREATED HID -------------- -------------------- --------------- ---------------------------------------- ---------- --- PUBLIC LINK_U2 U2 (DESCRIPTION = 02-JUL-20 NO (ADDRESS = (PROTOCOL = TCP)(HOST = 1 92.168.56.114)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11g) ) )二、测试创建私有dblink
conn u1/u1 create database link link_u2 connect to u2 identified by "u2" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11g) ) )';查询：
OWNER DB_LINK USERNAME HOST CREATED HID -------------- -------------------- --------------- ---------------------------------------- ---------- --- U1 LINK_U2 U2 (DESCRIPTION = 02-JUL-20 NO (ADDRESS = (PROTOCOL = TCP)(HOST = 1 92.168.56.114)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11g) ) )导出：
expdp u1/u1 directory=DATA_DIR dumpfile=dump_link2.dmp content=metadata_only schemas=u1 parfile=parfile.par logfile=link5.log [oracle@rhel75 backup]$ expdp u1/u1 directory=DATA_DIR dumpfile=dump_link2.dmp content=metadata_only schemas=u1 parfile=parfile.par logfile=link5.log Export: Release 12.2.0.1.0 - Production on Thu Jul 2 23:51:30 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** directory=DATA_DIR dumpfile=dump_link2.dmp content=metadata_only schemas=u1 parfile=parfile.par logfile=link5.log Processing object type SCHEMA_EXPORT/DB_LINK Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for U1.SYS_EXPORT_SCHEMA_01 is: /oracle/backup/dump_link2.dmp Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jul 2 23:51:36 2020 elapsed 0 00:00:05 [oracle@rhel75 backup]$删除u1的dblink： drop database link link_u2;
导入：
impdp u1/u1 directory=DATA_DIR dumpfile=dump_link2.dmp logfile=link.log schemas=u1 [oracle@rhel75 backup]$ impdp u1/u1 directory=DATA_DIR dumpfile=dump_link2.dmp logfile=link.log schemas=u1 Import: Release 12.2.0.1.0 - Production on Thu Jul 2 23:52:47 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "U1"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "U1"."SYS_IMPORT_SCHEMA_01": u1/******** directory=DATA_DIR dumpfile=dump_link2.dmp logfile=link.log schemas=u1 Processing object type SCHEMA_EXPORT/DB_LINK Job "U1"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Jul 2 23:52:50 2020 elapsed 0 00:00:03 [oracle@rhel75 backup]$再次查询u1的dblink:
OWNER DB_LINK USERNAME HOST CREATED HID -------------- -------------------- --------------- ---------------------------------------- ---------- --- SYS SYS_HUB ORCL11G 24-JUN-20 NO U1 LINK_U2 U2 (DESCRIPTION = 02-JUL-20 NO (ADDRESS = (PROTOCOL = TCP)(HOST = 1 92.168.56.114)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl11g) ) )测试非公有dblink是否能默认导出：
expdp u1/u1 directory=DATA_DIR dumpfile=dump_u1.dmp schemas=u1 logfile=dump.log & [oracle@rhel75 backup]$ expdp u1/u1 directory=DATA_DIR dumpfile=dump_u1.dmp schemas=u1 logfile=dump.log & [1] 70053 [oracle@rhel75 backup]$ Export: Release 12.2.0.1.0 - Production on Thu Jul 2 23:54:47 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "U1"."SYS_EXPORT_SCHEMA_01": u1/******** directory=DATA_DIR dumpfile=dump_u1.dmp schemas=u1 logfile=dump.log Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "U1"."T" 8.366 MB 86274 rows . . exported "U1"."T2" 8.366 MB 86274 rows . . exported "U1"."TEST" 0 KB 0 rows Master table "U1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for U1.SYS_EXPORT_SCHEMA_01 is: /oracle/backup/dump_u1.dmp Job "U1"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jul 2 23:55:47 2020 elapsed 0 00:00:59结论：非公有dblink默认是导出的；public dblink则默认不导出。
