传输表空间
测试环境:
源库
ip:192.168.1.200 实例名:ORA11GR2
目标库:
IP:192.168.1.8 实例名:OCMU
1:【在ORA11GR2实例】创建目录对象
1 2 3 4 5 |
SYS@ORA11GR2>create or replace directory dir_ora11gr2 as '/home/oracle'; Directory created. SYS@ORA11GR2> |
2:【在ORA11GR2实例】创建测试表空间ts_ora11gr2及测试用户xxf和测试表t_ora11gr2
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 |
SYS@ORA11GR2>create tablespace ts_ora11gr2 datafile '/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf' size 20m; Tablespace created. SYS@ORA11GR2>grant connect,create table to xxf identified by xxf; Grant succeeded. SYS@ORA11GR2>conn xxf/xxf Connected. XXF@ORA11GR2>create table t_ora11gr2 tablespace ts_ora11gr2 as select * from all_objects; Table created. XXF@ORA11GR2>select count(*) from t_ora11gr2; COUNT(*) ---------- 71589 XXF@ORA11GR2>select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T_ORA11GR2 TS_ORA11GR2 XXF@ORA11GR2> |
3:【在ORA11GR2实例】将表空间ts_ora11gr2改为只读模式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
XXF@ORA11GR2>conn / as sysdba Connected. SYS@ORA11GR2>alter tablespace ts_ora11gr2 read only; Tablespace altered. SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2'; TABLESPACE_NAME STATUS ------------------------------ --------- TS_ORA11GR2 READ ONLY SYS@ORA11GR2>exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ocmu ~]$ |
4:【在ORA11GR2实例】利用数据泵导出表空间ts_ora11gr2结构信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@ocmu ~]$ expdp system/oracle dumpfile=ts_ora11gr2.dmp directory=dir_ora11gr2 transport_tablespaces=ts_ora11gr2 transport_full_check=Y Export: Release 11.2.0.1.0 - Production on Fri Apr 5 11:25:11 2013 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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ts_ora11gr2.dmp directory=dir_ora11gr2 transport_tablespaces=ts_ora11gr2 transport_full_check=Y Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/ts_ora11gr2.dmp ************************************************************************** Datafiles required for transportable tablespace TS_ORA11GR2: /u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:25:55 [oracle@ocmu ~]$ ls ts_ora11gr2* ts_ora11gr2.dmp [oracle@ocmu ~]$ |
5:【在OCMU实例】创建目录对象
1 2 3 4 5 |
SYS@OCMU>create or replace directory dir_ocmu as '/home/oracle'; Directory created. SYS@OCMU> |
6:将【ORA11GR2实例】数据泵导出的dmp文件scp到【OCMU实例】/home/oracle目录下
1 2 3 4 5 6 7 8 9 10 11 12 |
[oracle@ocmu ~]$ pwd /home/oracle [oracle@ocmu ~]$ scp 192.168.1.200:/home/oracle/ts_ora11gr2.dmp . The authenticity of host '192.168.1.200 (192.168.1.200)' can't be established. RSA key fingerprint is 35:b3:59:37:e6:a1:3b:34:7d:01:84:ee:5d:9b:48:24. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.200' (RSA) to the list of known hosts. oracle@192.168.1.200's password: ts_ora11gr2.dmp 100% 100KB 100.0KB/s 00:00 [oracle@ocmu ~]$ ls oradiag_oracle ts_ora11gr2.dmp [oracle@ocmu ~]$ |
7: 将【ORA11GR2实例】表空间ts_ora11gr2的数据文件scp到【OCMU实例】/u01/app/oracle/oradata/OCMU目录下
1 2 3 4 5 6 7 8 9 |
[oracle@ocmu OCMU]$ pwd /u01/app/oracle/oradata/OCMU [oracle@ocmu OCMU]$ scp 192.168.1.200:/u01/app/oracle/oradata/ORA11GR2/ts_ora11gr2_01.dbf . oracle@192.168.1.200's password: ts_ora11gr2_01.dbf 100% 20MB 20.0MB/s 00:01 [oracle@ocmu OCMU]$ ls ts_ora11gr2_01.dbf ts_ora11gr2_01.dbf [oracle@ocmu OCMU]$ |
8: 【在OCMU实例】利用数据泵导入表空间ts_ora11gr2结构信息,并将表空间ts_ora11gr2中的对象映射到hr用户下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[oracle@ocmu ~]$ impdp system/oracle dumpfile=ts_ora11gr2.dmp directory=dir_ocmu transport_datafiles=/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf remap_schema="(xxf:hr)" Import: Release 11.2.0.1.0 - Production on Fri Apr 5 11:43:37 2013 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 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=ts_ora11gr2.dmp directory=dir_ocmu transport_datafiles=/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf remap_schema=(xxf:hr) Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:43:50 [oracle@ocmu ~]$ |
9:【在OCMU实例】验证传输是否成功
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 |
SYS@OCMU>select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE EXAMPLE ONLINE TS_ORA11GR2 READ ONLY 7 rows selected. SYS@OCMU>select owner,table_name,tablespace_name from dba_tables where owner='HR' and table_name='T_ORA11GR2'; OWNER TABLE_NAME TABLESPACE_NAME ----------- ------------------ --------------- HR T_ORA11GR2 TS_ORA11GR2 SYS@OCMU>select count(*) from hr.t_ora11gr2; COUNT(*) ---------- 71589 SYS@OCMU> |
10:收尾工作【ORA11GR2实例】、【ORA11GR2实例】
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
【OCUM实例】 SYS@OCMU>alter tablespace ts_ora11gr2 read write; Tablespace altered. SYS@OCMU>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2'; TABLESPACE_NAME STATUS ------------------------------ --------- TS_ORA11GR2 ONLINE SYS@OCMU> 【ORA11GR2实例】 SYS@ORA11GR2>alter tablespace ts_ora11gr2 read write; Tablespace altered. SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_ORA11GR2'; TABLESPACE_NAME STATUS ------------------------------ --------- TS_ORA11GR2 ONLINE SYS@ORA11GR2> |