This was first published on https://blog.dbi-services.com/12c-online-datafile-move-and-resize (2016-06-18)
Republishing here for new followers. The content is related to the the versions available at the publication date
I’ve described in previous posts how the 12c online datafile move works: your session process do the copy and tells other writers (dbwr and direct-path inserts) to write blocks to both files (old one and new one for blocks that have already been copied). Readers read old file until the copy is completed. The target file is created at the start of the move, with the same size, and then is filled as long as the copy phase runs. What happens if the size of the source file increases? I run a datafile move from one session. The datafile size is 14.6 GB.
SQL> alter database move datafile '/u02/app/oracle/oradata/CDB/sysaux011460.dbf' to '/u03/app/oracle/oradata/CDB/sysaux014244.dbf';We see the start of the operation in the alert.log:
2016-06-18 14:23:09.254000 +00:00 Moving datafile /u02/app/oracle/oradata/CDB/sysaux011460.dbf (3) to /u03/app/oracle/oradata/CDB/sysaux014244.dbf 2016-06-18 14:23:10.600000 +00:00
If I want to resize the datafile manually, I can’t:
SQL> alter database datafile '/u02/app/oracle/oradata/CDB/sysaux011460.dbf' resize 15G * ERROR at line 1: ORA-63000: operation disallowed: data file /u02/app/oracle/oradata/CDB/sysaux011460.dbf is being moved
So what happens if the datafile is autoextensible and I add data to it? I’ve run some ‘allocate extent’ and inserts and got the resize to occur:
2016-06-18 14:23:10.600000 +00:00 Resize operation completed for file# 3, old size 15319040K, new size 15329280K 2016-06-18 14:23:12.126000 +00:00 Resize operation completed for file# 3, old size 15329280K, new size 15400960K 2016-06-18 14:23:13.836000 +00:00and let that continue
Resize operation completed for file# 3, old size 18513920K, new size 18585600K 2016-06-18 14:27:08.730000 +00:00 Resize operation completed for file# 3, old size 18585600K, new size 18657280K 2016-06-18 14:27:11.079000 +00:00 2016-06-18 14:28:03.905000 +00:00 Resize operation completed for file# 3, old size 18657280K, new size 18728960K 2016-06-18 14:28:05.179000 +00:00 Resize operation completed for file# 3, old size 18872320K, new size 18944000Kuntil the filesystem is full
2016-06-18 14:28:28.647000 +00:00 ORA-1653: unable to extend table SYS.TEST01 by 128 in tablespace SYSAUX ORA-1653: unable to extend table SYS.TEST01 by 128 in tablespace SYSAUX Errors in file /u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_26342.trc: ORA-27061: waiting for async I/Os failed Linux-x86_64 Error: 28: No space left on device Additional information: 4294967295 Additional information: 1048576 ORA-1653: unable to extend table SYS.TEST02 by 128 in tablespace SYSAUX ORA-1653: unable to extend table SYS.TEST02 by 1024 in tablespace SYSAUX
My datafile is now 18GB.
I’m checking the size for both files here:
[oracle@CDB]$ ls -l /u0?/app/oracle/oradata/CDB/sysaux* -rw-r----- 1 oracle oinstall 19398664192 Jun 18 14:38 /u02/app/oracle/oradata/CDB/sysaux011460.dbf -rw-r----- 1 oracle oinstall 15686705152 Jun 18 14:38 /u03/app/oracle/oradata/CDB/sysaux014244.dbf
The source one is 19398664192=18GB which is exactly what I got in the last resize message from the alert.log but the target one is still 14.6GB which is the size when it has been created at the beginning of the move. The double write occurs only for the blocks that have already been copied and the move did not reach the 14.6 GB yet.
We can see that from disk usage. ‘ls’ displays the declared size but ‘du’ counts the actual size – only the blocks that have been written yet.
[oracle@CDB]$ du -k /u0?/app/oracle/oradata/CDB/sysaux* 18944012 /u02/app/oracle/oradata/CDB/sysaux011460.dbf 6894604 /u03/app/oracle/oradata/CDB/sysaux014244.dbfThe target file has only 6894604=6.5 GB yet but it keeps increasing:
[oracle@CDB]$ du -k /u0?/app/oracle/oradata/CDB/sysaux* 18944012 /u02/app/oracle/oradata/CDB/sysaux011460.dbf 7013388 /u03/app/oracle/oradata/CDB/sysaux014244.dbf
Note: I verified that if the move datafile session is suspended, the target file disk usage does not increase even when we have activity on the tablespace.
The move is continuing and at the point it reaches a block above the initial size the target file is resized:
[oracle@CDB]$ ls -l /u0?/app/oracle/oradata/CDB/sysaux* -rw-r----- 1 oracle oinstall 19398664192 Jun 18 15:18 /u02/app/oracle/oradata/CDB/sysaux011460.dbf -rw-r----- 1 oracle oinstall 19398664192 Jun 18 15:18 /u03/app/oracle/oradata/CDB/sysaux014244.dbf
And finally, the move is completed without any problem:
Sat Jun 18 15:52:57 2016 Move operation committed for file /u03/app/oracle/oradata/CDB/sysaux011460.dbf
Online datafile move is compatible with autoextensible datafile resize, without any problem. Of course, you should not plan a resize at the same time as a large load, for performance reasons, but it is works. Actually this feature is very reliable: no locks, efficient, and cleanup is well done even in case of crash (info is in controlfile).