测试transport tablespace

最近事情不是很多，看看官方文档，做个测试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.youkuaiyun.com/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