Alter database datafile offline drop

There was a topic on oracle-l recently and that remind me that offline drop provides one useful feature which people usually not leveraging.

This feature not only allows you to open your database without datafile foo...

SQL> startup
ORACLE instance started.

Total System Global Area  700448768 bytes
Fixed Size                  1260844 bytes
Variable Size             310379220 bytes
Database Buffers          385875968 bytes
Redo Buffers                2932736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/XE/datafile/foo.dbf'


SQL> alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.

But what's really important about this is that offline drop doesn't really drops anything (it just updates the controlfile to say that file isn't there) and what you can do later is:

[oracle@srm oradata]$ rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Dec 30 19:12:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN> connect target;

connected to target database: XE (DBID=2555430687)

RMAN> restore tablespace foo;

Starting restore at 30-DEC-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=73 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/XE/datafile/foo.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/XE/backupset/2008_12_30/o1_mf_nnnd0_TAG20081230T190854_4oog0pt0_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oradata/fra/XE/backupset/2008_12_30/o1_mf_nnnd0_TAG20081230T190854_4oog0pt0_.bkp tag=TAG20081230T190854
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 30-DEC-08

RMAN> recover tablespace foo;

Starting recover at 30-DEC-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 30-DEC-08

RMAN> sql 'alter tablespace foo online';

sql statement: alter tablespace foo online

In other words, it can be used to facilitate your database restore in certain cases. Imagine that your database has transactional and reporting data and that that data is spread across different tablespaces. In case your transactional data volume is small compared to reporting stuff (which is usually the case), you can plan your restore like this:

Restore everything but your analytical tablespaces, then offline drop missing datafiles and open your database => you are back into transactional business and immediate world stops screaming around you.

Restore your analytical tablespaces => they don't have to steer the company blindly anymore.

Depending on the data volumes, the first step might take only a fraction of time compared to restoring everything in one shot.