
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.dbfAfter 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 WRITEVerify 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 SYSTEMSYSTEM 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