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