In this post I will show a method to move datafiles with minimal downtime. A testcase moves an 8Gb datafile with 5 seconds downtime.
If you need to move a datafile in Oracle, but the database or even tablespace cannot be taken offline, you have a hard job.I came accros the following post, and decided to make a nice script around it. The script will put the tablespace in backup mode, copy it to the new location, end the backup mode. After that,
take the datafile offline, rename it in the database, recover it and put it online.Here is my script (move_online.sql):
There will be short period of unavailability of the datafile.
The output looks as follows on my test database (Suse Linux), I marked my input
If we take a look at the alert logging, we can see the timing of this:
We can see, there is about four seconds offline status of the datafile. Now this is a 11Mb datafile. Another testcase moving a 8.2Gb file, took about 6 minutes to copy but we had online 5 seconds(!) offline status.
Note that the old file is renamed. You should remove it manually afterwards if all went well.
ConclusionConclusion of this method is, that we can move datafiles with a minimal offline duration provided that the database is running in archivelog. You have to be aware of the risks. If you have jobs running or queries against the datafile when it is offline, they probably fail.
Furthermore, if you have many updates and inserts on the tablespace while the copy is running, you might end up with a lot of redo logging.
Using a file that is not much used (as can be seen from the filestats), and moving the file outside office hours might reduce these risks.