I consider as best of the new features those which allow to perform a maintenance or structure changes with no requirement to close the database or putting any part of it offline and especially when this feature is not bound to an extra paid option.
One of those comes with Oracle Database 12cR1 version and it can help reduce downtime for operation during which until 11gR2 needed downtime of database or tablespace needed to be offline. Simply said now you can move datafile online while the database/tablespace is open/online.
So we've got new command which we can use either to rename or relocate datafile:
ALTER DATABASE MOVE DATAFILE 'oldfilename' TO 'newfilename';
- Before moving or renaming of datafile ensure that you have sufficient free disk space that at minimum equals to size of moved datafile.
- Above command works for datafiles and it doesn't work for control files, temporary files or redo logs
- Command works irrespective of ARCHIVELOG or NOARCHIVELOG mode
- FLASHBACK DATABASE doesn't have any impact to this operation
- As always, do full backup of database after operation (and do backup before as well)
Anyway, the idea behind this post is not to replace documentation (for detailed information about command and it's options take a look here: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837) but to test how much time this operation needs to complete (or what is the expected overhead) and comparing to existing solution(s) used until now. I've skipped the rename of datafile on OS level as it doesn't have sense because operation using "mv" command is being done within a second while using MOVE DATAFILE method performs a full copy of datafile first. So this test consist of moving datafile only.
At first, the baseline using dd and cp commands which are common methods in existing solutions prior 12c (skipped shutdown/startup mount, tablespace offline/online operations and move/rename commands):
[oracle@lab00 ORA12CR1]$ time dd if=data01.dbf of=/storage/oradata01/ORA12CR1/newdata01.dbf bs=16384 1966081+0 records in 1966081+0 records out 32212271104 bytes (32 GB) copied, 29.0502 s, 1.1 GB/s real 0m29.051s user 0m0.286s sys 0m28.719s [oracle@lab00 ORA12CR1]$ time cp data02.dbf /storage/oradata01/ORA12CR1/newdata02.dbf real 0m27.204s user 0m0.074s sys 0m27.094s
Second test using DBMS_FILE_TRANSFER package:
SYS@ORA12CR1:SQL> create directory SRCDIR as '/storage/oradata00/ORA12CR1'; Directory created. SYS@ORA12CR1:SQL> create directory TGTDIR as '/storage/oradata01/ORA12CR1'; Directory created. SYS@ORA12CR1:SQL> set timing on SYS@ORA12CR1:SQL> ALTER TABLESPACE TEST offline; Tablespace altered. Elapsed: 00:00:00.11 SYS@ORA12CR1:SQL> BEGIN DBMS_FILE_TRANSFER.COPY_FILE('SRCDIR','data01.dbf', 'TGTDIR', 'newdata01.dbf'); END; 2 / PL/SQL procedure successfully completed. Elapsed: 00:01:15.76 SYS@ORA12CR1:SQL> ALTER TABLESPACE test RENAME DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf'; Tablespace altered. Elapsed: 00:00:00.06 SYS@:SQL> ALTER TABLESPACE data ONLINE; Tablespace altered. Elapsed: 00:00:00.08
Now with new feature, online move operation:
SYS@ORA12CR1:SQL> set timing on SYS@ORA12CR1:SQL> ALTER DATABASE MOVE DATAFILE '/storage/oradata00/ORA12CR1/data01.dbf' TO '/storage/oradata01/ORA12CR1/newdata01.dbf'; Database altered. Elapsed: 00:07:42.79
We can see significant difference between old method (offline tablespace) and new feature (online datafile move). I would say it's a tax or penalty for online operation.
According to times as above I would personally choose the "offline" method in case of huge or massive migration of datafiles and especially when time matters. But in case of mission critical system where of course the uptime is the main target and in case serveral changes have to be made or mean time for the operation is not important then this option becomes very handy.