ORA-01157, ORA-01110.

You’ve mistakenly added a datafile to a file system instead of ASM and you see ORA-01157 or ORA-01110.  In 10gR2, you see ORA-01157 and ORA-01110 errors and the database instance is not opened.  In Oracle 11gR2 RAC, you can open all Oracle RAC instances; however the errors ORA-01157 and ORA-01110 are still reported in alert logs, so you you still need to resolve the errors by moving it from a file system to ASM.

Total System Global Area 1073741824 bytes

Fixed Size                  2089576 bytes

Variable Size             230690200 bytes

Database Buffers          838860800 bytes

Redo Buffers                2101248 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 139 - see DBWR trace file

ORA-01110: data file 139: '/apps/oracle/product/10.2.0/DB04/dbs/DG_DBA_DD501'

Scenarios 1:   Datafile is belonged to SYSAUX tablespace.  You can bring SYSAUX tablespace offline and using RMAN to move the file to ASM.

Step 1 – check for file name and file number

select file_name, file_id, tablespace_name from dba_data_files where tablespace_name = 'SYSAUX';

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME

---------- ------------------------------

+DG_DATA/LAX/datafile/sysaux.279.768593301

         2 SYSAUX

/tmp/DG_DATA

        27 SYSAUX

Step 2

SQL> alter tablespace SYSAUX offline;

Tablespace altered.

Step 3

LAX1 > rman target /

RMAN> copy datafile 27 to '+DG_DATA';

Starting backup at 22-MAR-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=937 instance=LAX1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00027 name=/tmp/DG_DATA

output file name=+DG_DATA/LAX/datafile/sysaux.305.778612925 tag=TAG20120322T172203 RECID=2 STAMP=778612925

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 22-MAR-12

Step 4

RMAN> switch datafile 27 to copy;

datafile 27 switched to datafile copy "+DG_DATA/LAX/datafile/sysaux.305.778612925"

 Step 5

SQL> alter tablespace sysaux online;

Tablespace altered.

 Senarios 2:  Database is in noarchivelog mode.  The datafile is a regular data / index type files..

Follow above step 1, 3, 4

Step 6

SQL > alter database datafile 27 online

Scenarios 3:  Database is in Archivelog mode.  The data file is a regular data / index type files...

Follow step # 1, 3, 4, 7, 8

Step 7—Recover the datafile

RMAN> run {

 allocate channel d1 device type disk;

 allocate channel d2 device type disk;

 recover datafile 27;

 }

allocated channel: d1

channel d1: sid=1522 instance=EDOCPRD1 devtype=DISK

allocated channel: d2

channel d2: sid=1582 instance=EDOCPRD1 devtype=DISK

Step 8

SQL > alter database datafile 27 online