前言
跨網(wǎng)絡(luò)傳輸數(shù)據(jù)庫，可以通過使用network_link參數(shù)來執(zhí)行導(dǎo)入操作，導(dǎo)入操作將使用數(shù)據(jù)庫鏈路，不需要生成dump文件。
操作步驟如下:
1、在目標(biāo)數(shù)據(jù)庫中創(chuàng)建鏈接到源數(shù)據(jù)庫的數(shù)據(jù)鏈路。執(zhí)行導(dǎo)入操作的用戶必須要有datapump_imp_full_database權(quán)限，并且連接到源數(shù)據(jù)庫的數(shù)據(jù)鏈路也必須連接到一個有datapump_exp_full_database角色的用戶。在源數(shù)據(jù)庫中用戶不能有sysdba管理權(quán)限。
2、在源數(shù)據(jù)庫上將所有用戶表空間置為只讀模式
3、將源數(shù)據(jù)庫中所有用戶表空間相關(guān)的數(shù)據(jù)文件傳輸?shù)侥繕?biāo)數(shù)據(jù)庫。如果源平臺與目標(biāo)平臺的字節(jié)編碼不同，那么查詢v$transportable_platform視圖來進(jìn)行查看。并且將可以使用以下一種方法來轉(zhuǎn)換數(shù)據(jù)文件：
.使用dbms_file_transfer包中的get_file或put_file過程來傳輸數(shù)據(jù)文件。這些過程會自動將數(shù)據(jù)文件的字節(jié)編碼轉(zhuǎn)換為目標(biāo)平臺的字節(jié)編碼。
.使用rman的convert命令來將數(shù)據(jù)文件的字節(jié)編碼轉(zhuǎn)換為目標(biāo)平臺的字節(jié)編碼。
4、在目標(biāo)數(shù)據(jù)庫上執(zhí)行導(dǎo)入操作。使用Data Pump工具來導(dǎo)入所有用戶表空間的元數(shù)據(jù)與管理表空間的元數(shù)據(jù)與真實(shí)數(shù)據(jù)。
確保以下參數(shù)正確設(shè)置:
.transportable=always
.transport_datafiles=list_of_datafiles
.full=y
.network_link=database_link
.version=12
如果源數(shù)據(jù)庫為11.2.0.3或11g之后的版本，那么必須設(shè)置version=12。如果源數(shù)據(jù)庫與目標(biāo)數(shù)據(jù)庫都是12c，那么version參數(shù)不用設(shè)置。
如果源數(shù)據(jù)庫包含任何加密表空間或表空間包含加密列，那么你必須指定encryption_pwd_prompt=yes或指定encryption_password參數(shù)。
Data Pump跨網(wǎng)絡(luò)導(dǎo)入將會復(fù)制所有用戶表空間所存儲對象的元數(shù)據(jù)與管理表空間中的元與用戶對象的真實(shí)數(shù)據(jù)。當(dāng)導(dǎo)入完成后，用戶表空間將會置于讀寫模式。
5、可選操作將源數(shù)據(jù)庫中的所有用戶表空間置為讀寫模式。
下面的例子是將源數(shù)據(jù)庫jyrac傳輸?shù)侥繕?biāo)數(shù)據(jù)庫jypdb
1.在目標(biāo)數(shù)據(jù)庫中以sys用戶來創(chuàng)建鏈接到源數(shù)據(jù)庫的數(shù)據(jù)鏈路。源數(shù)據(jù)庫中的用戶為jy
SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.
SQL> create public database link jyrac_link
2 connect to jy identified by "jy"
3 using '(DESCRIPTION =
4 (ADDRESS_LIST =
5 (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
6 )
7 (CONNECT_DATA =
8 (SERVER = DEDICATED)
9 (SERVICE_NAME =jyrac)
10 )
11 )';
Database link created.
2.在源數(shù)據(jù)庫上將所有用戶表空間置為只讀模式
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TEST ONLINE
8 rows selected.
SQL> alter tablespace test read only;
Tablespace altered.
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace example read only;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY
UNDOTBS2 ONLINE
EXAMPLE READ ONLY
TEST READ ONLY
8 rows selected.
3.在目標(biāo)數(shù)據(jù)庫中使用dbms_file_transfer包中的get_file過程將源數(shù)據(jù)庫中所有用戶表空間相關(guān)的數(shù)據(jù)文件傳輸?shù)侥繕?biāo)數(shù)據(jù)庫上
在源數(shù)據(jù)庫中創(chuàng)建目錄tts_datafile（存儲數(shù)據(jù)文件)
SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';
Directory created.
SQL> grant execute,read,write on directory tts_datafile to public;
Grant succeeded.
在目標(biāo)數(shù)據(jù)庫中創(chuàng)建目錄tts_datafile（存儲數(shù)據(jù)文件)
SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';
Directory created.
SQL> grant execute,read,write on directory tts_datafile to public;
Grant succeeded.
在目標(biāo)數(shù)據(jù)庫中執(zhí)行dbms_file_transfer.get_file過程將源數(shù)據(jù)庫中所有用戶表空間所相關(guān)的數(shù)據(jù)文件傳輸?shù)侥繕?biāo)數(shù)據(jù)庫中
SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf');
PL/SQL procedure successfully completed.
SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf');
PL/SQL procedure successfully completed.
SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf');
PL/SQL procedure successfully completed.
ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE JUN 02 16:00:00 N users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE UNPROT COARSE JUN 02 16:00:00 N test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE UNPROT COARSE JUN 02 16:00:00 N example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.300.945620337
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.299.945620391
DATAFILE UNPROT COARSE JUN 02 16:00:00 Y FILE_TRANSFER.298.945620417
DATAFILE UNPROT COARSE JUN 02 00:00:00 N testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDO_2.277.939167063
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS2.278.945029905
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y UNDOTBS1.273.939167015
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y TESTTB.295.944828399
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSTEM.274.939167015
DATAFILE UNPROT COARSE JUN 02 00:00:00 Y SYSAUX.275.939167015
4.在目標(biāo)數(shù)據(jù)庫上執(zhí)行導(dǎo)入操作。使用Data Pump工具來導(dǎo)入所有用戶表空間的元數(shù)據(jù)與管理表空間的元數(shù)據(jù)與真實(shí)數(shù)據(jù)。
[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' version=12 directory=TTS_DUMP_LOG logfile=import.log
Import: Release 12.2.0.1.0 - Production on Fri Jun 2 16:30:40 2017
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 "SYSTEM"."SYS_IMPORT_FULL_01": system/********@JYPDB_175 full=y network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf version=12 directory=TTS_DUMP_LOG logfile=import.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
.......
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
ORA-39082: Object type PROCEDURE:"APEX_030200"."F" created with compilation warnings
ORA-39082: Object type PROCEDURE:"APEX_030200"."APEX_ADMIN" created with compilation warnings
ORA-39082: Object type PROCEDURE:"APEX_030200"."HTMLDB_ADMIN" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1689 error(s) at Fri Jun 2 16:43:47 2017 elapsed 0 00:13:03
傳輸完成后我們抽查用戶jy的dba_tables表的數(shù)據(jù)在傳輸后是否與源數(shù)據(jù)庫中的數(shù)據(jù)一致。
源數(shù)據(jù)庫
SQL> conn sys/xxzx7817600@jyrac as sysdba
Connected.
SQL> select count(*) from jy.dba_tables;
COUNT(*)
----------
2141
目標(biāo)數(shù)據(jù)庫
SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.
SQL> select count(*) from jy.dba_tables;
COUNT(*)
----------
2141
查詢傳輸后用戶表空間的狀態(tài)是否為online，可以看到test,example,users表空間狀態(tài)為online
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
UNDO_2 ONLINE
USERS ONLINE
TESTTB ONLINE
TEMP2 ONLINE
TEMP3 ONLINE
EXAMPLE ONLINE
TEST ONLINE
UNDOTBS2 ONLINE
12 rows selected.
5.將源數(shù)據(jù)庫中的所有用戶表空間設(shè)置為讀寫模式
SQL> alter tablespace test read write;
Tablespace altered.
SQL> alter tablespace example read write;
Tablespace altered.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TEST ONLINE
8 rows selected.
到此通過網(wǎng)絡(luò)執(zhí)行完整數(shù)據(jù)庫傳輸?shù)牟僮骶屯瓿闪恕?/p>
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- PLSQL Developer連接oracle數(shù)據(jù)庫配置教程
- PLSQL Developer登錄的默認(rèn)密碼介紹
- Plsql Developer連接Oracle時出現(xiàn)Could not initialize oci.dll解決方案
- Windows 64位下裝安裝Oracle 11g，PLSQL Developer的配置問題,數(shù)據(jù)庫顯示空白的完美解決方案(圖文教程)
- 通過PLSQL Developer創(chuàng)建Database link,DBMS_Job,Procedure,實(shí)現(xiàn)Oracle跨庫傳輸數(shù)據(jù)的方法(推薦)
