In my previous article i have given the idea about different errors coming in Oracle. In my last article i have given the idea about ORA-27101: shared memory realm does not exist error in detail. In this article i would like to give more detailed information about ORA-01157: cannot identify/lock data file string – see DBWR trace file error. The ORA-01157: cannot identify/lock data file string – see DBWR trace file error is another type of most common error which is coming due the lock fail to specified file. This kind of error will occure when user tries to bring tablespaces online. I would like to give you the scenario as well as solution of this popular error.
Scenario for the error ORA-01157:
The most common scenario for this error is when DBA or developer tries to bring tablespaces online this kind of issue will come followed by Ora-01110 error.
SQL> alter tablespace Amit_tablespace;
alter tablespace Amit_tablespace
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 8: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\Amit_tablespace.DBF’
The above error will come when we try to alter the table-space. I would like to discuss the cause of this error and then need to give the resolution steps to solve this error in detail.
Why ORA-01157 error is coming?
The above error will come because of the background process was unable to file the data files. The second reason might be the file was failed to lock because the file was already in use.The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.
What action needs to check ?
Stpe 1 : The error is occurred due to the file so the first step is to make the file available to database.
Step 2 :The next step is to open the database or do ALTER SYSTEM CHECK DATAFILES.
Reasons behind this error might be
1.If the specified datafile is deleted or corrupt
2.If the specified datafile is renamed or moved
3.Mount point is incorrect
4.There might be issues with read or write permission on datafile
What to do to resolve error – ORA-01157: cannot identify/lock data file string – see DBWR trace file ?
In this section i would like to give you few checks so that you can try to resolve the error with that checks.
Check 1: If datafile is deleted or corrupt
There might be two situations in this scenario.
Situation 1 : If the data file is delete and is not of TEMP or UNDO tablespace.
Resolution : In this situation we need to recovered it by using valid backup only.
Situation 2 :If the data file is delete and is not of TEMP or UNDO tablespace but the tablespace does not contain important segements.
Resolution :
The datafile can be dropped offline and recreate it.
Check 2 : If datafile is renamed or moved.
Resolution : In this situation we require to rename that file to correct name or we need to get that file in to correct position.
Check 3 : If Mount point is incorrect
Resolution : We require to simply recreate the mount point.
Check 4 : Check if it is related to permission to the datafile,
Resolution : We require to give correct permission to the datafile at Operating system level.
Real life Example of ORA-01157: cannot identify/lock data file :
The following example shows that the ORA-01157 error at local enviroment when user tries to restore a cold backup. In the following situation fortunately the tablespace was added for testing purpose and not so important. In the situation we require to use “OFFLINE DROP” clause for dropping the datafile and then we require to open the specified database.
C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on wed aug 5 10:12:16 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 723467856 bytes
Fixed Size 4553345 bytes
Variable Size 34556789023 bytes
Database Buffers 324567845bytes
Redo Buffers 234566 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 – see DBWR trace file
ORA-01110: data file 16: 'E:\oracle\app\Amit.S\oradata\orcl\amit_datafile.dbf'
SQL> select NAME from v$datafile where file#=16;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\APP\NIMISH.GARG\ORADATA\ORCL\Amit_datafo;e.DBF
SQL> alter database datafile 16 OFFLINE DROP;
Database altered.
SQL> alter database open;
Database altered.
The above alter statement is used to drop that datafile. But make sure that the tablespace does not contain the important data while dropping the datafile. You should always have database backup to take or fetch data files from there.I hope you like this article. If you like this article or if you have any comments kindly comment in comments section.