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

From: Joe Cooper <aregularjoe8_at_yahoo.com>
Date: Mon, 27 Dec 2004 06:08:10 -0800 (PST)
Message-ID: <20041227140810.26782.qmail@web50406.mail.yahoo.com>

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

Received on Mon Dec 27 2004 - 08:03:27 CST