Oracle Database 12c: The secret of moving datafiles online

Some time ago, my colleague Michael Schwalm wrote about a new Oracle Database 12c feature for moving database files online. In this post, we will not describe the syntax of the command, but focus on Oracle internals.

To do that, I have written a tiny test protocol composed of two parts:

• Session 1: starts a batch (schema BATCH) processing which runs for hours
• Session 2: that’s us, we are connected as SYS (of course we are DBA’s) and we start moving the big datafile from file system u00 to u01 ONLINE.

Session 1 and Session 2 are working concurrently. Session 1 writes heavily into the big datafile, whereas session 2 is moving this datafile online to a new (and of course faster) file system.

On Linux (Oracle Linux), I have used the “fuser” command to figure out which processes are currently working on my original BIG datafile:

# fuser /u00/oradata/PDB1/tbs_big_file.dbf 
/u00/oradata/PDB1/tbs_big_file.dbf:  3771  3775  3781  4487

Those process IDs belong to Oracle background processes and the batch session, as shown below using the Linux command “ps”:

# ps -ef | egrep "3771|3775|3781|4487" | grep -v egrep
oracle    3771     1  5 14:37 ?        00:00:32 ora_dbw0_CDBUTF8
oracle    3775     1  0 14:37 ?        00:00:00 ora_ckpt_CDBUTF8
oracle    3781     1  0 14:37 ?        00:00:00 ora_smon_CDBUTF8
oracle    4487     1 16 14:45 ?        00:00:34 oracleCDBUTF8 (LOCAL=NO)

As the move of the big datafile has been initiated, I have performed the same operation to figure out which processes are working on the new datafiles on the faster file system:

# fuser /u01/oradata/PDB1/tst01.dbf 
/u01/oradata/PDB1/tst01.dbf:  3771  4767

# ps -ef | egrep "3771|4767"| grep -v grep
oracle    3771     1 11 14:37 ?        00:02:38 ora_dbw0_CDBUTF8
oracle    4767  4766  0 14:56 ?        00:00:01 oracleCDBUTF8 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

For verification, I have queried the dynamic performance views V$SESSION and V$PROCESS to crosscheck the process id (SPID):

SQL> SELECT sess.username, proc.spid,sess.sid,sess.serial# 
     FROM v$session sess, v$process proc 
     WHERE proc.addr=sess.paddr AND sess.username IN ('BATCH','SYS');
USERNAME    SPID    SID        SERIAL#
----------  ------- ---------- ----------
BATCH       4487        245         465
SYS         4767        133         295

As the online move operation takes several minutes, I had the idea to check the content of the view V$SESSION_LONGOPS.
This view normally displays the status of various operations that run for longer than 6 seconds (in absolute time):

SQL> SELECT sid,serial#,opname,ROUND(sofar/totalwork*100,2) "% Complete" 
      FROM v$session_longops WHERE opname LIKE 'Online%';
SID        SERIAL#    OPNAME                    % Complete
---------- ---------- ------------------------- ----------
245        465     Online data file move        44.11

As you can see, this enables you to monitor the operation. In addition, if you decide to display the column V$SESSION_LONGOPS.MESSAGE, you will see the progression in Bytes:

Online data file move: data file 15: 4736417792 out of 10737418240 bytes done

It clearly appears that the user process (Session 2 as SYS) is responsible for the copy of the datafile.
Like for a user-managed backup, the datafile copy is inconsistent, block might be fuzzy (Oracle terminology to describe blocks which are modified during a copy operation).

The secret of the online datafile move resides in the fact that the Database Writer Process (DBWR) writes twice the changed blocks (from any database session) during the operation.
You can verify this by comparing the “physical write total bytes” at database instance level (V$SYSSTAT):

SQL> SELECT stan.name, syst.value 
FROM v$sysstat syst,v$statname stan 
WHERE syst.statistic#=stan.statistic# 
AND stan.name='physical write total bytes';

P. S.: The moved datafile integrates the database structure only once the operation is completed.
I hope you enjoyed this small post about Oracle Database 12c!

Cheers,
Jérôme