HOW TO MOVE DATAFILES ONLINE FROM ORACLE DATABASE 12C
Since 12cR1, moving datafile become easy in one command. Before that, DBA need to issue a set of command by bringing them offline, move them using OS cp utility and bring back online which caused lot of human error’s of removing the datafiles or corrupting them.
There was a situation, where datafiles were residing in mount point /u01 which suddenly filled due to bulk load into the table. Hence, only way to release space from the mount point is to move the datafile which consume more space.
[oracle@staxhost ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 3.8G 1.1G 2.7G 30% /dev/shm
tmpfs 3.8G 9.2M 3.8G 1% /run
tmpfs 3.8G 0 3.8G 0% /sys/fs/cgroup
/dev/sda2 20G 16G 4.3G 79% /
/dev/sda1 30G 8.0G 22G 28% /apps01
/dev/sda5 29G 28G 1.7G 95% /u01 <=====
tmpfs 767M 12K 767M 1% /run/user/42
tmpfs 767M 4.0K 767M 1% /run/user/54321
Check the existing datafiles which can be moved.
kIsH@STAX<>col NAME for a20
kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>select name,bytes/1073741824 GB from v$datafile order by GB desc;
NAME GB
-------------------- ----------
/u01/app/oracle/orad 4.96582031
ata/STAXDB/undotbs01
.dbf
/u01/app/oracle/orad 1.18164063
ata/STAXDB/system01.
dbf
/u01/app/oracle/orad .9375
ata/STAXDB/sysaux01.
dbf
/u01/app/oracle/orad .4296875
ata/STAXDB/pdbseed/s
ysaux01.dbf
Datafiles can be transferred using
- file_id
- file_name
With 2 options
- reuse(default)
- keep
Under certain conditions
- if OMF is configured, then keep clause is not allowed in the command and destination is not specified due to db_create_file_dest parameter.
- if OMF is not configured, then destination is required
kIsH@STAX<>show parameter db_create_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/STAXDB
/
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
Move the datafile using alter command. Below is the syntax for reuse of the original file.
Warning: If the datafile size is huge (30G for example), then move operation will be very slow depending on the IO capacity and resource. It is always safe to move the files during non busy hours of the database.
kIsH@STAX<>select file#,name from v$datafile where name like '%undo%';
FILE# NAME
------- --------------------
4 /u01/app/oracle/oradata/
STAXDB/undotbs01.dbf
8 /u01/app/oracle/orad
ata/STAXDB/pdbseed/u
ndotbs01.dbf
21 /u01/app/oracle/orad
ata/STAXDB/STAXDB/E9
E87BBFFBE82E19E053C7
38A8C06078/datafile/
FILE# NAME
------- --------------------
o1_mf_undotbs1_kmg6h
19y_.dbf
kIsH@STAX<>alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse;
Database altered.Validate the moved files
kIsH@STAX<>!ls -lrth /u01/app/oracle/oradata/STAXDB/undotbs01.dbf
ls: cannot access '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf': No such file or directory
kIsH@STAX<>!ls -lrth /apps01/oradata/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5.0G Oct 19 16:05 /apps01/oradata/undotbs01.dbf
alertlog entries records the move operation
alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse
2022-10-19T15:11:20.779618+05:30
Moving datafile /u01/app/oracle/oradata/STAXDB/undotbs01.dbf (4) to /apps01/oradata/undotbs01.dbf
2022-10-19T15:11:35.638905+05:30
Move operation committed for file /apps01/oradata/undotbs01.dbf
2022-10-19T15:11:37.994600+05:30
Completed: alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse
2022-10-19T15:17:19.146890+05:30
Control autobackup written to DISK device
handle '/apps01/RECO/STAXDB/autobackup/2022_10_19/o1_mf_s_1118503039_knzkx739_.bkp'
- kcffo_mv_prepare(kernel cache file management file operations) prepare the datafile for online move by creating a clone of the original file using OS cp utility.
- kcffo_mv_domove actually copy the blocks from original file to clone and remove the original file.
- Control file auto backup is taken.
*** 2022-10-19T15:11:20.779490+05:30 (CDB$ROOT(1))
*** SESSION ID:(69.34374) 2022-10-19T15:11:20.779515+05:30
*** CLIENT ID:() 2022-10-19T15:11:20.779521+05:30
*** SERVICE NAME:(SYS$USERS) 2022-10-19T15:11:20.779525+05:30
*** MODULE NAME:(sqlplus@staxhost (TNS V1-V3)) 2022-10-19T15:11:20.779529+05:30
*** ACTION NAME:() 2022-10-19T15:11:20.779533+05:30
*** CLIENT DRIVER:(SQL*PLUS) 2022-10-19T15:11:20.779537+05:30
*** CONTAINER ID:(1) 2022-10-19T15:11:20.779541+05:30
Moving datafile /u01/app/oracle/oradata/STAXDB/undotbs01.dbf (4) to /apps01/oradata/undotbs01.dbf
kcffo_mv_prepare: the secondary file /apps01/oradata/undotbs01.dbf is created with size 650880
*** 2022-10-19T15:11:35.635386+05:30 (CDB$ROOT(1))
kcffo_mv_domove: Blocks copied for file /apps01/oradata/undotbs01.dbf size 650880
Move operation committed for file /apps01/oradata/undotbs01.dbf
*** 2022-10-19T15:11:36.700132+05:30 (CDB$ROOT(1))
Move operation completed for file /apps01/oradata/undotbs01.dbf
Check the space in /u01 mount. Space should be released after some time of the move but not immediately.
[oracle@staxhost ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 3.8G 1.1G 2.7G 30% /dev/shm
tmpfs 3.8G 9.2M 3.8G 1% /run
tmpfs 3.8G 0 3.8G 0% /sys/fs/cgroup
/dev/sda2 20G 16G 4.2G 79% /
/dev/sda1 30G 14G 17G 45% /apps01
/dev/sda5 29G 23G 6.7G 77% /u01 <===
tmpfs 767M 16K 767M 1% /run/user/42
tmpfs 767M 4.0K 767M 1% /run/user/54321
Again moved the datafile by duplicating the original file using ‘KEEP’ option. This option will be helpful for backup reasons. In case, there is any corruption or mistake during movement of the datafile, original file is in place. Using keep option, the same file can be cloned multiple times with different names or different directories as per needs.
kIsH@STAX<>alter database move datafile 4 to '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' keep;
Database altered.
kIsH@STAX<>!ls -lrt /u01/app/oracle/oradata/STAXDB/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5332017152 Oct 19 22:23 /u01/app/oracle/oradata/STAXDB/undotbs01.dbf
kIsH@STAX<>!ls -lrt /apps01/oradata/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5332017152 Oct 19 22:23 /apps01/oradata/undotbs01.dbf
kIsH@STAX<>alter database move datafile 4 to '/apps01/oradata/undotbs02.dbf';
Database altered.
Tried to move temporary files but unable to do because, tempfiles can be dropped and recreated if no sorting data is stored in tempfiles.
kIsH@STAX<>alter database move tempfile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep;
alter database move tempfile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep
*
ERROR at line 1:
ORA-00905: missing keyword
kIsH@STAX<>alter database move datafile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep;
alter database move datafile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/STAXDB/temp01.dbf" in the current container