In this article we will discuss how to move datafile online in oracle. We know that Oracle introduced moving datafiles online in 12c to minimize the downtime. Prior to oracle 12c, required taking datafile offline and copy it at OS level and bring it back online again. Oracle 12c includes an enhancement to ALTER DATABASE MOVE DATAFILE command to moved, relocate or rename when database is online.
Steps to move datafile online in oracle.
Check the existing datafiles: –
SQL>SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;
FILE# NAME
———- ——————————————————————
1 /data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf
3 /data01/12cdatabase/oracle/oradata/gcdb1/sysaux01.dbf
4 /data01/12cdatabase/oracle/oradata/gcdb1/undotbs01.dbf
6 /data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf
Moving/relocate Datafile oracle: –
SQL> ALTER DATABASE MOVE DATAFILE ‘/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf’ TO ‘/tmp/system01.dbf’;
Database altered.
SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;
FILE_ID FILE_NAME
———————————————————————-
1 /tmp/system01.dbf
Rename datafile in oracle:-
SQL> ALTER DATABASE MOVE DATAFILE ‘/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf’ TO ‘/data01/12cdatabase/oracle/oradata/gcdb1/system01.dbf’;
Database altered.
The next example uses the file number for the source file and keeps the original file.
SQL> ALTER DATABASE MOVE DATAFILE 1 TO ‘/tmp/system01.dbf’ KEEP;
Migrate/relocate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE ‘/u00/data/users_01.dbf’ TO ‘+DG_DATA’;
Migrate/ relocate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE ‘+DG_DATA/DBNAME/DATAFILE/users_01.dbf ‘ TO ‘+DG_DATA_02’;
Overwrite the datafile with the same name, if it exists at the new destination:
SQL> ALTER DATABASE MOVE DATAFILE ‘/u00/data/users_01.dbf’ TO ‘/u00/data_new/users_01.dbf’ REUSE;
Copy the file to a new destination while retaining the old copy in the old destination:
SQL> ALTER DATABASE MOVE DATAFILE ‘/u00/data/users_01.dbf’ TO ‘/u00/data_new/users_01.dbf’ KEEP;
In this post we learned how to move or relocate datafile in oracle database.
Useful Post : –