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.
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
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]