expdp/impdp 迁移表空间

cmsdb 的拆库已经完成，但是应为是在standby 的基础上拆的库，导致sid一致，无法再同一台机器上多一套standby 

我们尝试了建立不同实例的standby 库，都告失败，只剩下2个方法可行了，一个是nid 修改数据库名字，一个是建新库，然后把数据库转到新库里面。 

nid 我们在生产库上还没有实施过的经验，虽然 oracle 很早就提供了这个工具，但还是有其不确定性。

建新库，转数据，用expdp/impdp 导数据就太慢了，总共要处理40g左右的数据，停机时间还是比较长。于是测试了表空间迁移的方法

测试证明可行性还是很高的。


下面是测试步骤 


1。 首先要检查要迁移的表空间是否支持表空间迁移
       限制主要包括： 表空间对象的自包含性， 表空间中，不能有system,sys等用户的对象。

      首先表空间设只读： 
       alter tablespace pchouse_test read only ; 

       运行检查脚本：

1	SYS AS SYSDBA at test745 > EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('PCHOUSE_TEST', TRUE,TRUE);

2

3	PL/SQL procedure successfully completed.

4

5	SYS AS SYSDBA at test745 >

6	SYS AS SYSDBA at test745 > SELECT * FROM TRANSPORT_SET_VIOLATIONS;

7

8	no rows selected

     系统表   TRANSPORT_SET_VIOLATIONS  没有数据说明该表空间是支持迁移的。如果有数据，则要在迁移前把这些对象的处理好，
      
2 。 导出meta数据

01	[oracle@test740_5 oracle]$ expdp lsliang/lsliang directory=dmp_dir dumpfile=p.dat  TRANSPORT_TABLESPACES=pchouse_test TRANSPORT_FULL_CHECK=Y

02

03	Export: Release 10.2.0.4.0 - 64bit Production on D？？úò？, 21 6？？, 2010 11:50:22

04

05	Copyright (c) 2003, 2007, Oracle.  All rights reserved.

06

07	Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

08	With the Partitioning, OLAP, Data Mining and Real Application Testing options

09	Starting "LSLIANG"."SYS_EXPORT_TRANSPORTABLE_01":  lsliang/******** directory=dmp_dir dumpfile=p.dat TRANSPORT_TABLESPACES=pchouse_test TRANSPORT_FULL_CHECK=Y

10	Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

11	Processing object type TRANSPORTABLE_EXPORT/TABLE

12	Processing object type TRANSPORTABLE_EXPORT/INDEX

13	Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

14	Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

15	Processing object type TRANSPORTABLE_EXPORT/COMMENT

16	Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

17	Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

18	Master table "LSLIANG"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

19	******************************************************************************

20	Dump file set for LSLIANG.SYS_EXPORT_TRANSPORTABLE_01 is:

21	/data1/oracle/p.dat

22	Job "LSLIANG"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:50:38

3. 把导出metadata文件以及表空间对应的数据文件copy到目标机器相应的数据目录下，数据目录不要求一致。

1	[oracle@test740_5 oracle]$ scp p.dat  /data/oracle/oradata/test745/PCHOUSE_TEST.dbf  oracle@192.168.74.6:/data/oracle/oradata/test746

2	oracle@192.168.74.6's password:

3	p.dat                                                                                             100% 2104KB   2.1MB/s   00:00

4	PCHOUSE_TEST.dbf                                                                                  100%  300MB  42.9MB/s   00:07

5	[oracle@test740_5 oracle]$

4. 到目标机上导入meta data数据 
  
    首先要要在目标机器上指定一个用户，新建一个用户或者指定一个已有的用户

我们新建了一个

01	SQL> create user pchouse_app identified by pchouse_app

02	2  default tablespace pchouse_test;

03	create user pchouse_app identified by pchouse_app

04

*

05	ERROR at line 1:

06	ORA-00959: tablespace 'PCHOUSE_TEST' does not exist

07

08

09	SQL> create user pchouse_app identified by pchouse_app;

10

11	User created.

12

13	SQL> grant dba to pchouse_app;

14

15	Grant succeeded.

16

17

SQL> exit

metadata数据导入：

01	[oracle@test740_6 oracle]$ impdp system/system  directory=dmp_dir dumpfile=p.dat  transport_datafiles=/data/oracle/oradata/test746/PCHOUSE_TEST.dbf  remap_schema=pchouse_app:pchouse_app

02

03	Import: Release 10.2.0.4.0 - 64bit Production on D？？úò？, 21 6？？, 2010 11:50:38

04

05	Copyright (c) 2003, 2007, Oracle.  All rights reserved.

06

07	Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

08	With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

10	Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=dmp_dir dumpfile=p.dat transport_datafiles=/data/oracle/oradata/test746/PCHOUSE_TEST.dbf remap_schema=pchouse_app:pchouse_app

11	Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

12	Processing object type TRANSPORTABLE_EXPORT/TABLE

13	Processing object type TRANSPORTABLE_EXPORT/INDEX

14	Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

15	Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

16	Processing object type TRANSPORTABLE_EXPORT/COMMENT

17	Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

18	Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

19	Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:50:50

20

21	[oracle@test740_6 oracle]$

5检查下

01	SQL> select table_name from dba_tables where wner='PCHOUSE_APP';

02

03	TABLE_NAME

04	------------------------------

05

OPERATION

06	TBLAPPROVE

07	TBLAPPUPFILE

08	TBLARTICLE

09	TBLARTICLEAUTO

10	TBLARTICLEBRIEF

11	TBLBACKUPLOG

12	TBLBACKUPTEMPLATE

13	TBLARTICLECOPY

14	TBLARTICLEPRODUCT

15	TBLARTICLE_ARCH

16

17	TABLE_NAME

18	------------------------------

19	.................

20	... 此处省略n行

21

22

SQL>

23	SQL> select count(*) from pchouse_app.tbltext ;

24

25

COUNT(*)

26	----------

27

14739

检查数据时 ok 的

6 。 在目标库上把表空间设为读写状态 源库上的也要这么做

1	SQL> alter tablespace pchouse_test read write ;

2

3	Tablespace altered.

4

5

SQL>

如果采用表空间传输的方式来迁移数据，那么主要的费时间的地方，就取决于数据文件的scp的速度了。

over !!! 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-731990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133735/viewspace-731990/