当前位置： 首页 > Oracle, oracle 10g > 正文

传输表空间(TTS transport tablespace)传输多个表空间

May282014
作者：dbdream   发布：2014-05-28 15:32   分类：Oracle, oracle 10g     抢沙发  

客户有一台服务器，上面建了两个实例，打算整合成一个库，两个库没有同名的用户、表空间、数据文件，这就很好弄了，EXP/EXPDP导入导出就可以，传输表空间(TTS transport tablespace)也可以，考虑到客户的服务器没有存放备份的空闲空间，所以使用传输表空间(TTS transport tablespace)来做这个事情。
由于客户环境表空间较多，单个表空间传输肯定不行，所以，把需要的表空间一起迁移过去。而且，两个实例的block szie不一样，需要设置参数。
测试环境：OEL5.7 ORACLE10.2.0.4.0
迁出实例EOSP，block szie 16K
迁入实例source，block szie 8K
下面进行测试，EOSP数据库创建测试用户和表空间。

SQL> create tablespace test1 datafile '/data/eosp/test01.dbf' size 10M;

Tablespace created.

SQL> create tablespace test2 datafile '/data/eosp/test02.dbf' size 10M;

Tablespace created.

SQL> create tablespace test3 datafile '/data/eosp/test03.dbf' size 10M;

Tablespace created.

SQL> create tablespace test4 datafile '/data/eosp/test04.dbf' size 10M;

Tablespace created.

SQL> create tablespace index1 datafile '/data/eosp/index01.dbf' size 10M;

Tablespace created.

SQL> create user user1 identified by user1 default tablespace test1;

User created.

SQL> create user user2 identified by user2 default tablespace test2;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> grant connect,resource to user2;

Grant succeeded.

user1用户创建测试数据，创建分区表分布在test1、test2、test3、test4表空间上。

SQL> conn user1/user1
Connected.

SQL> create table test1(id number,name varchar2(10))
  2  partition by list(id)
  3  (partition p1 values(1) tablespace test1,
  4  partition p2 values(2) tablespace test2,
  5  partition p3 values(3) tablespace test3,
  6  partition p4 values(default) tablespace test4);

Table created.

SQL> insert into test1 values(1,'zhangsan');

1 row created.

SQL> insert into test1 values(2,'lisi');

1 row created.

SQL> insert into test1 values(3,'wangwu');

1 row created.

