Rename a database file

At times it may be necessary to move or rename datafiles belonging to a database for performance reasons or to have a meaningful name. There are two methods of doing this. The first is at a database level, which needs the database to be in the mount state and the second is at a tablespace level. I would choose the first method if there are many files to be reorganized - prepare a script and run it with the database in the mount state (not opened).

Database Level - Steps:

1. Shutdown normal the database.

SVRMGR> CONNECT INTERNAL
SVRMGR> SHUTDOWN

2. Rename or copy the files to their new name or location.

$ cp /disk1/MYSID/md1.dbf /disk1/MYSID/mydata1.dbf
$ cp /disk2/MYSID/md1.dbf /disk1/MYSID/mydata2.dbf

3. Startup and mount the database

SVRMGR> CONNECT INTERNAL
SVRMGR> STARTUP MOUNT

4. Rename the datafile inside Oracle.

SVRMGR> ALTER DATABASE RENAME FILE
SVRMGR> '/disk1/MYSID/md1.dbf' TO
SVRMGR> '/disk1/MYSID/mydata1.dbf';
SVRMGR> ALTER DATABASE RENAME FILE
SVRMGR> '/disk2/MYSID/md1.dbf' TO
SVRMGR> '/disk1/MYSID/mydata2.dbf';

5. Open the database

SVRMGR> ALTER DATABASE OPEN;

A redo log file also can be renamed in the above method.

Tablespace Level - Steps:

If the database is 24x7 and cannot be shutdown, files can be reorganized by taking the tablespace offline.

1. Make the tablespace where the datafile belongs to offline.

SVRMGR> ALTER TABLESPACE MYDATA OFFLINE;

2. Copy the files at the operating system.

$ cp /disk1/MYSID/md1.dbf /disk1/MYSID/mydata1.dbf

3. Rename the datafile inside Oracle.

SVRMGR> ALTER DATABASE RENAME FILE
SVRMGR> '/disk1/MYSID/md1.dbf' TO
SVRMGR> '/disk1/MYSID/mydata1.dbf';

4. Make the tablespace online.

SVRMGR> ALTER TABLESPACE MYDATA ONLINE;

Once the datafiles are renamed or moved, it is better to create a backup of the control file.

SVRMGR> CONNECT INTERNAL;
SVRMGR> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The control file creation script is written to the USER_DUMP_DEST (init.ora parameter) directory.

You cannot drop or delete a datafile.