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