RE: Accidentally created local df in RAC
Date: Thu, 8 Mar 2012 14:03:55 +0000
Message-ID: <DF78EADE484D37419A53F5C898629DB7015936B2_at_USMAIL2K1001.us.micros.int>
Did you offline it long enough ago that the redo to recover is not still in your redo logs?
Stephan Uzzell
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri
Sent: Thursday, 08 March, 2012 08:26
To: Lei Zeng; oracle-l_at_freelists.org
Subject: RE: Accidentally created local df in RAC
Lei,
Unfortunately you can't drop a datafile that's offline when you use LMTs. So I'll have to online the datafile to drop it, but to online it I'll have to recover and since it's noarchive mode I have nothing to recover with.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM<http://www.acxiom.com/>
[Friend Us on Facebook]<http://www.facebook.com/acxiomcorp> [Link Us on LinkedIn] <http://www.linkedin.com/groupRegistration?gid)01735> [Follow Us on Twitter] <http://twitter.com/acxiom>
[cid:image004.png_at_01CB84F1.26214350]
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.
From: Lei Zeng [mailto:lei.zeng_at_yahoo.com] Sent: Wednesday, March 07, 2012 11:13 PM To: Herring Dave - dherri; oracle-l_at_freelists.org Subject: Re: Accidentally created local df in RAC
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> ';
Lei
DBspeed http://dbspeed.com/case_study.html
From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
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 10.2.0.4, 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.
Thoughts?
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com<mailto:dave.herring_at_acxiom.com>
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM<http://www.acxiom.com/>
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 08 2012 - 08:03:55 CST