Recommended Methods for migrating data from Oracle Database (from ASM to local hard disk or from local hard disk to ASM)

A problem occurred some time ago, that is, a data file in the RAC environment was put on a local hard disk instead of in the ASM disk group. It's a strange phenomenon.

A problem occurred some time ago, that is, a data file in the RAC environment was put on a local hard disk instead of in the ASM disk group. It's a strange phenomenon.

A problem occurred some time ago, that is, a data file in the RAC environment was put on a local hard disk instead of in the ASM disk group. This is a strange phenomenon. What's more strange is that RAC actually allows this situation.

In fact, the solution is very simple. It is to use rman to migrate local data files to the ASM disk.

SQL 'alter tablespace xxx offline ';
Backup as copy datafile 39 format '+ data ';
Switch datafile 39 to copy;
Report schema
SQL 'alter tablespace xxx online ';

Because there is a lot of BLOB and other information on it, but after the migration, it is found that there is no impact on the index or large object, it is more reliable. Didn't the original dba change? Further research is required.

The following are related records:

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.829856133
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_jjjzxxmjbxx.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...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.5379829863819
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
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 = 00039 name =/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_8312866213537.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.829856133
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_jjjzxxmjbxx.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...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.5379829863819
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
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

Recommended reading:

How to change the password of ASM sys

How to copy data files in ASM to the Operating System

Restoration After all Oracle 11g rac asm disks are lost

Oracle 11g from entry to proficient in PDF + CD source code

Installing Oracle 11g R2 using RHEL6 ASM

Oracle 10g manual creation of the ASM Database

Solutions to various problems after installing Oracle 11gR2 in Ubuntu 12.04 (amd64)

How to change the sys password of Oracle 10g ASM