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.
alter database move datafile '/d03/oradata/CDB1/test01.dbf' to '/d03/oradata/CDB2/tst01.dbf';
alter database move datafile '/d03/oradata/CDB2/tst01.dbf' to '/d03/oradata/CDB2/tst02.dbf';
alter database move datafile '/d03/oradata/CDB2/tst02.dbf' to '/d03/oradata/CDB2/tst01.dbf' keep;
alter database move datafile '/d03/oradata/CDB2/tst01.dbf' to '/d03/oradata/CDB2/tst02.dbf' reuse;
Alter database move datafile '/d03/oradata/CDB2/tst02.dbf' to '+DATA';
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;
This feature is not available for temp files, redo log files and control files, which is good to have in future.