Move/rename datafiles in Oracle

Moving datafiles of a database: The datafiles reside under /home/oracle/ORACLE_HOME/database/OLD_LOCATION and have go to /home/oracle/ORACLE_HOME/database/NEW_LOCATION/.


SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME SUBSTR(FILE_NAME,1,70)

----------------------------------------------------------------------

SYSTEM /home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf

UNDO /home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf

DATA /home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf

SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora


SQL> select name from v$controlfile;


NAME
--------------------------------------------------------------------------------
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_1.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_2.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_3.ora


Now, as the files to be moved are known, the database can be shut down:

SQL> shutdown

The files can be copied to their destination:


$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/system.dbf
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/undo.dbf
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/data.dbf
$
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo1.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo2.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo3.ora
$
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_1.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_1.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_2.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_2.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_3.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_3.ora


The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.


$ cp /home/oracle/ORACLE_HOME/dbs/initOLD.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/initNEW.ora


The new location for the control files must be written into the (copied) init.ora file:
/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


control_files = (/home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_1.ora,
                        /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_2.ora,
                       /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_3.ora)

$ sqlplus "/ as sysdba"

SQL> startup exclusive mount pfile=/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/system.dbf';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/undo.dbf';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/data.dbf';


SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo1.ora';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo2.ora';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo3.ora';

SQL> shutdown

SQL> startup pfile=/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


Thanks :) 

Please share your really important  comments and views  for improvement......