Moving a datafile Online

A datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.

The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.

run {
copy datafile ''
to '';
sql 'ALTER TABLESPACE OFFLINE IMMEDIATE';
set newname for datafile ''
to '';
switch datafile all;
recover tablespace TABLESPACE_NAME;
sql 'ALTER TABLESPACE TABLESPACE_NAME ONLINE';
}

The most time consuming task is moving or copying the file by itself, so this operation goes first, once it's ready, then the datafile goes offline and the switch command is issued, this updates the controlfile contents and performs the recover from the time the tablespace was backed up until it was moved.

This is the part of the process that will generate errors if used with concurrent operation on this.

ERROR at line 1:
ORA-00376: file cannot be read at this time
ORA-01110: data file 7: ''

So a Maintenance window has to be open to be able to work on the affected tablespaces.

What happens with a Dataguard Environment?
It is not affected when datafiles are moved at the primary database since the recovery process looks for the datafile ID, not the datafile location at the destination.