Oracle 数据库迁移数据的建议方法（ASM 到本地硬盘，或者本地硬盘到 ASM）

前一段时间遇到了一个问题，那就是RAC环境下的一个数据文件，竟然放在了本地硬盘，而没有放在ASM磁盘组中。非常怪异的现象，更怪异的是RAC竟然允许这种情况。其实解决方法很简单，就是使用rman来讲本地的数据文件迁移到ASM磁盘组上。sql "alter tablespace xxx offline" ;
backup as copy datafile 39 format "+DATA" ;
switch datafile 39 to copy ;
report schema
sql "alter tablespace xxx online" ;因为上面有很多BLOB等信息，但是迁移后发现，对索引或者大对象没有任何影响，还是比较靠谱的。原来的dba没有变？有待深究。下面是相关的记录：RMAN>report schema
2>;

Reportof database schema for database with db_unique_name NMGGT

Listof Permanent Datafiles
===========================
FileSize（MB） Tablespace RB segsDatafile Name
------------ -------------------- ------- ------------------------
1 16384 SYSTEM *** +DATA_NMGT/nmggt/datafile/system.515.829856217
2 16384 SYSAUX *** +DATA_NMGT/nmggt/datafile/sysaux.514.829856227
3 16384 UNDOTBS1 *** +DATA_NMGT/nmggt/datafile/undotbs1.513.829856235
4 16384 UNDOTBS2 *** +DATA_NMGT/nmggt/datafile/undotbs2.511.829856251
5 1024 USERS *** +DATA_NMGT/nmggt/datafile/users.510.829856259
6 10 NMGT_YS_DHXMGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.829858495
7 10 NMGT_YS_DZGZCD *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.829858495
8 10 NMGT_YS_DZHJJDZGY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.829858495
9 10 NMGT_YS_TKQCR *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.829858497
10 190 NMGT_YS_NMKZ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.829858497
11 1000 NMGT_YS_TDZZXM *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.829858497
12 10 NMGT_YS_DZZLHJGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.829858497
13 10 NMGT_YS_KYQJKGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.829858497
14 10 NMGT_YS_KYQDA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.829858497
15 50 NMGT_YS_YQKQXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.829858499
16 10 NMGT_YS_KYQSDHC *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.829858499
17 10 NMGT_YS_KCZYCLPSBA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.829858499
18 10600 NMGT_YS_KCZYZTGH *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.829858499
19 15 NMGT_YS_JJZXXMJBXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.829858499
20 10 NMGT_YS_XZFY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.829858499
21 50 NMGT_YS_KYQNJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.829858499
22 61 NMGT_YS_KCZYCLDJTJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.829858501
23 26 NMGT_YS_KCZYCLKJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.829858501
24 330 NMGT_YS_KYQSZFA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.829858501
25 204800 NMGT_XAJDBT *** +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.829858501
26 4096000 NMGT_BJCQ *** +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.829858613
27 153600 NMGT_SHSY *** +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.829862455
28 100 NMGT_TLW_GISCONFIG *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.829862541
29 409600 NMGT_TLW_NMGYDYS *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.829862541
30 512000 NMGT_TLW_NMGYDBP *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.829862783
31 512000 NMGT_TLW_NMGKYQ *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.829863117
32 512000 NMGT_TLW_NMGOTHER *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.829863461
33 200 nmgt_tlw_nmgtt *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.829863819
34 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.521.829863821
35 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.522.829863823
36 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.523.829863827
37 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.524.829863829
38 400 SDE *** +DATA_NMGT/nmggt/datafile/sde.525.829863831
39 400 SDE_TBS *** /u01/app/Oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbs
40 25 NMGT_YS_DATAMANAGER *** +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.829905653
41 100 DLGIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.831117765
42 100 NMGWEB_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.831117829
43 1124 NMGKYQ_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.831117829
44 1124 TLWELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.831117829
45 2148 ELARPBAK_DRTBS *** +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.831117829
46 100 DLINIT_DRTBS *** +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.831117829
47 1124 DLMIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.831117831
48 1124 DLSYS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.831117831
49 100 EC_DRTBS *** +DATA_NMGT/nmggt/datafile/ec_drtbs.558.831117831
50 100 UNIFLOW_DEMO_DRTBS *** +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.831117831
51 100 U2_DRM_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.831117831
52 100 U2_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drtbs.561.831117831
53 100 FORM_DRTBS *** +DATA_NMGT/nmggt/datafile/form_drtbs.562.831117833
54 100 NEWUNISSO_DRTBS *** +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.831117833
55 1409024 SEAS_DRTBS *** +DATA_NMGT/nmggt/datafile/seas_drtbs.564.831117835
56 6244 NEUDOC_DRTBS *** +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.831117841
57 3172 UNIEAP_DRTBS *** +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.831117841
58 4196 ELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.831117841
59 100 NMGADMIN_GTTBS *** +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.831119363
60 100 GTDZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.831119363
61 100 GTKZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.831119365
62 100 SDE_GTTBS *** +DATA_NMGT/nmggt/datafile/sde_gttbs.571.831119365
63 100 EGOV_GIS_GTTBS *** +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.831119365
64 5120 DBFS_YS *** +DBFS_DG/nmggt/datafile/dbfs_ys.256.831124155
65 1024 DBFS_CQ *** +DBFS_DG/nmggt/datafile/dbfs_cq.257.831124157
66 1024 SDE_TBS2 *** +DATA_NMGT/nmggt/datafile/sde_tbs2.397.831235049

