12c online datafile move and resize

By Franck Pachot

.
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

manual resize

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

automatic resize

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:00

and 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 18944000K

until 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.

file size

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.dbf

The 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

Conclusion: no problem

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).