Skip to content

Alter Database Rename File

  • by

Move Data File Offline

Not like the new released statement ALTER DATABASE MOVE DATAFILE which can move data files online, the old-fashioned file renaming procedure which is using ALTER DATABASE RENAME FILE can also move data files for us, but it can only do it offline.

  • 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.

1. Alter Tablespace 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;

Performing recover might be needed after bringing data files online. In my opinion, taking the entire tablespace offline is safer than bringing individual data file offline.

2. Copy Data Files to New Location

[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.

3. Alter Database Rename File

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.

4. Alter Tablespace 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.

5. Remove 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 *