ORA-01511 and ORA-01523

While renaming a datafile or redo log file you might face these both errors together and command would fail as shown below.

SQL> ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’; ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users01.dbf’; * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01523: cannot rename datafile to d:\oracle\oradata\db\users01.dbf’ – File already part of database

As error message ORA-01523 is clearly stating, the new name we have specified for the datafile (or log file), a data/log file already exists with the same name. To solve this problem you should choose a different path/name of the destination file. Following is an example of it.

SQL> ALTER DATABASE RENAME FILE ‘d:\oracle\oradata\db\users01.dbf’ to ‘e:\oracle\oradata\users02.dbf’;

An Interesting Scenario
Once we faced a scenario where we were receiving this error during an RMAN restore. We were trying to restore all datafiles to a single destination (on source database where backup was taken, the datafiles were at different locations), and restore was failing with ORA-01523 when RMAN tried to execute “SWITCH DATAFILE ALL” statement. After the failure, we tried to manually renaming the datafiles using alter database statement, as I showed in example above, but it again failed with same error message.

Later we realized that we have same datafile name for multiple datafiles at the source system, for example, there was a file d:\oracle\oradata\db\users01.dbf as well as a file c:\oracle\oradata\db\users01.dbf. If you have noticed, “users01.dbf” is the physical file that existed on 2 different locations. During restore, RMAN first restored d:\oracle\oradata\db\users01.dbf as d:\oracle\oradata\db\users01.dbf, and then restored c:\oracle\oradata\db\users01.dbf as d:\oracle\oradata\db\users01.dbf, thus overwriting previously restored users01.dbf. After the restoration, when it tried to rename c:\oracle\oradata\db\users01.dbf as d:\oracle\oradata\db\users01.dbf, it must return ORA-01523 because there is already d:\oracle\oradata\db\users01.dbf available at this location.

In such scenario you should first check which file physical exists after the restoration and then rename this file to a different name, and then restore the missing file again.

Lesson learnt is that we should always make sure that we must never use same physical file name multiple times (on different locations) in the single database to avoid such issues.