Oracle: How to move OMF datafiles in 11g and 12c

By Franck Pachot

.
With 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.

Testcase

Let’s create a tablespace with two datafiles. It’s OMF and goes into /u01:

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata' scope=memory;
System altered.

SQL> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata

SQL> create tablespace DEMO_OMF datafile size 5M;
Tablespace created.

SQL> alter tablespace DEMO_OMF add datafile size 5M;
Tablespace altered.

And I want to move those files in /u02.

12c online move

Here is how I generate my MOVE commands for all datafiles in /u01:

set echo off linesize 1000 trimspool on pagesize 0 feedback off
spool _move_omf.rcv
prompt set echo on;;
prompt report schema;;
prompt alter session set db_create_file_dest='/u02/app/oracle/oradata';;
select 'alter database move datafile '||file_id||';' from dba_data_files where file_name like '/u01/%' 
/
prompt report schema;;
spool off

which generates the following:

set echo on;
report schema;
alter session set db_create_file_dest='/u02/app/oracle/oradata';
alter database move datafile 7;
alter database move datafile 2;
report schema;

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.

11g backup as copy

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:

• you will have to wait that the earliest transaction finishes.
• your downtime includes the whole copy. When activity is low the recovery is probably faster.

Here is how I generate my RMAN commands for all datafiles in /u01:

set echo off linesize 1000 trimspool on pagesize 0 feedback off
spool _move_omf.rcv
prompt set echo on;;
prompt report schema;;
with files as (
 select file_id , file_name , bytes from dba_data_files where file_name like '/u01/%' and online_status ='ONLINE' 
)
select stmt from (
select 00,bytes,file_id,'# '||to_char(bytes/1024/1024,'999999999')||'M '||file_name||';' stmt from files
union all
select 10,bytes,file_id,'backup as copy datafile '||file_id||' to destination''/u02/app/oracle/oradata'';' stmt from files
union all
select 20,bytes,file_id,'sql "alter database datafile '||file_id||' offline";' from files
union all
select 30,bytes,file_id,'switch datafile '||file_id||' to copy;' from files
union all
select 40,bytes,file_id,'recover datafile '||file_id||' ;' from files
union all
select 50,bytes,file_id,'sql "alter database datafile '||file_id||' online";' from files
union all
select 60,bytes,file_id,'delete copy of datafile '||file_id||';' from files
union all
select 90,bytes,file_id,'report schema;' from files
union all
select 91,bytes,file_id,'' from files
order by 2,3,1
)
/

which generates the following:

set echo on;
report schema;
#          5M /u01/app/oracle/oradata/DEMO/datafile/o1_mf_demo_omf_b0vg07m8_.dbf;
backup as copy datafile 2 to destination'/u02/app/oracle/oradata';
sql "alter database datafile 2 offline";
switch datafile 2 to copy;
recover datafile 2 ;
sql "alter database datafile 2 online";
delete copy of datafile 2;
report schema;

(I have reproduced the commands for one datafile only here.)

And I can run it in RMAN. Run it as cmdfile or in a run block so that it stops if an error is encountered. Of course, it’s better to run them one by one and check that the datafiles are online at the end. Note that it does not concern SYSTEM tablespace for which the database must be closed.

Online datafile move is my favorite Oracle 12c feature. And it’s the first new feature that you will practice if you come at our 12c new features workshop. And in any versions RMAN is my preferred way to manipulate database files.