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

Leave a Reply