使用Data Pump传输表空间

使用Data Pump传输表空间

确认环境：

源库：

系统：RedHat企业版5.4

数据库版本：ORACLE 10.2.0.1.0

IP：10.37.100.100

目标库：

系统：RedHat企业版5.4

数据库版本：ORACLE 10.2.0.3.0

IP：10.37.100.101

需要传输的表空间：TTS_TEST

源库创建测试表空间：

SQL> create tablespace TTS_TEST datafile '/u01/app/oracle/oradata/orcl/tts01.dbf' size 20m,'/u01/app/oracle/oradata/orcl/tts02.dbf' size 20m;

Tablespace created.

创建用户tts，默认表空间是tts_test,并赋予相应权限，登入后插入数据：

SQL> create user tts identified by oracle default tablespace tts_test;

User created.

SQL> alter user tts account unlock;

User altered.

SQL> grant connect,resource to tts;

Grant succeeded.

SQL> alter user xtt quota unlimited on tts_test;

User altered.

SQL> grant select on scott.dept to tts;

Grant succeeded.

SQL> grant select on scott.emp to tts;

Grant succeeded.

SQL> create table tts_dept

2 as

3 select * from scott.dept

4 /

Table created.

SQL> create table tts_emp

2 as

3 select * from scott.emp

4 /

Table created.

源库上查看版本支持情况：

SQL> select db.name,db.platform_name,tp.endian_format

2 from v$transportable_platform tp,v$database db

3 where tp.platform_name=db.platform_name

4 /

NAME PLATFORM_NAME ENDIAN_FORMAT

--------- -------------------- --------------

ORCL Linux IA (32-bit) Little

查看数据库版本：

SQL> select version from v$instance;

VERSION

-----------------

10.2.0.1.0

备库上查看版本情况：

SQL> select db.name,db.platform_name,tp.endian_format

2 from v$transportable_platform tp,v$database db

3 where tp.platform_name=db.platform_name

4 /

NAME PLATFORM_NAME ENDIAN_FORMAT

--------- -------------------- --------------

ORCL Linux IA (32-bit) Little

查看数据库版本：

SQL> select version from v$instance;

VERSION

-----------------

10.2.0.3.0

源库上使用DBMS_TTS包中的TRANSPORT_SET_CHECK过程来验证传输表空间的自包含情况：

SQL> exec dbms_tts.transport_set_check('tts_test',true,true);

PL/SQL procedure successfully completed.

其中：

tts_test 是要执行检查的表空间名

第一个ture限定是否检查约束，默认是false

第二个ture限定是否执行严格自包含方式检查，默认是false

查看检查结果，无输出结果视为一切通过：

SQL> select * from transport_set_violations;

no rows selected

利用Data Pump生成可传输的表空间集

执行导出前需先将表空间置为read only状态

SQL> alter tablespace tts_test read only;

Tablespace altered.

利用transprot_taplespaces参数导出迁移的表空间（其他参数前面已经介绍过）

[oracle@localhost ~]$ expdp system/oracle driectory=expdp_dir dumpfile=tt

s_test.dmp nologfile=y transport_tablespaces=tts_test

.

.

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

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:

/u01/expdp_dir/tts_test.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:00:02

将表空间切换回read wriote状态

SQL> alter tablespace tts_test read write;

Tablespace altered.

将传输集和相应的数据文件复制到目标数据库：

[oracle@localhost ~]$ scp 10.37.100.100:/u01/expdp_dir/tts_test.dmp /u01/expdp_dir/

oracle@10.37.100.100's password:

tts_test.dmp 100% 72KB 72.0KB/s 00:00

[oracle@localhost ~]$ scp 10.37.100.100:/u01/app/oracle/oradata/orcl/tts0* /u01/app/oracle/oradata/orcl/

oracle@10.37.100.100's password:

tts01.dbf 100% 20MB 20.0MB/s 00:01

tts02.dbf 100% 20MB 10.0MB/s 00:02

执行导入前先查看源库的BLOCK_SIZE大小

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

BLOCK_SIZE

----------

8192

目标库查看BLOCK_SIZE：

SQL> show parameter block_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

如果发现两数据库的BLOCK_SIZE不一致，需要设置DB_nK_CACHE_SIZE的大小，如：

SQL> alter system set db_8K_cache_size=20m;

System altered.

目标数据库开始执行导入：

创建tts用户：

SQL> create user tts identified by oracle;

User created.

SQL> alter user tts account unlock ;

User altered.

SQL> grant connect,resource to tts;

Grant succeeded.

执行导入：

[oracle@localhost orcl]$ impdp system/oracle directory=expdp_dir dumpfile=tts_test.dmp n

ologfile=y transport_datafiles=/u01/app/oracle/oradata/orcl/tts01.dbf,/u01/app/oracle/oradata/orcl/tts02.dbf

.

.

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=expdp_dir dumpfile=tts_test.dmp nologfile=y transport_datafiles=/u01/app/oracle/oradata/orcl/tts01.dbf,/u01/app/oracle/oradata/orcl/tts02.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:27:40

tts用户登入查看：

[oracle@localhost orcl]$ sqlplus tts/oracle

SQL> select tname from tab;

TNAME

------------------------------

TTS_DEPT

TTS_EMP

需要注意的是虽然表空间及数据传送过来了，但是tts的默认表空间并没有变化，如果有必要需要手动其指定：

SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

TTS USERS

SQL> alter user tts default tablespace tts_test;

User altered.

如果需要，成人用品店www.xfqiao.com将新迁移过来的表空间置为READ WRITE状态

SQL> alter tablespace tts_test read write;

Tablespace altered.