Wrongly added datafile in ASM, migrating the non-ASM datafile to ASM datafile:
One of our junior DBA added the datafile to tablespace (which is running on ASM storage), without + symbol before diskgroup name,
File got added in the dbs location, (regular filesystem).
we have to move the datafile from non-asm(regular filesystem) to ASM file.
Here are the steps:
new 1: select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA'
FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB
---------- ---------------------------------------------------------------------- ---------- --------------------- --------- ----------
130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767
131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767
1062 /ora/app/oracle/product/11.2.0.4/dbs/DATA_01 2048 ONLINE YES 32768
SQL> alter database datafile 1062 offline;
Database altered.
SQL>
SQL> select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA'
2 ;
FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB
---------- ---------------------------------------------------------------------- ---------- --------------------- --------- ----------
130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767
131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767
1062 /ora/app/oracle/product/11.2.0.4/dbs/DATA_01 RECOVER
DPOTHPRD1:dpqctprd01:oracle$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 15 00:33:57 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DPOTHPRD (DBID=2188772963)
RMAN> backup as copy datafile 1062 format '+DATA_01';
Starting backup at 15-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=762 instance=DPOTHPRD1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=01062 name=/ora/app/oracle/product/11.2.0.4/dbs/DATA_01
output file name=+DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 tag=TAG20151015T003436 RECID=15 STAMP=893118886
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 15-OCT-15
RMAN>
RMAN> switch datafile 1062 to copy;
datafile 1062 switched to datafile copy "+DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879"
RMAN>
SQL>select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB from dba_data_files where tablespace_name='PROD_DATA';
FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB STATUS
---------- ------------------------------------------------------------------------------------------ ---------- --------------------- --------- ---------- ---------------------------
130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767 AVAILABLE
131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767 AVAILABLE
1062 +DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 RECOVER AVAILABLE
SQL> recover datafile 1062;
Media recovery complete.
SQL>
SQL> alter database datafile 1062 online;
Database altered.
SQL> select file_id,file_name,bytes/1024/1024 Size_MB,online_status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 Max_MB,status from dba_data_files where tablespace_name='PROD_DATA';
FILE_ID FILE_NAME SIZE_MB ONLINE_STATUS AUTOEXTEN MAX_MB STATUS
---------- ------------------------------------------------------------------------------------------ ---------- --------------------- --------- ---------- ---------------------------
130 +DATA_01/dpothprd/datafile/PROD_DATA.350.833277281 32767 ONLINE YES 32767 AVAILABLE
131 +DATA_01/dpothprd/datafile/PROD_DATA.349.833277283 32512 ONLINE YES 32767 AVAILABLE
1062 +DATA_01/dpothprd/datafile/PROD_DATA.1276.893118879 2048 ONLINE YES 32768 AVAILABLE
