rename/relocate SYSTEM, SYSAUX or User Tablespace datafiles

 

 

This is traditional approach to move/rename SYSTEM, SYSAUX or users tablespace. In this method, we need database downtime as we can’t take SYSTEM or SYSAUX tablespace offline.
I recommend full cold backup before performing mentioned activity.
Consider scenario where I want to move SYSTEM and SYSAUX tablespace to new location.
Step I:
After considering full cold backup of your database, shutdown database with normal or immediate mode only.

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

Step II:
After shut down database normally, I am going to copy datafiles from original location to its new location.
Following query will provide tablespace name and its datafiles details.

SQL> select file_name,tablespace_name from dba_data_files;

Copying datafiles:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/app/oracle/oradata/RTS_NEW/system01.dbf
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf

After copy, verify size of datafiles on both locations to check whether copy successful or not.
Step III:
Start database in mount mode:

[oracle@PR oradata]$ sqlplus / as sysdba 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.

Step IV:
After successful mount, rename the datafiles to its locations as below:

SQL> alter database rename file '/u01/app/oracle/oradata/RTS/system01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/system01.dbf';
Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/RTS/sysaux01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf';
Database altered.

Step V:
Open database:

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Verify that system and sysaux datafiles are moved/renamed successful to new location.

SQL> select file_name,tablespace_name from dba_data_files;
/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/RTS_NEW/system01.dbf SYSTEM

SYSTEM and SYSAUX datafiles relocated successfully to new location.
This can be done with the help of RMAN, but only non-system tablespaces can be relocated. Click me to know more about it.
OR
You can relocate datafiles without getting offline in oracle 12c.