As we already know, now with Oracle Database 12c, it is easier to move a data file. There is more to it… Let us see few operations in 11g, and how they all got simplified with Oracle 12c online file move feature…
1. Rename a file or move a file from one file system to another
- Take tablespace offline, thus users/application cannot access the content.
- Copy the file to new destination using OS commands
- Use ALTER DATABASE RENAME FILE ‘x’ TO ‘y’;
- Make the tablespace online
With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.
- ALTER DATABASE MOVE DATAFILE ‘x’ TO ‘y’;
2. Move a file from one ASM diskgroup to another, Move a file from file system to ASM, Move a file from ASM to file system
- Take the data file to be moved offline using ALTER DATABASE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ OFFLINE
- Connect to RMAN and perform copy using COPY DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2’ [Note, if you are copying a non-OMF file, make sure to include full path and file name for the destination].
- Find the name of the new file if using OMF by querying DBA_DATA_FILES.
- Rename the file using ALTER DATABASE RENAME FILE ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2/mydb/datafile/x1.dbf’
- Connect to RMAN to switch to the new file using SWITCH DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ TO COPY
- Recover the data file to make it consistent using RECOVER DATAFILE ‘+DG2/mydb/datafile/x1.dbf’ TO
- Finally, make the file online using ALTER DATABASE DATAFILE ‘+DG2/mydb/datafile/x1.dbf’ ONLINE;
With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.
ASM to ASM
- ALTER DATABASE MOVE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2’;
File System to ASM
- ALTER DATABASE MOVE DATAFILE ‘/u01/db/x1.dbf’ TO ‘+DG2’;
ASM to File System
- ALTER DATABASE MOVE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ TO ‘/u01/db1/x2.dbf”;
The TO clause can be omitted for OMF, where unique data file name will be created on the location specified by DB_CREATE_FILE_DEST.
There are couple of keywords you may use with the MOVE operation.
- KEEP does not delete the file from the source location.
- REUSE will overwrite the file, if file with same name exists in the destination location.