@H_5020@<span style="color: #ff0000">前言
@H502_0@跨网络传输数据库,可以通过使用network_link参数来执行导入操作,导入操作将使用数据库链路,不需要生成dump文件。
@H_502_0@<span style="color: #ff0000">
操作步骤如下:
@H_502_0@1、在目标数据库中创建链接到源数据库的数据链路。执行导入操作的用户必须要有datapump_imp_full_database权限,并且连接到源数据库的数据链路也必须连接到一个有datapump_exp_full_database角色的用户。在源数据库中用户不能有sysdba管理权限。 @H_502_0@2、在源数据库上将所有用户表空间置为只读模式 @H_502_0@3、将源数据库中所有用户表空间相关的数据文件传输到目标数据库。如果源平台与目标平台的字节编码不同,那么查询v$transportable_platform视图来进行查看。并且将可以使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会自动将数据文件的字节编码转换为目标平台的字节编码。 @H_502_0@.使用rman的convert命令来将数据文件的字节编码转换为目标平台的字节编码。 @H_502_0@4、在目标数据库上执行导入操作。使用Data Pump工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。 @H_502_0@确保以下参数正确设置:
@H_502_0@1.在目标数据库中以sys用户来创建链接到源数据库的数据链路。源数据库中的用户为jy
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.
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.
@H_502_0@在源数据库中创建目录tts_datafile(存储数据文件)
Directory created.
sql> grant execute,read,write on directory tts_datafile to public;
Grant succeeded.
Directory created.
sql> grant execute,write on directory tts_datafile to public;
Grant succeeded.
PL/sql procedure successfully completed.
sql> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',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',destination_file_name => 'users01.dbf');
PL/sql procedure successfully completed.
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
@H_502_0@源数据库
sql> select count(*) from jy.dba_tables;
COUNT(*)
2141
sql> select count(*) from jy.dba_tables;
COUNT(*)
2141
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.
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.