测试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.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