Rename Oracle Files

Today I come acorss one situation where I need to rename our database files.

There are three types of files we can rename or move.

1. Datafiles

2. Controlfile

3. Redolog files

Note: We can't rename temporary files, for that we need to just recreate temporary tablespace with rename or new located tempfile.

Datafiles

There is two option for rename datafiles.

Option:1

1. shutdown the database

2. move or rename the datafiles in the file system

3. startup mount the database

4. rename datafiles in the database

SQL> alter database rename file

'full_old_path_location\datafile_name01.dbf'

to

'full_new_path_location\datafile_name01.dbf';

5. Open the database

Option: 2

1. Offline the tablespace which belong to rename or move datafiles

SQL> alter tablespace TBS_NAME offline;

2. Move or rename the datafiles on file system

3. rename or move the datafiles in the database

SQL> alter tablespace TBS_NAME

rename datafile

'full_old_path_location\datafile_name01.dbf'
to
'full_new_path_location\datafile_name01.dbf';

4. Online the tablespace which belong to rename datafiles

Redologfiles

1. shutdown the database

2. Move or Rename the redologfiles in the file system

3. startup mount the database

4. Rename or Move the redologfiles in the database

SQL> alter database

rename file

'full_old_path_location\logfile_name01.log'

to

'full_new_path_location\logfile_name01.log';

5. Open the database

Controlfiles

1. set the new name or new location in the spfile

NOTE: Below command will work if database start with SPFILE

SQL> alter system set

control_files='full_path_location\controlfile_name01.ctl'

SCOPE=SPFILE;

2. shutdown the database

3. Rename or move the controlfile in the file system

4. startup the database

NOTE: If database start with PFILE then use the following procedure

1. shutdown the database

2. edit the PFILE and set the new location or rename the controlfile

3. rename or move the controlfile in the file system

4. startup the database with the modified PFILE