Temp File

How to Resolve ORA-01516: nonexistent log file, datafile, or temporary file

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.

ORA-01516

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.

Solutions

Just drop it and create a new one for yourself.

Please note that, I have offlined this tempfile before I drop it.

  1. Drop the original tempfile.
  2. SQL> alter database tempfile '/oradata/ORCL/TEMP2.dbf' drop including datafile;

    Database altered.

  3. Add a new tempfile in the new destination.
  4. SQL> alter tablespace TEMP1 add tempfile '/new/ORCL/TEMP2.dbf' size 500m autoextend on next 50m maxsize unlimited;

    Tablespace altered.

We finished our job. No more ORA-01516.

Alos see: How Will the Database React to Missing Tempfiles

Leave a Reply

Your email address will not be published. Required fields are marked *