TTS实现跨版本迁移数据
浏览:3900次 出处信息
以前对Transportable Tablespaces(TTS)一直理解不深,今天无意中看到TTS可以实现数据库升级,今天测试了实现使用TTS 迁移9.2.0.4的一个表空间到11.2.0.3,平台均为Linux 32位
源端版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production |
创建测试环境
SQL> create tablespace tts_xff 2 datafile '/u01/oracle/oradata/xifenfei/tts_xifenfei01.dbf' size 10m autoextend on next 10m, 3 '/u01/oracle/oradata/xifenfei/tts_xifenfei02.dbf' size 10m autoextend on next 10m 4 ; Tablespace created. SQL> create user tts_xff identified by xifenfei; User created. SQL> grant dba to tts_xff; Grant succeeded. SQL> conn tts_xff/xifenfei Connected. SQL> create table t1 tablespace tts_xff 2 as 3 select * from dba_objects; Table created. SQL> create table t2 tablespace tts_xff 2 as 3 select * from dba_objects; Table created. SQL> create table t_xifenfei tablespace tts_xff 2 as 3 select * from dba_objects; Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- T1 TABLE T2 TABLE T_XIFENFEI TABLE SQL> select count (*) from t1; COUNT (*) ---------- 30805 SQL> conn / as sysdba Connected. SQL> alter tablespace tts_xff read only ; Tablespace altered. |
导出并传输测试表空间
[oracle@xifenfei ~]$ exp userid=\'/ as sysdba\' tablespaces=tts_xff file = /tmp/tts_xff .dmp transport_tablespace=y Export: Release 9.2.0.4.0 - Production on Sun Oct 7 04:53:25 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TTS_XFF ... . exporting cluster definitions . exporting table definitions . . exporting table T1 . . exporting table T2 . . exporting table T_XIFENFEI . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. [oracle@xifenfei ~]$ scp /tmp/tts_xff .dmp 192.168.1.10: /tmp/ oracle@192.168.1.10's password: tts_xff.dmp 100% 16KB 16.0KB /s 00:00 [oracle@xifenfei ~]$ scp /u01/oracle/oradata/xifenfei/tts_xifenfei * 192.168.1.10: /u01/oracle/oradata/ora11g/ oracle@192.168.1.10's password: tts_xifenfei01.dbf 100% 10MB 3.3MB /s 00:03 tts_xifenfei02.dbf 100% 10MB 5.0MB /s 00:02 |
目标库版本
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production |
创建用户
SQL> create user tts_11g identified by xifenfei; User created. SQL> grant dba to tts_11g; Grant succeeded. |
导入表空间
[oracle@xifenfei ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_xff file = /tmp/tts_xff .dmp > transport_tablespace=y datafiles= /u01/oracle/oradata/ora11g/tts_xifenfei01 .dbf, > /u01/oracle/oradata/ora11g/tts_xifenfei02 .dbf fromuser=tts_xff touser=tts_11g Import: Release 11.2.0.3.0 - Production on Sat Sep 29 04:18:04 2012 Copyright (c) 1982, 2011, Oracle and /or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V09.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing TTS_XFF's objects into TTS_11G . . importing table "T1" . . importing table "T2" . . importing table "T_XIFENFEI" Import terminated successfully without warnings. |
测试数据
SQL> alter tablespace tts_xff read write; Tablespace altered. SQL> conn tts_11g/xifenfei Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- T1 TABLE T2 TABLE T_XIFENFEI TABLE SQL> select count (*) from t1; COUNT (*) ---------- 30805 SQL> delete from t1; 30805 rows deleted. SQL> commit ; Commit complete. |
至此测试完成,证明使用tts可以实现跨版本迁移数据
补充说明
1.10g及其以上版本可以实现不同平台的tts迁移,可能需要使用rman convert转换
2.迁移前需要使用 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK检测依赖性
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:仅仅只备份是不够的
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: TTS
- 发布时间:2012-10-22 22:01:15
近3天十大热文
- [55] IOS安全–浅谈关于IOS加固的几种方法
- [55] Oracle MTS模式下 进程地址与会话信
- [54] 如何拿下简短的域名
- [53] android 开发入门
- [52] Go Reflect 性能
- [52] 图书馆的世界纪录
- [49] 读书笔记-壹百度:百度十年千倍的29条法则
- [48] 【社会化设计】自我(self)部分――欢迎区
- [38] 程序员技术练级攻略
- [32] 视觉调整-设计师 vs. 逻辑