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.
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf'; Always provide complete filenames (including their paths) to properly identify the old and new datafiles. Restart database in mount mode. First thing first, we need to start the database in mount mode to. For years, moving datafiles was a pain in the DBA behinds. It required some database action, then some OS actions and then some other database actions. Starting Oracle 12c we can now move datafile online, without interfering the application and without taking the tablespace offline or read only. There are multiple ways to move or rename the data files in oracle 11g, oracle 12c has come with a wonderful feature where we can move the files being online. However prior to 12c we need to do this practice after making the database offline, there are online ways are also available but in those also there is some time delay in processing. RMAN list copy of datafile 1; using target database control file instead of recovery catalog List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time - - - - 4 1 A 14-75 14-01-27 Name: C: ORACLEXE APP ORACLE ORADATA XE SYSTEM.DBF RMAN delete datafilecopy 4; allocated channel: ORADISK1.
Step I:
After considering full cold backup of your database, shutdown database with normal or immediate mode only.
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.
Copying datafiles:
After copy, verify size of datafiles on both locations to check whether copy successful or not.
Step III:
Start database in mount mode:
Step IV:
After successful mount, rename the datafiles to its locations as below:
Step V:
Open database:
Verify that system and sysaux datafiles are moved/renamed successful to new location.
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
Thanks, Stay Tune. 🙂