Oracle Database 12c: move datafile online

The new Oracle Database 12c allows to move datafiles ONLINE while they’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
  • recover datafile
  • alter datafile online

From 12c:

  • move the datafile! 🙂

The actual command for moving the datafile is:

where the source can be specified using the file#, or the actual path.

The destination must be specified only if moving to a non-OMF file, otherwise it takes the db_create_file_dest parameter:

in the latter it will move the system datafile back to my +DATA diskgroup.

So, it’s a great enhancement to move database:

  • from a storage to another without using ASM
  • migrating online from an ASM DG to another
  • moving from FS to ASM and viceversa
  • (not tested) move datafiles on Windows from a logical disk to another!
  • etc.

Full example (including some information on the proper MV enqueue):

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.

Cheers

Ludo

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.