Relocate/Rename of data files in Oracle 10g

Relocate/Rename of data files in Oracle 10g.

Cause and Prerequisites

You have created a datafile on the wrong filesystem or drive.

You add a new filesystem(s), drive (s) to the system and want to redistribute the data files.You restore some datafiles to a new location because the original is not available any more

Following Step perform for relocate data files as per Oracle

1. Full database backup and check consistency of backup.

2. Shutdown Database

3. Take the backup of contolfile.

Syntex: - cp –pr control01.dbf /backup

cp /ORCL/redolog_1/ctrlfile1/control01.ctl   /ORCL/redolog_1/ctrlfile1/control01.ctl_bkp

cp /ORCL/redolog_2/ctrlfile2/control02.ctl   /ORCL/redolog_2/ctrlfile2/control02.ctl_bkp

cp /ORCL/redolog_3/ctrlfile3/control03.ctl   /ORCL/redolog_3/ctrlfile3/control03.ctl_bkp

4. Copy the datafiles to the new location by using OS commands.

Syntex: - cp -pr ‘Source_Location’ ‘Target_Location’

Copy Datafiles

cd /ORCL/datafile_2

cp  orcl_ts04.dbf  /ORCL/datafile_11/datafile11

cd /ORCL/datafile_3  

cp  orcl_idx04.dbf  /ORCL/datafile_11/datafile11

cd /ORCL/datafile_4

cp  orcl_idx20.dbf  /ORCL/datafile_11/datafile11

5. After that perform this activity

SQL> Startup mount;

6. Execute this scripts:-

Syntex:- Alter database rename file ‘Source_Location/file_name’ ‘Target_Location/file_name’

Alter database rename file ' /ORCL/datafile_2/orcl_ts04.dbf' to ' /ORCL/datafile_11/datafile11/orcl_ts04.dbf';

Alter database rename file ' /ORCL/datafile_3/orcl_idx04.dbf' to ' /ORCL/datafile_11/datafile11/orcl_idx04.dbf';

Alter database rename file ' /ORCL/datafile_4/orcl_idx20.dbf ' to ' /ORCL/datafile_11/datafile11/orcl_idx20.dbf';

7. After that open the database

SQL> alter database open;

Database altered.

8. When database opened successfully then Remove datafiles from old location.

Remove Datafiles

cd /ORCL/datafile_2

rm  orcl_ts04.dbf

cd /ORCL/datafile_3  

rm  orcl_idx04.dbf

cd /ORCL/datafile_4

rm  orcl_idx20.dbf 

Thanks & Regards

Gaurav Sharma