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
A Buyer’s Guide to Modern Virtualization
Storage Maintenance
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 from V$SESSION_LONGOPS;</code> <code>02–JUN–17 10:51:44 0 158 Online data file move: data file 4: 10737418240 out of 10737418240 bytes 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—–. 1 oracle oinstall 11G Jun 2 11:49 test.dbf</code> <code>–rw–r—–. 1 oracle oinstall 11G Jun 2 11:49 tester.dbf</code> Please consult the <a href=“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.2 Docs</a> for further details. |
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 to a new location and retains original data file in old location |
Check out our series on non-disruptive DR drills for Oracle:
Part 1 : Configure ActiveDR and protect your DB volumes (why and how)
Part 2: Accessing the DB volumes at the DR site and opening the database
Part 3 : Non-disruptive DR drills with some simple scripting
Part 4 : Controlled and emergency failovers/failbacks
Written By:
