Move online data file – Oracle 12c New Features

Posted by

0

One of the Oracle Database 12c new features is the ability of the database to move datafiles to a new location while they are online and being accessed.

From the Oracle 12c documentation:

move_datafile_clause

Use the MOVE DATAFILE clause to move an online data file to a new location. The database can be open and accessing the data file when you perform this operation. The database creates a copy of the data file when it is performing this operation. Ensure that there is adequate disk space for the original data file and the copy before using this clause.

move_datafile_clause

Example:

Database files:

SQL> col name format a50
SQL> select file#, status, name from v$datafile where file# = 6;

FILE# STATUS NAME
---------- ------- --------------------------------------------------
6 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf

6 rows selected.

ASM DATA diskgroup configuration:

SQL> col name format a10
SQL> select name,total_mb,free_mb,usable_file_mb from v$asm_diskgroup

NAME         TOTAL_MB    FREE_MB USABLE_FILE_MB
---------- ---------- ---------- --------------
DATA            61436      61374          61374

Move the datafile to +DATA diskgroup

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users01.dbf' to '+DATA';

Database altered.

Confirm that datafile new location is +DATA diskgroup

SQL> select file#, status, name from v$datafile where file# = 6;

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
         6 ONLINE  +DATA/ORCL/DATAFILE/users.256.837699483

6 rows selected.

Leave a Reply