Oracle transport tablespace

本来没想过发布这个文章，只是周边有一朋友工作中遇到合并数据库的情况，他是通过expdp提取出五个库对象，然后impdp到新库里面。我觉得这种方法特别耗时，尤其在数据量比较大的时候。这种时候我觉得采用表空间传输方法效率更高点，当然我们还可以使用RMAN DUPLICATE方式。下面就把TRANSPORT TABLESPACE一测试案例贴出来分享。
 


传输表空间有三个基本要求：
1）要传输的表空间是自包含的；
2）要传输的表空间是只读的；
3）源数据库与目标数据库的字符集相同。


跨平台表空间传输还需要考虑字节顺序和平台。
引用eygle的原文：
数据文件之所以不能跨平台，主要是由于不同平台的字节顺序不同。
在各种计算机体系结构中，由于对于字、字节等的存储机制有所不同，通信双方交流的信息单元(比特、字节、字、双字等)应该以什么样的顺序进行传送就成了一个问题，如果不达成一致的规则，通信双方将无法进行正确的编译码从而导致通信失败。
 
查看当前平台信息方法：


Col platform_name for a40
Set line 120


SELECT A.platform_id, A.platform_name, B.endian_format
  FROM   v$database A, v$transportable_platform B
  WHERE  B.platform_id (+) = A.platform_id;
 
我们也可以通过数据库查询oracle支持的平台转换：


sys@LUOCS11G> Col platform_name for a40
sys@LUOCS11G> Select * from v$transportable_platform order by 3;


PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
          3 HP-UX (64-bit)                           Big
          6 AIX-Based Systems (64-bit)               Big
         18 IBM Power Based Linux                    Big
          2 Solaris[tm] OE (64-bit)                  Big
          4 HP-UX IA (64-bit)                        Big
         16 Apple Mac OS                             Big
          1 Solaris[tm] OE (32-bit)                  Big
          9 IBM zSeries Based Linux                  Big
         17 Solaris Operating System (x86)           Little
         19 HP IA Open VMS                           Little
         20 Solaris Operating System (x86-64)        Little
         12 Microsoft Windows x86 64-bit             Little
         13 Linux x86 64-bit                         Little
          8 Microsoft Windows IA (64-bit)            Little
         21 Apple Mac OS (x86-64)                    Little
         11 Linux IA (64-bit)                        Little
          5 HP Tru64 UNIX                            Little
         10 Linux IA (32-bit)                        Little
          7 Microsoft Windows IA (32-bit)            Little
         15 HP Open VMS                              Little


20 rows selected.
 




====== TEST STARTING ======


Source database:
 Windows 7 64 bit – 11.2.0.1 db version
 


col platform_name for a40
set line 120
SELECT A.platform_id, A.platform_name, B.endian_format
  FROM   v$database A, v$transportable_platform B
  WHERE  B.platform_id (+) = A.platform_id;


PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- ---------------------------
         12 Microsoft Windows x86 64-bit             Little
 
 
Destination Database:
 Linux 32 bit – 11.2.0.1 db version
 


col platform_name for a40
set line 120
SELECT A.platform_id, A.platform_name, B.endian_format
  FROM   v$database A, v$transportable_platform B
  WHERE  B.platform_id (+) = A.platform_id;


PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
         10 Linux IA (32-bit)                        Little
 
 
Setup:


SQL> create tablespace L datafile 'E:\APP\LUOCS\ORADATA\LUOCS\l01.dbf' size 10M;


表空间已创建。


SQL> grant resource, connect to l identified by oracle;


授权成功。


SQL> alter user l default tablespace l;


用户已更改。


SQL> conn l/oracle
已连接。


SQL> create table t1 (
  2     id number,
  3     name varchar2(50)
  4  );


表已创建。


SQL> conn / as sysdba
已连接。


SQL> insert into l.t1
  2      select object_id,
  3             object_name
  4      from dba_objects
  5      where rownum <= 10000
  6  /


已创建10000行。


SQL> select count(*) from l.t1;


  COUNT(*)
----------
     10000


SQL> commit;


提交完成。


SQL> conn l/oracle
已连接。
SQL> create index idx_id on t1(id);


索引已创建。
Source database:


SQL> conn / as sysdba
已连接。
SQL> exec dbms_tts.transport_set_check(ts_list => 'L', incl_constraints => TRUE,
 full_check => FALSE);


PL/SQL 过程已成功完成。


SQL> SELECT * FROM SYS.transport_set_violations;


未选定行


-- 满足自包含原则。


SQL> alter tablespace l read only;


表空间已更改。
 


导出要传输的表空间：


C:\>exp '/ as  sysdba' tablespaces=l transport_tablespace=y file=d:\l_exp.dmp


Export: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:33:32 2012


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.




连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 L...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表                              T1
EXP-00091: 正在导出有问题的统计信息。
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
导出成功终止, 但出现警告。




SQL> alter tablespace l read write;




表空间已更改。
使用rman的convert命令转换文件格式：


C:\>rman target /


恢复管理器: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:42:49 2012


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


连接到目标数据库: LUOCS (DBID=578752031)


RMAN> convert tablespace l to platform 'Linux IA (32-bit)' format 'd:\%N%f';


启动 conversion at source 于 25-9月 -12
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=136 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00005 名称=E:\APP\LUOCS\ORADATA\LUOCS\L01.DBF
已转换的数据文件 = D:\L5
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at source 于 25-9月 -12


