Re: what's the diffrent between offline and offline drop

I'm away from my office this week, and currently (conveniently?) away from my 
VPN access, so this response is strictly from memory.  However, if memory 
serves me correctly, the difference is that on *NIX systems, 'offline drop' not 
only makes the datafile unavailable to the Oracle instance, it removes the 
datafile from the O/S (freeing the disk space for other uses).  Thus, if you 
use the 'drop' option and don't have adequate backups elsewhere, you'd have a 
hard time recovering the file.  As you have clearly demonstrated, it appears to 
make no difference on Windows systems.
 
Of course, this is from memory, so any corroboration (or opposing view) is 
welcome.
 
Joe Cooper
Senior Oracle DBA
Highline Data
Austin, TX

alex <tech_a@xxxxxxx> wrote:
Hi oracle-l

i did a test about 'alter database datafile xxx offline (drop)'
it seems for the end user, there is no diffrent. i can recover the datafile and 
take the datafile online even i offline drop the datafile.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ALEX\SYSTEM01.DBF
C:\ORACLE\ORADATA\ALEX\RBS01.DBF
C:\ORACLE\ORADATA\ALEX\USERS01.DBF
C:\ORACLE\ORADATA\ALEX\TEMP01.DBF
C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF
C:\ORACLE\ORADATA\ALEX\INDX01.DBF

6 rows selected.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' offline;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;
alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF'


SQL> recover datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF';
Media recovery complete.
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;

Database altered.

SQL>
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' offline drop;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;
alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF'


SQL> recover datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF';
Media recovery complete.
SQL> alter database datafile 'C:\ORACLE\ORADATA\ALEX\TOOLS01.DBF' online;

Database altered.


Thanks
Alex

tech_a@xxxxxxx
2004-12-27


--
http://www.freelists.org/webpage/oracle-l

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: