Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If a data file in the ASM disk group does not belong to the system tablespace, it is possible to move it to a different disk group or to a different directory in the same disk group when the database is open.
In order to move to a different directory in the same disk group, the db_unique_name parameter of the database must be changed as the new directory. Current disk group should be specified as the destination disk group. This article describes the steps to move to a data file to different disk group.
Specify the name of the datafile
1 2 3 4 5 | SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------- +DATA/orcl/datafile/users.526.799775723 |
Take datafile offline to move
1 2 3 | SQL> alter database datafile '+DATA/orcl/datafile/users.526.799775723' offline; Database altered. |
Copy via RMAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | RMAN> copy datafile '+DATA/orcl/datafile/users.526.799775723' to '+DATA01'; Starting backup at 14-SEP-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=3185 instance=orcl2 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=5690 instance=orcl2 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/orcl/datafile/users.526.799775723 output file name=+DATA01/orcl/datafile/users.522.954674461 tag=TAG20170914T112059 RECID=2 STAMP=954674494 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 Finished backup at 14-SEP-17 Starting Control File and SPFILE Autobackup at 14-SEP-17 piece handle=+RECO/orcl/autobackup/c-3629146145-20170914-00 comment=NONE Finished Control File and SPFILE Autobackup at 14-SEP-17 |
1 2 3 | SQL> ALTER DATABASE RENAME FILE '+DATA/orcl/datafile/users.526.799775723' TO '+DATA01/orcl/datafile/users.522.954674461'; Database altered. |
Recover New Data File
The new file needs to be recovered.
1 2 | SQL> RECOVER DATAFILE '+DATA01/orcl/datafile/users.522.954674461'; Media recovery complete. |
Take New Data File Online
1 2 3 | SQL> ALTER DATABASE DATAFILE '+DATA01/orcl/datafile/users.522.954674461' ONLINE; Database altered. |
When we check, the new file name will appear with the current datafile number.
1 2 3 4 5 | SQL> SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------- +DATA01/orcl/datafile/users.522.954674461 |
