How to move data files from one mount point to another mount point

How to move/rename data files

We can move or rename data files other than SYSTEM, SYSAUX and UNDO data files while the database is up and running. But we need to keep tablespaces offline while the data files move or name.  Database should be in mount stage while SYSTEM, SYSAUX and UNDO data files to move or rename.

Step1:  Find the tablespace names and associated data files.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME        FILE_NAME

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

USERS                              /u01/app/oracle/oradata/TEST/users01.dbf

UNDOTBS1                     /u01/app/oracle/oradata/TEST/undotbs01.dbf

SYSAUX                          /u01/app/oracle/oradata/TEST/sysaux01.dbf

SYSTEM                          /u01/app/oracle/oradata/TEST/system01.dbf

EXAMPLE                       /u01/app/oracle/oradata/TEST/example01.dbf

Step 2: Move/rename USERS and EXAMPLE data files.

a) Create new directory structure where we want to move the files.

[oracle@server3 TEST]$ mkdir –p /u02/oradata/TEST

b) Move the data files related to USERS and EXAMPLE tablespaces. Put both USERS and EXAMPLE tablespaces offline. Both the tablespace will not be available when we put them offline.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace example offline;

Tablespace altered.

c) Move the USERS and EXAMPLE data files to new location

SQL> !

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/users01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/example01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ exit

d) Update the controlfile and data dictionary with data files new location.

SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/TEST/users01.dbf' to '/u02/oradata/TEST/users01.dbf';

Tablespace altered.

SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/TEST/example01.dbf' to '/u02/oradata/TEST/example01.dbf';

Tablespace altered.

SQL>

e) Bring tablespaces online.

SQL> alter tablespace users online;

Tablespace altered

SQL> alter tablespace example online;

Tablespace altered

f) Find the updated location details for USERS and EXAMPLE tablespaces.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

USERS                  /u02/oradata/TEST/users01.dbf

UNDOTBS1         /u01/app/oracle/oradata/TEST/undotbs01.dbf

SYSAUX              /u01/app/oracle/oradata/TEST/sysaux01.dbf

SYSTEM              /u01/app/oracle/oradata/TEST/system01.dbf

EXAMPLE           /u02/oradata/TEST/example01.dbf

Step 3: Move SYSTEM, SYSAUX, UNDO data files to new location. Database should be in mount stage to move these data files. Shutdown the database and start in mount stage.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  221331456 bytes

Fixed Size                                2251856 bytes

Variable Size                        163578800 bytes

Database Buffers                50331648 bytes

Redo Buffers                         5169152 bytes

Database mounted.

SQL>

a) Move SYSTEM, SYSAUX and UNDO related data files to new location.

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/system01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/sysaux01.dbf /u02/oradata/TEST/

[oracle@server3 ~]$ mv /u01/app/oracle/oradata/TEST/undotbs01.dbf /u02/oradata/TEST/

b) Update controlfile and data dictionary with new data files locations.

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

Database altered.

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

Database altered.

SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/TEST/example01.dbf' to '/u02/oradata/TEST/example01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

USERS                    /u02/oradata/TEST/users01.dbf

UNDOTBS1           /u02/oradata/TEST/undotbs01.dbf

SYSAUX                /u02/oradata/TEST/sysaux01.dbf

SYSTEM                /u02/oradata/TEST/system01.dbf

EXAMPLE             /u02/oradata/TEST/example01.dbf