Oracle expdp：NETWORK_LINK
关于“expdp”的NETWORK_LINK参数，它通常用于将远端的数据库的数据导出到本地机器的场景。
它的正常使用需要依赖TNS、DBLINK。
本文会呈现EXPDP的NETWORK_LINK使用的技术细节。
——————————————————————
环境说明。
源：
OS:LINUX
ORACLE：RAC（10.2.0.5.0）
DB_NAME:ORCL
Instance：ORCL1、ORCL2、ORCL3
Schema：SCOTT
TNS：ORCL
目标：
OS:MS Windows7
ORACLE：Oracle（10.2.0.4.0）
SID：pwc
TNS：PWD
DUMP Directory:F:\Dump_me
Directory Object Name：dumpme
Database Link Name：pwc2orcl
Dump File Name：dump_rac_orcl_node1_20140925_1.dmp
Dump Log File Name：dump_rac_orcl_node1_20140925_1.log
一、创建目标到源的TNS链接
在目标端的$ORACLE_HOME/network/admin/tnsnames.ora文件中添加如下内容：
文件：F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.141)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.142)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.143)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) ) ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.141)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) (INSTANCE_NAME = ORCL1) ) ) |
如图：
测试目标端到源端的TNS连通性：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
PS F:\oracle\product\10.2.0\db_1\BIN> pwd Path ---- F:\oracle\product\10.2.0\db_1\BIN PS F:\oracle\product\10.2.0\db_1\BIN> ./tnsping ORCL TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 25-SEP-2014 11:32:54 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.141)(PORT = 1521)) (ADDRESS = (PROTO COL = TCP)(HOST = 192.168.119.142)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.143)(PORT = 1521)) (LOAD _BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (20 msec) PS F:\oracle\product\10.2.0\db_1\BIN> PS F:\oracle\product\10.2.0\db_1\BIN> ./tnsping ORCL1 TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 25-SEP-2014 12:51:51 Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files: F:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.119.141)(PORT = 1521)) (CONNECT_DATA = ( SERVER = DEDICATED) (SERVICE_NAME = ORCL) (INSTANCE_NAME = ORCL1))) OK (20 msec) PS F:\oracle\product\10.2.0\db_1\BIN> |
二、目标端：创建到源端的DBLINK
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
PS F:\oracle\product\10.2.0\db_1\BIN> ./sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 25 12:38:33 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select object_type,count(*) from dba_objects where object_type='DATABASE LINK' group by object_type; no rows selected SQL> create public database link pwc2orcl connect to scott identified by oracle using 'orcl'; Database link created. SQL> create public database link pwc2orcl1 connect to scott identified by oracle using 'orcl1'; Database link created. SQL> SQL> col object_name for a14 SQL> col owner for a14 SQL> select object_type,count(*) from dba_objects where object_type='DATABASE LINK' group by object_type; OBJECT_TYPE COUNT(*) ------------------- ---------- DATABASE LINK 2 SQL> select object_name,owner,object_type,status from dba_objects where object_type='DATABASE LINK' and object_name like '%PWC2ORCL%'; OBJECT_NAME OWNER OBJECT_TYPE STATUS -------------- -------------- ------------------- ------- PWC2ORCL PUBLIC DATABASE LINK VALID PWC2ORCL1 PUBLIC DATABASE LINK VALID SQL> SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 50 PWC Oracle MS Windows 7 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from scott.dept@PWC2ORCL; DEPTNO DNAME LOC ---------- -------------- ------------- 50 ORCL RAC Node1 Linux 64 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from scott.dept@PWC2ORCL1; DEPTNO DNAME LOC ---------- -------------- ------------- 50 ORCL RAC Node1 Linux 64 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> |
在这个过程中，你可能会遇到如下的错误：
|
1 2 3 4 5 6 7 8 9 |
SQL> select * from scott.dept@PWC2ORCL1; select * from scott.dept@PWC2ORCL1 * ERROR at line 1: ORA-28000: the account is locked ORA-02063: preceding line from PWC2ORCL1 SQL> |
这说明当前DBLINK创建时“connect to”指定的用户状态被锁定了。
需要确认当前指定用户的口令与创建DBLINK时候的口令一致，否则就算源端用户解锁，依旧会因为密码不正确的次数太多再次被锁住。
源端的状态）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select username,account_status from dba_users where username='SCOTT'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SCOTT LOCKED SQL> alter user scott identified by oracle account unlock; User altered. SQL> select username,account_status from dba_users where username='SCOTT'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SCOTT OPEN SQL> |
如上变更后，目标端再次连接就没问题了。
三、目标端：创建目录对象
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
SQL> col object_name for a24 SQL> select count(*) from dba_objects where object_type='DIRECTORY'; COUNT(*) ---------- 4 SQL> select owner,object_name,object_type,status from dba_objects where object_type='DIRECTORY'; OWNER OBJECT_NAME OBJECT_TYPE STATUS -------------- ------------------------ ------------------- ------- SYS DATA_PUMP_DIR DIRECTORY VALID SYS ORACLE_OCM_CONFIG_DIR DIRECTORY VALID SYS ADMIN_DIR DIRECTORY VALID SYS WORK_DIR DIRECTORY VALID SQL> SQL> create or replace directory dumpme as 'F:\Dump_me'; Directory created. SQL> select owner,object_name,object_type,status from dba_objects where object_type='DIRECTORY'; OWNER OBJECT_NAME OBJECT_TYPE STATUS -------------- ------------------------ ------------------- ------- SYS DATA_PUMP_DIR DIRECTORY VALID SYS ORACLE_OCM_CONFIG_DIR DIRECTORY VALID SYS ADMIN_DIR DIRECTORY VALID SYS WORK_DIR DIRECTORY VALID SYS DUMPME DIRECTORY VALID SQL> grant read,write on directory dumpme to scott; Grant succeeded. SQL> select count(*) from dba_directories; COUNT(*) ---------- 5 SQL> SQL> col directory_path for a50 SQL> set linesize 300 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH -------------- ------------------------ -------------------------------------------------- SYS DUMPME F:\Dump_me SYS DATA_PUMP_DIR F:\oracle\product\10.2.0/admin/pwc/dpdump/ SYS ORACLE_OCM_CONFIG_DIR F:\oracle\product\10.2.0\db_1/ccr/state SYS ADMIN_DIR C:\ADE\aime_rf\oracle/md/admin SYS WORK_DIR C:\ADE\aime_rf\oracle/work SQL> SQL> SQL> SQL> set linesize 400 SQL> col directory_path for a30 SQL> col directory_name for a14 SQL> select privilege,directory_name,directory_path from user_tab_privs t,all_directories d where t.table_name(+)=d.dire ctory_name and directory_name='DUMPME' order by 2,1; PRIVILEGE DIRECTORY_NAME DIRECTORY_PATH ---------------------------------------- -------------- ------------------------------ READ DUMPME F:\Dump_me WRITE DUMPME F:\Dump_me SQL> SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options PS F:\oracle\product\10.2.0\db_1\BIN> |
四、源端：给被导出用户授权
这里，被导出的用户是：SCOTT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> col host_name for a13 SQL> select instance_name,status,host_name from v$instance; INSTANCE_NAME STATUS HOST_NAME ---------------- ------------ ------------- ORCL1 OPEN oel1 SQL> SQL> grant exp_full_database to scott; Grant succeeded. SQL> |
五、目标端：导出
导出目录：F:\Dump_me
导出前：
|
1 2 3 4 5 6 7 8 9 |
PS F:\Dump_me> pwd Path ---- F:\Dump_me PS F:\Dump_me> dir PS F:\Dump_me> |
开始导出：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
PS F:\oracle\product\10.2.0\db_1\BIN> .\expdp.exe system/oracle directory=dumpme dumpfile=dump_rac_orcl_node1_20140925_1 .dmp logfile=dump_rac_orcl_node1_20140925_1.log network_link='pwc2orcl1' Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 25 September, 2014 13:27:32 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dumpme dumpfile=dump_rac_orcl_node1_20140925_1.dmp logfile=dump_rac_orcl_node1_20140925_1.log network_link=pwc2orcl1 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB 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/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.687 KB 5 rows . . exported "SCOTT"."EMP" 7.820 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: F:\DUMP_ME\DUMP_RAC_ORCL_NODE1_20140925_1.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:28:08 PS F:\oracle\product\10.2.0\db_1\BIN> |
导出后：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
PS F:\Dump_me> dir 目录: F:\Dump_me Mode LastWriteTime Length Name ---- ------------- ------ ---- -a--- 2014/9/25 13:28 233472 DUMP_RAC_ORCL_NODE1_20140925_1.DMP -a--- 2014/9/25 13:28 1961 dump_rac_orcl_node1_20140925_1.log PS F:\Dump_me> |
文件：dump_rac_orcl_node1_20140925_1.log
至此，EXPDP的导出全部完成。
————————————————————————————
Ending。
