25 April, 2007

Recovery without UNDO Tablespace DataFiles

If you encounter a situation where an UNDO datafile is missing from a cloned database you can actually drop and recreate the UNDOTBS tablespace with
a) Shutdown immediate
b) Edit the init.ora to unset "undo_management=AUTO" {Oracle then ignores the setting of "undo_tablespace"}
c) Startup
d) Drop Tablespace UNDOTBS
e) Create UNDO Tablespace UNDOTBS datafile ...
f) Shutdown
g) Reset "undo_management=AUTO"
h) Startup Database


Anonymous said...

"Edit the init.ora to unset "undo_management=AUTO" {Oracle then ignores the setting of "undo_tablespace"}"

Did you mean "undo_management=MANUAL" there?

Anyway, the topic of the posting reads Recovery. Isnt that going to be an incomplete recovery without UNDO files?

Hemant K Chitale said...

If you unset (ie comment-out with a "#") undo_management, Oracle defaults to MANUAL (at least in 9i)

My posting was in the context of a cloned or restored database where one of the Undo Tablespace datafiles is missing.

If you are doing a real Recovery from a Hot Backup, you would still need the UNDO datafiles from the Hot Backup as the OPEN database after the Recover would need to be able to Rollback uncomitted transactions.


Hemant K Chitale said...

Tom Kyte has covered this here : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582