之前对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_orcl
2 datafile '/u01/oracle/oradata/orcl/tts_orcl01.dbf' size 10m autoextend on next 10m,
3 '/u01/oracle/oradata/orcl/tts_orcl02.dbf' size 10m autoextend on next 10m
4 ;
Tablespace created.
SQL> create user tts_orcl identified by orcl;
User created.
SQL> grant dba to tts_orcl;
Grant succeeded.
SQL> conn tts_orcl/orcl
Connected.
SQL> create table t1 tablespace tts_orcl
2 as
3 select * from dba_objects;
Table created.
SQL> create table t2 tablespace tts_orcl
2 as
3 select * from dba_objects;
Table created.
SQL> create table t_orcl tablespace tts_orcl
2 as
3 select * from dba_objects;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
T2 TABLE
T_orcl TABLE
SQL> select count(*) from t1;
COUNT(*)
----------
30805
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts_orcl read only;
Tablespace altered.
导出并传输测试表空间
[oracle@orcl ~]$ exp userid=\'/ as sysdba\' tablespaces=tts_orcl file=/tmp/tts_orcl.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_orcl
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@orcl ~]$ scp /tmp/tts_orcl.dmp 192.168.1.10:/tmp/
oracle@192.168.1.10's password:
tts_orcl.dmp 100% 16KB 16.0KB/s 00:00
[oracle@orcl ~]$ scp /u01/oracle/oradata/orcl/tts_orcl* 192.168.1.10:/u01/oracle/oradata/ora11g/
oracle@192.168.1.10's password:
tts_orcl01.dbf 100% 10MB 3.3MB/s 00:03
tts_orcl02.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 orcl;
User created.
SQL> grant dba to tts_11g;
Grant succeeded.
导入表空间
[oracle@orcl ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_orcl file=/tmp/tts_orcl.dmp
> transport_tablespace=y datafiles=/u01/oracle/oradata/ora11g/tts_orcl01.dbf,
> /u01/oracle/oradata/ora11g/tts_orcl02.dbf fromuser=tts_orcl 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_orcl"
Import terminated successfully without warnings.
测试数据
SQL> alter tablespace tts_orcl read write;
Tablespace altered.
SQL> conn tts_11g/orcl
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
T2 TABLE
T_orcl 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检测依赖性