Skip to content

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

  • by

ORA-01157

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:
'/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf'

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

Solutions

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