Rename and Relocate datafiles

This post will show you How to rename your datafiles and also relocate datafiles.

Rename Datafiles in Single Tablespace  (Database Open Mode) :-

Caution : Backup your tablespace Before you will do some change in your tablespace.

Step 1:  I just create tablespaces with two datafiles

SQL> create tablespace renametbs datafile 'd:\backup\renametbs01.dbf' size 50m;

Tablespace created.

SQL> alter tablespace renametbs add datafile 'd:\backup\renametbs02.dbf' size 50m;

Tablespace altered.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='RENAMETBS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\BACKUP\RENAMETBS01.DBF
RENAMETBS

D:\BACKUP\RENAMETBS02.DBF
RENAMETBS

Step 2: Tablespace Offline

SQL> alter tablespace renametbs offline normal;

Tablespace altered.

Step  3: Rename your datafile, Here you need to listen something , when you’re execute rename command, It will show some errors like below

L> alter tablespace renametbs rename datafile 'd:\backup\renametbs01.dbf','d:\backup\renametbs02.dbf' TO 'd:\backup\renametbs1.dbf','d:\backup\renametbs2.dbf

ter tablespace renametbs rename datafile 'd:\backup\renametbs01.dbf','d:\backup\renametbs02.dbf' TO 'd:\backup\renametbs1.dbf','d:\backup\renametbs2.dbf'

ROR at line 1:
A-01525: error in renaming data files
A-01141: error renaming data file 8 - new file 'd:\backup\renametbs1.dbf' not
und
A-01110: data file 8: 'D:\BACKUP\RENAMETBS01.DBF'
A-27041: unable to open file
D-04002: unable to open file
S-Error: (OS 2) The system cannot find the file specified.

What is this means, After tablespace offline, you need to copy and paste same location of your datafiles and rename manully (OS level) which name you will implement, Otherwise the system cannot find the files. 

SQL> alter tablespace renametbs rename datafile 'D:\BACKUP\RENAMETBS01.DBF','D:\BACKUP\RENAMETBS02.DBF' TO 'D:\BACKUP\RENAMETBS1.DBF','D:\BACKUP\RENAMETBS2.DBF
';

Tablespace altered.

Step 4: Tablespace Online and Take backup.

SQL> alter tablespace renametbs online;

Tablespace altered.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='RENAMETBS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\BACKUP\RENAMETBS1.DBF
RENAMETBS

D:\BACKUP\RENAMETBS2.DBF
RENAMETBS
SQL>

Relocate Datafiles in Single Tablespace (Database Open Mode) :

Step 1: Check your current Datafiles location

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='RENAMETBS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\BACKUP\RENAMETBS1.DBF
RENAMETBS

D:\BACKUP\RENAMETBS2.DBF
RENAMETBS

Step 2: you need to know your datafiles size,

SQL> select file_name,bytes from dba_data_files where tablespace_name='RENAMETBS';

FILE_NAME
--------------------------------------------------------------------------------
     BYTES
----------
D:\BACKUP\RENAMETBS1.DBF
  52428800

D:\BACKUP\RENAMETBS2.DBF
  52428800

Step 3: Tablespace offline

SQL> alter tablespace renametbs offline normal;

Tablespace altered.

Step 4: Copy your datafiles to New location and Rename it manually (OS level).

SQL> alter tablespace renametbs rename datafile 'D:\BACKUP\RENAMETBS1.DBF','D:\BACKUP\RENAMETBS2.DBF' TO 'C:\RENAMETB\RENAMETBS1.DBF','C:\RENAMETB\RENAMETBS2.D
BF';

Tablespace altered.

Step 5:  After relocated , your current datafile location and tablespace online , take backup.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='RENAMETBS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
C:\RENAMETB\RENAMETBS1.DBF
RENAMETBS

C:\RENAMETB\RENAMETBS2.DBF
RENAMETBS

SQL> alter tablespace renametbs online;

Tablespace altered.

SQL>

Rename Datafile for Multiple Tablespace  (Database Mount Mode):

Step 1: Startup mount

SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             209715664 bytes
Database Buffers          318767104 bytes
Redo Buffers                5804032 bytes
Database mounted.

Step 2: Check your datafile

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSTEM01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSAUX01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\UNDOTBS01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\USERS01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\EXAMPLE01.DBF
D:\BACKUP\RCRMAN01.DBF
D:\BACKUP\USERSTBS01.DBF
C:\RENAMETB\RENAMETBS1.DBF
D:\BACKUP\TBSPITR01.DBF
C:\RENAMETB\RENAMETBS2.DBF
D:\BACKUP\TESTTB01.DBF

11 rows selected.

Here I choosed “C:\RENAMETB\RENAMETBS1.DBF ” to ” C:\RENAMETB\RENAMETBS01.DBF “, AS Same Like “D:\BACKUP\TESTTB01.DBF” to “D:\BACKUP\TESTTB1.DBF 

Step 3: Rename Datafile for multiple tablespace

SQL> alter database rename file 'C:\renametb\renametbs1.dbf','D:\backup\testtb01.dbf' to 'C:\renametb\renametbs01.dbf','D:\backup\testtb1.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSTEM01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\SYSAUX01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\UNDOTBS01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\USERS01.DBF
C:\APP\ORACLE\MAZAR\ORADATA\AZARDB\EXAMPLE01.DBF
D:\BACKUP\RCRMAN01.DBF
D:\BACKUP\USERSTBS01.DBF
C:\RENAMETB\RENAMETBS01.DBF
D:\BACKUP\TBSPITR01.DBF
C:\RENAMETB\RENAMETBS2.DBF
D:\BACKUP\TESTTB1.DBF

11 rows selected.

Step 4: Alter your database opened.

SQL> alter database open;

Database altered.

SQL> select file_name,tablespace_name from dba_data_files where tablespace_name in ('RENAMETBS','TESTTB');

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
C:\RENAMETB\RENAMETBS01.DBF
RENAMETBS

C:\RENAMETB\RENAMETBS2.DBF
RENAMETBS

D:\BACKUP\TESTTB1.DBF
TESTTB
SQL>

Refer :-

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/dfiles005.htm#i1006457