Search My Oracle Blog

Custom Search

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:

Ram 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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com