Renaming Data Files

We had an issue recently were someone's fat fingers (not guilty, honest) had accidently created a data file with an extension of .bdf as opposed to .dbf.

/u03/oradata/PRD/datafile/my_data_file.004.bdf

This is not really an issue - as far as the DB is concerned the database engine does not really care about the extension.

However in this instance the incorrectly named file was slipping past the “Database Exclude” filter in the sys admins file system backup utility.

Now Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Unfortunately this was 11g.

There are two ways to re-name a datafile, however both methods will require some downtime.

Method 1. - Take the associated tablespace offline and then rename the datafile.

This requires an outage to rename the datafile as we have to take tablespace offline to rename it.

 -- Take Table Space off-line

ALTER TABLESPACE PRD_PRIMARY_DATA OFFLINE NORMAL;

-- Rename Data File

ALTER TABLESPACE MY_DATA

RENAME DATAFILE '/u03/oradata/PRD/datafile/my_data_file.004.bdf'

TO '/u03/oradata/PRD/datafile/my_data_file.004.dbf';

-- Put Data File back on-line

ALTER TABLESPACE MY_DATA ONLINE;

Method 2. - Shutdown database and rename datafile

This could also be performed by taking the shuting down the database

Rename datafile by taking down the database.

sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE

SQL> HOST mv /u03/oradata/PRD/datafile/my_data_file.004.bdf /u03/oradata/PRD/datafile/my_data_file.004.dbf

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RENAME FILE '/u03/oradata/PRD/datafile/my_data_file.004.bdf' TO '/u03/oradata/PRD/datafile/my_data_file.004.dbf';

SQL> ALTER DATABASE OPEN;

Oracle - Renaming and Relocating Datafiles

Oracle Base - Renaming or Moving Oracle Files