Listof Temporary Files
=======================
FileSize（MB） Tablespace Maxsize（MB）Tempfile Name
------------ -------------------- ----------- --------------------
1 32767 TEMP 32767 +DATA_NMGT/nmggt/tempfile/temp.512.829856243

RMAN>sql "alter tablespace sde_tbs offline" ;

sqlstatement: alter tablespace sde_tbs offline

RMAN>backup as copy datafile 39 format "+DATA_NMGT" ;

Startingbackup at 12-NOV-13
usingchannel ORA_DISK_1
channelORA_DISK_1: starting datafile copy
inputdatafile file number=00039name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbs
outputfile name=+DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619tag=TAG20131112T085659 RECID=1 STAMP=831286620
channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finishedbackup at 12-NOV-13

StartingControl File and SPFILE Autobackup at 12-NOV-13
piecehandle=+RECO_NMGT/nmggt/autobackup/2013_11_12/s_831286620.537.831286621comment=NONE
FinishedControl File and SPFILE Autobackup at 12-NOV-13

RMAN>switch datafile 39 to copy ;

datafile39 switched to datafile copy"+DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619"

RMAN>report schema ;

Reportof database schema for database with db_unique_name NMGGT

Listof Permanent Datafiles
===========================
FileSize（MB） Tablespace RB segsDatafile Name
------------ -------------------- ------- ------------------------
1 16384 SYSTEM *** +DATA_NMGT/nmggt/datafile/system.515.829856217
2 16384 SYSAUX *** +DATA_NMGT/nmggt/datafile/sysaux.514.829856227
3 16384 UNDOTBS1 *** +DATA_NMGT/nmggt/datafile/undotbs1.513.829856235
4 16384 UNDOTBS2 *** +DATA_NMGT/nmggt/datafile/undotbs2.511.829856251
5 1024 USERS *** +DATA_NMGT/nmggt/datafile/users.510.829856259
6 10 NMGT_YS_DHXMGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.829858495
7 10 NMGT_YS_DZGZCD *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.829858495
8 10 NMGT_YS_DZHJJDZGY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.829858495
9 10 NMGT_YS_TKQCR *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.829858497
10 190 NMGT_YS_NMKZ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.829858497
11 1000 NMGT_YS_TDZZXM *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.829858497
12 10 NMGT_YS_DZZLHJGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.829858497
13 10 NMGT_YS_KYQJKGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.829858497
14 10 NMGT_YS_KYQDA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.829858497
15 50 NMGT_YS_YQKQXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.829858499
16 10 NMGT_YS_KYQSDHC *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.829858499
17 10 NMGT_YS_KCZYCLPSBA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.829858499
18 10600 NMGT_YS_KCZYZTGH *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.829858499
19 15 NMGT_YS_JJZXXMJBXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.829858499
20 10 NMGT_YS_XZFY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.829858499
21 50 NMGT_YS_KYQNJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.829858499
22 61 NMGT_YS_KCZYCLDJTJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.829858501
23 26 NMGT_YS_KCZYCLKJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.829858501
24 330 NMGT_YS_KYQSZFA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.829858501
25 204800 NMGT_XAJDBT *** +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.829858501
26 4096000 NMGT_BJCQ *** +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.829858613
27 153600 NMGT_SHSY *** +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.829862455
28 100 NMGT_TLW_GISCONFIG *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.829862541
29 409600 NMGT_TLW_NMGYDYS *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.829862541
30 512000 NMGT_TLW_NMGYDBP *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.829862783
31 512000 NMGT_TLW_NMGKYQ *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.829863117
32 512000 NMGT_TLW_NMGOTHER *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.829863461
33 200 nmgt_tlw_nmgtt *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.829863819
34 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.521.829863821
35 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.522.829863823
36 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.523.829863827
37 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.524.829863829
38 400 SDE *** +DATA_NMGT/nmggt/datafile/sde.525.829863831
39 0 SDE_TBS *** +DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619
40 25 NMGT_YS_DATAMANAGER *** +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.829905653
41 100 DLGIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.831117765
42 100 NMGWEB_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.831117829
43 1124 NMGKYQ_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.831117829
44 1124 TLWELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.831117829
45 2148 ELARPBAK_DRTBS *** +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.831117829
46 100 DLINIT_DRTBS *** +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.831117829
47 1124 DLMIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.831117831
48 1124 DLSYS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.831117831
49 100 EC_DRTBS *** +DATA_NMGT/nmggt/datafile/ec_drtbs.558.831117831
50 100 UNIFLOW_DEMO_DRTBS *** +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.831117831
51 100 U2_DRM_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.831117831
52 100 U2_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drtbs.561.831117831
53 100 FORM_DRTBS *** +DATA_NMGT/nmggt/datafile/form_drtbs.562.831117833
54 100 NEWUNISSO_DRTBS *** +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.831117833
55 1409024 SEAS_DRTBS *** +DATA_NMGT/nmggt/datafile/seas_drtbs.564.831117835
56 6244 NEUDOC_DRTBS *** +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.831117841
57 3172 UNIEAP_DRTBS *** +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.831117841
58 4196 ELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.831117841
59 100 NMGADMIN_GTTBS *** +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.831119363
60 100 GTDZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.831119363
61 100 GTKZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.831119365
62 100 SDE_GTTBS *** +DATA_NMGT/nmggt/datafile/sde_gttbs.571.831119365
63 100 EGOV_GIS_GTTBS *** +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.831119365
64 5120 DBFS_YS *** +DBFS_DG/nmggt/datafile/dbfs_ys.256.831124155
65 1024 DBFS_CQ *** +DBFS_DG/nmggt/datafile/dbfs_cq.257.831124157
66 1024 SDE_TBS2 *** +DATA_NMGT/nmggt/datafile/sde_tbs2.397.831235049

