本地庫建立dblink
1，新增tnsname
[oracle@node1 ~]$ cd $ORACLE_HOME/dbs
[oracle@node1 dbs]$ ls
hc_DBUA0.dat init.ora lkTEST peshm_DBUA0_0 snapcf_test.f
hc_test.dat inittest.ora orapwtest peshm_test_0 spfile.ora
[oracle@node1 dbs]$ cd ..
[oracle@node1 db_1]$ cd network/admin
[oracle@node1 admin]$ vi tnsnames.ora
testdb=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=testdb)
(SERVER=dedicated)
)
)
2，本地庫建立dblink
SYS@ test>CREATE PUBLIC DATABASE LINK "testdb"
2 CONNECT TO scott
3 IDENTIFIED BY "oracle"
4 USING 'testdb';
3，資源庫開啟監聽
[oracle@solaris102:/export/home/oracle]$ lsnrctl start
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:07
Copyright (c) 1991, 2011, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
[oracle@solaris102:/export/home/oracle]$ lsnrctl status
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 24-MAY-2014 15:21:13
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 24-MAY-2014 15:20:39
Uptime 0 days 0 hr. 0 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/solaris102/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.102)(PORT=1521)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
4，檢視本地庫的目錄
SYS@ test>select * from dba_directories;
SYS DUMP_DIR
/home/oracle/dump_dir

5，資源庫上建立一張表
scott@TESTDB>select count(*) from test;
COUNT(*)
----------
14257
如果匯出不成功需要在資源庫上執行
grant這個命令要在源端資料庫上面執行
grant exp_full_database to scott
6，在本地匯出資源庫的test表
[oracle@node1 dump_dir]$ expdp scott/oracle directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test

Export: Release 11.2.0.1.0 - Production on Sat May 24 15:34:00 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dump_dir dumpfile=test.dmp network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 1.269 MB 14257 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dump_dir/test.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:34:04

7，將資源庫的test表直接匯入到本地庫
[oracle@node1 dump_dir]$ impdp scott/oracle directory=dump_dir network_link=testdb tables=test
Import: Release 11.2.0.1.0 - Production on Sat May 24 15:37:36 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dump_dir network_link=testdb tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."TEST" 14257 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 15:37:42

[oracle@node1 dump_dir]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 24 15:38:05 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
@ >conn scott/oracle
Connected.
SCOTT@ test>select count(*) from test;
COUNT(*)
----------
14257

刪除dblink
SYS@ test>drop public database link "testdb";
Database link dropped.

重新建立dblink
SYS@ test>create public database link "testdb" connect to system identified by "oracle" using 'testdb';
Database link created.

expdp 和network 不落地資料遷移

相關文章