Skip to content

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

  • by


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.

1. Missing File

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.

Then bring the tablespace online.

2. Inaccessible File

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

Then 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 *