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 tried to rename it. 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;
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
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.
Just drop it and create a new one for yourself.
Please note that, I have offlined this tempfile before I drop it.
- Drop the original tempfile.
- Add a new tempfile in the new destination.
SQL> alter database tempfile '/oradata/ORCL/TEMP2.dbf' drop including datafile;
SQL> alter tablespace TEMP1 add tempfile '/new/ORCL/TEMP2.dbf' size 500m autoextend on next 50m maxsize unlimited;
We finished our job. No more ORA-01516.