Tuesday, December 30, 2008

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

    1. Waking up from hibernation. Welcome back. ;-)

    2. Hey Alex - I know this is an old post; but I think my question would still be relevant. Do you know definitively if "offline drop" (undocumented afaik) is exactly the same as "offline for drop" (documented)? Or is there any difference in how Oracle might process these two statements?

    3. Hi Jeremy,

      in my tests both statements performed equivalently as far as this blog post is concerned allowing the datafile to be recovered and tablespace brought online. There seems to be no differences from this perspective.