Prior to Oracle 12c, moving datafiles has always been an offline task, you had to put a tablespace or your complete database offline in order to move or rename a datafile. Since12c version, Oracle gives a new ALTER DATABASE MOVE command to allow datafiles to be moved or renamed when both database and datafile are online.
let’s see that!
Commande Syntax :
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ]
- REUSE : overwrite the existing file if it already exists.
- KEEP : retain the data file in the old location and copy it to the new location. When the source file is an OMF file the
KEEPoption can not be used.
Example :
Identify the datatafile to be relocated or renamed. In this example we will move the datafile number 8 “tbs_data01.dbf” for the tablespace “TBS_TEST“.
SQL> SET LINESIZE 100
SQL> COLUMN name FORMAT A50
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';
FILE# NAME
---------- -------------------------------------------------------
8 /opt/oracle/oradata/TEST/tbs_data01.dbf
9 /opt/oracle/oradata/TEST/tbs_data02.dbf
10 /opt/oracle/oradata/TEST/tbs_data03.dbf
11 /opt/oracle/oradata/TEST/tbs_idx01.dbf
SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf'
TO '/u01/oracle/oradata/TEST/tbs_data01.dbf';
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';
FILE# NAME
---------- -------------------------------------------------------
8 /u01/oracle/oradata/TEST/tbs_data01.dbf
9 /opt/oracle/oradata/TEST/tbs_data02.dbf
10 /opt/oracle/oradata/TEST/tbs_data03.dbf
11 /opt/oracle/oradata/TEST/tbs_idx01.dbf
SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf'
TO '+DATA/oradata/TEST/tbs_data01.dbf';
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';
FILE# NAME
---------- -------------------------------------------------------
8 +DATA/oradata/TEST/tbs_data01.dbf
9 /opt/oracle/oradata/TEST/tbs_data02.dbf
10 /opt/oracle/oradata/TEST/tbs_data03.dbf
11 /opt/oracle/oradata/TEST/tbs_idx01.dbf
SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf'
TO '/opt/oracle/oradata/TEST/new_name_tbs_data01.dbf';
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';
FILE# NAME
---------- -------------------------------------------------------
8 /opt/oracle/oradata/TEST/new_name_tbs_data01.dbf
9 /opt/oracle/oradata/TEST/tbs_data02.dbf
10 /opt/oracle/oradata/TEST/tbs_data03.dbf
11 /opt/oracle/oradata/TEST/tbs_idx01.dbf
Move datafile for a Pluggable Database (PDB) :
From the container database (CDB) we can not move files that belong to a pluggable database. We must switch to the PDB container and the datafile can be moved as normal.
SQL> ALTER SESSION SET container=pdb1;
Session altered.
SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/pdb1/tbs_data01.dbf'
TO '/u01/oracle/oradata/pdb1/tbs_data01.dbf';
Database altered.
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';
FILE# NAME
---------- -------------------------------------------------------
8 /u01/oracle/oradata/pdb1/tbs_data01.dbf
9 /opt/oracle/oradata/pdb1/tbs_data02.dbf
10 /opt/oracle/oradata/pdb1/tbs_data03.dbf
11 /opt/oracle/oradata/pdb1/tbs_idx01.dbf
Move datafile for a DATA GUARD :
Moving datafile for the primary database does not move automaticly the datafile for the standby database. It can be done manually on the standby database, but we have to turn off the apply process.
dgmgrl sys/${SYS_PASSWORD}@db_pry
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Nov 30 16:21:03 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> EDIT DATABASE 'db_stby' SET STATE='APPLY-OFF';
Succeeded.
sqlplus sys/${SYS_PASSWORD}@db_stby as sysdba
Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/pdb1/tbs_data01.dbf'
TO '/u01/oracle/oradata/pdb1/tbs_data01.dbf';
Database altered.
SQL> SELECT file#, name FROM v$datafile WHERE file# = 8;
FILE# NAME
---------- -------------------------------------------------------
8 /u01/oracle/oradata/pdb1/tbs_data01.dbf
dgmgrl sys/${SYS_PASSWORD}@db_pry
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Nov 30 16:21:03 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> EDIT DATABASE 'db_stby' SET STATE='APPLY-ON';
Succeeded.
Monitor Move progess :
Via the V$SESSION_LONGOPS view, we can monitor the data file move progress. The reported bytes done will increment until the move is completed.
SQL> select to_char(start_time,’DD-MON-YY hh24:mi:ss’),
timestamp,
time_remaining,
elapsed_seconds,
message
from V$SESSION_LONGOPS;
30-NOV-20 15:30:25 0 35
Online data file move: data file 8: 233591317380 out of 233591317380 bytes done
Founder of RakDBA.com
Expert database administrator on Oracle & SQL Server databases with 10 years experience. Certified OCA for Oracle and MSCP for SQL SERVER 2012 administration.