Transportable Tablespace Mode
oracle 数据泵分别提供了四种导入导出模式:
FULL export Mode(使用full参数)
Schema Mode(使用Schemas参数)
Table Mode(使用tables参数)
Tablespace Mode(使用tablespaces参数)
Transportable Tablespace Mode(使用 TRANSPORT_TABLESPACES参数)
在Transportable Tablespace Mode中,只有表空间中的table的metadata被unload。这允许表空间的数据文件可以通过拷贝,以及和impdp(transportable tablespace import)。这个需要用户EXP_FULL_DATABASE数据。
在Transportable Tablespace Mode中,需要表空间中table完全自包含。所有的表的分区都在同一tablespace中。Transportable Tablespace job不能被重启,其并行度只能为1.
*transport_tablespaces导出
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=trans.dmp transport_tablespaces=P1 transport_full_check=y logfile=tts.log
命令详解:
transport_full_check=y
该参数只能在Transportable Tablespace Mode中使用,用来检查transport_tablespaces指定的表空间P1内对象与表空间P1外对象之间依赖关系。
当指定了transport_full_check=y,若表空间P1内对象与表空间P1外对象之间存在依赖关系,比如表空间P1中对象的索引在另外一个表空间中,这时就会返回错误,该导出job就会被终止。
TRANSPORT_FULL_CHECK=n,判定表空间中对象不依赖另外一个对象。这种依赖关系的要考虑以下的这种情况:一个表不依赖索引,而索引依赖于表,如果表空间中包含一个表,但是没有index,或者这个index在另外一个空间中,那么这个判定结果OK,如果表空间中包含一个index,但是该index依赖的表存在于另外一个表空间中,那么此时结果就为NO,那么job就会被停止。
transport_tablespaces=P1表空间必须存在且状态为read-only。
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=trans.dmp transport_tablespaces=P1 transport_full_check=y logfile=tts.log
Export: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 15:22:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "YYP"."SYS_EXPORT_TRANSPORTABLE_01": yyp/******** directory=dir0 dumpfile=trans.dmp transport_tablespaces=P1 transport_full_check=y logfile=tts.log
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'P1' is not read only
Job "YYP"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 15:23:14
SQL> alter tablespace P1 read only;
Tablespace altered.
SQL> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
P1 READ ONLY
7 rows selected.
SQL>
再执行transport tablespace 导出:
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=trans.dmp transport_tablespaces=P1 transport_full_check=y logfile=tts.log
Export: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 15:29:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "YYP"."SYS_EXPORT_TRANSPORTABLE_01": yyp/******** directory=dir0 dumpfile=trans.dmp transport_tablespaces=P1 transport_full_check=y logfile=tts.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "YYP"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for YYP.SYS_EXPORT_TRANSPORTABLE_01 is:
/tmp/dir0/trans.dmp
Job "YYP"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:29:57
[oracle@localhost dir0]$
*transport_tablespaces导入
把表空间对应数据文件进行备份
[oracle@localhost dir0]$ cd /oracle/database/oradata/orcl/
[oracle@localhost orcl]$ cp p1.dbf p2.dbf
[oracle@localhost orcl]$ ls
control01.ctl example01.dbf redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl p1.dbf redo02.log system01.dbf users01.dbf
control03.ctl p2.dbf redo03.log temp01.dbf
先将P1这个表空间删除。
SQL> drop tablespace p1 including contents and datafiles;
Tablespace dropped.
[oracle@localhost orcl]$ ls
control01.ctl example01.dbf redo02.log system01.dbf users01.dbf
control02.ctl p2.dbf redo03.log temp01.dbf
control03.ctl redo01.log sysaux01.dbf undotbs01.dbf
执行导入:
[oracle@localhost dir0]$ impdp yyp/oracle directory=dir0 dumpfile=trans.dmp TRANSPORT_TABLESPACES=P1 TRANSPORT_DATAFILES=\'/oracle/database/oradata/orcl/p2.dbf\'
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 15:43:36
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces
---在执行transport_tablespace导入的时候,不能和dumpfile连用。transport_tablespace导入将一个数据库中表空间,导入到另外一个目标数据库中。
[oracle@localhost dir0]$ impdp yyp/oracle directory=dir0 dumpfile=trans.dmp TRANSPORT_DATAFILES=\'/oracle/database/oradata/orcl/p2.dbf\'
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 15:44:13
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "YYP"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "YYP"."SYS_IMPORT_TRANSPORTABLE_01": yyp/******** directory=dir0 dumpfile=trans.dmp TRANSPORT_DATAFILES='/oracle/database/oradata/orcl/p2.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "YYP"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:44:21
SQL> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
P1 READ ONLY
7 rows selected.
SQL> select file_name from dba_data_files where tablespace_name='P1';
FILE_NAME
--------------------------------------------------------------------------------
/oracle/database/oradata/orcl/p2.dbf
SQL> select segment_name from dba_segments where tablespace_name='P1';
SEGMENT_NAME
--------------------------------------------------------------------------------
TESTP2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25651216/viewspace-748262/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25651216/viewspace-748262/