sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle传输表空间 TTS实现跨版本迁移数据 9i to 11G

时间:2016-06-28 22:38   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇运维DBA在生产环境中迁移升级数据库案例,通过使用Oracle数据库传输表空间Transportable Tablespaces(TTS)实现9i to 11G迁移升级过程

实现使用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/oracleplus/tts_oracleplus01.dbf' size 10m autoextend on next 10m,
  3  '/u01/oracle/oradata/oracleplus/tts_oracleplus02.dbf' size 10m autoextend on next 10m
  4  ;

Tablespace created.

SQL> create user tts_xff identified by oracleplus;

User created.

SQL> grant dba to tts_xff;

Grant succeeded.

SQL> conn tts_xff/oracleplus
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_oracleplus  tablespace tts_xff
  2  as
  3  select * from dba_objects;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_oracleplus                     TABLE

SQL> select count(*) from t1;

  COUNT(*)
----------
     30805

SQL> conn / as sysdba 
Connected.
SQL> alter tablespace tts_xff read only;

Tablespace altered.

1.导出并传输测试表空间

[oracle@oracleplus ~]$ 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_oracleplus
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@oracleplus ~]$ 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@oracleplus ~]$ scp /u01/oracle/oradata/oracleplus/tts_oracleplus* 192.168.1.10:/u01/oracle/oradata/ora11g/
oracle@192.168.1.10's password: 
tts_oracleplus01.dbf                                                100%   10MB   3.3MB/s   00:03    
tts_oracleplus02.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 oracleplus;

User created.

SQL> grant dba to tts_11g;

Grant succeeded.

2.导入表空间

[oracle@oracleplus ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp
> transport_tablespace=y datafiles=/u01/oracle/oradata/ora11g/tts_oracleplus01.dbf, 
> /u01/oracle/oradata/ora11g/tts_oracleplus02.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_oracleplus"
Import terminated successfully without warnings.

测试数据

SQL> alter tablespace tts_xff read write;

Tablespace altered.

SQL> conn tts_11g/oracleplus
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_oracleplus                     TABLE

SQL> select count(*) from t1;

  COUNT(*)
----------
     30805

SQL> delete from t1;

30805 rows deleted.

SQL> commit;

Commit complete.

至此测试完成,证明使用tts可以实现跨版本迁移数据

3.传输表空间使用说明

1.10g及其以上版本可以实现不同平台的tts迁移,可能需要使用rman convert转换
2.迁移前需要使用 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK检测依赖性


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle传输表空间 TTS实现跨版本迁移数据 9i to 11G

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/617.html

Oracle研究中心

关键词:

Oracle传输表空间

TTS实现跨版本迁移数据

Oracle数据库使用传输表空间迁移升级案例

Oracle 9i to 11G使用TTS实现