数据泵导入导出远程数据库数据

源库：192.168.162.200 oracle11g
目标库：192.168.162.199 node01

1.源端创建测试数据
[oracle@oracle11g admin]$ sqlplus test/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 5 02:33:28 2022

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table tt (id number(10));

Table created.

SQL> insert into tt values (1);

1 row created.

SQL> insert into tt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt;

        ID
----------
         1
         2
         
2.目标库tnsnames.ora添加以下内容
ORCL2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.200)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

测试：
[oracle@node01 admin]$ tnsping orcl2

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 15-JUL-2022 07:07:29

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)

3.创建dblink
SQL> create public database link dblk_system connect to system identified by oracle using 'orcl2';

Database link created.

测试：
SQL> select * from dual@dblk_system;

DU
--
X

4.创建directory并授权
SQL> create or replace directory dmpdir as '/home/oracle';

Directory created.

SQL> grant read,write on directory dmpdir to system;

Grant succeeded.

5.目标端创建test用户
SQL> create user test identified by oracle account unlock;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

6.测试network_link方式在目标端导入数据
[oracle@node01 admin]$ impdp system/oracle directory=dmpdir tables=test.tt remap_schema=test:test remap_tablespace=user:user network_link=dblk_system logfile=impdp_test_tt_system.log

Import: Release 11.2.0.4.0 - Production on Fri Jul 15 07:23:37 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dmpdir tables=test.tt remap_schema=test:test remap_tablespace=user:user network_link=dblk_system logfile=impdp_test_tt_system.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "TEST"."TT"                                      2 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Fri Jul 15 07:23:44 2022 elapsed 0 00:00:06

查询导入后结果：
SQL> select * from test.tt;

        ID
----------
         1
         2
         
7.测试network_link方式导出数据
[oracle@node01 admin]$ expdp system/oracle directory=dmpdir dumpfile=test_emp.dmp tables=test.emp network_link=dblk_system logfile=test_emp.log

Export: Release 11.2.0.4.0 - Production on Fri Jul 15 07:14:10 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dmpdir dumpfile=test_emp.dmp tables=test.emp network_link=dblk_system logfile=test_emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."EMP"                                8.562 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/test_emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jul 15 07:14:16 2022 elapsed 0 00:00:06