ORA-01516: nonexistent log file, datafile, or temporary file
There're two scenarios that throw ORA-01516 going to be introduced in this post.
1. Rename Tempfile
I was planning to move a tempfile from one disk to another in a 9i database, but I got "ORA-01516: nonexistent log file, datafile, or temporary file" while I renamed the tempfile. In this post I will describe how I got it.
First of all, I offline the tempfile first.
SQL> alter database tempfile '/oradata/ORCL/TEMP2.dbf' offline;
Database altered.
I copied the tempfile to the new destination, then I performed a renaming, but failed.
SQL> alter database rename file '/oradata/ORCL/TEMP2.dbf' to '/new/ORCL/TEMP2.dbf';
alter database rename file '/oradata/ORCL/TEMP2.dbf' to '/new/ORCL/TEMP2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or temporary file
"/oradata/ORCL/TEMP2.dbf"
Normally, it's a filename mismatch, maybe a typo in the statement. So I checked the absolute path in the statement very carefully and found nothing wrong. This is weird. I have done such operation a hundred of times in 11g and 12c databases, and never met the error before.
2. Offline Tempfile
Tried to offline a tempfile, but it failed with ORA-01516.
SQL> alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' offline;
alter database tempfile '+DATA/ORCLCDB/ORCLPDB/temp01.dbf' offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "+DATA/ORCLCDB/ORCLPDB/temp01.dbf" in the current container
ORA-01516 means that the file you try to operate on is not existing or not found, you should make sure the file path is correctly specified. There're could be several possibilities to cause ORA-01516.
Causes and Solutions
- Incorrect file path
- Misspelled file name
- Incorrect database
- Already dropped
- Nothing ever
The file that you want to operate on may be in another directory like /oradata2 or disk group DATA2. You should make sure about it.
Occasionally, you might take one file as another or misspelled the file name. Please inspect the filename again.
In wrong database, the file is definitely not found. You should go for the right database.
For a non-CDB database, you may check the database name like this:
SQL> select name from v$database;
NAME
---------
ORCL
For a pluggable database, you may check the database name like this:
SQL> select name from v$pdbs;
NAME
---------
ORCLPDB
Chances are, you could have dropped the file before and you don't remember.
There's no such file in the database ever since the creation. You might copy and paste the statement from somewhere else.
Last Resort
Just drop it and create a new one for yourself.
Drop the original tempfile.
SQL> alter database tempfile '/oradata/ORCL/TEMP2.dbf' drop including datafile;
Database altered.
Add a new tempfile in the new destination.
SQL> alter tablespace TEMP1 add tempfile '/new/ORCL/TEMP2.dbf' size 500m autoextend on next 50m maxsize unlimited;
Tablespace altered.
We finished our job.