Blog - comments

Thank you very much for this post, it finally worked for me! I've unpacked the standalone offline in...
Carel Plasman

Hi, yes it's MB. Thanks Hemant. I'm correcting the post.

Is it GBs or MBs ?

Hemant K Chitale
Hi Lothar, Thanks for your comment. I'm looking forward to see you radical idea. Only half solutions...
Hi Lothar, Thanks for your comment. I'm looking forward to see you radical idea. Only half solutions...
Blog Jérôme Witt Oracle Database 12c: The secret of moving datafiles online

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC 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 our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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');

----------  ------- ---------- ----------
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, syst.value
FROM v$sysstat syst,v$statname stan
WHERE syst.statistic#=stan.statistic#
AND'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!



Tagged in: Data file Oracle 12c
Rate this blog entry:

Jérôme Witt is Senior Consultant at dbi Services. He started his Consultant career a few years ago. He is specialized in database and infrastructure management, engineering, and optimization. He is very skilled in Oracle high availability, backup & recovery, and tuning technologies. His expertise also includes the open source field (Linux/Unix), advanced Perl, Shell, Windows PowerShell programming, and Automation tools (UC4). Jérôme Witt is Oracle Certified Professional 11g (OCP 11g), Oracle Certified Expert Tuning (OCE), and ITIL V3 Foundation certified. Prior to joining dbi services, Jérôme Witt was Consultant at Trivadis in Basel. He also worked as a Junior Automation specialist at Selmoni AG in Basel. Jérôme Witt holds a BTS degree in Information Systems and Industrial Networks from France. His branch-related experience covers Pharma, Health Care, Banking & Financial Services, Energy, Automotive etc.


  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Tuesday, 31 March 2015
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter