ORA-01157: cannot identify/lock data file

While starting up a database, you may face following error messages which would not allow you to open the database.

ORA-01157: cannot identify/lock data file <file#> - see DBWR trace file
ORA-01110: data file <file#>: <file_name>

This would be because of missing SYSTEM tablespace datafile(s) or current UNDO tablespace datafile(s).

For non-system tablespace datafiles and non-current undo tablespace datafiles, you might be able to open the database (or database may already be open when these errors are reported in alert log file).

To solve the issue, you would need to restore the datafile(s) from the backup and recover the datafiles to successfully open. If these files belong to some data tablespace, data to the application might not be available until datafiles are recovered and restore.

Same error message on ASM because of missing Alias
While creating datafiles on ASM, we usually specify only diskgroup name in ADD DATAFILE command (‘+DATA’). This would add a datafile on a default location (‘+DATA/<unique_db_name/datafile/<system_generated_file_name>) with a system generated name of datafiles. For the ease of management, sometimes we add datafiles on ASM by specifying full name of datafile like ‘+DATA/system02.dbf’ – this actually creates an alias (+DATA/system02.dbf) which we can use to reference this datafiles, but actual datafile would still be at the default location (‘+DATA/<unique_db_name/datafile/<system_generated_file_name>).

See the example bellow where I added a datafile using command ALTER TABLESPACE SYSTEM ADD DATAFILE ‘+data/system02.dbf’ SIZE 10M

ASMCMD> cd +data ASMCMD> ls -l Type      Redund  Striped  Time             Sys  Name                                                                 Y    CDB/                                                                 Y    CDB1/                                                                 Y    NONCDB/ DATAFILE  UNPROT  COARSE   JAN 23 14:00:00  N    system02.dbf => +DATA/NONCDB/DATAFILE/SYSTEM.335.869494199 ##Here you see that system02 is an alias pointing to actual physical datafile

Now if I remove this alias, or it is removed accidently, same ORA-01157 would be reported. To resolve the issue, you would need to add back the alias because controlfile only knows the path of the datafiles which you mentioned while creating the datafile.

[grid@salman1 ~]$ asmcmd ASMCMD> ls CRS/ DATA/ NEWDATA/ ASMCMD> cd +DATA/NONCDB/DATAFILE/ ASMCMD> ls SYSAUX.278.868462893 SYSTEM.279.868463067 SYSTEM.335.869494199 UNDOTBS1.277.868463305 USERS.280.868463303 ASMCMD> ls -l Type      Redund  Striped  Time             Sys  Name DATAFILE  UNPROT  COARSE   JAN 22 12:00:00  Y    SYSAUX.278.868462893 DATAFILE  UNPROT  COARSE   JAN 22 12:00:00  Y    SYSTEM.279.868463067 DATAFILE  UNPROT  COARSE   JAN 20 14:00:00  Y    SYSTEM.335.869494199 DATAFILE  UNPROT  COARSE   JAN 22 12:00:00  Y    UNDOTBS1.277.868463305 DATAFILE  UNPROT  COARSE   JAN 22 12:00:00  Y    USERS.280.868463303 ASMCMD> mkalias SYSTEM.335.869494199 +DATA/system02.dbf