Oracle 12c Online Database Move Datafile

After watching an Oracle R12c new features presentation at a recent conference somebody asked me what was my favourite Oracle R12c database feature, for me it was easy has to be ‘ALTER DATABASE MOVE DATAFILE’

Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, with Oracle 12c this is no longer the case.

Storage Maintenance

You can use the ALTER DATABASE MOVE DATAFILE to move a data file to different location, disk or storage system. This provides a simple Online method of migrating to a new storage platform.

Online_Data_Move

The Oracle ALTER DATABASE MOVE DATAFILE does all the hard work for you including renaming or relocating the data file at operating system level and changing pointers in the Control Files.

Oracle also provide a way of monitor progress by using the V$SESSION_LONGOPS view, the reported bytes done will increment until the move is completed.

SQL> select file_name, tablespace_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST                /u01/app/oracle/oradata/PSTG/datafile/test.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;

SQL> select to_char(start_time,’DD-MON-YY hh24:mi:ss’),  timestamp, time_remaining, elapsed_seconds, message from V$SESSION_LONGOPS;

02-JUN-17 10:51:44   0 158
Online data file move: data file 4: 10737418240 out of 10737418240 bytes done

SQL>  select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST               /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

Please note before you begin make sure you have enough space on the source and target storage platforms as Oracle will allocate the required storage space on the target system and will not free any space up on the source until the move is complete.

If you specify the ‘KEEP’ parameter Oracle will copy the data file to the new location and retain the old datafile in the original location, it will be no longer reference in the control files and can be removed when no longer required.

SQL>  select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/tester.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Database altered.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
——————– ———————————————————–

TEST     /u01/app/oracle/oradata/PSTG/datafile/test.dbf

[oracle@ol72-oraclepub datafile]$ ls -lh

-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 test.dbf
-rw-r—–. 1 oracle oinstall  11G Jun  2 11:49 tester.dbf

Please consult the Oracle R12.2 Docs for further details.

Code examples:

Rename:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;

Relocate:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u02/app/oracle/oradata/PSTG/datafile/test.dbf’;

Copy:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;

Move into ASM:

ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ TO ‘+DATA/data/datafile/test.dbf’;

REUSE: overwrite data file with the same name
KEEP: copies the data file to a new location and retains original data file in old location

Advertisements

One thought on “Oracle 12c Online Database Move Datafile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s