RE: Accidentally created local df in RAC
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "rodrigo@xxxxxxxxxxxxxxx" <rodrigo@xxxxxxxxxxxxxxx>
- Date: Thu, 8 Mar 2012 14:08:37 +0000
That's the solution Oracle gave but unfortunately the database is in
noarchivelog mode so it doesn't work. If I was given the opportunity to
restart the db I'd then do the following:
1) Use DBMS_FILE_TRANSFER to copy the file to ASM.
2) Rename the datafile.
3) Shutdown the db then startup mount.
4) Recover the datafile, then online it.
5) Open the db.
I believe that since the db is in noarchivelog, having this offline'ed datafile
in an online state of RECOVER doesn't affect anything. In a way the
noarchivelog mode is a hinderance and a blessing, in that no online backup
operations can be done so Oracle won't complain about this datafile. Once a
week BCV backups are done which require a shutdown so I may try to work with
that to clean things up. But again, if I leave things as is there shouldn't be
any issues either.
It's been a learning experience as I don't believe I've ever had to deal with
ASM + RAC + >8i + a db in noarchivelog mode.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring@xxxxxxxxxx
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@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: Rodrigo Mufalani [mailto:rodrigo@xxxxxxxxxxxxxxx]
Sent: Thursday, March 08, 2012 7:55 AM
To: Herring Dave - dherri
Cc: Lei Zeng; oracle-l@xxxxxxxxxxxxx
Subject: RE: Accidentally created local df in RAC
Hi David,
Have you considered use command copy from RMAN to copy the datafile offline
to ASM?
RMAN> copy datafile '/some/local/path/dt01.dbf' to '+DATA01';
Then online it.
Best Regards,
Rodrigo Mufalani
Oracle Ace Member
Tel.: +55 21 88514817
http://www.mufalani.com.br
[http://mufalani.com.br/site/wp-content/uploads/2012/01/logo.png]
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@xxxxxxxxxx
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@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@xxxxxxxxx]
Sent: Wednesday, March 07, 2012 11:13 PM
To: Herring Dave - dherri; oracle-l@xxxxxxxxxxxxx
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@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 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@xxxxxxxxxx<mailto:dave.herring@xxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
