I recently ran into a situation where I needed to move some Oracle database datafiles. This is on Oracle Linux. There are several mount points (EXT4) but one was much lower on free space than the rest. While another one had much more free space available.
This database will move to ASM in the near future. I needed to only buy a little more time. The version of the database is 19c. I knew there was the ability to perform an online datafile move.
Oracle Database Online Operations
There are many advantages to being on a recent version of the Oracle database. A number of operations can be performed online. Starting with 12c, Oracle brought us more of these operations. Oracle continues to bring us more of these online operations with each new release. Here is just a sampling of what’s available.
- Online datafile move
- Online partition and sub-partition move
- Online statistics gathering for bulk loads (I blogged about this feature here)
- Online statistics during conventional data manipulation language (DML)
- Online Reorganization and Redefinition
- Transparent online conversion support for auto-renaming in non-Oracle-managed files mode
Online Move Datafile
Identify Datafiles
The first thing I did was review the tablespaces and where the datafiles were located. I found a tablespace that has mostly archival type data. This was an easy candidate to choose. There were four datafiles that made up the tablespace. I used the following query to determine the layout and size of the datafiles.
SELECT file#,
creation_time,
status,
name,
ROUND (bytes / 1024 / 1024 / 1024, 2) AS "SIZE (GB)",
con_id
FROM v$datafile
ORDER BY name;
Test in Dev
First, I tested my online datafile move in my development database. This is an exact copy of production. Doing this allows me to get an idea of timing and also to make sure I don’t run into bugs. The test was a success, so I moved on to production.
Next I perform the move operation. This is a container database, so I need to first set to my pluggable database container.
SQL> alter session set container=MYPDB1;
Session altered.
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oradata/MYPDB1/mydata01.dbf' TO '/u02/app/oradata/MYPDB1/mydata01.dbf';
Database altered.
Finally, the file move on our system only took a few minutes for 21 GB datafile. Depending on how your system is setup, this may be slower or faster.
Space Not Being Released
After the online move completed, the space was not released on the original mount point. Checking the list of open files, it was still in use by an Oracle process. The file is marked as deleted.
$ lsof -u oracle | grep deleted
oracle_75 75231 oracle 519u REG 252,10 22363906048 66846783 /u01/app/oradata/MYPDB1/mydata01.dbf (deleted)
Interestingly, the space was reclaimed after about 10 – 15 minutes. I did find a My Oracle Support document, but it wasn’t super helpful .
Move Datafile – Additional Checks
SQL*Plus will provide confirmation that the move of the datafile occurred. You can also check two other places. First, look in the database alert log. You’ll see something similar to this:
2022-03-05T13:43:05.072908-05:00
MYDB(3):ALTER DATABASE MOVE DATAFILE '/u01/app/oradata/MYPDB1/mydata01.dbf' TO '/u02/app/oradata/MYPDB1/mydata01.dbf'
2022-03-05T13:43:05.106634-05:00
Moving datafile /u01/app/oradata/MYPDB1/mydata01.dbf (272) to /u02/app/oradata/MYPDB1/mydata01.dbf
2022-03-05T13:45:27.882448-05:00
Move operation committed for file /u02/app/oradata/MYPDB1/mydata01.dbf
2022-03-05T13:45:29.968835-05:00
MYDB(3):Completed: ALTER DATABASE MOVE DATAFILE '/u01/app/oradata/MYPDB1/mydata01.dbf' TO '/u02/app/oradata/MYPDB1/mydata01.dbf'
Secondly, you can backup the control file to trace. The new datafile location will be part of the CREATE DATABASE script. This looked like this for me:
CREATE CONTROLFILE REUSE DATABASE...
'/u02/app/oradata/MYPDB1/mydata01.dbf',
Trace File
Additionally, I found more information in the trace directory. I was performing a grep on the datafile name and discovered the following in a trace file.
*** 2022-03-05T13:43:05.106634-05:00 (CDB$ROOT(1))
Moving datafile /u01/app/oradata/MYPDB1/mydat01.dbf (272) to /u02/app/oradata/MYPDB1/mydata01.dbf
*** 2022-03-05T13:43:05.149313-05:00 (CDB$ROOT(1))
kcffo_mv_prepare: the secondary file /u02/app/oradata/MYPDB1/mydata01.dbf is created with size 2741224
*** 2022-03-05T13:45:27.873433-05:00 (CDB$ROOT(1))
kcffo_mv_domove: Blocks copied for file /u02/app/oradata/MYPDB1/mydata01.dbf size 2741224
Move operation committed for file /u02/app/oradata/MYPDB1/mydata01.dbf
*** 2022-03-05T13:45:28.916956-05:00 (CDB$ROOT(1))
Move operation completed for file /u02/app/oradata/MYPDB1/mydata01.dbf
Summary
In conclusion, the Oracle database is providing us with many online operations. In this case, we used the online datafile move. An easy to use method to move your datafiles while the database is online. A task much easier to perform than in earlier versions.
