First of all, I would like to thanks OTN/ODC community for helping us to connect with like-minded people. Also thanks to Tim Hall for setting all of us for sharing some content with Oracle community.

Even though Oracle 12c brought a lot of new features and changes but online datafile movement is one of my favorite feature. For years, moving datafiles was a pain for DBA’s and involved other teams (OS and application) as well in completing the datafile re-location. But not anymore now. With Oracle 12c, we can move the datafiles online, without taking application downtime and tablespaces offline or read-only mode.

More reasons to pick up this feature as my favourite:

  • Only one command “ALTER DATABASE MOVE DATAFILE” can rename, relocate, or copy a datafile.
  • DBA can use “KEEP or REUSE” options to make a copy of the file or reuse to over-write the existing file.
  • This feature has the ability to move the files in and out of ASM without using RMAN.
  • DBA can move a datafile online in both ARCHIVE and NO ARCHIVE database modes which is great.
  • Also a flashback database does not revert the datafile online operation which is really useful as well.
#Recolate
alter database move datafile '/d03/oradata/CDB1/test01.dbf' to '/d03/oradata/CDB2/tst01.dbf';
#Rename
alter database move datafile '/d03/oradata/CDB2/tst01.dbf' to '/d03/oradata/CDB2/tst02.dbf';
#Copy
alter database move datafile '/d03/oradata/CDB2/tst02.dbf' to '/d03/oradata/CDB2/tst01.dbf' keep;
#Reuse
alter database move datafile '/d03/oradata/CDB2/tst01.dbf' to '/d03/oradata/CDB2/tst02.dbf' reuse;
#ASM
Alter database move datafile '/d03/oradata/CDB2/tst02.dbf' to '+DATA';
#Flashback
create restore point before_datafile_change;
alter database move datafile '/d03/oradata/CDB2/tst02.dbf' to '/d03/oradata  /CDB2/tst01';
FLASHBACK DATABASE TO RESTORE POINT before_datafile_change;
alter database open resetlogs;
select file_name from dba_data_files;
/d03/oradata/CDB2/tst01

This feature is not available for temp files, redo log files and control files, which is good to have in future.

#ThanksODC #ThanksOTN

Leave a Reply