Online rename and relocation of an active data file - Oracle Database 12c Release 1 (12.1) New Features

Online rename and relocation of an active data file

Online rename and relocation of an active data file is one of the feature in Oracle Database 12c ,unlike in the previous releases. We can rename or move the datafile without putting tablespace in READ ONLY mode, followed by data file offline action. While rename or move the datafile, application users can perform all type of queries (DML and DDL).

Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa

This tutorial, You can learn, how to rename or move datafile online.

To rename or relocate online data files:

In SQL*Plus, connect to the database as a user with ALTER DATABASE system privilege.

Issue the ALTER DATABASE MOVE DATAFILE statement and specify the data file.

Note :- You don’t need to perform any OS level of action  like copy or move .From  oracle 12c onwards oracle internally itself perform all action while rename and relocation datafile.

Tutorial cover :-

Renaming an Online Data File

Relocating an Online Data File

Copying an Online Data File

Relocating an Online Data File and Overwriting an Existing File

Relocating an Online Data File to Oracle ASM

Renaming an Online Data File:-

This example I am going to renames the data file users01.dbf to users1.dbf while keeping the data file in the same location.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'  TO '/u01/app/oracle/oradata/ORCL/users1.dbf';

Relocating an Online Data File :-

This example I am going to moves the data file users1.dbf from the /u01/app/oracle/oradata/ORCL/ directory to the /u02/app/oracle/oradata/ORCL/ directory.

After the move operation, the file is no longer in the /u01/app/oracle/oradata/ORCL/ directory.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCL/users1.dbf' TO '/u02/app/oracle/oradata/ORCL/users1.dbf';

The file is no longer in the /u01/app/oracle/oradata/ORCL/ directory

Copying an Online Data File:-

This example copies the data file users1.dbf from the /u02/app/oracle/oradata/ORCL/ directory to the /u03/app/oracle/oradata/ORCL/ directory. After the operation, the old file is retained in the /u02/app/oracle/oradata/ORCL/ directory.

Old file is retained in the /u02/app/oracle/oradata/ORCL/ directory

Relocating an Online Data File and Overwriting an Existing File:-

This example moves the data file user1.dbf from the /u03/app/oracle/oradata/ORCL/ directory to the /u02/app/oracle/oradata/ORCL/ directory. If a file with the same name exists in the /u02/oracle/rbdb1/ directory, then the statement overwrites the file.

ALTER DATABASE MOVE DATAFILE '/u03/app/oracle/oradata/ORCL/users1.dbf'  TO '/u02/app/oracle/oradata/ORCL/users1.dbf' REUSE;

Relocating an Online Data File to Oracle ASM:-

This example moves the data file user1.dbf from the /u01/oracle/rbdb1/ directory to an Oracle ASM location.

ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/ORCL/users1.dbf'  TO '+dgroup_01/data/orcl/datafile/users1.dbf';

Moving a File from One ASM Location to Another ASM Location:-

This example moves the data file from one Oracle ASM location to another Oracle ASM location.

ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/users1.dbf'  TO '+dgroup_02/data/orcl/datafile/users1.dbf';

You also can move an online data file with Oracle ASM by mirroring the data file and then removing the original file location from the mirror. The online data file move operation might be faster when you use Oracle ASM to move the file instead of the ALTER DATABASE MOVE DATAFILE statement.

Hope it helps. Please stay tuned, add me to your G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards

Senthilkumar Devaraj