oracle expdp 11,oracle小知识点11--传输表空间通过impdp/expdp

传输表空间可通过rman,imp/exp或impdp/expdp去实现,以下通过impdp/expdp实现.

os: centos 6.6

db version:11.2.0.4.0

以下测试:

##方便测试,源库和目标库为同一个库

##源库

[oracle@ct6605 ct66]$ sqlplus / as sysdba

##建立要传输的test表空间

SQL> create tablespace test datafile '/u02/oradata/ct66/test01.dbf' size 10m ;

##在test表空间下建表scott.test01

SQL> create table scott.test01 tablespace test as select 1 i from dual;

##在users表空间下建表scott.test01的索引scott.idx_testtts_01

SQL> create index scott.idx_testtts_01  on scott.test01(i) tablespace users;

##查询表

SQL> select * from scott.test01;

I

----------

1

##确认平台的版本和endian format是否支持

##这里因为测试在同一个库,肯定是支持.如果两个库的endian format不同,是需要有转换的步骤

SQL> select  b.name,i.version,b.platform_name, a.ENDIAN_FORMAT

from v$transportable_platform a,v$database b,v$instance i

where a.PLATFORM_ID=b.PLATFORM_ID;

/*

NAME    VERSION    PLATFORM_NAME    ENDIAN_FORMAT

CT66    11.2.0.4.0    Linux x86 64-bit    Little

*/

##确认表空间test是否是自包含

SQL> begin

dbms_tts.transport_set_check('test', true,true);

end;

/

##因为前面把表scott.test01的索引建在了users表空间下,这里查询出来不是自包含

SQL> select * from transport_set_violations;

VIOLATIONS

ORA-39907: Index SCOTT.IDX_TESTTTS_01 in tablespace USERS points to table SCOTT.TEST01 in tablespace TEST.

##重建索引到test表空间

SQL> alter index scott.idx_testtts_01 rebuild tablespace test;

##再次查检表空间的自包含

SQL> begin

dbms_tts.transport_set_check('test', true,true);

end;

##已经满足

SQL> select * from transport_set_violations;

VIOLATIONS

##查询test表空间的block_size

SQL> select block_size from dba_tablespaces where tablespace_name='TEST';

BLOCK_SIZE

----------

8192

##建立用于导出test表空间元数据的目录

SQL> create directory home_dump as '/home/oracle';

##把要传输的test表空间设为只读

SQL> alter tablespace test read only;

##导出test表空间的元数据

[oracle@ct6605 ct66]$ expdp system dumpfile=tbs_test.dmp directory=home_dump transport_tablespaces=test nologfile=y

Export: Release 11.2.0.4.0 - Production on Wed Jan 6 15:09:31 2016

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.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=tbs_test.dmp directory=home_dump transport_tablespaces=test nologfile=y

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

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/tbs_test.dmp

******************************************************************************

Datafiles required for transportable tablespace TEST:

/u02/oradata/ct66/test01.dbf

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 6 15:10:00 2016 elapsed 0 00:00:24

##复制test表空间的数据文件到目标数据库.这里因为是同一个库的测试,所以先临时复制到/home/oracle下

[oracle@ct6605 ct66]$ cp /u02/oradata/ct66/test01.dbf ~/test01.dbf

[oracle@ct6605 ~]$ sqlplus / as sysdba

##将要传输的test表空间设为读写,正常工作

SQL> alter tablespace test read write;

##目标库:

##因为这里测试,源库和目标库为同一个库,所以要先删除test表空间

SQL> drop tablespace test including contents and datafiles;

##此时表scott.test01也不存在了

SQL> select * from scott.test01;

/*

ORA-00942:table or view does not exist

*/

##检查目标库的db_block_size和传输的表空间的block_size是否一样

SQL> select name,value from v$parameter where name ='db_block_size';

/*

NAME    VALUE

db_block_size    8192

*/

##如果不一样,需要增加传输的表空间所对应的block_size的cache.

alter system set db_xk_cachae_size=100m;

##复制test表空间的数据文件到数据目录

[oracle@ct6605 ct66]$ cp ~/test01.dbf /u02/oradata/ct66/

##导入test表空间的元数据

[oracle@ct6605 ct66]$ impdp system dumpfile=tbs_test.dmp directory=home_dump transport_datafiles=/u02/oradata/ct66/test01.dbf nologfile=y

Import: Release 11.2.0.4.0 - Production on Wed Jan 6 15:17:48 2016

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

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.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/******** dumpfile=tbs_test.dmp directory=home_dump transport_datafiles=/u02/oradata/ct66/test01.dbf nologfile=y

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 6 15:17:55 2016 elapsed 0 00:00:03

[oracle@ct6605 ~]$ sqlplus / as sysdba

##将目标库的test表空间设为读写

SQL> alter tablespace test read write;

##此时可以看到scott.test01已经导入

SQL> select * from scott.test01;

I

----------

1

备注:

1.源库和目标库的字符集必须相同.

2.传输表空间中含有XMLTypes时,须使用imp/exp,并确保imp/exp的constraints和triggers参数设置为y.

3.不能传输system表空间或sys用户拥有的对象.

4.表空间可以传输到相同或更高版本的目标库.

5.通过impdp/expdp的方式,需要在创建传输集之前,将表空间设为read only.如果生产库无法read only,可考虑通过dataguard的快照数据库方法或者通过rman.

6.以传输表空间导入之后,最好对这个表空间做一次rman备份.