拷贝L5和l_exp.dmp文件到目标数据库服务器上。略！


Destination Database:


[oracle@localhost ~]$ ll L5 l_exp.dmp 
-rw-r--r-- 1 oracle oinstall 10493952 09-25 15:31 L5
-rw-r--r-- 1 oracle oinstall     4096 09-25 15:31 l_exp.dmp


[oracle@localhost ~]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 15:36:12 2012


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: LUOCS11G (DBID=1468663667)


RMAN> convert datafile '/home/oracle/L5' db_file_name_convert '/home/oracle/L5','/u01/app/oracle/oradata/luocs11g/L01.dbf';


Starting conversion at target at 25-SEP-2012 15:38:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/L5
converted datafile=/u01/app/oracle/oradata/luocs11g/L01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 25-SEP-2012 15:38:09
创建相应的用户：


SQL> create user l identified by oracle;


User created.


SQL> grant create session, create table to l;


Grant succeeded.
执行导入：
先确保客户端字符集：


[oracle@localhost ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK


[oracle@localhost ~]$ imp \'/ as sysdba\' tablespaces=l transport_tablespace=y file=/home/oracle/l_exp.dmp datafiles=/u01/app/oracle/oradata/luocs11g/L01.dbf


Import: Release 11.2.0.1.0 - Production on Tue Sep 25 15:42:21 2012


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.




Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing L's objects into L
. . importing table                           "T1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
这时候表空间已经成功传输到目标数据库中：


SQL> select name from v$datafile where name like '%L%';


NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/luocs11g/L01.dbf


再查数据：
SQL> select count(*) from l.t1;


  COUNT(*)
----------
     10000
这时候导入的表空间为read only状态，确认无误可以改为读写模式：


SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'L';


TABLESPACE_NAME                                              STATUS
------------------------------------------------------------ ------------------
L                                                            READ ONLY


SQL> alter tablespace L read write;


Tablespace altered.
====== THE END ======


后语：本测试案例中，我使用了exp/imp方式，当然我们完全可以使用expdp/impdp 数据泵来实现。 
另外，如果不是跨平台跨字节顺序，那么传输Transport Tablespace操作简单了很多，所以选择此方法做数据迁移，值得推荐。




下面是不跨平台的迁移测试案例：
一、创建测试表空间
SQL> create tablespace testdata datafile '/oradata/testdata01.dbf' size 10M;

Tablespace created.

SQL> create tablespace testindex datafile '/oradata/testindex01.dbf' size 5M;

Tablespace created.

SQL> conn jscn/jscn
Connected.
SQL> create table test (id number) tablespace testdata;

Table created.

SQL> create index ind_id on test(id) tablespace testindex;

Index created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

二、测试开始
1、检查支持的平台
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          1 Solaris[tm] OE (32-bit)          Big
          2 Solaris[tm] OE (64-bit)          Big
          7 Microsoft Windows IA (32-bit)    Little
         10 Linux IA (32-bit)                Little
          6 AIX-Based Systems (64-bit)       Big
          3 HP-UX (64-bit)                   Big
          5 HP Tru64 UNIX                    Little
          4 HP-UX IA (64-bit)                Big
         11 Linux IA (64-bit)                Little
         15 HP Open VMS                      Little
          8 Microsoft Windows IA (64-bit)    Little

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          9 IBM zSeries Based Linux          Big
         13 Linux x86 64-bit                 Little
         16 Apple Mac OS                     Big
         12 Microsoft Windows x86 64-bit     Little
         17 Solaris Operating System (x86)   Little
         18 IBM Power Based Linux            Big
         20 Solaris Operating System (x86-64 Little
            )

         19 HP IA Open VMS                   Little

19 rows selected.

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; 

PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
--------------
Linux x86 64-bit
Little

2、Pick a Self-Contained Set of Tablespaces
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('testdata,testindex', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

3、Generate a Transportable Tablespace Set

3.1 Make all tablespaces in the set you are copying read-only. 
SQL> ALTER TABLESPACE testdata READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE testindex READ ONLY;

Tablespace altered.

3.2Invoke the Data Pump export utility on the host system and specify which  tablespaces are in the transportable set.

SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';

Directory created.


[oracle@SHOW-100-12 ~]$ expdp system/sys DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir  TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=Y

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 15:12:01

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********/ DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/dpump_dir/expdat0515.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:12:20

3.3 在目标中创建directory，拷贝文件到目标数据库文件
[oracle@SHOW-100-12 oradata]$ scp test* 192.168.8.201:/oradata/
Address 192.168.8.201 maps to bogon, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.8.201''s password:
testdata01.dbf                                               100%   10MB  10.0MB/s   00:01
testindex01.dbf                                              100% 5128KB   5.0MB/s   00:00

[oracle@IM-8-201 ~]$ mkdir /home/oracle/dpump_dir
SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';

3.4 导入生成表空间
[oracle@SHOW-100-12 oradata]$ impdp system/sys@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir  TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf  REMAP_SCHEMA=jscn:security

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 16:44:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********/@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf REMAP_SCHEMA=jscn:security
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:21:47


注意：这里要求目标数据库和源数据库字符集必须相同
如何修改字符集，参考如下
http://blog.csdn.net/rulev5/article/details/7020134

3.5 验证数据
[oracle@IM-8-201 dpump_dir]$ sqlplus / as sysdba
SQL> conn security/security
SQL> select * from test;

        ID
----------
         1
         2