分类： Oracle
2017-07-12 14:45:32
使用可传输表空间向rac环境迁移数据
可传输表空间,是一种快速的转移数据的方式,基本过程如下:
1、检查表空间的自包含性
2、使用exp(expdp)导出表空间的元数据,这个过程很快
3、将表空间置于read only模式，防止数据写入
4、将包含元数据的dmp文件和表空间数据文件拷贝到目标库上
5、使用imp(imdp)导入到元数据
如果与目标库的数据文件路径不一致、或字节顺序不一致，或可以通过rman工具进行转换。字节顺序是指存储多字节二进制数据的方式,可以查询字节顺序：
SQL> select * from v$transportable_platform order by platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Big
10 IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
上述平台中，只要字节顺序相同，数据文件就不需要转换，如linux 64bit到windows平台。
可传输表空间有如下限制：
1、源和目标必须处于相同的平台，在10g中，这条约束被放宽了；
2、源和目标数据库必须使用相同的字符集和国家字符集；
3、目标数据库不能已经包含同名的表空间；
4、目标数据库中必须已经存在传输表空间中对象的用户
5、传输表空间必须是自包含的,否则需要相关的表空间一起传输,比如:表在一个表空间,而索引在另外一个表空间,这两个表空间必须一起传输.
6、传输表空间无法处理同义词、用户、权限
下面是 10g for windows单实例到 11g rac for linux环境的表空间传输的实验：
环境如下:
源 库: oracle 10g R2 for Windows 32bit
目标库: oracle 11g RAC for linux as4 64bit
-------------------------------------------------------------------------------------------------------------------------------------
1.创建表空间
SQL> create tablespace ts_test datafile 'D:/oracle/product/10.2.0/oradata/orcl/ts_test.dbf' size 10m;
Tablespace created
SQL> create table test tablespace ts_test as select * from dba_objects;
Table created
SQL> select count(*) from test;
COUNT(*)
----------
53390
SQL>
2.设置为只读模式
SQL> alter tablespace ts_test read only;
Tablespace altered
3.检查表空间自包含性,是完全自包含的:
SQL> exec sys.dbms_tts.transport_set_check('ts_test',true);
PL/SQL procedure successfully completed
SQL> select * from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
4.使用exp或exdp导出表空间的元数据
C:/>exp 'sys/zlsoft as sysdba' tablespaces=ts_test transport_tablespace=y file=d:/backup/exp_ts_test.dmp
Export: Release 10.2.0.1.0 - Production on 星期二 9月 9 16:01:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 TS_TEST...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 TEST
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。
5. 将dmp文件和数据文件拷贝到linux服务器上
6. 使用rman进行数据文件格式转换,由于RAC环境使用了ASM,只有通过rman工具将数据文件加入到ASM中，这里ASM磁盘组的名称为DATA
RMAN> convert datafile '/backup/TS_TEST.DBF' format '+DATA/orcl/datafile/ts_test01.dbf';
Starting conversion at target at 09-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 instance=orcl1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=122 instance=orcl1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=106 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/backup/TS_TEST.DBF
converted datafile=+DATA/orcl/datafile/ts_test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 09-SEP-08
Starting Control File and SPFILE Autobackup at 09-SEP-08
piece handle=+DATA/orcl/autobackup/2008_09_09/s_664992614.411.664992623 comment=NONE
Finished Control File and SPFILE Autobackup at 09-SEP-08
注意:
从10g开始,支持低版本数据文件到高版本的传输,9i->10g->11g,但只能跨越一个大的版本,也就是说11g不能识别9i的数据文件。否则在用rman进行转换时,会出现如下的错误:
RMAN> convert datafile '/backup/TS_TEST01.DBF' format '+DATA/orcl/datafile/ts_test01.dbf';
Starting conversion at target at 09-SEP-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of conversion at target command at 09/09/2008 15:48:55
ORA-19942: datafile copy /backup/TS_TEST01.DBF not created with compatibility 10.0 or greater
7.在目标库执行imp操作,注意指定正确的datafiles参数
[oracle@rac1 backup]$ imp file='/backup/exp_ts_test.dmp' tablespaces=ts_test transport_tablespace=y datafiles='+DATA/orcl/datafile/ts_test01.dbf'
Import: Release 11.1.0.6.0 - Production on Tue Sep 9 16:24:06 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: sys/zlsoft as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ZLHIS's objects into ZLHIS
. . importing table "TEST"
. importing SYS's objects into SYS
Import terminated successfully with warnings.
[oracle@rac1 backup]$
--验证一下
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
TOOLS
HXB
TEST
TS_TEST
10 rows selected.
SQL> select count(*) from zlhis.test;
COUNT(*)
----------
53390
SQL>
最后就是记得将源库的表空间设置为read write模式
