Home » RDBMS Server » Backup & Recovery » undo data file is lost (& its require for recovery of one of table.)
undo data file is lost [message #336508] Sun, 27 July 2008 23:21 Go to next message
IT Guru
Messages: 59
Registered: January 2007
Oracle 9i, Windows / Linux

Sorry for long scenario uire fo

Undo tablespace = undo , undo data file = undo.dbf
redo log group = 2
Database is not in archive mode.
There dept table in test tablespacel.

Now modifing few rows in undo tablespace. But not to commit.

Update dept
set dept_name = 'account'
Where dept_name = 'finnance'

now uncommited data is in data buffer cache.
move same to data file. By

Alter system switch logfile; [4-5] times
Alter system checkpoint;

Now instance is cresh

Shutdown abort.

now move undotablespace datafile undo.dbf to diffrent location.

start database.

Will chk for undo.dbf gives error & wont open.

At mount stage, drop undo.dbf offline.

alter database datafile "undo.dbf" offline drop;

now database can be open.

But one can't view dept id as it has uncommited data & require undo.dbf for recovery.

select * from dept ; will fail

But if I disbale foreign key in its child table emp which are depended on dept_id of dept & re-enable same wont be getting error ?????????

& now I move undo.dbf to origanl location & I wont that dept table get recover using that undo.dbf .

How its possible.????????????


Re: undo data file is lost [message #336509 is a reply to message #336508] Sun, 27 July 2008 23:24 Go to previous messageGo to next message
Messages: 24958
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guideline as stated in URL above

Do NOT describe.

Using sqlplus with CUT & PASTE then show us!
Re: undo data file is lost [message #336532 is a reply to message #336508] Mon, 28 July 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Version with 4 decimals.

Re: undo data file is lost [message #336562 is a reply to message #336508] Mon, 28 July 2008 02:53 Go to previous messageGo to next message
Messages: 754
Registered: October 2007
Senior Member
What was your purpose of doing so much R&D?
Re: undo data file is lost [message #337719 is a reply to message #336562] Thu, 31 July 2008 18:31 Go to previous message
Messages: 506
Registered: March 2005
Senior Member
In one line you can't recover uncommited data which are lost due to corruption

how instance recovery is done?
first it does roll forward which is called cache recovery and then it does transaction recovery.
Previous Topic: I am not able to start RMAN excutable
Next Topic: ORA-00312: online log 1 thread
Goto Forum:

Current Time: Sun Oct 23 09:56:59 CDT 2016

Total time taken to generate the page: 0.10739 seconds