How To Rename Or Move A Datafile In Oracle: In this article we will see How To Rename Or Move A Datafile In Oracle Database.
Here is a sample procedure for relocating a datafile.
Assume the following conditions:
An open database has a tablespace named users that is made up of datafiles all located on the same disk.
The datafiles of the users tablespace are to be relocated to different and separate disk drives.
You are currently connected with administrator privileges to the open database.
You have a current backup of the database.
In Oracle 12c and later versions :
If you are in 12c version ,then renaming a datafile can be done online, without making the datafile offline.
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='MASTER';
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------------ -------
/u01/app/oracle/oradata/ORCL/master01.dbf MASTER ONLINE
SQL>
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/master01.dbf' to '/home/oracle/master02.dbf';
Database altered.
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='MASTER';
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------------ -------
/home/oracle/master02.dbf MASTER ONLINE
SQL>
In 11g and previous versions:
If you are in 11g or previous version, you need to follow below steps to move or rename a datafile without shutting down the database.
- First make the datafile offline.
- move the datafile as os level.
- Rename the datafile at database level.
- recover the particular datafile.
- make the datafile online.
Step-1 First make the datafile offline.
SQL>
SQL> set lines 200 pages 100
SQL> col FILE_NAME for a70
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='MASTER';
FILE_NAME TABLESPACE_NAME ONLINE_
------------------------------------------------------------ ------------------------------ -------
/u01/app/oracle/oradata/ORCL/master01.dbf MASTER ONLINE
SQL>
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/master01.dbf' offline;
Database altered.
SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='MASTER';
FILE_NAME TABLESPACE_NAME ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/u01/app/oracle/oradata/ORCL/master01.dbf MASTER RECOVER
SQL>Step-2 Move the datafile as os level.
SQL>
SQL> !mv /u01/app/oracle/oradata/ORCL/master01.dbf /home/oracle/master02.dbf
SQL>Step-3 Rename the datafile at database level.
SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/master01.dbf' to '/home/oracle/master02.dbf';
Database altered.
SQL>Step-4 Recover the particular datafile.
SQL>
SQL> alter database datafile '/home/oracle/master02.dbf' online;
alter database datafile '/home/oracle/master02.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/master02.dbf'
SQL>
SQL>
SQL> recover datafile 6;
Media recovery complete.
SQL>
SQL>Step-5 Make the datafile online.
SQL>
SQL> alter database datafile '/home/oracle/master02.dbf' online;
Database altered.
SQL>
SQL>
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='MASTER';
FILE_NAME TABLESPACE_NAME ONLINE_
---------------------------------------------------------------------- ------------------------------ -------
/home/oracle/master01.dbf MASTER ONLINE
SQL>I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.