Listof Temporary Files
=======================
FileSize（MB） Tablespace Maxsize（MB）Tempfile Name
------------ -------------------- ----------- --------------------
1 32767 TEMP 32767 +DATA_NMGT/nmggt/tempfile/temp.512.829856243

RMAN>sql "alter tablespace sde_tbs online" ;

sqlstatement: alter tablespace sde_tbs online推荐阅读：如何修改ASM的sys密码 http://www.linuxidc.com/Linux/2013-09/89744.htm如何将ASM中的数据文件复制到操作系统中 http://www.linuxidc.com/Linux/2013-08/88418.htmOracle 11g RAC ASM磁盘全部丢失后的恢复 http://www.linuxidc.com/Linux/2013-07/87000.htmOracle 11g从入门到精通 PDF+光盘源代码 http://www.linuxidc.com/Linux/2013-06/85670.htmRHEL6 ASM方式安装Oracle 11g R2 http://www.linuxidc.com/Linux/2013-06/86002.htmOracle 10g 手工创建ASM数据库 http://www.linuxidc.com/Linux/2013-01/78229.htmUbuntu 12.04（amd64）安装完Oracle 11gR2后各种问题解决方法 http://www.linuxidc.com/Linux/2013-06/86155.htm如何修改Oracle 10g ASM的sys密码 http://www.linuxidc.com/Linux/2012-11/75163.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle的验证方式Oracle中变异表（ORA-04091）处理方法两则相关资讯 Oracle asm Oracle数据库迁移

Oracle ASM异常dismount磁盘组（04月10日）
迁移Oracle数据库文件到ASM （05/19/2015 08:33:24）
Oracle ASM 如何添加新磁盘到磁盘（01/13/2015 22:52:11）

Oracle中的ASM文化（08/20/2015 20:51:26）
Linux同平台Oracle数据库整体物理（05/15/2015 08:45:18）
将Oracle数据库从32位平台迁移到64 （11/13/2014 19:26:36）

本文评论查看全部评论（0）

首页 / 数据库 / MySQL / Oracle 数据库迁移数据的建议方法（ASM 到 本地硬盘，或者本地硬盘到 ASM）

首页 / 数据库 / MySQL / Oracle 数据库迁移数据的建议方法（ASM 到本地硬盘，或者本地硬盘到 ASM）