Skip to content

How to Resolve ORA-01516 Error Message

  • by

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

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

  1. Incorrect file path
  2. 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.

  3. Misspelled file name
  4. Occasionally, you might take one file as another or misspelled the file name. Please inspect the filename again.

  5. Incorrect database
  6. 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
  7. Already dropped
  8. Chances are, you could have dropped the file before and you don't remember.

  9. Nothing ever
  10. 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.

Also see: How Will the Database React to Missing Tempfiles

Leave a Reply

Your email address will not be published.