Rename the data files for Oracle
- Take the datafile offline with the 'ALTER DATABASE DATAFILE '/old/location' OFFLINE;' command. Copy or move the datafile to its new location. On Unix this can be done with the 'dd' command.
- 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.
How to rename or move a datafile in oracle. 14389 views 1 min, 32 sec read 3 If you are in 12c version,then renaming a datafile can be done online, without making.
Datafile movement can be performed without a database restart for those tablespaces, which can be taken offline (All except System, Undo or Temp).
Following are the steps for move or rename the Oracle Datafile:
1: Login to the database as SYSDBA
sqlplus sys as sysdba
2: Make the tablespace offline which file need to rename:
select tablespace_name,file_name from dba_data_files;
Alter tablespace USERS offline;
3: Copy the datafile to the required location at the OS level
$ cp filename1 filename2
4: Rename the datafile using Alter Database command
alter database rename file ' to ' ;
Step 5: Make the tablespace online.
SQL> alter tablespace USERS online;
The above mentioned steps is not worked for system , temp and undo tablespaces as they cannot be taken offline.
Normally we drop and recreate the undo/temp tablespaces rather than moving the datafiles as it doesn’t involve any downtime.
Following are the steps to move system datafiles:
Steps to move SYSTEM datafile from one location to another:
1: Login to the database as SYSDBA
sqlplus sys as sysdba
Step 2: Shutdown the database
Shutdown immediate
Step 3: Copy/rename the datafile at the OS level
$ cp filename1 filename2
Step 4: Start the database at MOUNT state.
At this stage control file is read.
Startup mount
Oracle Move Datafile Online
Step 5: Rename the datafile using Alter Database command
alter database rename file ' to ' ;
Step 6: Open the database
Alter database open;
Oracle Move Datafile Online
For drop or recreate temp tablespace follows following link:
Manage the Temp Tablespace