Oracle 数据库迁移数据的建议方法(ASM 到 本地硬盘,或者本地硬盘到 ASM)

前一段时间遇到了一个问题,那就是RAC环境下的一个数据文件,竟然放在了本地硬盘,而没有放在ASM磁盘组中。非常怪异的现象,更怪异的是RAC竟然允许这种情况。

其实解决方法很简单,就是使用rman来讲本地的数据文件迁移到ASM磁盘组上。

 

 

[sql] 
  1. sql ‘alter tablespace xxx offline’ ;
  2. backup as copy datafile 39  format ‘+DATA’ ;
  3. switch datafile 39 to copy ;
  4. report schema
  5. sql ‘alter tablespace xxx online’ ;

 

因为上面有很多BLOB等信息,但是迁移后发现,对索引或者大对象没有任何影响,还是比较靠谱的。原来的dba没有变?有待深究。

 

下面是相关的记录:

[plain]  
  1. RMAN>report schema
  2. 2>;
  3. Reportof database schema for database with db_unique_name NMGGT
  4. Listof Permanent Datafiles
  5. ===========================
  6. FileSize(MB) Tablespace           RB segsDatafile Name
  7. ———— ——————– ——- ————————
  8. 1    16384   SYSTEM               ***    +DATA_NMGT/nmggt/datafile/system.515.829856217
  9. 2    16384   SYSAUX               ***    +DATA_NMGT/nmggt/datafile/sysaux.514.829856227
  10. 3    16384   UNDOTBS1             ***     +DATA_NMGT/nmggt/datafile/undotbs1.513.829856235
  11. 4    16384   UNDOTBS2             ***    +DATA_NMGT/nmggt/datafile/undotbs2.511.829856251
  12. 5    1024    USERS                ***    +DATA_NMGT/nmggt/datafile/users.510.829856259
  13. 6    10      NMGT_YS_DHXMGL       ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.829858495
  14. 7    10      NMGT_YS_DZGZCD       ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.829858495
  15. 8    10      NMGT_YS_DZHJJDZGY    ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.829858495
  16. 9   10       NMGT_YS_TKQCR        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.829858497
  17. 10   190     NMGT_YS_NMKZ         ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.829858497
  18. 11   1000    NMGT_YS_TDZZXM       ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.829858497
  19. 12   10      NMGT_YS_DZZLHJGL     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.829858497
  20. 13   10      NMGT_YS_KYQJKGL      ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.829858497
  21. 14   10      NMGT_YS_KYQDA        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.829858497
  22. 15   50      NMGT_YS_YQKQXX       ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.829858499
  23. 16   10      NMGT_YS_KYQSDHC      ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.829858499
  24. 17   10      NMGT_YS_KCZYCLPSBA   ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.829858499
  25. 18   10600   NMGT_YS_KCZYZTGH     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.829858499
  26. 19   15      NMGT_YS_JJZXXMJBXX   ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.829858499
  27. 20   10      NMGT_YS_XZFY         ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.829858499
  28. 21   50      NMGT_YS_KYQNJ        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.829858499
  29. 22   61      NMGT_YS_KCZYCLDJTJ   ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.829858501
  30. 23   26      NMGT_YS_KCZYCLKJ     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.829858501
  31. 24   330     NMGT_YS_KYQSZFA      ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.829858501
  32. 25   204800  NMGT_XAJDBT          ***    +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.829858501
  33. 26   4096000 NMGT_BJCQ            ***    +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.829858613
  34. 27   153600  NMGT_SHSY            ***    +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.829862455
  35. 28   100     NMGT_TLW_GISCONFIG   ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.829862541
  36. 29   409600  NMGT_TLW_NMGYDYS     ***     +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.829862541
  37. 30   512000  NMGT_TLW_NMGYDBP     ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.829862783
  38. 31   512000  NMGT_TLW_NMGKYQ      ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.829863117
  39. 32   512000  NMGT_TLW_NMGOTHER    ***     +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.829863461
  40. 33   200     nmgt_tlw_nmgtt       ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.829863819
  41. 34   5120    NMGT                 ***    +DATA_NMGT/nmggt/datafile/nmgt.521.829863821
  42. 35   5120     NMGT                 ***    +DATA_NMGT/nmggt/datafile/nmgt.522.829863823
  43. 36   5120    NMGTHD               ***    +DATA_NMGT/nmggt/datafile/nmgthd.523.829863827
  44. 37   5120    NMGTHD               ***    +DATA_NMGT/nmggt/datafile/nmgthd.524.829863829
  45. 38   400     SDE                  ***    +DATA_NMGT/nmggt/datafile/sde.525.829863831
  46. 39   400     SDE_TBS              ***    /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbs
  47. 40   25      NMGT_YS_DATAMANAGER  ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.829905653
  48. 41   100     DLGIS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.831117765
  49. 42   100     NMGWEB_DRTBS         ***    +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.831117829
  50. 43   1124    NMGKYQ_DRTBS         ***    +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.831117829
  51. 44   1124    TLWELARP_DRTBS       ***    +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.831117829
  52. 45   2148    ELARPBAK_DRTBS       ***     +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.831117829
  53. 46   100     DLINIT_DRTBS         ***    +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.831117829
  54. 47   1124    DLMIS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.831117831
  55. 48   1124    DLSYS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.831117831
  56. 49   100     EC_DRTBS             ***    +DATA_NMGT/nmggt/datafile/ec_drtbs.558.831117831
  57. 50   100     UNIFLOW_DEMO_DRTBS   ***    +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.831117831
  58. 51   100     U2_DRM_DRTBS         ***    +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.831117831
  59. 52   100     U2_DRTBS             ***    +DATA_NMGT/nmggt/datafile/u2_drtbs.561.831117831
  60. 53   100     FORM_DRTBS           ***     +DATA_NMGT/nmggt/datafile/form_drtbs.562.831117833
  61. 54   100     NEWUNISSO_DRTBS      ***    +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.831117833
  62. 55   1409024 SEAS_DRTBS           ***    +DATA_NMGT/nmggt/datafile/seas_drtbs.564.831117835
  63. 56   6244    NEUDOC_DRTBS         ***    +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.831117841
  64. 57   3172    UNIEAP_DRTBS         ***    +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.831117841
  65. 58   4196    ELARP_DRTBS          ***    +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.831117841
  66. 59   100     NMGADMIN_GTTBS       ***    +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.831119363
  67. 60   100     GTDZ_GTTBS           ***    +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.831119363
  68. 61   100     GTKZ_GTTBS           ***     +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.831119365
  69. 62   100     SDE_GTTBS            ***    +DATA_NMGT/nmggt/datafile/sde_gttbs.571.831119365
  70. 63   100     EGOV_GIS_GTTBS       ***    +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.831119365
  71. 64   5120    DBFS_YS              ***     +DBFS_DG/nmggt/datafile/dbfs_ys.256.831124155
  72. 65   1024    DBFS_CQ              ***    +DBFS_DG/nmggt/datafile/dbfs_cq.257.831124157
  73. 66   1024    SDE_TBS2             ***    +DATA_NMGT/nmggt/datafile/sde_tbs2.397.831235049
  74. Listof Temporary Files
  75. =======================
  76. FileSize(MB) Tablespace           Maxsize(MB)Tempfile Name
  77. ———— ——————– ———– ——————–
  78. 1    32767   TEMP                 32767      +DATA_NMGT/nmggt/tempfile/temp.512.829856243
  79. RMAN>sql ‘alter tablespace sde_tbs offline’ ;
  80. sqlstatement: alter tablespace sde_tbs offline
  81. RMAN>backup as copy datafile 39 format ‘+DATA_NMGT’ ;
  82. Startingbackup at 12-NOV-13
  83. usingchannel ORA_DISK_1
  84. channelORA_DISK_1: starting datafile copy
  85. inputdatafile file number=00039name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbs
  86. outputfile name=+DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619tag=TAG20131112T085659 RECID=1 STAMP=831286620
  87. channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
  88. Finishedbackup at 12-NOV-13
  89. StartingControl File and SPFILE Autobackup at 12-NOV-13
  90. piecehandle=+RECO_NMGT/nmggt/autobackup/2013_11_12/s_831286620.537.831286621comment=NONE
  91. FinishedControl File and SPFILE Autobackup at 12-NOV-13
  92. RMAN>switch datafile 39 to copy  ;
  93. datafile39 switched to datafile copy”+DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619″
  94. RMAN>report schema ;
  95. Reportof database schema for database with db_unique_name NMGGT
  96. Listof Permanent Datafiles
  97. ===========================
  98. FileSize(MB) Tablespace           RB segsDatafile Name
  99. ———— ——————– ——- ————————
  100. 1    16384   SYSTEM               ***    +DATA_NMGT/nmggt/datafile/system.515.829856217
  101. 2    16384   SYSAUX               ***    +DATA_NMGT/nmggt/datafile/sysaux.514.829856227
  102. 3    16384   UNDOTBS1             ***    +DATA_NMGT/nmggt/datafile/undotbs1.513.829856235
  103. 4    16384   UNDOTBS2             ***    +DATA_NMGT/nmggt/datafile/undotbs2.511.829856251
  104. 5    1024    USERS                ***    +DATA_NMGT/nmggt/datafile/users.510.829856259
  105. 6    10      NMGT_YS_DHXMGL       ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.829858495
  106. 7    10      NMGT_YS_DZGZCD       ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.829858495
  107. 8    10      NMGT_YS_DZHJJDZGY    ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.829858495
  108. 9    10      NMGT_YS_TKQCR        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.829858497
  109. 10   190     NMGT_YS_NMKZ         ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.829858497
  110. 11   1000    NMGT_YS_TDZZXM       ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.829858497
  111. 12   10      NMGT_YS_DZZLHJGL     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.829858497
  112. 13   10      NMGT_YS_KYQJKGL      ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.829858497
  113. 14   10      NMGT_YS_KYQDA        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.829858497
  114. 15   50      NMGT_YS_YQKQXX       ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.829858499
  115. 16   10      NMGT_YS_KYQSDHC      ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.829858499
  116. 17   10      NMGT_YS_KCZYCLPSBA   ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.829858499
  117. 18   10600   NMGT_YS_KCZYZTGH     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.829858499
  118. 19   15      NMGT_YS_JJZXXMJBXX   ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.829858499
  119. 20   10      NMGT_YS_XZFY         ***     +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.829858499
  120. 21   50      NMGT_YS_KYQNJ        ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.829858499
  121. 22   61      NMGT_YS_KCZYCLDJTJ   ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.829858501
  122. 23   26      NMGT_YS_KCZYCLKJ     ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.829858501
  123. 24   330     NMGT_YS_KYQSZFA      ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.829858501
  124. 25   204800  NMGT_XAJDBT          ***     +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.829858501
  125. 26   4096000 NMGT_BJCQ            ***    +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.829858613
  126. 27   153600  NMGT_SHSY            ***    +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.829862455
  127. 28   100     NMGT_TLW_GISCONFIG   ***     +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.829862541
  128. 29   409600  NMGT_TLW_NMGYDYS     ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.829862541
  129. 30   512000  NMGT_TLW_NMGYDBP     ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.829862783
  130. 31   512000  NMGT_TLW_NMGKYQ      ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.829863117
  131. 32   512000  NMGT_TLW_NMGOTHER    ***    +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.829863461
  132. 33   200     nmgt_tlw_nmgtt       ***     +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.829863819
  133. 34   5120    NMGT                 ***    +DATA_NMGT/nmggt/datafile/nmgt.521.829863821
  134. 35   5120    NMGT                 ***    +DATA_NMGT/nmggt/datafile/nmgt.522.829863823
  135. 36   5120    NMGTHD               ***     +DATA_NMGT/nmggt/datafile/nmgthd.523.829863827
  136. 37   5120    NMGTHD               ***    +DATA_NMGT/nmggt/datafile/nmgthd.524.829863829
  137. 38   400     SDE                  ***    +DATA_NMGT/nmggt/datafile/sde.525.829863831
  138. 39   0       SDE_TBS              ***    +DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619
  139. 40   25      NMGT_YS_DATAMANAGER  ***    +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.829905653
  140. 41   100     DLGIS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.831117765
  141. 42   100     NMGWEB_DRTBS         ***    +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.831117829
  142. 43   1124    NMGKYQ_DRTBS         ***    +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.831117829
  143. 44   1124    TLWELARP_DRTBS       ***     +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.831117829
  144. 45   2148    ELARPBAK_DRTBS       ***    +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.831117829
  145. 46   100     DLINIT_DRTBS         ***    +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.831117829
  146. 47   1124    DLMIS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.831117831
  147. 48   1124    DLSYS_DRTBS          ***    +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.831117831
  148. 49   100     EC_DRTBS             ***     +DATA_NMGT/nmggt/datafile/ec_drtbs.558.831117831
  149. 50   100     UNIFLOW_DEMO_DRTBS   ***    +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.831117831
  150. 51   100     U2_DRM_DRTBS         ***    +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.831117831
  151. 52   100     U2_DRTBS             ***     +DATA_NMGT/nmggt/datafile/u2_drtbs.561.831117831
  152. 53   100     FORM_DRTBS           ***    +DATA_NMGT/nmggt/datafile/form_drtbs.562.831117833
  153. 54   100     NEWUNISSO_DRTBS      ***    +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.831117833
  154. 55   1409024 SEAS_DRTBS           ***    +DATA_NMGT/nmggt/datafile/seas_drtbs.564.831117835
  155. 56   6244    NEUDOC_DRTBS         ***    +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.831117841
  156. 57   3172    UNIEAP_DRTBS         ***     +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.831117841
  157. 58   4196    ELARP_DRTBS          ***    +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.831117841
  158. 59   100     NMGADMIN_GTTBS       ***    +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.831119363
  159. 60   100     GTDZ_GTTBS           ***     +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.831119363
  160. 61   100     GTKZ_GTTBS           ***    +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.831119365
  161. 62   100     SDE_GTTBS            ***    +DATA_NMGT/nmggt/datafile/sde_gttbs.571.831119365
  162. 63   100     EGOV_GIS_GTTBS       ***    +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.831119365
  163. 64   5120    DBFS_YS              ***    +DBFS_DG/nmggt/datafile/dbfs_ys.256.831124155
  164. 65   1024    DBFS_CQ              ***    +DBFS_DG/nmggt/datafile/dbfs_cq.257.831124157
  165. 66   1024    SDE_TBS2             ***    +DATA_NMGT/nmggt/datafile/sde_tbs2.397.831235049
  166. Listof Temporary Files
  167. =======================
  168. FileSize(MB) Tablespace           Maxsize(MB)Tempfile Name
  169. ———— ——————– ———– ——————–
  170. 1    32767   TEMP                 32767      +DATA_NMGT/nmggt/tempfile/temp.512.829856243
  171. RMAN>sql ‘alter tablespace sde_tbs online’ ;
  172. sqlstatement: alter tablespace sde_tbs online

 

 

 

 


[sql] 
  1. <p>如需转载,请注明出处:</p><p>blog.csdn.net/renfengjun 或者 www.orcl.cc</p><p>
  2. </p><p>
  3. </p><p>
  4. </p>

标签