How to move all types of datafiles from one location to another in oracle 11g

Oracle Version: 11g
OS Version: Rhel 6

Step 1. Check the datafiles location & tablespace name .
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/users01.dbf           USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf         UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf          SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf          SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf         EXAMPLE
/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf            TEMP

Step 2.Shutdown the database and  move the data files into new location.

SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/users01.dbf /u02/oradata/users01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/undotbs01.dbf /u02/oradata/undotbs01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/sysaux01.dbf  /u02/oradata/sysaux01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/system01.dbf /u02/oradata/system01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf /u02/oradata/GHHSTORE_DATA_TBS.dbf
SQL>! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf
Step 3. Start the database in mount point and rename the datafiles.

SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/undotbs01.dbf' to
'/u02/oradata/undotbs01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to
'/u02/oradata/sysaux01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/system01.dbf' to
'/u02/oradata/system01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf' to
'/u02/oradata/GHHSTORE_DATA_TBS.dbf' ;

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Step 4.Open the database and check datafile location.
SQL>alter database open;
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u02/oradata/users01.dbf             USERS
/u02/oradata/undotbs01.dbf           UNDOTBS1
/u02/oradata/sysaux01.dbf           SYSAUX
/u02/oradata/system01.dbf             SYSTEM
/u02/oradata/example01.dbf           EXAMPLE
/u02/oradata/GHHSTORE_DATA_TBS.dbf   GHHSTORE_DATA_TBS

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME                          TABLESPACE_NAME
---------------------------------- ------------------------------
/u02/oradata/temp01.dbf            TEMP

Done..

Incase if we cant shutdown the database, we can still perform the activity except for system datafile
and undo datafile.However we can change the default undo tablespace by creating a new undo tablespace
and drop the old one.

For users or any non-default system datafile:-
SQL> alter tablespace users offline;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';
SQL>  alter tablespace users online;

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

FILE_NAME                                          ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/users01.dbf                           ONLINE  USERS

For sysaux datafile:-
SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> ! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to '/u02/oradata/sysaux01.dbf';

Database altered.

SQL> alter tablespace sysaux online;

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

FILE_NAME                                          ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/sysaux01.dbf                          ONLINE  SYSAUX


For temporary datafile:-

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME                                 TABLESPACE_NAME         STATUS
---------------------------------------- ----------------------  -------
/u01/app/oracle/oradata/prim/temp01.dbf      TEMP                   ONLINE

SQL> alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' offline;

Database altered.

SQL> ! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';

Database altered.
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;

FILE_NAME                                 TABLESPACE_NAME         STATUS
---------------------------------------- ----------------------  -------
/u02/oradata/temp01.dbf       TEMP                    OFFINE

SQL> alter database tempfile '/u02/oradata/temp01.dbf' online;

Database altered.
SQL> select file_name, tablespace_name , status from dba_temp_files;

FILE_NAME                                TABLESPACE_NAME                STATUS
---------------------------------------- ------------------------------ -------
/u02/oradata/temp01.dbf                  TEMP                           ONLINE


For system Datafile:-
We cant offline a system tablespace. so for the we need to shutdown the database and then
move it to the new location as describe before.


For undo datafile:-
We cant drop or move default undo tablespace.
First create a new undo tablespace and then drop the old one.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/undotbs01.dbf' size 100M;

Tablespace created.
SQL>  alter system set undo_tablespace= undotbs2 ;
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>select file_name , online_status , tablespace_name from dba_data_files where tablespace_name like 'UNDO%';

FILE_NAME                                ONLINE_ TABLESPACE_NAME
---------------------------------------- ------- ------------------------------
/u02/oradata/undotbs01.dbf               ONLINE  UNDOTBS2





Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.