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:
|
1 |
SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf'; |
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:
|
1 |
SQL> alter database move datafile 1; |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> select file#, name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------------------------------------ 1 +DATA/CLASSIC/DATAFILE/system.267.814717093 2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123 3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147 4 +DATA/CLASSIC/DATAFILE/users.258.814717205 5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305 SQL> select * from x$ksqst where indx=63; ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN ---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- KSQSTEXPL KSQSTEVIDX ------------------------------------------------------------------------------------------------------------------------ ---------- 000000008EE528A8 63 1 0 MV 0 0 0 0 0 datafile move Held during online datafile move operation or cleanup 157 SQL> alter database move datafile 1 to '/data/CLASSIC/system01.dbf'; Database altered. SQL> SQL> select file#, name from v$datafile; FILE# NAME ---------- ------------------------------------------------------------------------------------------ 1 /data/CLASSIC/system01.dbf 2 +DATA/CLASSIC/DATAFILE/sysaux.266.814717123 3 +DATA/CLASSIC/DATAFILE/undotbs1.260.814717147 4 +DATA/CLASSIC/DATAFILE/users.258.814717205 5 +DATA/CLASSIC/DATAFILE/fda1.284.815853305 SQL> select * from x$ksqst where indx=63; ADDR INDX INST_ID CON_ID KS KSQSTREQ KSQSTWAT KSQSTSGT KSQSTFGT KSQSTWTM KSQSTRSN ---------------- ---------- ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- KSQSTEXPL KSQSTEVIDX ------------------------------------------------------------------------------------------------------------------------ ---------- 000000008EE528A8 63 1 0 MV 56 0 3 53 0 datafile move Held during online datafile move operation or cleanup 157 |
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
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023