Re: Damaged Undo Tablespace

Hi Schrödinger's Cat,

You're not going to like what I have to say ...

Your assumption that there is nothing you need to worry about losing in your undo tablespace is sadly incorrect. It's potentially storing plenty of information you need to worry about and the fact that you have been unable to restore this information is why Oracle is being so difficult to you.

Because your database fell over due to a power failure means the database was unable to perform a clean shutdown. Therefore it's quite likely there were incomplete transactions occurring in your database at the time. Therefore it's quite possible that uncommitted data has been written to your datafiles and are sitting there at the time of the failure. Note Oracle has no way of knowing if this is the case or not.

For Oracle to be able to provide you with a consistent database, it must first ensure that all committed transactions are restored (which it can do with the redo logs) *AND* it must be able to rollback any uncommitted transactions. This can only be performed by fully recovering and restoring the undo tablespace (as this is where all the rollback information is stored). Failure to do this will result in failure to open the database !!

I told you you wouldn't like what I have to say.

This is a classic example why you must backup the database. Note any backup of the undo tablespace datafiles with all redo logs since the backup would ensure a complete recovery. This is also a classic example of why an export is not a valid backup of the database.

I wouldn't take my word on this and I would discuss your situation with Oracle Support. But I'm afraid, all things being equal, you will have no alternative but to use your antiquated export.

Good Luck

Richard

Daniel Morgan wrote:
>
> "Schrödinger's Cat" wrote:
>
> > Additional Info:
> >
> > I can start and mount the database, but when I try to open it, I get an
> > error that says that the UNDO TABLESPACE can not be opened, and then the
> > whole database closes. I tried setting the UNDO mode to manual figuring
> > that then it should not need the undo tablespace, but the same thing
> > occurs...no way to get the the database open.
> >
> > I would appreciate any help!!! My next step is pretty drastic (new
> > database, re-import from very old data and re-update the data)...please help
> > me to avoid that.
> >
> > Thanks
> >
> > "Schrödinger's Cat" <72446.2772_at_compuserve.com> wrote in message
> > news:kOk59.1485$WW3.436120635_at_twister2.starband.net...
> > > We had a power failure during database (9i) operation. All the data
> > > tablespaces seem to be fine, but the database will not start because the
> > > undo tablespace is corrupt. There is nothing I need to worry about losing
> > > in the Undo space. I do not have a backup of the database in 9i. I have
> > > tried dropping the undo tablespace, removing it, etc. Nothing will allow
> > > the thing to come back online. I am not the most experience Oracle guy,
> > so
> > > there is probably something obvious...would someone tell me what that
> > > obvious thing is? What do I do to get this database back? Thanks.
> > >
> > > Lost in outer<table>space
> > >
> > >
>
> Do you have a metalink account?
>
> Based on no reseach and a wild guess I'd try creating a rollback tablespace and
> segments and dropping the UNDO before mounting the database. Though if it were
> mine I'd be at metalink armed with all of the ORA- messages and information from
> the ALERT log you haven't provided.
>
> Daniel Morgan