Move datafiles (system,sysaux,users,undo,temp)from one location to another where database shutdown is permitted.

Move datafiles (system,sysaux,users,undo,temp)from one location to another where database
shutdown is permitted.


..First step..........perform some tasks

To know the how many Tablesapces in our database;


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

To know the datafiles of all Tablesapces.



SQL> select name from v$datafile;

NAME
----------------------------------------
/u01/app/oracle/oradata/dborcl/system01.
dbf

/u01/app/oracle/oradata/dborcl/example01
.dbf

/u01/app/oracle/oradata/dborcl/sysaux01.
dbf

/undotbs_1/dborcl/undotbs01.dbf
/u01/app/oracle/oradata/dborcl/users01.d



........................
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

.....2............................copy the old location datafile to another file location................


[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/system01.dbf /redoa/system01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/example01.dbf /redob/example01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/sysaux01.dbf /redoc/sysaux01.dbf
[oracle@ab ~]$ cp /undotbs_1/dborcl/undotbs01.dbf /temptbs_1/undotbs01.dbf
[oracle@ab ~]$ cp /temptbs_1/dborcl/temp01.dbf /undotbs_1/temp01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/users01.dbf /u01/app/users01.dbf


.....2...............alter the database files to new location. startup the database in mount satge...............




[oracle@ab ~]$ export ORACLE_SID=dborcl
[oracle@ab ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 12 18:46:56 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size                  2294456 bytes
Variable Size             775949640 bytes
Database Buffers          100663296 bytes
Redo Buffers                6303744 bytes
Database mounted.




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

Database altered.

SQL>  alter database rename file '/u01/app/oracle/oradata/dborcl/example01.dbf' to '/redob/example01.dbf';

Database altered.

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

Database altered.

SQL> alter database rename file '/undotbs_1/dborcl/undotbs01.dbf' to '/temptbs_1/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/temptbs_1/dborcl/temp01.dbf' to '/undotbs_1/temp01.dbf';

Database altered.

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

Database altered.

SQL>
.........3.......................open the database................................................

SQL> alter database open;

Database altered.


.........4...............now chesk the location of datafiles............................................

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/users01.dbf

UNDOTBS1
/temptbs_1/undotbs01.dbf

SYSAUX
/redoc/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf

EXAMPLE
/redob/example01.dbf