Oracle Move Datafile

  • 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 the datafile offline.
  • O racle database does not provide an easy user interface to rename a datafile of tablespace, nor database administrator can easily move or relocate the datafile to another location or directory that different from original location on creation of database. The rename or move place task has to be performed via Oracle SQLPlus command line interface.

Question: I need to move datafiles from one diskgroup to another ASM diskgroup.How do I migrate ASM files between diskgroups? Answer: Moving data files from one ASM diskgroup to another diskgroup involves these steps: Step 1: Get the data file name: select filename from dbadatafiles; Step 2: Identify the target diskgroup to migrate to. Also see my notes on 12c move oracle data files online. To move a datafile, either take the tablespace it belongs to offline, or shut down the database and bring it back up into a mounted state. The system tablespace, temporary tablespaces and tablespaces used for rollback will require the database be in. 2) If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. 3)You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

Oracle Move Datafile

The requirement here is to move or relocate the database files to some other location. So, there are 2 scenarios here,
1. Relocating all the database files – datafiles, online redo logs, and controlfiles ( This requires the database to be in mounted mode )
2. Relocating Non-system datafiles – This can be relocated with minimal outage.

The examples below use /data02/oradata as the new destination. You can use any destination you like, including new ASM disk groups. So For this example, we have :

Relocating all the database files

This solution requires the database to be in mounted mode. We will relocate all database files, controlfiles and redo logs as per steps given below.

1. Restart database in mount mode.

First thing first, we need to start the database in mount mode to be able to perform the relocation.

2. Copy all datafiles to the new location

Next step is to start copying the datafiles to new location. There are 2 cases here.
a. Copying datafiles with different names (database residing on OS file system)
When you specify the variable %U, RMAN auto generates new filenames while copying.

b. Copying datafiles with same name
To keep the same names you can use db_file_name_convert option as follows:

3. Switch to the datafile copies

Switch the datafile copies to be able to read them from new location.

4. Relocate the online redo logs

As online redo logs are not backed up by RMAN, you will need to relocate them outside of RMAN:
a. identify the list of online redo logs:

b. make an o/s copy of the line redo logs to the new location:

c. now rename the log files, do this for each of the redo log files:

5. Relocate the controlfiles

Oracle Move Datafile To New Location

Follwo the steps below to relocate the controlfiles.
a. backup current controlfile to new location:

Oracle Move Datafile Online 11g

b. duplicate the controlfile copy:

Move

c. change the controlfile locations:

6. Start the database

Once you have completed all the above steps, you may proceed to start the database.

7. Relocating TEMP files

If you need to relocate temp then simply drop and recreate it in SQL*Plus:

Relocating a few non-system datafiles

The below mentioned steps can be done when the database is open. You can only do this for non-system datafiles which are less in number. It only needs a minimal outage during the rename. For the purpose of this post example, we will relocate the datafile 5 to new location.

1. backup the datafile to the new location

To relocate the datafile 5, we would take a backup of the datafile first.

2. Take the datafile offline

Oracle Move Datafile

Take the datafile offline and rename it by using the SWITCH command:

Oracle move datafile to asm

3. Online the datafile

Put it online and confirm its new location: