dbi services Blog
Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!
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
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!