Moving datafiles – almost online

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):

-- Script to move a datafile (almost) online.
-- Do not use easily, there is a small downtime
--
-- You can minimize downtime by following these steps:
-- 1) alter tablespace <TS name> begin backup;
-- 2) HOST cp <old name> <new name>;
-- 3) alter tablespace <TS name> end backup;
-- 4) alter database datafile <old name> offline;
-- 5) alter database rename file <old name> to <new name>;
-- 6) recover datafile <new name>;
-- 7) alter database datafile <new name> online;

WHENEVER SQLERROR EXIT

--

-- Entry Section
--

set heading off

set feedback off
set verify off
define TSNAME = &TablespaceName

prompt

prompt Existing files, pick a low-active file if possible
--
-- Display filestatistics, pick a low-active file if possible
--

col file_name format a40

col tablespace_name format a12 heading Tablespace

set heading on set lines 120 set pages 999 select

   f.file_name,
   f.tablespace_name,
   s.phyrds,
   s.phywrts
from v$filestat s,
     dba_data_files f
where f.tablespace_name='&&TSNAME'
 and f.file_id=s.file#
 ;

set heading off

prompt
prompt Enter the old file without quotes:
define OLDFILE = &Old_Filename
prompt
prompt Enter the new file without quotes:
define NEWFILE = &New_Filename

--
-- Processing
--
prompt Set backup status...
alter tablespace &&TSNAME begin backup;

prompt Copying file...
host cp &&OLDFILE &&NEWFILE

prompt End backup status...
alter tablespace &&TSNAME end backup;

prompt Set file offline...
alter database datafile '&&OLDFILE' offline;

prompt Rename file in database...
alter database rename file '&&OLDFILE' to '&&NEWFILE';

prompt Recover datafile...

recover datafile '&&NEWFILE';

prompt Set file online...

alter database datafile '&&NEWFILE' online;

--

-- Cleanup
--
prompt Rename old file...
host mv &&OLDFILE &&OLDFILE._`date +"%Y%m%d_%T"`.old

--

-- Display Status
--
prompt Datafiles with wrong status:
set feedback on
select name, b.status from v$backup b, v$datafile f

where f.file#=b.file# and b.status !='NOT ACTIVE'

;

prompt All actions are logged in alert logging.

prompt

prompt Ready!

Warning:

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

strings:

SQL> @move_online.sql

Enter value for tablespacename: USERS

Existing files, pick a low-active file if possible

FILE_NAME                          Tablespace    PHYRDS PHYWRTS

---------------------------------- ------------ ------- -------
/u04/oradata/DBATST01/users01.dbf  USERS            446     413
/u04/oradata/DBATST01/users02.dbf  USERS              6       2

Enter the old file without quotes:

Enter value for old_filename: /u04/oradata/DBATST01/users02.dbf

Enter the new file without quotes:

Enter value for new_filename: /u01/oradata/DBATST01/users02.dbf

Set backup status...

Copying file...
End backup status...
Set file offline...
Rename file in database...
Recover datafile...
Media recovery complete.
Set file online...
Rename old file...

Datafiles with wrong status:

no rows selected

All actions are logged in alert logging.

Ready!

Logging

If we take a look at the alert logging, we can see the timing of this:

alter tablespace USERS begin backup

Mon Nov  8 10:43:28 2010
Completed: alter tablespace USERS begin backup
Mon Nov  8 10:43:28 2010
alter tablespace USERS end   backup
Completed: alter tablespace USERS end backup
Mon Nov  8 10:43:28 2010
alter database datafile

'/u04/oradata/DBATST01/users02.dbf' offline

Completed: alter database datafile

'/u04/oradata/DBATST01/users02.dbf' offline

Mon Nov  8 10:43:28 2010
alter database rename file '/u04/oradata/DBATST01/users02.dbf' to '/u01/oradata/DBATST01/users02.dbf'
Starting control autobackup
Control autobackup written to DISK device handle '/backup/mdoradb01/DBATST01c-4185353572-20101108-13'
Completed: alter database rename file '/u04/oradata/DBATST01/users02.dbf' to '/u01/oradata/DBATST01/users02.dbf'
Mon Nov  8 10:43:29 2010
ALTER DATABASE RECOVER  datafile '/u01/oradata/DBATST01/users02.dbf'
Media Recovery Start
Mon Nov  8 10:43:29 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 41

Reading mem 0  Mem# 0: /u04/oradata/DBATST01/redo02.log

Mon Nov  8 10:43:29 2010
Media Recovery Complete (DBATST01)
Completed: ALTER DATABASE RECOVER  datafile '/u01/oradata/DBATST01/users02.dbf'
Mon Nov  8 10:43:32 2010
alter database datafile '/u01/oradata/DBATST01/users02.dbf' online
Starting control autobackup
Control autobackup written to DISK device handle '/backup/mdoradb01/DBATST01c-4185353572-20101108-14'
Completed: alter database datafile '/u01/oradata/DBATST01/users02.dbf' online

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.

Conclusion

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