oracle tts exp,TTS实现跨版本迁移数据 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788...-CSDN博客

以前对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

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

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检测依赖性