ORA-01157 and ORA-01110

ORA-01157 is raised when Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like
- Datafile is deleted or corrupt
- Datafile is renamed or moved
- Mount point is incorrect
- Issues with Read/write permission on Datafile

To solve ORA-01157 we can use one of the following steps:
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recoved it by using a valid backup.
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not containt important segments, that can be dropped offline
- If datafile is renamed or moved then we need to get it in its original position
- If Mount point is incorrect, simply recreate the mount point
- if it is due to permission then we need to grant the permission at OS level

I faced ORA-01157 at my local environment, when I was restoring a cold backup. Fortunatially it was with a tablespace which was added for testing purpose and was not critical. So I simply drop the datafile using "OFFLINE DROP" clause and opened the database.

 

SQL> select tablespace_name,status,contents from dba_tablespaces;

select tablespace_name,status,contents from dba_tablespaces

                                            *

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

MOUNTED

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

ORA-01110: data file 2: '/u01/app/oracle/oradata/RMAN/datafile/apex01.dbf'

 

..............................................................................................................................

Solution

 

SQL> select NAME from v$datafile where file#=2;

 

NAME

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

/u01/app/oracle/oradata/RMAN/datafile/apex01.dbf

 

SQL> alter database datafile 2 offline drop;

 

Database altered.

 

SQL> alter database open;

 

Database altered.