在上一节中，介绍了使用expdp/impdp方式迁移单实例数据库至rac环境，本节中将简要的介绍下，使用传输表空间方式将单实例数据库的表空间迁移至rac环境下，同样需要借助expdp/impdp数据泵实现，但同上一节所演示的结果相比，在大数据量情况下，使用传输表空间方式迁移，可以节约许多时间，但前途是数据库的字符集要一致，且需要将准备迁移的表空间进行只读操作！
环境介绍：
数据库的版本均为10.2.0.5，数据库字符集编码为utf8
操作系统的版本单实例数据库(源库)为rhel5.4 64 bit
rac(目标数据库)为ceontos4.8 64bit
一：在源库上创建表空间，用户，建表，插入数据，建索引以及目录对象等
| SQL> create tablespace exp_rac1 datafile SQL> create tablespace exp_rac1_index datafile SQL> create user test2 identified by oracle SQL> grant connect,resource to test2; SQL> create table test2.source2 as select * from dba_source; SQL> insert into test2.source2 select * from test2.source2; SQL> / SQL> commit; SQL> exec dbms_stats.gather_table_stats('TEST2','SOURCE2'); SQL> select count(*) from test2.source2; COUNT(*) SQL> select sum(bytes/(1024*1024)) MB from dba_extents MB SQL> create index test2.i_source2 SQL> select table_name,tablespace_name from dba_indexes TABLE_NAME TABLESPACE_NAME SQL> select sum(bytes/(1024*1024)) MB from dba_extents MB SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2'; SQL> grant read,write on directory expdp_dir2 to test2; SQL> !mkdir -p /home/oracle/expdp_dir2 |
二：将需要迁移的表空间只读，并拷贝表空间数据文件，如果在asm环境下，可以使用rman操作
| SQL> alter tablespace exp_rac1 read only; SQL> alter tablespace exp_rac1_index read only; [oracle@server49 ~]$ cd /home/oracle/expdp_dir2/ |
三：使用expdp命令导出传输表空间的元数据并复制到目标数据库上(rac环境)
| [oracle@server49 ~]$ expdp \'sys/123456 as sysdba\' directory=expdp_dir2 dumpfile=source2.dmp logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index Export: Release 10.2.0.5.0 - 64bit Production on Sunday, 01 January, 2012 22:44:51 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production dumpfile=source2.dmp logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index [oracle@server49 ~]$ ls /home/oracle/expdp_dir2/ |
四：在目标数据库上创建用户和目录对象，并利用rman将表空间的数据文件导入到asm实例上
| [oracle@rac1 ~]$ sqlplus sys/123456@racdb1 as sysdba Connected to: SQL> create user test2 identified by oracle account unlock; SQL> grant connect,resource to test2; SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2'; SQL> grant read,write on directory expdp_dir2 to test2; RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_01.dbf' format '+DATA/racdb/datafile/exp_rac1_01.dbf'; Starting backup at 2012-01-01 23:41:49 RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_index_01.dbf' format '+DATA/racdb/datafile/exp_rac1_index_01.dbf'; Starting backup at 2012-01-01 23:44:02 ASMCMD> pwd |
五：利用impdp导入传输表空间的元数据
| [oracle@rac1 ~]$ impdp \'sys/123456 as sysdba\' directory=expdp_dir2 dumpfile=source2.dmp transport_datafiles='+DATA/racdb/datafile/exp_rac1_01.dbf','+DATA/racdb/datafile/exp_rac1_index_01.dbf' Import: Release 10.2.0.5.0 - 64bit Production on Monday, 02 January, 2012 0:08:33 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production dumpfile=source2.dmp transport_datafiles=+DATA/racdb/datafile/exp_rac1_01.dbf, +DATA/racdb/datafile/exp_rac1_index_01.dbf |
六：测试
| [oracle@rac1 ~]$ sqlplus sys/123456@racdb as sysdba Connected to: SQL> select count(*) from test2.source2; COUNT(*) SQL> col table_name format a20 TABLE_NAME TABLESPACE_NAME SQL> select sum(bytes/(1024*1024)) MB from dba_extents MB SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME 本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/757707如需转载请自行联系原作者 ylw6006 |
