{"id":330,"date":"2013-06-26T16:31:25","date_gmt":"2013-06-26T14:31:25","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=330"},"modified":"2020-08-18T16:57:41","modified_gmt":"2020-08-18T14:57:41","slug":"oracle-database-12c-move-datafile-online","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-move-datafile-online\/","title":{"rendered":"Oracle Database 12c: move datafile online"},"content":{"rendered":"<p>The new Oracle Database 12c allows to<strong> move datafiles ONLINE<\/strong> while they&#8217;re been used. This allows great availability when dealing with database moving, compared to the previous approach.<\/p>\n<p>Pre 12c:<\/p>\n<ul>\n<li>copy datafile with RMAN<\/li>\n<li>offline datafile<\/li>\n<li>switch datafile to copy<\/li>\n<li>recover datafile<\/li>\n<li>alter datafile online<\/li>\n<\/ul>\n<p>From 12c:<\/p>\n<ul>\n<li>move the datafile! \ud83d\ude42<\/li>\n<\/ul>\n<p>The actual command for moving the datafile is:<\/p>\n<pre class=\"toolbar-overlay:false lang:pgsql decode:true\">SQL&gt; alter database move datafile 1 to '\/data\/CLASSIC\/system01.dbf';<\/pre>\n<p>where the source can be specified using the file#, or the actual path.<\/p>\n<p>The destination must be specified only if moving to a non-OMF file, otherwise it takes the <strong>db_create_file_dest<\/strong> parameter:<\/p>\n<pre class=\"toolbar-overlay:false lang:pgsql decode:true\">SQL&gt; alter database move datafile 1;<\/pre>\n<p>in the latter it will move the system datafile back to my +DATA diskgroup.<\/p>\n<p>So, it&#8217;s a great enhancement to move database:<\/p>\n<ul>\n<li>from a storage to another without using ASM<\/li>\n<li>migrating online from an ASM DG to another<\/li>\n<li>moving from FS to ASM and viceversa<\/li>\n<li>(not tested) move datafiles on Windows from a logical disk to another!<\/li>\n<li>etc.<\/li>\n<\/ul>\n<p>Full example (including some information on the proper MV enqueue):<\/p>\n<pre class=\"toolbar-overlay:false lang:pgsql decode:true\">SQL&gt; select file#, name from v$datafile;\r\n\r\nFILE# NAME\r\n---------- ------------------------------------------------------------------------------------------\r\n1 +DATA\/CLASSIC\/DATAFILE\/system.267.814717093\r\n2 +DATA\/CLASSIC\/DATAFILE\/sysaux.266.814717123\r\n3 +DATA\/CLASSIC\/DATAFILE\/undotbs1.260.814717147\r\n4 +DATA\/CLASSIC\/DATAFILE\/users.258.814717205\r\n5 +DATA\/CLASSIC\/DATAFILE\/fda1.284.815853305\r\n\r\nSQL&gt; select * from x$ksqst where indx=63;\r\n\r\nADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN\r\n---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------\r\nKSQSTEXPL KSQSTEVIDX \r\n------------------------------------------------------------------------------------------------------------------------ ---------- \r\n000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move \r\nHeld during online datafile move operation or cleanup 157\r\n\r\nSQL&gt; alter database move datafile 1 to '\/data\/CLASSIC\/system01.dbf';\r\n\r\nDatabase altered.\r\n\r\nSQL&gt;\r\n\r\nSQL&gt; select file#, name from v$datafile;\r\n\r\nFILE# NAME\r\n---------- ------------------------------------------------------------------------------------------\r\n1 \/data\/CLASSIC\/system01.dbf\r\n2 +DATA\/CLASSIC\/DATAFILE\/sysaux.266.814717123\r\n3 +DATA\/CLASSIC\/DATAFILE\/undotbs1.260.814717147\r\n4 +DATA\/CLASSIC\/DATAFILE\/users.258.814717205\r\n5 +DATA\/CLASSIC\/DATAFILE\/fda1.284.815853305\r\n\r\nSQL&gt; select * from x$ksqst where indx=63;\r\n\r\nADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN\r\n---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------\r\nKSQSTEXPL KSQSTEVIDX\r\n------------------------------------------------------------------------------------------------------------------------ ----------\r\n000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move\r\nHeld during online datafile move operation or cleanup 157<\/pre>\n<p>Controlfiles cannot be moved online yet. The other kind of files (temp and redo logs) off course can be moved easily by creating the new ones and deleting the old ones, as it was on pre-12c releases.<\/p>\n<p>Cheers<\/p>\n<p>Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The new Oracle Database 12c allows to move datafiles ONLINE while they&#8217;re been used. This allows great availability when dealing with database moving, compared to the previous approach. Pre 12c: copy datafile with RMAN offline datafile switch datafile to copy &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-move-datafile-online\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,330,132],"tags":[85,86,75],"class_list":["post-330","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-oracle-inst-upg","category-triblog","tag-datafile","tag-online","tag-oracle-database-12c"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/330","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=330"}],"version-history":[{"count":5,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/330\/revisions"}],"predecessor-version":[{"id":364,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/330\/revisions\/364"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=330"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}