Re: <URGENT> How do I recover when the UNDO tablespace data file is corrupt? </URGENT>

Hi All,
Thankyou very much for the inputs. I had already raised an SR with Oracle and they tried a bunch of things on the db yesterday. Finally, after about three hours of trails, they recommended me to upgrade this issue to a SEV-1; The problem is that, when I try to offline the offending the UNDO tablespace file, it cribs that no such file exists, whilst my Windows explorer SHOWS the file. Finally, we have decided to chuck the database and create a new one from scratch. This is NOT a production db and hence it is easier for us to re-create the db, instead of trying to force open or recover the db.

Regards,
Srinivas.

On Mon, Apr 21, 2008 at 8:15 PM, David Barbour <david.barbour1_at_gmail.com> wrote:

> What version of Oracle are you using? What OS platform? Are you in
> archivelog mode with backups from your last known good backup?
>
> Start by taking a deep breath and don't make things any worse than they
> already are.
>
> Are you using RMAN or some other tool for backups and recoveries?
>
> Take a backup (if you can) before trying anything.
>
> Think about the steps Scott outlined. Do you already have a pfile, or do
> you need to create one before you proceed?
>
> Best bet might be to restore the undo file from backup and roll it forward
> using archivelogs.
>
> Check Metalink note #1013221.6, it covers
>
> Problem Description:
> ====================
>
> This is a recovery scenario in which a datafile in a rollback segment
> tablespace has been lost or damaged to a point that Oracle cannot recognize it
>
> anymore. Trying to startup the database will result in ORA-1157, ORA-1110, and
> possibly an operating system level error such as ORA-7360. Trying to shut down
> the database in normal or immediate mode will result in ORA-1116, ORA-1110,
>
> and possibly an operating system level error such as ORA-7368.
>
>
>
> On 4/21/08, Scott.Washburn_at_ferguson.com <Scott.Washburn_at_ferguson.com>
> wrote:
> >
> > Here is handy steps for any UNDO tablespace issue.
> >
> > Some example are :
> >
> >
> >
> > * undo tablespace was dropped
> >
> > * undo was not recreated
> >
> > * undo was recreated without specifying 'undo' keyword.
> >
> >
> >
> > To resolve the issue, Follow the following steps :
> >
> >
> >
> > 1. set undo_management to MANUAL
> >
> > 2. startup pfile
> >
> > 3. drop undo tablespace (If it was created wrongly without undo
> > keyword)
> >
> > 4. create undo tablespace undotbs datafile '??????' size 1M reuse;
> >
> > 5. shutdown database.
> >
> > 6. set undo_management to AUTO-
> >
> > 7. startup
> >
> >
> >
> > *Scott Washburn*
> >
> > * *
> >
> > *Oracle** Database Administrator*
> >
> > Wolseley North America Division
> > 12500 Jefferson Ave
> >
> > Newport News · VA · 23602-4314
> > T: +1 (757) 223-6404 F: +1 (757) 989 2501 E: scott.washburn_at_ferguson.com
> >
> >
> >
> > www.wolseley.com
> >
> > Wolseley plc registered office
> >
> > Parkview 1220 Arlington Business Park
> >
> > Theale Nr Reading Rg7 4GA United Kingdom
> >
> > Registration No. 29846 England
> >
> >
> >
> >
> >
> >
> >
> >
> > ------------------------------
> >
> > *From:* oracle-l-bounce_at_freelists.org [mailto:
> > oracle-l-bounce_at_freelists.org] *On Behalf Of *Srinivas Chintamani
> > *Sent:* Monday, April 21, 2008 9:16 AM
> > *To:* oracle-l_at_freelists.org
> > *Subject:* <URGENT> How do I recover when the UNDO tablespace data file
> > is corrupt? </URGENT>
> >
> >
> >
> > Hi All,
> >
> > I am getting the following errors when I am trying to open my database.
> > There was a power failure to the server over the weekend, that caused me all
> > of this messs.
> >
> > Here are the errrors:
> > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > - - - - - -
> > ORA-01122: database file 2 failed verification check
> > ORA-01110: data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF'
> > ORA-01200: actual file size of 83976 is smaller than correct size of
> > 138240 blocks
> > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > - - - - - -
> >
> > Does anybody have any ideas on how to get my db started back? The last
> > known good back up is about a month-and-a-half old. All other datafiles
> > seem to be ok.
> >
> > I would really appreciate any help on getting my database up.
> >
> > --
> > Regards,
> > Srinivas Chintamani
> >
>
>

-- 
Regards,
Srinivas Chintamani

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