Move a datafile online in Oracle 12c

One of the new feature of Oracle 12c is relocating datafile online using the move command.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/EXAMPLE.dbf' 
      TO '/u01/app/oracle/oradata2/ORCL/EXAMPLE.dbf';0

if you want to do a copy instead of a move, use the keep keyword and the old datafile will be retained.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/EXAMPLE.dbf' 
    TO '/u01/app/oracle/oradata2/ORCL/EXAMPLE.dbf' KEEP;

Move a datafile with RMAN

Using RMAN is the best way before 12c to relocate a datafile with a minimum downtime. The only downtime is for switching from the old datafile to the new one and recover it. That means that the datafile size as no impact on the downtime.

Here is the situation before moving the datafile

RMAN> report schema
2> ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name XE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    360      SYSTEM               ***     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2    650      SYSAUX               ***     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3    25       UNDOTBS1             ***     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4    100      USERS                ***     C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

The first step is to create a copy of your datafile in the new destination. Here, we will copy the USERS datafile to
C:\ORACLEXE\APP\ORACLE\ORADATA2\XE:

RMAN> copy datafile 4 to 'C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF';

Starting backup at 14-01-27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF tag=TAG20140127T132133 RECID=1 STAMP=837955296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-01-27

Now I have a copy of my datafile in the new destination, I have to switch from the old datafile to the new one. Of course this operation cannot be done online, I’ll have to put my tablespace offline, this is the begining of the downtime.

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

sql statement: ALTER TABLESPACE USERS OFFLINE

Now I can switch from the old datafile to the new one:

RMAN> SWITCH DATAFILE 4 TO COPY;

datafile 4 switched to datafile copy "C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF"

Now we have to recover the datafile:

RMAN> RECOVER DATAFILE 4;

Starting recover at 14-01-27
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-01-27

And we put back the tablespace USERS online:

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

sql statement: ALTER TABLESPACE USERS ONLINE

Here is the new situation:

Report of database schema for database with db_unique_name XE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

The old datafile is kept as copy:

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       4    A 14-01-27        379024     14-01-27
        Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

We can remove it as we no longer need it:

RMAN> DELETE DATAFILECOPY 2;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       4    A 14-01-27        379024     14-01-27
        Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF RECID=2 STAMP=837955601
Deleted 1 objects

That’s it !

Move the system datafile

For the system datafile there is an additionnal step which must be performed :

RMAN> COPY DATAFILE 1 TO 'C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\SYSTEM.DBF';

Starting backup at 14-01-27
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\SYSTEM.DBF tag=TAG20140127T135113 RECID=3 STAMP=837957084
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-01-27

Now I have to put my database in mount state:

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     535662592 bytes

Fixed Size                     1384760 bytes
Variable Size                343936712 bytes
Database Buffers             184549376 bytes
Redo Buffers                   5791744 bytes

Now we can switch to the new datafile and recover it:

RMAN> switch datafile 1 to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\SYSTEM.DBF"

RMAN> recover datafile 1;

Starting recover at 14-01-27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 14-01-27

Open the database:

RMAN> alter database open;

database opened

Remove the old file:

RMAN> list copy of datafile 1;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
4       1    A 14-01-27        380075     14-01-27
        Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

RMAN> delete datafilecopy 4;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
4       1    A 14-01-27        380075     14-01-27
        Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF RECID=4 STAMP=837957331
Deleted 1 objects

RMAN>

Thank you for reading.