System datafile added to filesystem location

Hi Folks,

Many time's this happens. Either due to typo error or accidently we add to filesystem and it was supposed to be in ASM file system.

Solution is to rename the datafile to new (ASM filesystem) location.

Here are steps to fix this and place the datafile back to ASM file system.

In my example, a system datafile was accidently added to dbs location. System datafile is different from non system datafile.



SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME                                       FILE_ID
------------------------------------------------                            ---------------
+ASHISH_DATA/ORCL/datafile/system01.dbf           1
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA          5



Now to fix this:

1.) Start the database is mount mode.
2.) Using RMAN,
rman> copy datfile <datafile#> to '+DISKGROUP';
rman> switch datafile <datafile#> to copy;
3.) open the database;

Example:

You need know the datafile# of the file you want to rename.

Use below two statement to see datafile # and if any extents exists on that datafile


SQL> select count(*) from dba_extents where file_id=5;


COUNT(*)
----------
0


SQL> SQL> select file_id from dba_data_files where file_name='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA';

FILE_ID
----------
5


Now I have my datafile# as 5.

SQL> startup mount
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             482345024 bytes
Database Buffers          243269632 bytes
Redo Buffers                2842624 bytes
Database mounted.
SQL>


Now database is in mount mode, connect to rman;

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 14 06:08:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=3487895465, not open)

RMAN> copy datafile 5 to '+ASHISH_DATA';

Starting backup at 14-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=02 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/dbhome_1/                                                                                        dbs/ +ASHISH_DATA
output file name=+ASHISH_DATA/ORCL/datafile/system.273.917057511 tag=TAG20160714T056450                                                                                         RECID=1 STAMP=917057510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-JUL-16

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+ASHISH_DATA/ORCL/datafile/system.273.917057511"



Now connect to sql prompt and open the database


SQL> alter database open;

Database altered.

SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME                          FILE_ID
-----------------------------------------------------                         --------------
+ASHISH_DATA/ORCL/datafile/system01.dbf 1
+ASHISH_DATA/ORCL/datafile/system.273.917057511 5

Best of luck !!!!!!!!!!!!!