12c New Feature: Online datafile move


01010101

Prior to Oracle 12c, moving datafiles has always been an offline task. The DBA needs to offline the datafile, copy it at OS level and bring it back online again. With beginning of 12.1.0.1 the database includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved or renamed online.

The operation is not compatible with

  • The datafile is offline
  • concurrent FLASHBACK DATABASE operation
  • concurrent media recovery
  • datafile resize (shrink) operation

If a flashback database is executed to a time when the file was not yet moved, the operation will not change the file name to the original name, although it will bring back the old contents of the file.

Example move datafile to ASM

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +DATA/DB12EE/DATAFILE/system.316.821292787
         2 +DATA/DB12EE/DATAFILE/example.326.821292923
         3 +DATA/DB12EE/DATAFILE/sysaux.315.821292721
         4 +DATA/DB12EE/DATAFILE/undotbs1.318.821292863
         5 +DATA/DB12EE/DATAFILE/undotbs2.327.821293485
         6 /u01/app/oradata/DB12EE/users.317.821292861

6 rows selected.

In this example we move datafile 6 online to ASM Instance.

SQL> alter database move datafile  6 to '+DATA' keep;
Database altered.

We did not need to offline the datafile and move them at OS level as needed prior 12c.You did not need to specify a online parameter. If we define the keep clause in the statement, the database will retain the original copy of the datailefile in the source destination (like copy datafile).

We can monitor the operation with the v$session_longops view to estimate a time when the move is finish.

SQL> col message format a80
SQL> select message,time_remaining/60 from v$session_longops where sofar != totalwork

MESSAGE                                      TIME_REMAINING/60
-------------------------------------------------------------------------------- -----------------
Online data file move: data file 6: 2171600896 out of 17768120320 bytes done              5.75

After the operation completes the datafile is migrated to ASM

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
     1 +DATA/DB12EE/DATAFILE/system.316.821292787
     2 +DATA/DB12EE/DATAFILE/example.326.821292923
     3 +DATA/DB12EE/DATAFILE/sysaux.315.821292721
     4 +DATA/DB12EE/DATAFILE/undotbs1.318.821292863
     5 +DATA/DB12EE/DATAFILE/undotbs2.327.821293485
     6 +DATA/DB12EE/DATAFILE/users.317.850307257

What we see in the alert.log

Sun Jun 15 12:43:03 2014
Move operation committed for file +DATA/DB12EE/DATAFILE/users.317.850307257
Completed: alter database move datafile  6 to '+DATA' keep

 Notes for Pluggable Database

Move operations cannot be done from the Container Database.

SQL> select file#,name from v$datafile;

FILE# NAME
----- ------------------------------------------------------------------------------------------
    1 +DATA/ECDB1/DATAFILE/system.275.820767583
    3 +DATA/ECDB1/DATAFILE/sysaux.265.820767517
    4 +DATA/ECDB1/DATAFILE/undotbs1.278.820767663
    5 +DATA/ECDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/system.289.820767729
    6 +DATA/ECDB1/DATAFILE/users.277.820767659
    7 +DATA/ECDB1/DD7C48AA5A4404A2E04325AAE80A403C/DATAFILE/sysaux.288.820767729
    8 +DATA/ECDB1/DATAFILE/undotbs2.306.820772717
    9 +DATA/ECDB1/E6C41538CC6E3E59E0436819A8C1715B/DATAFILE/system.487.826586257
   10 +DATA/ECDB1/E6C41538CC6E3E59E0436819A8C1715B/DATAFILE/sysaux.488.826586257
   11 +DATA/ECDB1/F581F7084C9437B7E0436819A8C1F440/DATAFILE/system.478.843223359
   12 +DATA/ECDB1/F581F7084C9437B7E0436819A8C1F440/DATAFILE/sysaux.477.843223359
   13 +DATA/ECDB1/F581F7084C9437B7E0436819A8C1F440/DATAFILE/mb.474.843238045
   14 +DATA/ECDB1/DATAFILE/cdb.473.843239355

If you try to move a dataifle from the CDB you will get a error similar to this. If you try this you will recive a ORA-01516 error

SQL> alter database move datafile 13 to '/u01/app/oradata/mb.474.843238045';
alter database move datafile 13 to '/u01/app/oradata/mb.474.843238045'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "13"

If we do this at PDB level all works fine

SQL> alter database move datafile 13 to '/u01/app/oradata/mb.474.843238045';
alter database move datafile 13 to '/u01/app/oradata/mb.474.843238045'
*
ERROR at line 1:
ORA-01135: file 13 accessed for DML/query is offline
ORA-01110: data file 13: '+DATA/ECDB1/F581F7084C9437B7E0436819A8C1F440/DATAFILE/mb.474.843238045'

Oopps, i did not open the PDB

SQL> ALTER PLUGGABLE DATABASE OPEN ;

Pluggable database altered.

SQL> alter database move datafile 13 to '/u01/app/oradata/mb.474.843238045';

Database altered.

Note to tempfiles

It seems that this feature did not work for tempfile.

SQL> alter database move datafile '+DATA/DB12EE/TEMPFILE/temp.325.821292913' to '/u01/app/oradata/DB12EE/temp.325.821292913';
alter database move datafile '+DATA/DB12EE/TEMPFILE/temp.325.821292913' to '/u01/app/oradata/DB12EE/temp.325.821292913'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"+DATA/DB12EE/TEMPFILE/temp.325.821292913"

Hope this helps. As always comments are welcome

 

Advertisements

2 Gedanken zu “12c New Feature: Online datafile move

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s