| undo data file is lost [message #336508] |
Sun, 27 July 2008 23:21  |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
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.????????????
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: undo data file is lost [message #337719 is a reply to message #336562] |
Thu, 31 July 2008 18:31  |
sunil_v_mishra
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
