For 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 storage’s e.g. from non-ASM to Oracle 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
- 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.