Home » RDBMS Server » Server Administration » Dropping undo tablespace after ORA-00376, 01110
Dropping undo tablespace after ORA-00376, 01110 [message #289419] Fri, 21 December 2007 09:36 Go to next message
jwayt@iobar.com
Messages: 3
Registered: December 2007
Location: Houston
Junior Member
I have a client who has no DBA. After some backup glitch, they started getting 376 errors, complaining file 2 cannot be read at this time. data file 2: ...UNDOTSP01.DBF

I read that the back could lock this and oracle would then flag it unreadable. How do I clear that flag?

I tried to drop tabalespace undotsp1 and it told me
ORA-01548: Active rollback segment '_SYSSMU1$' found, terminate dropping tablespace.

Please help. Their server is down.

Re: Dropping undo tablespace after ORA-00376, 01110 [message #289420 is a reply to message #289419] Fri, 21 December 2007 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
1) The first thing you should do is take a complete OS backup
2) The second thing you should do is hire a competent Oracle DBA consultant.
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289421 is a reply to message #289420] Fri, 21 December 2007 09:51 Go to previous messageGo to next message
jwayt@iobar.com
Messages: 3
Registered: December 2007
Location: Houston
Junior Member
Step 1 is done.
Step 2 is easy to say, hard to do. This systems has been doing well for 2 years.

Any way to hire 1 hour's time?
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289423 is a reply to message #289419] Fri, 21 December 2007 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Submit a Service Request & get Oracle to assist with recovery.
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289426 is a reply to message #289419] Fri, 21 December 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hope you have a good backup and are in archivelog mode.
If you are in noarchivelog mode then restart from your backup.
If you have no backup, recovering lost active undo datafile is among the most difficult cases to recover implying using hidden parameters and recreating the database (and of course losing some data).
Dropping the undo tablespace hardened even more the case.
1 hour? lol, count in days (depending on the size of your database) and be happy if you ever recover your data.

Regards
Michel
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289428 is a reply to message #289419] Fri, 21 December 2007 11:14 Go to previous messageGo to next message
jwayt@iobar.com
Messages: 3
Registered: December 2007
Location: Houston
Junior Member
Their best backup via oracle agent was a week ago Wednesday.

I've exported the User/schema. It gets all the tables throu stored procedures, then stops with the 376 error. It's missing integ contraints and a few arcane items afterward. Does it have enough to recover with?
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289431 is a reply to message #289428] Fri, 21 December 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insufficient data to compute.

Regards
Michel
Re: Dropping undo tablespace after ORA-00376, 01110 [message #289455 is a reply to message #289419] Fri, 21 December 2007 16:01 Go to previous message
halfydba
Messages: 20
Registered: November 2007
Location: Australia
Junior Member
Did you try to online the undo tablespace datafile? The error that you are getting is not related to an undo corruption. It leads me to believe at some stage Oracle could not write to the datafile so it took it off line. This is quite normal for all datafiles. Try a startup mount and check the status in v$datafile next time.

O and you can't just drop and undo tablespace. You have to ensure there are no active transaction first. There are ways to do this, but you have to really know what you are doing or it's dangerous to your database.
Previous Topic: Max SGA Size on Windows
Next Topic: how to extract db data to flat file
Goto Forum:
  


Current Time: Tue Dec 06 10:28:40 CST 2016

Total time taken to generate the page: 0.05414 seconds