使用传输表空间方式将单实例数据库的表空间迁移至rac环境下

说明：将Windows下oracle（11.2.0.1.0）用户数据迁移到rac（centos系统）

一、源库（windows平台）下将表空间read only，拷贝该表空间的数据文件并导出元数据

SQL> alter tablespace TABLESPACE_BANK read only;

SQL> create directory dumpdir as 'E:\dump';

expdp 'sys/oracle as sysdba' directory=dumpdir dumpfile=bank.dmp logfile=bank.log transport_tablespaces=TABLESPACE_BANK    --注意：在Windows平台单引号不需要转义

二、将上面的表空间数据文件以及导出的元数据文件拷贝到rac的其中一个节点的相应目录下

三、在目标数据库上创建用户和目录对象，并利用rman将表空间的数据文件导入到asm实例上
SQL> create user bank identified by bank;

User created.

SQL> grant connect,resource to bank;

Grant succeeded.

SQL> create directory dumpdir as '/home/oracle/dump';

Directory created.

SQL> grant read,write on directory dumpdir to bank;

RMAN> convert datafile '/home/oracle/dump/BANK.DBF' format '+data/db2016/datafile/BANK.DBF';

Starting conversion at target at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 instance=DB20161 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/dump/BANK.DBF
converted datafile=+DATA/db2016/datafile/bank.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 13-MAY-16


四、利用impdp导入传输表空间的元数据

[oracle@centosnode1 ~]$ impdp \'sys/oracle as sysdba\' directory=dumpdir dumpfile=BANK.DMP transport_datafiles='+data/db2016/datafile/bank.dbf' remap_schema=userbank:bank remap_tablespace=tablespace_bank:bank

Import: Release 11.2.0.1.0 - Production on Fri May 13 01:23:13 2016

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 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "sys/******** AS SYSDBA" directory=dumpdir dumpfile=BANK.DMP transport_datafiles=+data/db2016/datafile/bank.dbf remap_schema=userbank:bank remap_tablespace=tablespace_bank:bank
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 01:23:52

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30373263/viewspace-2100226/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30373263/viewspace-2100226/