Skip to content

How to Resolve ORA-01157: cannot identify/lock data file


Saw error ORA-01157 when we tried to bring a tablespace online.

SQL> alter tablespace example online;
alter tablespace example online
ERROR at line 1:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13:

ORA-01157 means that the data file indicated in error ORA-01110 could be missing or inaccessible, so the database fails to lock it.


We solve ORA-01157 respectively according to their phenomenon.

Forgot to Rename

Sometimes, DBA forgot to rename the data file in the database after moving the physical data file. Consequently, the database found no file.

[oracle@test ~]$ ls -l /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
ls: cannot access '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf': No such file or directory

To solve it, we should check the existence of the directory first, then point to the current file in the database by renaming it.

SQL> alter database rename file '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' to '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/erp_tbs_01.dbf';

Database altered.

Permission and Ownership

After restoring the data file, the permission set or ownership may be incorrect.

[oracle@test ~]$ ls -l /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
-rw-r----- 1 root root 209723392 Jun 28 02:13 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf

As we can see, the ownership has gone, we should correct it.

[root@test ~]# chown oracle:oinstall /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
[root@test ~]# ls -l /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf
-rw-r----- 1 oracle oinstall 209723392 Jun 28 02:13 /u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf

File Does Not Exist Physically

If the file can be found nowhere, you may need to restore and recover data files from backup by RMAN.

After fixing it, we take the tablespace online.

SQL> alter tablespace example online;

Tablespace altered.

We solved it.

Leave a Reply

Your email address will not be published. Required fields are marked *