SQL> insert into test1 values(4,'zhaoliu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1 partition(p1);

        ID NAME
---------- ----------
         1 zhangsan

SQL> select * from test1 partition(p2);

        ID NAME
---------- ----------
         2 lisi

SQL> select * from test1 partition(p3);

        ID NAME
---------- ----------
         3 wangwu

SQL> select * from test1 partition(p4);

        ID NAME
---------- ----------
         4 zhaoliu


SQL> create index ind1 on test1(id) local tablespace index1;

Index created.

SQL> select segment_name,partition_name,tablespace_name from dba_segments where owner='USER1';

SEGMENT_NAME         PARTITION_NAME                 TABLESPACE_NAME
-------------------- ------------------------------ ------------------------------
TEST1                P1                             TEST1
TEST1                P2                             TEST2
TEST1                P3                             TEST3
TEST1                P4                             TEST4
IND1                 P4                             INDEX1
IND1                 P3                             INDEX1
IND1                 P2                             INDEX1
IND1                 P1                             INDEX1

user2用户重复上面的操作即可，同样数据会分散到所有的这些表空间上。

SQL> conn user2/user2
Connected.
SQL>
SQL>
SQL> create table test1(id number,name varchar2(10))
  2  partition by list(id)
  3  (partition p1 values(1) tablespace test1,
  4  partition p2 values(2) tablespace test2,
  5  partition p3 values(3) tablespace test3,
  6  partition p4 values(default) tablespace test4);

Table created.

SQL> insert into test1 values(1,'zhangsan');

1 row created.

SQL> insert into test1 values(2,'lisi');

1 row created.

SQL> insert into test1 values(3,'wangwu');

1 row created.

SQL> insert into test1 values(4,'zhaoliu');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> create index ind1 on test1(id) local tablespace index1;

Index created.

这时候如果只迁移一个表空间是不可以的，检查时就会给出提示。

SQL> conn / as sysdba
Connected.
SQL> exec sys.dbms_tts.transport_set_check('test1',true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------
Default Partition (Table) Tablespace TEST2 for TEST1 not contained in transportable set
Partitioned table USER1.TEST1 is partially contained in the transportable set: check table partition
s by querying sys.dba_tab_partitions

Default Partition (Table) Tablespace TEST3 for TEST1 not contained in transportable set
Default Partition (Table) Tablespace TEST2 for TEST1 not contained in transportable set
Default Partition (Table) Tablespace TEST4 for TEST1 not contained in transportable set
Partitioned table USER2.TEST1 is partially contained in the transportable set: check table partition
s by querying sys.dba_tab_partitions

所以，需要将这些表空间一起传输过去才可以，一起传输校验，就不会有问题。

SQL> exec sys.dbms_tts.transport_set_check('TEST1,TEST2,TEST3,TEST4,INDEX1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

下面开始测试，迁移前，需要把需要迁移的表空间设置成只读模式，防止数据文件在迁移过程中被修改。

SQL> alter tablespace test1 read only;

Tablespace altered.

SQL> alter tablespace test2 read only;

Tablespace altered.

SQL> alter tablespace test3 read only;

Tablespace altered.

SQL> alter tablespace test4 read only;

Tablespace altered.

SQL> alter tablespace index1 read write

Tablespace altered.

然后就可以通过exp或expdp导出这些表空间的元数据了。

[oracle@source ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@source ~]$ exp '/ as sysdba' transport_tablespace=y tablespaces=test1,test2,test3,test4,index1 file=/home/oracle/tts.dmp

Export: Release 10.2.0.4.0 - Production on Fri May 23 22:50:08 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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 TEST1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          TEST1
. . exporting table                          TEST1
For tablespace TEST2 ...
. exporting cluster definitions
. exporting table definitions
For tablespace TEST3 ...
. exporting cluster definitions
. exporting table definitions
For tablespace TEST4 ...
. exporting cluster definitions
. exporting table definitions
For tablespace INDEX1 ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

将这些表空间对应的数据文件复制(或者move)到迁入数据库(source)的目录下(也可以在当前路径下)。

[oracle@source source]$ cd /u01/app/oracle/oradata/source
[oracle@source source]$ cp /data/eosp/test0* .
[oracle@source source]$ cp /data/eosp/index01.dbf .

迁入数据库创建用户并授权。

SQL> create user user1 identified by user1;

User created.

SQL> create user user2 identified by user2;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> grant connect,resource to user2;

Grant succeeded.

开始迁入表空间。

[oracle@source source]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@source source]$ imp '/ as sysdba' file=/home/oracle/tts.dmp fromuser=user1 touser=user1 fromuser=user2 touser=user2 transport_tablespace=y tablespaces=test1 datafiles=/u01/app/oracle/oradata/source/test01.dbf tablespaces=test2 datafiles=/u01/app/oracle/oradata/source/test02.dbf tablespaces=test3 datafiles=/u01/app/oracle/oradata/source/test03.dbf tablespaces=test4 datafiles=/u01/app/oracle/oradata/source/test04.dbf tablespaces=index1 datafiles=/u01/app/oracle/oradata/source/index01.dbf

Import: Release 10.2.0.4.0 - Production on Fri May 23 23:03:52 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, 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 ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing USER1's objects into USER1
. . importing table                        "TEST1"
. importing USER2's objects into USER2
. . importing table                        "TEST1"
Import terminated successfully without warnings.

数据验证。

SQL> conn user1/user1
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST1                          TABLE

SQL> select * from test1;

        ID NAME
---------- ----------
         1 zhangsan
         2 lisi
         3 wangwu
         4 zhaoliu

SQL> select segment_name,tablespace_name from dba_segments where owner='USER1';

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
TEST1                TEST1
TEST1                TEST2
TEST1                TEST3
TEST1                TEST4
IND1                 INDEX1
IND1                 INDEX1
IND1                 INDEX1
IND1                 INDEX1

8 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where owner='USER2';

SEGMENT_NAME         TABLESPACE_NAME
-------------------- ------------------------------
TEST1                TEST1
TEST1                TEST2
TEST1                TEST3
TEST1                TEST4
IND1                 INDEX1
IND1                 INDEX1
IND1                 INDEX1
IND1                 INDEX1

8 rows selected.

SQL> select name from v$datafile;

NAME
-------------------------------------------------
/u01/app/oracle/oradata/source/system01.dbf
/u01/app/oracle/oradata/source/undotbs01.dbf
/u01/app/oracle/oradata/source/sysaux01.dbf
/u01/app/oracle/oradata/source/users01.dbf
/u01/app/oracle/oradata/source/ggadm01.dbf
/u01/app/oracle/oradata/source/test01.dbf
/u01/app/oracle/oradata/source/test02.dbf
/u01/app/oracle/oradata/source/test03.dbf
/u01/app/oracle/oradata/source/test04.dbf
/u01/app/oracle/oradata/source/index01.dbf

10 rows selected.

迁出库(EOSP)将表空间设置为读写模式。

SQL> alter tablespace test1 read write;

Tablespace altered.

SQL> alter tablespace test2 read write;

Tablespace altered.

SQL> alter tablespace test3 read write

Tablespace altered.

SQL> alter tablespace test4 read write

Tablespace altered.

SQL> alter tablespace index1 read write

Tablespace altered.

然后关闭迁出库(EOSP)，修改应用连接的服务名，验证应用是否可正常访问数据库。

———————————————————-end———————————————-
—

本文固定链接: https://www.dbdream.com.cn/2014/05/%e4%bc%a0%e8%be%93%e8%a1%a8%e7%a9%ba%e9%97%b4tts-transport-tablespace%e4%bc%a0%e8%be%93%e5%a4%9a%e4%b8%aa%e8%a1%a8%e7%a9%ba%e9%97%b4/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2014年05月28日发表在 Oracle, oracle 10g 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 传输表空间(TTS transport tablespace)传输多个表空间 | 信春哥，系统稳，闭眼上线不回滚！
关键字: , , , ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

传输表空间(TTS transport tablespace)传输多个表空间：等您坐沙发呢！

发表评论

快捷键：Ctrl+Enter