Online move of active datafile in 12c

online move of active datafileFor rename and  relocation of datafile in pre 12c release, we need to put the tablespace in offline mode  then do the movement and then bring it online again. So  it was a offline task with number of task to perform

With 12c R1  , a data file movement  no longer requires a number of steps i.e. put the tablespace in offline mode  then do the movement and then bring it online again.  Datafile movement can happen online in 12c

In 12c R1, Online move of active datafile can happen simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.

Basic Syntax for Online move of active datafile

ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )

[ TO ( ‘filename’ | ‘ASM_filename’ ) ]

[ REUSE ] [ KEEP ]

 

All the syntax is explained below

‘filename’ | ‘ASM_filename’ | file_number We can specify filename, ASM filename  or file number for the original file
‘filename’ | ‘ASM_filename’ The output file can be  filesystem file or ASM file.

Obviously we cannot specify file number here

REUSE If the target file is present, even then the new file is created
KEEP The source file copy is retained

 

How to rename a data file

Original Location

/u01/app/data/tools01.dbf

New Location

/u02/app/data/tools01.dbf

 

 

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘/u02/app/data/tools01.dbf’;

 

The file is moved to the new location. The original file is deleted as we have not used the KEEP option

 

How to Migrate a data file from non-ASM to ASM

Original Location

/u01/app/data/tools01.dbf

New Location

‘+DATA/test/datafile/tools01.dbf’

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘+DATA/test/datafile/tools01.dbf’;

Or if we can to use OMF in ASM

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘+DATA;

The file is moved to the new location. The original file is deleted as we have not used the KEEP option

 

How to Overwrite the data file with the same name, if it exists at the new location

Original Location

/u01/app/data/tools01.dbf

New Location( file exists)

/u02/app/data/tools01.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘/u02/app/data/tools01.dbf’ REUSE ;

 

How Copy the file to a new location whilst retaining the old copy in the old location

 

Original Location

/u01/app/data/tools01.dbf

New Location

/u02/app/data/tools01.dbf

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘/u02/app/data/tools01.dbf’ KEEP ;

The file is moved to the new location. The original file is also retained

 

 

How to do Movement of a datafile in Pluggable database

The container database (CDB) cannot move files that belong to a pluggable database.

You need to login to the corresponding pluggable database to perform the movement

 

Pluggable database: TEST

Original Location

/u01/app/data/tools01.dbf

New Location

/u02/app/data/tools01.dbf

 

SQL > ALTER SESSION SET container=TEST;

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/app/data/tools01.dbf’  TO ‘/u02/app/data/tools01.dbf’ KEEP ;

The file is moved to the new location. The original file is also retained

 

 

Important points1) The online alter command does not work with tempfile

2) The operation is not compatible with

a)The datafile is offline

b)concurrent FLASHBACK DATABASE operation

c)concurrent media recovery

  1. d) datafile resize (shrink) operation

 

How to monitor the alter database move datafile command

You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view.

you can also refer the alert.log of the database where Oracle writes the details about action being taken place.