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

3 comments:

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

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