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.
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.
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;
[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.
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.
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.
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.
Thanks, Stay Tune. 🙂