How to Move Data Files to Another Place

  • by

Moving data files from a nearly full mount point to other mount points which could have more space to accommodate growing data files seems a common job for DBA. Here I simplify the scenario to a basic procedure that can be easy to follow.

  • Some data files will be moved to the new location.
  • Their tablespaces will remain unchanged.

Determine what datafiles are going to move

For example, we would like to move two data files below:

  • datafile_123.dbf
  • datafile_456.dbf

See what tablespaces will be affected

SQL> column datafile_name format a40;
SQL> column tablespace_name format a20;
SQL> select datafile_name, tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where in ('/oradata1/ORCL/datafile_123.dbf', '/oradata1/ORCL/datafile_456.dbf');

DATAFILE_NAME                            TABLESPACE_NAME
---------------------------------------- --------------------
/oradata1/ORCL/datafile_456.dbf          TBS_1
/oradata1/ORCL/datafile_123.dbf          TBS_4

Although the two data files are going to move to another place, their tablespace belonging to will remain unchanged in our case.

Take these tablespaces offline

SQL> alter tablespace tbs_1 offline;
SQL> alter tablespace tbs_4 offline;

We have to take their tablespaces offline first for further operations, but the result will affect online users who are using them. Therefore, you should notify online users before taking any actions.

Alternatively, you can take data files offline instead of tablespaces, if the database is in ARCHIVELOG mode. You don't have to know the tablespaces that the data files belong to.

SQL> alter database datafile '/oradata1/ORCL/datafile_123.dbf' offline;
SQL> alter database datafile '/oradata1/ORCL/datafile_456.dbf' offline;

Copy the data files to the new location on OS-level

[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_123.dbf /oradata2/ORCL/datafile_123.dbf
[oracle@test ~]$ cp -ip /oradata1/ORCL/datafile_456.dbf /oradata2/ORCL/datafile_456.dbf

For protecting the data files with same names in the new location from overwriting, we copy the data files with interactive mode "-i" for safety. Further more, we remain the meta data of source files.

Change the location of the data files to the new path on database-level

SQL> alter database rename file '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter database rename file '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';

As you can see, the renaming process is actually an operation of changing the absolute path of data files.

Alternatively, you can do renaming on tablespace-level.

SQL> alter tablespace tbs_1 rename datafile '/oradata1/ORCL/datafile_123.dbf' to '/oradata2/ORCL/datafile_123.dbf';
SQL> alter tablespace tbs_4 rename datafile '/oradata1/ORCL/datafile_456.dbf' to '/oradata2/ORCL/datafile_456.dbf';

I still think you should always operate data files on database level, if there's nothing to do with the tablespace throughout your plan.

Bring the tablespaces online

SQL> alter tablespace tbs_1 online;
SQL> alter tablespace tbs_4 online;

Or do this:

SQL> recover datafile '/oradata2/ORCL/datafile_123.dbf';
SQL> recover datafile '/oradata2/ORCL/datafile_456.dbf';
SQL> alter database datafile '/oradata2/ORCL/datafile_123.dbf' online;
SQL> alter database datafile '/oradata2/ORCL/datafile_456.dbf' online;

As you can see, I recover the two data files before bringing them online. Don't forget that, this is only for ARCHIVELOG mode.

Now, data files are now ready for service.

Remove the unused data files

For reclaiming the disk space, you have to remove the source data files.

[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_123.dbf
[oracle@test ~]$ rm -i /oradata1/ORCL/datafile_456.dbf

After that, you may check the disk space for sure.

Leave a Reply

Your email address will not be published. Required fields are marked *