Skip to main content
Enterprise applications

Datafile migration

Contributors jfsinmsp
PDFs

Individual Oracle datafiles can be moved with a single command.

For example, the following command moves the datafile IOPST.dbf from filesystem /oradata2 to filesystem /oradata3.

SQL> alter database move datafile  '/oradata2/NTAP/IOPS002.dbf' to '/oradata3/NTAP/IOPS002.dbf';
Database altered.

Moving a datafile with this method can be slow, but it normally should not produce enough I/O that it interferes with the day-to-day database workloads. In contrast, migration via ASM rebalancing can run much faster but at the expense of slowing down the overall database while the data is being moved.

The time required to move datafiles can easily be measured by creating a test datafile and then moving it. The elapsed time for the operation is recorded in the v$session data:

SQL> set linesize 300;
SQL> select elapsed_seconds||':'||message from v$session_longops;
ELAPSED_SECONDS||':'||MESSAGE
-----------------------------------------------------------------------------------------
351:Online data file move: data file 8: 22548578304 out of 22548578304 bytes done
SQL> select bytes / 1024 / 1024 /1024 as GB from dba_data_files where FILE_ID = 8;
        GB
----------
        21

In this example, the file that was moved was datafile 8, which was 21GB in size and required about 6 minutes to migrate. The time required obviously depends on the capabilities of the storage system, the storage network, and the overall database activity occurring at the time of migration.