Oracle 数据库迁移数据的建议方法（ASM 到 本地硬盘，或者本地硬盘到 ASM）
来源：互联网 发布：淘宝足迹不是本人浏览 编辑：程序博客网 时间：2024/05/02 23:38
前一段时间遇到了一个问题，那就是RAC环境下的一个数据文件，竟然放在了本地硬盘，而没有放在ASM磁盘组中。非常怪异的现象，更怪异的是RAC竟然允许这种情况。
其实解决方法很简单，就是使用rman来讲本地的数据文件迁移到ASM磁盘组上。
sql 'alter tablespace xxx offline' ;backup as copy datafile 39 format '+DATA' ;switch datafile 39 to copy ;report schemasql 'alter tablespace xxx online' ;
因为上面有很多BLOB等信息，但是迁移后发现，对索引或者大对象没有任何影响，还是比较靠谱的。原来的dba没有变？有待深究。
下面是相关的记录：
RMAN>report schema2>; 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.8298562172 16384 SYSAUX *** +DATA_NMGT/nmggt/datafile/sysaux.514.8298562273 16384 UNDOTBS1 *** +DATA_NMGT/nmggt/datafile/undotbs1.513.8298562354 16384 UNDOTBS2 *** +DATA_NMGT/nmggt/datafile/undotbs2.511.8298562515 1024 USERS *** +DATA_NMGT/nmggt/datafile/users.510.8298562596 10 NMGT_YS_DHXMGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.8298584957 10 NMGT_YS_DZGZCD *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.8298584958 10 NMGT_YS_DZHJJDZGY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.8298584959 10 NMGT_YS_TKQCR *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.82985849710 190 NMGT_YS_NMKZ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.82985849711 1000 NMGT_YS_TDZZXM *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.82985849712 10 NMGT_YS_DZZLHJGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.82985849713 10 NMGT_YS_KYQJKGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.82985849714 10 NMGT_YS_KYQDA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.82985849715 50 NMGT_YS_YQKQXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.82985849916 10 NMGT_YS_KYQSDHC *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.82985849917 10 NMGT_YS_KCZYCLPSBA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.82985849918 10600 NMGT_YS_KCZYZTGH *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.82985849919 15 NMGT_YS_JJZXXMJBXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.82985849920 10 NMGT_YS_XZFY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.82985849921 50 NMGT_YS_KYQNJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.82985849922 61 NMGT_YS_KCZYCLDJTJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.82985850123 26 NMGT_YS_KCZYCLKJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.82985850124 330 NMGT_YS_KYQSZFA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.82985850125 204800 NMGT_XAJDBT *** +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.82985850126 4096000 NMGT_BJCQ *** +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.82985861327 153600 NMGT_SHSY *** +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.82986245528 100 NMGT_TLW_GISCONFIG *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.82986254129 409600 NMGT_TLW_NMGYDYS *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.82986254130 512000 NMGT_TLW_NMGYDBP *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.82986278331 512000 NMGT_TLW_NMGKYQ *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.82986311732 512000 NMGT_TLW_NMGOTHER *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.82986346133 200 nmgt_tlw_nmgtt *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.82986381934 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.521.82986382135 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.522.82986382336 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.523.82986382737 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.524.82986382938 400 SDE *** +DATA_NMGT/nmggt/datafile/sde.525.82986383139 400 SDE_TBS *** /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbs40 25 NMGT_YS_DATAMANAGER *** +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.82990565341 100 DLGIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.83111776542 100 NMGWEB_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.83111782943 1124 NMGKYQ_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.83111782944 1124 TLWELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.83111782945 2148 ELARPBAK_DRTBS *** +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.83111782946 100 DLINIT_DRTBS *** +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.83111782947 1124 DLMIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.83111783148 1124 DLSYS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.83111783149 100 EC_DRTBS *** +DATA_NMGT/nmggt/datafile/ec_drtbs.558.83111783150 100 UNIFLOW_DEMO_DRTBS *** +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.83111783151 100 U2_DRM_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.83111783152 100 U2_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drtbs.561.83111783153 100 FORM_DRTBS *** +DATA_NMGT/nmggt/datafile/form_drtbs.562.83111783354 100 NEWUNISSO_DRTBS *** +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.83111783355 1409024 SEAS_DRTBS *** +DATA_NMGT/nmggt/datafile/seas_drtbs.564.83111783556 6244 NEUDOC_DRTBS *** +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.83111784157 3172 UNIEAP_DRTBS *** +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.83111784158 4196 ELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.83111784159 100 NMGADMIN_GTTBS *** +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.83111936360 100 GTDZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.83111936361 100 GTKZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.83111936562 100 SDE_GTTBS *** +DATA_NMGT/nmggt/datafile/sde_gttbs.571.83111936563 100 EGOV_GIS_GTTBS *** +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.83111936564 5120 DBFS_YS *** +DBFS_DG/nmggt/datafile/dbfs_ys.256.83112415565 1024 DBFS_CQ *** +DBFS_DG/nmggt/datafile/dbfs_cq.257.83112415766 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-13usingchannel ORA_DISK_1channelORA_DISK_1: starting datafile copyinputdatafile file number=00039name=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/sde_tbsoutputfile name=+DATA_NMGT/nmggt/datafile/sde_tbs.396.831286619tag=TAG20131112T085659 RECID=1 STAMP=831286620channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finishedbackup at 12-NOV-13 StartingControl File and SPFILE Autobackup at 12-NOV-13piecehandle=+RECO_NMGT/nmggt/autobackup/2013_11_12/s_831286620.537.831286621comment=NONEFinishedControl 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.8298562172 16384 SYSAUX *** +DATA_NMGT/nmggt/datafile/sysaux.514.8298562273 16384 UNDOTBS1 *** +DATA_NMGT/nmggt/datafile/undotbs1.513.8298562354 16384 UNDOTBS2 *** +DATA_NMGT/nmggt/datafile/undotbs2.511.8298562515 1024 USERS *** +DATA_NMGT/nmggt/datafile/users.510.8298562596 10 NMGT_YS_DHXMGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dhxmgl.498.8298584957 10 NMGT_YS_DZGZCD *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzgzcd.497.8298584958 10 NMGT_YS_DZHJJDZGY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzhjjdzgy.415.8298584959 10 NMGT_YS_TKQCR *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tkqcr.414.82985849710 190 NMGT_YS_NMKZ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_nmkz.463.82985849711 1000 NMGT_YS_TDZZXM *** +DATA_NMGT/nmggt/datafile/nmgt_ys_tdzzxm.461.82985849712 10 NMGT_YS_DZZLHJGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_dzzlhjgl.460.82985849713 10 NMGT_YS_KYQJKGL *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqjkgl.450.82985849714 10 NMGT_YS_KYQDA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqda.449.82985849715 50 NMGT_YS_YQKQXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_yqkqxx.448.82985849916 10 NMGT_YS_KYQSDHC *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqsdhc.447.82985849917 10 NMGT_YS_KCZYCLPSBA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclpsba.446.82985849918 10600 NMGT_YS_KCZYZTGH *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyztgh.445.82985849919 15 NMGT_YS_JJZXXMJBXX *** +DATA_NMGT/nmggt/datafile/nmgt_ys_jjzxxmjbxx.444.82985849920 10 NMGT_YS_XZFY *** +DATA_NMGT/nmggt/datafile/nmgt_ys_xzfy.443.82985849921 50 NMGT_YS_KYQNJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqnj.442.82985849922 61 NMGT_YS_KCZYCLDJTJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczycldjtj.441.82985850123 26 NMGT_YS_KCZYCLKJ *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kczyclkj.440.82985850124 330 NMGT_YS_KYQSZFA *** +DATA_NMGT/nmggt/datafile/nmgt_ys_kyqszfa.457.82985850125 204800 NMGT_XAJDBT *** +DATA_NMGT/nmggt/datafile/nmgt_xajdbt.456.82985850126 4096000 NMGT_BJCQ *** +DATA_NMGT/nmggt/datafile/nmgt_bjcq.424.82985861327 153600 NMGT_SHSY *** +DATA_NMGT/nmggt/datafile/nmgt_shsy.417.82986245528 100 NMGT_TLW_GISCONFIG *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_gisconfig.416.82986254129 409600 NMGT_TLW_NMGYDYS *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydys.516.82986254130 512000 NMGT_TLW_NMGYDBP *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgydbp.517.82986278331 512000 NMGT_TLW_NMGKYQ *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgkyq.518.82986311732 512000 NMGT_TLW_NMGOTHER *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgother.519.82986346133 200 nmgt_tlw_nmgtt *** +DATA_NMGT/nmggt/datafile/nmgt_tlw_nmgtt.520.82986381934 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.521.82986382135 5120 NMGT *** +DATA_NMGT/nmggt/datafile/nmgt.522.82986382336 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.523.82986382737 5120 NMGTHD *** +DATA_NMGT/nmggt/datafile/nmgthd.524.82986382938 400 SDE *** +DATA_NMGT/nmggt/datafile/sde.525.82986383139 0 SDE_TBS *** +DATA_NMGT/nmggt/datafile/sde_tbs.396.83128661940 25 NMGT_YS_DATAMANAGER *** +DATA_NMGT/nmggt/datafile/nmgt_ys_datamanager.526.82990565341 100 DLGIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlgis_drtbs.550.83111776542 100 NMGWEB_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgweb_drtbs.551.83111782943 1124 NMGKYQ_DRTBS *** +DATA_NMGT/nmggt/datafile/nmgkyq_drtbs.552.83111782944 1124 TLWELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/tlwelarp_drtbs.553.83111782945 2148 ELARPBAK_DRTBS *** +DATA_NMGT/nmggt/datafile/elarpbak_drtbs.554.83111782946 100 DLINIT_DRTBS *** +DATA_NMGT/nmggt/datafile/dlinit_drtbs.555.83111782947 1124 DLMIS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlmis_drtbs.556.83111783148 1124 DLSYS_DRTBS *** +DATA_NMGT/nmggt/datafile/dlsys_drtbs.557.83111783149 100 EC_DRTBS *** +DATA_NMGT/nmggt/datafile/ec_drtbs.558.83111783150 100 UNIFLOW_DEMO_DRTBS *** +DATA_NMGT/nmggt/datafile/uniflow_demo_drtbs.559.83111783151 100 U2_DRM_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drm_drtbs.560.83111783152 100 U2_DRTBS *** +DATA_NMGT/nmggt/datafile/u2_drtbs.561.83111783153 100 FORM_DRTBS *** +DATA_NMGT/nmggt/datafile/form_drtbs.562.83111783354 100 NEWUNISSO_DRTBS *** +DATA_NMGT/nmggt/datafile/newunisso_drtbs.563.83111783355 1409024 SEAS_DRTBS *** +DATA_NMGT/nmggt/datafile/seas_drtbs.564.83111783556 6244 NEUDOC_DRTBS *** +DATA_NMGT/nmggt/datafile/neudoc_drtbs.565.83111784157 3172 UNIEAP_DRTBS *** +DATA_NMGT/nmggt/datafile/unieap_drtbs.566.83111784158 4196 ELARP_DRTBS *** +DATA_NMGT/nmggt/datafile/elarp_drtbs.567.83111784159 100 NMGADMIN_GTTBS *** +DATA_NMGT/nmggt/datafile/nmgadmin_gttbs.568.83111936360 100 GTDZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtdz_gttbs.569.83111936361 100 GTKZ_GTTBS *** +DATA_NMGT/nmggt/datafile/gtkz_gttbs.570.83111936562 100 SDE_GTTBS *** +DATA_NMGT/nmggt/datafile/sde_gttbs.571.83111936563 100 EGOV_GIS_GTTBS *** +DATA_NMGT/nmggt/datafile/egov_gis_gttbs.572.83111936564 5120 DBFS_YS *** +DBFS_DG/nmggt/datafile/dbfs_ys.256.83112415565 1024 DBFS_CQ *** +DBFS_DG/nmggt/datafile/dbfs_cq.257.83112415766 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
如需转载，请注明出处：
blog.csdn.net/renfengjun 或者 www.orcl.cc
0 0
- Oracle 数据库迁移数据的建议方法（ASM 到 本地硬盘，或者本地硬盘到 ASM）
- Oracle数据库有文件系统迁移到ASM
- asm迁移到asm
- asm迁移到asm
- 如何导出一个client的用户数据到本地硬盘
- oracle数据库文件系统迁移到ASM
- 使用RMAN迁移文件系统数据库到ASM
- 数据库迁移-从文件系统到ASM
- 数据库迁移-从ASM到文件系统
- 把数据库从文件系统迁移到ASM
- 数据库从文件系统迁移到ASM
- 使用RMAN迁移文件系统数据库到ASM
- 迁移已存在的数据库到ASM中
- 单实例文件管理的数据库迁移到RAC+ASM
- 实验: 把数据库迁移到ASM（完全恢复）
- Oracle单实例下oracle数据库从文件系统迁移到ASM上
- c# winform 把数据库里读取出来的图片保存到本地硬盘上，图片下载
- oracle asm数据库从一个raid10磁阵迁移到raid5磁阵
- “iOS 模拟器”未能安装此应用程序的解决方法
- Oracle PlSQL常用命令
- xmlhttp.status值为500
- hdu 2577 How to Type
- TortoiseSVN
- Oracle 数据库迁移数据的建议方法（ASM 到 本地硬盘，或者本地硬盘到 ASM）
- 动态规划——4 背包问题
- struts2升级到2.3.15.1总结
- 错误1
- Partition List
- 贪婪算法——1 Prim算法
- JS之千位分隔符
- EasyHook库系列使用教程之三插入钩子示例
- System.OutOfMemoryException
