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

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Fri, 8 Apr 2011 06:36:30 +0200
Message-ID: <4814386347E41145AAE79139EAA39898150088A4B5_at_ws03-exch07.iconos.be>

Have you tried to make the changes in spfile first and then restart the database?

alter system set undo_retention=<the_time_desired_minimum_retention_time> scope=spfile sid='*';
alter system set undo_management='AUTO' scope=spfile sid='*';
alter system set undo_tablespace=undots scope=spfile sid='*';

shutdown immediate
startup

another options would be to add these parameter to an init file in which you first specify the spfile:

spfile=<path to your spfile>

undo_retention=<..>
undo_management='AUTO'
undo_tablespace=undots

shutdown immediate;
startup pfile=<path to the above pfile>;

after the db was started you can modify the parameters again to have them added to the spfile, after which you don't need the pfile anymore.

Recreating the spfile with the strings os command or with "create pfile ... from spfile" is also an option.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Newman, Christopher Sent: vrijdag 8 april 2011 6:13
To: oracle-l-freelists
Subject: Cold backup - Undo tablespace issue on restart Solaris 10 10g

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 07 2011 - 23:36:30 CDT