{"id":7126,"date":"2016-02-12T21:32:27","date_gmt":"2016-02-12T20:32:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/"},"modified":"2016-02-12T21:32:27","modified_gmt":"2016-02-12T20:32:27","slug":"12c-online-datafile-move-and-ongoing-changes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/","title":{"rendered":"12c online datafile move and ongoing changes"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nHere is a long answer for a question on our forum about online move:
\nwhat is internal mechanism to store\/keep ongoing changes in that particular datafile while copying is in progress<\/a>.
\n
\nThe short answer is that there is absolutely no lock, just small synchronization and messaging between sessions. And a very small overhead while the move is running: sessions that write to a part that has already been copied must double write until the end of the operation. Let’s take an example.<\/p>\n

Online move<\/h3>\n

I create a tablespace<\/p>\n

create tablespace TESTMOVE datafile '\/tmp\/TESTMOVE1.dbf' size 2000M;\nTablespace created.<\/code><\/pre>\n
Then I move it online<\/p>\n
alter database move datafile '\/tmp\/TESTMOVE1.dbf' to '\/tmp\/TESTMOVE2.dbf';<\/code><\/pre>\n
session longops<\/h3>\n
While it is running, I can see the progress from V$SESSION_LONGOPS<\/p>\n
   select * from v$session_longops where (sid,serial#) in ( select sid,serial# from v$session where sid=sys_context('userenv','sid') ) order by last_update_time desc;\n<\/code><\/pre>\n
Here is the result (sorry for the format – html table from ancient testcase, converted to simple monospaced text)<\/p>\n
\n   SID SERIAL#        OPNAME         TARGET TARGET_DESC   SOFAR    TOTALWORK  UNITS     START_TIME      LAST_UPDATE_TIME  TIMESTAMP TIME_REMAINING ELAPSED_SECONDS CONTEXT                                   MESSAGE                                   USERNAME   SQL_ADDRESS    SQL_HASH_VALUE    SQL_ID     SQL_PLAN_HASH_VALUE   SQL_EXEC_START   SQL_EXEC_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS QCSID CON_ID\n    17      45 Online data file move 2      data file   2097152000 2097152000 bytes 05-jul-13 14:24:26 05-jul-13 14:26:55 \u00a0                      0             138       0 Online data file move: data file 2: 2097152000 out of 2097152000 bytes done FRANCK   000000007A68C930     2874426962 5qgz3pqpp8jkk                   0 05-jul-13 14:24:26    16777216\n<\/code><\/pre>\n
some activity<\/h3>\n
Now I’m creating a table in that tablespace and a procedure do generated some DML on it:<\/p>\n
create table TEST (num ,txt) tablespace TESTMOVE as select rownum,to_char( date'-4712-01-01'+rownum-1,'Jsp') from (select * from dual connect by level <=1000),(select * from dual connect by level <=1000) order by 2;\nTable created.\ncreate table TEST2 tablespace TESTMOVE  as select * from TEST;\nTable created.\ncreate or replace procedure TESTACTIVITY as\n begin\n  commit;\n  execute immediate 'truncate table TEST2 reuse storage';\n  for i in 1..1 loop\n   lock table TEST in exclusive mode;\n   insert \/*+ APPEND *\/ into TEST2 select -num,txt from TEST;\n   commit;\n   delete from TEST2;\n   commit;\n  end loop;\n end;\n \/\nProcedure created.\n<\/code><\/pre>\n
I’m doing an APPEND insert so that my session is doing the writes (direct-write to the datafile)<\/p>\n
without move<\/h3>\n
I run my procedure and query my session statistics<\/p>\n
\nexec TESTACTIVITY;\nPL\/SQL procedure successfully completed.\nselect name,value from v$mystat join v$statname using(statistic#) where value>0 and name like '%physical%bytes%' order by 1;\n \n   NAME                                    VALUE\n   cell physical IO interconnect bytes 220946432\n   physical read bytes                 148291584\n   physical read total bytes           148291584\n   physical write bytes                 72654848\n   physical write total bytes           72654848\n<\/code><\/pre>\n
The procedure writes 70 MB and reads 140 MB<\/p>\n
with background move<\/h3>\n
Let’s run the datafile move in background:<\/p>\n
\nvariable job number\nexec dbms_job.submit(:job,q'[ begin execute immediate 'set role all'; execute immediate q'(alter database move datafile '\/tmp\/TESTMOVE2.dbf' to '\/tmp\/TESTMOVE1.dbf' )'; end; ]'); commit; dbms_lock.sleep(3);\nPL\/SQL procedure successfully completed.<\/code><\/pre>\n
If I query DBA_DATA_FILES, it’s not yet moved:<\/p>\n
\nselect * from dba_data_files where tablespace_name='TESTMOVE';\n&nnsp\n       FILE_NAME      FILE_ID TABLESPACE_NAME   BYTES    BLOCKS  STATUS   RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_\n   \/tmp\/TESTMOVE2.dbf      20 TESTMOVE        2097152000 256000 AVAILABLE           20 NO         0         0            0 2096103424      255872 ONLINE<\/code><\/pre>\n
which mean that my query will still read from the original location.<\/p>\n
However, when I run my procedure again:<\/p>\n
\nexec TESTACTIVITY;\nPL\/SQL procedure successfully completed.\nselect name,value from v$mystat join v$statname using(statistic#) where value>0 and name like '%physical%bytes%' order by 1;\n \n   NAME                                    VALUE\n   cell physical IO interconnect bytes 292413440\n   physical read bytes                 147103744\n   physical read total bytes           147103744\n   physical write bytes                 72654848\n   physical write total bytes          145309696\n<\/code><\/pre>\n
You see the difference: ‘physical write total bytes’ is the double of the 70M. The write is actually writing to both files.
\nThis is how it works. During the move, all sessions read from the original files and write to the original file, which is consistent.
\nIn addition to that, when the write is done on a block that has already been copied to the destination, then the session also writes to the destination. This is the double write. And the end, the destination file is consistent: all blocks are copied and maintained up to date.<\/p>\n
Actually, I’ve run my procedure several times and see the same statistics, and after several executions I get:<\/p>\n
\n   NAME                                    VALUE\n   cell physical IO interconnect bytes 249946112\n   physical read bytes                 146915328\n   physical read total bytes           147423232\n   physical write bytes                 72654848\n   physical write total bytes          102522880\n<\/code><\/pre>\n
and my online move has finished during that time. Once the move is done, all session will read from the new file and then the double write is not needed.<\/p>\n
At the end, the dictionary is updated to address the new file, and the old one can be deleted:<\/p>\n
\nselect * from dba_data_files where tablespace_name='TESTMOVE';\n \n       FILE_NAME      FILE_ID TABLESPACE_NAME   BYTES    BLOCKS  STATUS   RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_\n   \/tmp\/TESTMOVE1.dbf      20 TESTMOVE        2097152000 256000 AVAILABLE           20 NO         0         0            0 2096103424      255872 ONLINE\n<\/code><\/pre>\n
trace and alert.log<\/h3>\n
Note that when you trace, you see the write wait events on both files, but they have the same FILE_ID<\/p>\n
In the alert.log you can see the different phases:<\/p>\n
Moving datafile \/tmp\/TESTMOVE1.dbf (2) to \/tmp\/TESTMOVE2.dbf\n<\/code><\/pre>\n
From there, the move has created the second file and has signaled to the other sessions (including DBWR) that they must write to both, and the the copy starts (\u2018db file sequential read\u2019 and \u2018db file single write\u2019 in 1MB chunks)<\/p>\n
Then when all blocks are copied:<\/p>\n
Move operation committed for file \/tmp\/TESTMOVE2.dbf\n<\/code><\/pre>\n
and the sessions can read and write on the new file<\/p>\n
Completed: alter database move datafile '\/tmp\/TESTMOVE1.dbf' to '\/tmp\/TESTMOVE2.dbf'<\/code><\/pre>\n
This is a very nice feature. Oracle introduced it for the ILM option (in order to move cold data to cheaper disks automatically – and automatically means that it must be online) but we can also use it to migrate to new storage, to ASM, or to balance the datafile on multiple filesystems.<\/p>\n
It’s possible in Enterprise Edition without options and it’s my preferred 12c new feature for database administration. We start with it when teaching our 12c new feature training. This year, because everybody waits for 12.2 we have not planned fixed dates for that workshop, but deliver it on demand: https:\/\/www.dbi-services.com\/trainings\/oracle-12c-new-features-workshop\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Here is a long answer for a question on our forum about online move: what is internal mechanism to store\/keep ongoing changes in that particular datafile while copying is in progress.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[654,209],"type_dbi":[],"class_list":["post-7126","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-online","tag-oracle-12c"],"acf":[],"yoast_head":"\n12c online datafile move and ongoing changes - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12c online datafile move and ongoing changes\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Here is a long answer for a question on our forum about online move: what is internal mechanism to store\/keep ongoing changes in that particular datafile while copying is in progress.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-12T20:32:27+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c online datafile move and ongoing changes\",\"datePublished\":\"2016-02-12T20:32:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\"},\"wordCount\":603,\"commentCount\":0,\"keywords\":[\"online\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\",\"name\":\"12c online datafile move and ongoing changes - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-02-12T20:32:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12c online datafile move and ongoing changes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"12c online datafile move and ongoing changes - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/","og_locale":"en_US","og_type":"article","og_title":"12c online datafile move and ongoing changes","og_description":"By Franck Pachot . Here is a long answer for a question on our forum about online move: what is internal mechanism to store\/keep ongoing changes in that particular datafile while copying is in progress.","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/","og_site_name":"dbi Blog","article_published_time":"2016-02-12T20:32:27+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c online datafile move and ongoing changes","datePublished":"2016-02-12T20:32:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/"},"wordCount":603,"commentCount":0,"keywords":["online","Oracle 12c"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/","name":"12c online datafile move and ongoing changes - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-02-12T20:32:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-ongoing-changes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12c online datafile move and ongoing changes"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7126"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7126\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7126"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}