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 d.name datafile_name, t.name tablespace_name from v$datafile d inner join v$tablespace t on d.ts# = t.ts# where d.name 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.