Friday, September 28, 2018

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.

No comments:

Post a Comment

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files. I got the issue below wh...