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.

Thanks, Stay Tune. 🙂

Leave a Reply