跨网络传输数据库,可以通过使用 network_link 参数来执行导入操作,导入操作将使用数据库链路,不需要生成 dump 文件。操作步骤如下: 1. 在目标数据库中创建链接到源数据库的数据链路。执行导入操作的用户必须要有 datapump_imp_full_database 权限,并且连接到源数据库的数据链路也必须连接到一个有 datapump_exp_full_database 角色的用户。在源数据库中用户不能有 sysdba 管理权限。
2. 在源数据库上将所有用户表空间置为只读模式
3. 将源数据库中所有用户表空间相关的数据文件传输到目标数据库。如果源平台与目标平台的字节编码不同,那么查询 v$transportable_platform 视图来进行查看。并且将可以使用以下一种方法来转换数据文件:. 使用 dbms_file_transfer 包中的 get_file 或 put_file 过程来传输数据文件。这些过程会自动将数据文件的字节编码转换为目标平台的字节编码。
. 使用 rman 的 convert 命令来将数据文件的字节编码转换为目标平台的字节编码。
4. 在目标数据库上执行导入操作。使用 Data Pump 工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。确保以下参数正确设置:.transportable=always.transport_datafiles=list_of_datafiles.full=y.network_link=database_link.version=12 如果源数据库为 11.2.0.3 或 11g 之后的版本,那么必须设置 version=12。如果源数据库与目标数据库都是 12c,那么 version 参数不用设置。
如果源数据库包含任何加密表空间或表空间包含加密列,那么你必须指定 encryption_pwd_prompt=yes 或指定 encryption_password 参数。
Data Pump 跨网络导入将会复制所有用户表空间所存储对象的元数据与管理表空间中的元与用户对象的真实数据。当导入完成后,用户表空间将会置于读写模式。
5. 可选操作将源数据库中的所有用户表空间置为读写模式。
下面的例子是将源数据库 jyrac 传输到目标数据库 jypdb1. 在目标数据库中以 sys 用户来创建链接到源数据库的数据链路。源数据库中的用户为 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. 在源数据库上将所有用户表空间置为只读模式
- 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. 在目标数据库中使用 dbms_file_transfer 包中的 get_file 过程将源数据库中所有用户表空间相关的数据文件传输到目标数据库上在源数据库中创建目录 tts_datafile(存储数据文件)
- 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.
在目标数据库中创建目录 tts_datafile(存储数据文件)
- 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.
在目标数据库中执行 dbms_file_transfer.get_file 过程将源数据库中所有用户表空间所相关的数据文件传输到目标数据库中
- 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. 在目标数据库上执行导入操作。使用 Data Pump 工具来导入所有用户表空间的元数据与管理表空间的元数据与真实数据。
- [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 表的数据在传输后是否与源数据库中的数据一致。源数据库
- SQL> conn sys/xxzx7817600@jyrac as sysdba
- Connected.
- SQL> select count(*) from jy.dba_tables;
- COUNT(*)
- ----------
- 2141
目标数据库
- SQL> conn sys/xxzx7817600@jypdb as sysdba
- Connected.
- SQL> select count(*) from jy.dba_tables;
- COUNT(*)
- ----------
- 2141
查询传输后用户表空间的状态是否为 online,可以看到 test,example,users 表空间状态为 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. 将源数据库中的所有用户表空间设置为读写模式
- 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.
到此通过网络执行完整数据库传输的操作就完成了。
来源: http://www.linuxidc.com/Linux/2017-06/144501.htm