Prior to Oracle 12c, moving non-ASM data files on or between storage arrays required taking data files ‘offline’ and negotiating an outage with the business, with Oracle 12c this is no longer the case. In the Post I will detail how the new ‘ALTER DATABASE MOVE DATAFILE’ feature can be used to move data files online to a different location, disk or storage system.
BUYER’S GUIDE, 14 PAGES
Reevaluating Your Virtualization Strategy?
Explore your options in our guide to modern virtualization.
The Oracle ALTER DATABASE MOVE DATAFILE feature provides a simple Online method of migrating data to a new storage platform performing all the heavy lifting for you including renaming or relocating of the data files at an operating system level and changing references in the Oracle Control Files.
During a data file move, progress can be monitored via the V$SESSION_LONGOPS view, the reported bytes done will increment until the move is completed e.g.
1
2
SQL></code>;<code>select file_name,tablespace_name from dba_data_files;</code><code>TABLESPACE_NAME FILE_NAME</code>——————–———————————————————–…<code>TEST /u01/app/oracle/oradata/PSTG/datafile/test.dbf</code><code>SQL&gt;</code><code>ALTER DATABASE MOVE DATAFILE‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’TO‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;</code><code>SQL&gt;</code> <code>select to_char(start_time,’DD–MON–YY hh24:mi:ss’), timestamp,time_remaining,elapsed_seconds,message fromV$SESSION_LONGOPS;</code><code>02–JUN–1710:51:44 0158
Online data file move:data file4:10737418240out of10737418240bytes done</code><code>SQL&gt;</code> <code>select tablespace_name,file_name from dba_data_files;</code><code>TABLESPACE_NAME FILE_NAME</code>——————–———————————————————–…<code>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 be reference in the control files and can be removed when no longer required.
1
SQL&gtm;</code> <code>select tablespace_name,file_name from dba_data_files;</code><code>TABLESPACE_NAME FILE_NAME</code>——————–———————————————————–…<code>TEST /u01/app/oracle/oradata/PSTG/datafile/tester.dbf</code><code>SQL&gt</code>;<code>ALTER DATABASE MOVE DATAFILE</code>;<code>‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’TO‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’KEEP</code>;<code>Database altered.</code><code>SQL&gt</code>;<code>select tablespace_name,file_name from dba_data_files;</code><code>TABLESPACE_NAME FILE_NAME</code>——————–———————————————————–…<code>TEST /u01/app/oracle/oradata/PSTG/datafile/test.dbf</code><code>[oracle@ol72–oraclepub datafile]$</code>;<code>ls–lh</code>…<code>–rw–r—–.1oracle oinstall 11GJun 211:49test.dbf</code><code>–rw–r—–.1oracle oinstall 11GJun 211:49tester.dbf</code>Please consult the<ahref=“https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-data-files-and-temp-files.html#ADMIN13837”target=“_blank”rel=“noopener noreferrer”>Oracle R12.2Docs</a> forfurther details.
Video Player is loading.
Current Time 0:00
/
Duration 0:00
Loaded: 0%
Stream Type LIVE
Remaining Time -0:00
1x
Chapters
descriptions off, selected
captions settings, opens captions settings dialog
captions off, selected
This is a modal window.
Beginning of dialog window. Escape will cancel and close the window.
End of dialog window.
This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.
Examples:
Below are examples of how the ALTER DATABASE MOVE DATAFILE command can be used to Rename, Relocate, Copy or Move data files to to ASM.
Rename:
1
ALTER DATABASE MOVE DATAFILE‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’TO‘/u01/app/oracle/oradata/PSTG/datafile/tester.dbf’;
Relocate:
1
ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’TO‘/u02/app/oracle/oradata/PSTG/datafile/test.dbf’;
Copy:
1
ALTER DATABASE MOVE DATAFILE‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’TO‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’ KEEP;
Move to ASM:
1
ALTER DATABASE MOVE DATAFILE ‘/u01/app/oracle/oradata/PSTG/datafile/test.dbf’TO‘+DATA/data/datafile/test.dbf’;</code><code>REUSE:overwrite data file with the same name</code><code>KEEP:copies the data file toanewlocation andretains original data file inold location
Check out our series on non-disruptive DR drills for Oracle:
