{"id":3999,"date":"2014-09-10T17:22:16","date_gmt":"2014-09-10T15:22:16","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-how-to-move-omf-datafiles-in-11g-and-12c\/"},"modified":"2014-09-10T17:22:16","modified_gmt":"2014-09-10T15:22:16","slug":"oracle-how-to-move-omf-datafiles-in-11g-and-12c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-how-to-move-omf-datafiles-in-11g-and-12c\/","title":{"rendered":"Oracle: How to move OMF datafiles in 11g and 12c"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nWith OMF datafiles, you don’t manage the datafile names. But how do you set the destination when you want to move them to another mount point? Let’s see how easy (and online) it works in 12c. And how to do it with minimal downtime in 11g.<\/p>\n

Testcase<\/h3>\n

Let’s create a tablespace with two datafiles. It’s OMF and goes into \/u01:<\/p>\n

SQL> alter system set db_create_file_dest='\/u01\/app\/oracle\/oradata' scope=memory;\nSystem altered.\n\nSQL> show parameter db_create_file_dest\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ndb_create_file_dest                  string      \/u01\/app\/oracle\/oradata\n\nSQL> create tablespace DEMO_OMF datafile size 5M;\nTablespace created.\n\nSQL> alter tablespace DEMO_OMF add datafile size 5M;\nTablespace altered.\n<\/code><\/pre>\n
And I want to move those files in \/u02.<\/p>\n
12c online move<\/h3>\n
Here is how I generate my MOVE commands for all datafiles in \/u01:<\/p>\n
set echo off linesize 1000 trimspool on pagesize 0 feedback off\nspool _move_omf.rcv\nprompt set echo on;;\nprompt report schema;;\nprompt alter session set db_create_file_dest='\/u02\/app\/oracle\/oradata';;\nselect 'alter database move datafile '||file_id||';' from dba_data_files where file_name like '\/u01\/%' \n\/\nprompt report schema;;\nspool off\n<\/code><\/pre>\n
 <\/p>\n
which generates the following:<\/p>\n
 <\/p>\n
set echo on;\nreport schema;\nalter session set db_create_file_dest='\/u02\/app\/oracle\/oradata';\nalter database move datafile 7;\nalter database move datafile 2;\nreport schema;\n<\/code><\/pre>\n
This works straightforward and online. That is the right solution if you are in 12c Enterprise Edition. The OMF destination is set at session level here. The move is done online, without any lock. The only overhead is that writes occured twice during the move operation. And in 12c we can run any SQL statement from RMAN, which is great.<\/p>\n
11g backup as copy<\/h3>\n
How do you manage that in 11g? I like to do it with RMAN COPY. If you’re in ARCHIVELOG then you can copy the datafiles one by one: backup it as copy, offline it, recover it, switch to it, online it. This is the fastest way. You can avoid the recovery step by putting the tablespace offline but:<\/p>\n