Cold backup - Undo tablespace issue on restart Solaris 10 10g

From: Newman, Christopher <cjnewman_at_uillinois.edu>
Date: Thu, 7 Apr 2011 23:12:37 -0500
Message-ID: <3A3138F2D3E329459121B953867EE8ED01AB6293D526_at_ADMINMAIL12.ui.uillinois.edu>

Hi Folks,

We have a database that didn't come back after a cold backup (clean shutdown), with the following:

ORA-30012: undo tablespace 'l' does not exist or of wrong type

Ok, my undo tablespace is UNDOTS... where is the "1" coming from? No matter, I was able to open the database, however using this technique, I'm unable to restart it without going through the same procedure again.

Procedure to 'fix'.

Modified the undo_management to MANUAL in the pfile.

Startup mount;
alter database datafile '/u06/oradata/DSQA01/und01DSQA01.dbf' offline drop;

Remove datafile at OS level, then startup, this doesn't produce the 30012, merely a simple datafile not found.

Alter database open;

drop tablespace undots;

create undo tablespace undots datafile '/u06/oradata/DSQA01/und01DSQA01.dbf' size 25m autoextend on next 1m maxsize 1024m;

Shutdown, change the undo_management to AUTO, and the problem returns (the 30012). Leaving the undo_management to MANUAL, and I'm good to go.

My undo tablespace shows up, is online, and is the correct type: UNDOTS ONLINE UNDO

Adding a new undo tablespace and switching to it makes no difference.

My question then, is how to get back to AUTO undo_management and get it to open again. I'm not confident at all that this DB is OK.

DSQA01_SQL > show parameter undo;

NAME                                 TYPE             VALUE
------------------------------------ ---------------- ------------------------------
undo_management                      string           MANUAL
undo_retention                       integer          0
undo_tablespace                      string           l

DSQA01_SQL > alter system set undo_tablespace=UNDOTS; alter system set undo_tablespace=UNDOTS
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid ORA-30014: operation only supported in Automatic Undo Management mode

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 07 2011 - 23:12:37 CDT