【Oracle】expdp/impdp導出導入dblink

expdp導出dblink

impdp導入dblink

一、public 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

導入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.

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

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)

)

)

二、非public dblink

二、測試建立私有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

導出：

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

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

删除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

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

Job "U1"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Jul 2 23:52:50 2020 elapsed 0 00:00:03

再次查詢u1的dblink:

SYS SYS_HUB ORCL11G 24-JUN-20 NO

測試非公有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

Export: Release 12.2.0.1.0 - Production on Thu Jul 2 23:54:47 2020