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

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 21 Apr 2008 10:45:06 -0400
Message-ID: <69eafc3f0804210745w64aa16f6hf178a648933a09e9@mail.gmail.com>


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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 21 2008 - 09:45:06 CDT

Original text of this message