Re: Accidentally created local df in RAC

  • From: Lei Zeng <leizeng2003@xxxxxxxxx>
  • To: "Dave.Herring@xxxxxxxxxx" <Dave.Herring@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 7 Mar 2012 21:14:41 -0800 (PST)

Oracle has a 'drop' datafile command since 10gR2 (if the datafile is empty file)
I never tried myself but I saw it in other people's blog.
Would you please test it out on a development box first? Just be cautious.
alter tablespace <tb name> drop datafile '<file_name> '; 

From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
Sent: Wednesday, March 7, 2012 3:35 PM
Subject: Accidentally created local df in RAC

I've got a potential resume-faxing situation in dealing with a 20+ TB db.  This 
is on a 6-node RAC, Oracle, RHEL 4.x, ASM, OMF.

I added a datafile to a tablespace on node1 and unfortunately forgot the plus 
sign in front of the diskgroup.  We use OMF so what happened is instead of a 
datafile getting added to ASM under <diskgroup>/<db>... it created one on node1 
under $ORACLE_HOME/dbs.  Of course the other 5 nodes started complaining about 
ORA-01157 errors.  I saw this right away and quickly offlined the datafile.  
Now the datafile has an ONLINE_STATUS of "RECOVER".

Unfortunately the database is in noarchivelog mode and also critical production 
(odd combo, I know), so I'm paranoid on trying anything further.  My thought is 
to use DBMS_FILE_TRANSFER to put the datafile in ASM, then do a rename and 
bring it online.  Is that the best method?  I'm pretty sure nothing got written 
to the datafile as DBA_EXTENTS shows no extents in that FILE_ID.


Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA

The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.



Other related posts: