{"id":8366,"date":"2016-06-18T17:24:19","date_gmt":"2016-06-18T15:24:19","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/"},"modified":"2016-06-18T17:24:19","modified_gmt":"2016-06-18T15:24:19","slug":"12c-online-datafile-move-and-resize","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/","title":{"rendered":"12c online datafile move and resize"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nI&#8217;ve described in previous posts how the 12<em>c<\/em> 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.<br \/>\nThe 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?<br \/>\n<!--more--><br \/>\nI run a datafile move from one session. The datafile size is 14.6 GB.<\/p>\n<pre><code>\nSQL&gt; alter database move datafile '\/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf' to '\/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf';\n<\/code><\/pre>\n<p>We see the start of the operation in the alert.log:<\/p>\n<pre><code>\n2016-06-18 14:23:09.254000 +00:00\nMoving datafile \/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf (3) to \/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf\n2016-06-18 14:23:10.600000 +00:00\n<\/code><\/pre>\n<h3>manual resize<\/h3>\n<p>If I want to resize the datafile manually, I can&#8217;t:<\/p>\n<pre><code>\nSQL&gt; alter database datafile '\/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf' resize 15G\n*\nERROR at line 1:\nORA-63000: operation disallowed: data file\n\/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf is being moved\n<\/code><\/pre>\n<h3>automatic resize<\/h3>\n<p>So what happens if the datafile is autoextensible and I add data to it? I&#8217;ve run some &#8216;allocate extent&#8217; and inserts and got the resize to occur:<\/p>\n<pre><code>\n2016-06-18 14:23:10.600000 +00:00\nResize operation completed for file# 3, old size 15319040K, new size 15329280K\n2016-06-18 14:23:12.126000 +00:00\nResize operation completed for file# 3, old size 15329280K, new size 15400960K\n2016-06-18 14:23:13.836000 +00:00\n<\/code><\/pre>\n<p>and let that continue<\/p>\n<pre><code>\nResize operation completed for file# 3, old size 18513920K, new size 18585600K\n2016-06-18 14:27:08.730000 +00:00\nResize operation completed for file# 3, old size 18585600K, new size 18657280K\n2016-06-18 14:27:11.079000 +00:00\n2016-06-18 14:28:03.905000 +00:00\nResize operation completed for file# 3, old size 18657280K, new size 18728960K\n2016-06-18 14:28:05.179000 +00:00\nResize operation completed for file# 3, old size 18872320K, new size 18944000K\n<\/code><\/pre>\n<p>until the filesystem is full<\/p>\n<pre><code>\n2016-06-18 14:28:28.647000 +00:00\nORA-1653: unable to extend table SYS.TEST01 by 128 in tablespace SYSAUX\nORA-1653: unable to extend table SYS.TEST01 by 128 in tablespace SYSAUX\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdb\/CDB\/trace\/CDB_ora_26342.trc:\nORA-27061: waiting for async I\/Os failed\nLinux-x86_64 Error: 28: No space left on device\nAdditional information: 4294967295\nAdditional information: 1048576\nORA-1653: unable to extend table SYS.TEST02 by 128 in tablespace SYSAUX\nORA-1653: unable to extend table SYS.TEST02 by 1024 in tablespace SYSAUX\n<\/code><\/pre>\n<p>My datafile is now 18GB.<\/p>\n<h3>file size<\/h3>\n<p>I&#8217;m checking the size for both files here:<\/p>\n<pre><code>\n[oracle@CDB]$ ls -l \/u0?\/app\/oracle\/oradata\/CDB\/sysaux*\n-rw-r----- 1 oracle oinstall 19398664192 Jun 18 14:38 \/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf\n-rw-r----- 1 oracle oinstall 15686705152 Jun 18 14:38 \/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf\n<\/code><\/pre>\n<p>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.<\/p>\n<p>We can see that from disk usage. &#8216;ls&#8217; displays the declared size but &#8216;du&#8217; counts the actual size &#8211; only the blocks that have been written yet.<\/p>\n<pre><code>\n[oracle@CDB]$ du -k \/u0?\/app\/oracle\/oradata\/CDB\/sysaux*\n18944012        \/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf\n6894604 \/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf\n<\/code><\/pre>\n<p>The target file has only 6894604=6.5 GB yet but it keeps increasing:<\/p>\n<pre><code>\n[oracle@CDB]$ du -k \/u0?\/app\/oracle\/oradata\/CDB\/sysaux*\n18944012        \/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf\n7013388 \/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf\n<\/code><\/pre>\n<p>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.<\/p>\n<p>The move is continuing and at the point it reaches a block above the initial size the target file is resized:<\/p>\n<pre><code>\n[oracle@CDB]$ ls -l \/u0?\/app\/oracle\/oradata\/CDB\/sysaux*\n-rw-r----- 1 oracle oinstall 19398664192 Jun 18 15:18 \/u02\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf\n-rw-r----- 1 oracle oinstall 19398664192 Jun 18 15:18 \/u03\/app\/oracle\/oradata\/CDB\/sysaux014244.dbf\n<\/code><\/pre>\n<p>And finally, the move is completed without any problem:<\/p>\n<pre><code>\nSat Jun 18 15:52:57 2016\nMove operation committed for file \/u03\/app\/oracle\/oradata\/CDB\/sysaux011460.dbf\n<\/code><\/pre>\n<h3>Conclusion: no problem<\/h3>\n<p>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).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . I&#8217;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 [&hellip;]<\/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-8366","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-online","tag-oracle-12c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>12c online datafile move and resize - 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-resize\/\" \/>\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 resize\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I&#8217;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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-18T15:24:19+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=\"4 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-resize\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c online datafile move and resize\",\"datePublished\":\"2016-06-18T15:24:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\"},\"wordCount\":411,\"commentCount\":0,\"keywords\":[\"online\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\",\"name\":\"12c online datafile move and resize - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-06-18T15:24:19+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-resize\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/#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 resize\"}]},{\"@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 resize - 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-resize\/","og_locale":"en_US","og_type":"article","og_title":"12c online datafile move and resize","og_description":"By Franck Pachot . I&#8217;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 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/","og_site_name":"dbi Blog","article_published_time":"2016-06-18T15:24:19+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c online datafile move and resize","datePublished":"2016-06-18T15:24:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/"},"wordCount":411,"commentCount":0,"keywords":["online","Oracle 12c"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/","name":"12c online datafile move and resize - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-06-18T15:24:19+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-resize\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-online-datafile-move-and-resize\/#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 resize"}]},{"@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\/8366","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=8366"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8366\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8366"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}