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

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

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

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

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

下面是测试步骤

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

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

       运行检查脚本：

1SYS AS SYSDBA at test745 > EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('PCHOUSE_TEST', TRUE,TRUE);
2 
3PL/SQL procedure successfully completed.
4 
5SYS AS SYSDBA at test745 > 
6SYS 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 
03Export: Release 10.2.0.4.0 - 64bit Production on D？？úò？, 21 6？？, 2010 11:50:22
04 
05Copyright (c) 2003, 2007, Oracle.  All rights reserved.
06 
07Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
08With the Partitioning, OLAP, Data Mining and Real Application Testing options
09Starting "LSLIANG"."SYS_EXPORT_TRANSPORTABLE_01":  lsliang/******** directory=dmp_dir dumpfile=p.dat TRANSPORT_TABLESPACES=pchouse_test TRANSPORT_FULL_CHECK=Y 
10Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
11Processing object type TRANSPORTABLE_EXPORT/TABLE
12Processing object type TRANSPORTABLE_EXPORT/INDEX
13Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
14Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
15Processing object type TRANSPORTABLE_EXPORT/COMMENT
16Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
17Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
18Master table "LSLIANG"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
19******************************************************************************
20Dump file set for LSLIANG.SYS_EXPORT_TRANSPORTABLE_01 is:
21  /data1/oracle/p.dat
22Job "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
2oracle@192.168.74.6's password: 
3p.dat                                                                                             100% 2104KB   2.1MB/s   00:00   
4PCHOUSE_TEST.dbf                                                                                  100%  300MB  42.9MB/s   00:07   
5[oracle@test740_5 oracle]$ 

4. 到目标机上导入meta data数据

首先要要在目标机器上指定一个用户，新建一个用户或者指定一个已有的用户

我们新建了一个

01SQL> create user pchouse_app identified by pchouse_app
02  2  default tablespace pchouse_test; 
03create user pchouse_app identified by pchouse_app
04*
05ERROR at line 1:
06ORA-00959: tablespace 'PCHOUSE_TEST' does not exist
07 
08 
09SQL> create user pchouse_app identified by pchouse_app; 
10 
11User created.
12 
13SQL> grant dba to pchouse_app; 
14 
15Grant succeeded.
16 
17SQL> 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 
03Import: Release 10.2.0.4.0 - 64bit Production on D？？úò？, 21 6？？, 2010 11:50:38
04 
05Copyright (c) 2003, 2007, Oracle.  All rights reserved.
06 
07Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
08With the Partitioning, OLAP, Data Mining and Real Application Testing options
09Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
10Starting "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 
11Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
12Processing object type TRANSPORTABLE_EXPORT/TABLE
13Processing object type TRANSPORTABLE_EXPORT/INDEX
14Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
15Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
16Processing object type TRANSPORTABLE_EXPORT/COMMENT
17Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
18Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
19Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:50:50
20 
21[oracle@test740_6 oracle]$ 

5检查下

01SQL> select table_name from dba_tables where wner='PCHOUSE_APP'; 
02 
03TABLE_NAME
04------------------------------
05OPERATION
06TBLAPPROVE
07TBLAPPUPFILE
08TBLARTICLE
09TBLARTICLEAUTO
10TBLARTICLEBRIEF
11TBLBACKUPLOG
12TBLBACKUPTEMPLATE
13TBLARTICLECOPY
14TBLARTICLEPRODUCT
15TBLARTICLE_ARCH
16 
17TABLE_NAME
18------------------------------
19.................
20... 此处省略n行
21 
22SQL> 
23SQL> select count(*) from pchouse_app.tbltext ;
24 
25  COUNT(*)
26----------
27     14739

检查数据时 ok 的

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

1SQL> alter tablespace pchouse_test read write ; 
2 
3Tablespace altered.
4 
5SQL> 

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

over !!!

ITPub博客

expdp/impdp 迁移表空间

最新文章