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

Printable View

10-25-2006, 10:09 AM
vr76413

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

Hello guys..

I just moved into this new job and inherited this problem when i started the database.

======================
SQL> startup
ORACLE instance started.

Total System Global Area 303532544 bytes
Fixed Size 731648 bytes
Variable Size 285212672 bytes
Database Buffers 16777216 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

==================================

so, i went into alert log and saw this...

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

so, i changed the pfile and changed undo_management from AUTO to MANUAL and started the database. Database started fine. So then, i deleted the old UNDO that is creating the problems, and created a new UNDO, and shutdown the database.

Then, I went and changed the pfile and changed the undo_management from MANUAL to AUTO and started the database.

Its giving the same error....

any help ?

thanks for help
Ron
10-25-2006, 10:23 AM
brhynold

Have you changed the undo_tablespace parameter in the pfile to reflect the new undo tablespace name.
10-25-2006, 10:29 AM
vr76413

oops...i forgot that..

thakyou brhynold

ron :)
10-25-2006, 08:21 PM
yxez

Hmmmn...I'm just curious what cause the problem, do u think that
someone had accidentally deleted the undo datafile? or drop the
undo tablespace.
10-25-2006, 08:36 PM
yxez

Hi dear, im just confused....I haven't really explore much of this undo thing...

Quote:

Originally Posted by vr76413

so, i changed the pfile and changed undo_management from AUTO to MANUAL and started the database. Database started fine. So then, i deleted the old UNDO that is creating the problems, and created a new UNDO, and shutdown the database.

Then, I went and changed the pfile and changed the undo_management from MANUAL to AUTO and started the database.

Ron

So you mean here that changing the pfile from AUTO to MANUAL and
deleting the UNDO(supposedly creating the problem) was not the
real solution right?

The real problem is that, the undo tablespace name was not reflected in the pfile's (undo_tablespace parameter).
I mean the solution is just to reflect the undo tablespace name.

Am i right? davey...
05-02-2009, 04:12 PM
abdulhameedom

What is the alternative method

What is the alternative method

i have the same error " ORA-30012: undo tablespace "

from u r advice i have changed the pfile parameter for undo to manual -"undo_management=MANUAL".

but i need a alternative solution for this from the beginning,, (while Creating the database)?

And i want to know why this is happening (reason)?
05-03-2009, 05:47 AM
davey23uk

happening becuase you didnt create an undo tablespace properly - use dbca to create a database
05-03-2009, 01:53 PM
gtcol

Read yxez's reply carefully. Reflect undo tablespace's name in pfile's undo_tablespace parameter. It should resolve the issue.

HTH
gtcol
05-03-2009, 04:30 PM
abdulhameedom

thank U my dear brother's.. i solved the problem with ur help..
am just a beginner, if i have any droughts or problem arrives please help me like this,,

U ppls still active in this blog,, that makes me more happy,, god bless U brothers
08-27-2009, 01:25 PM
tarana

Quote:

Originally Posted by davey23uk

happening becuase you didnt create an undo tablespace properly - use dbca to create a database

can u explain the process of creating database by using DBCA
08-27-2009, 03:47 PM
PAVB

Quote:

Originally Posted by tarana

can u explain the process of creating database by using DBCA

What's wrong with Oracle documentation? http://download.oracle.com/docs/cd/B...install003.htm
08-27-2009, 04:16 PM
gandolf989

Quote:

Originally Posted by PAVB

What's wrong with Oracle documentation? http://download.oracle.com/docs/cd/B...install003.htm

But Pablo, that would require thinking and a little work... ;)
03-03-2010, 07:21 AM
4iappsDBA

Please Seniors,, why u leaders making confusion in this issue,, please give me a straight answer to over come this undo tablespace.
03-03-2010, 08:05 AM
gandolf989

Quote:

Originally Posted by 4iappsDBA

Please Seniors,, why u leaders making confusion in this issue,, please give me a straight answer to over come this undo tablespace.

Make sure that the undo tablespace specified in you init/spfile points to your undo tablespace.
03-03-2010, 08:57 AM
4iappsDBA

Undo Problem Solved

Yes you are correct just now i did and its working fine..

i would like to post the database creation syntax here, please dont mine.

its just to upcome from UNDO TABLESPACE problem in common.

1) In "init.ora" file please set the UNDO TABLESPACE name and keep it on ur mind.

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
UNDO_TABLESPACE=UNDOTBS1

2) Type creation commands in a notepad which will helps you to write it without any wrong spells,directory paths and commas(').

3) Here u have to remember the UNDO TABLESPACE NAME u given in "init.ora" file, and specify the same in the line "undo tablespace UNDOTBS1"

For u reference here am giving a sample prg.

create database iapps
logfile group 1 'C:/oracle/product/10.2.0/oradata/iapps/redo1.log' size 10m,
group 2 'C:/oracle/product/10.2.0/oradata/iapps/redo2.log' size 10m,
group 3 'C:/oracle/product/10.2.0/oradata/iapps/redo3.log' size 10m
datafile 'C:/oracle/product/10.2.0/oradata/iapps/system.dbf' size 50m autoextend on
next 2m maxsize 90m
sysaux datafile 'C:/oracle/product/10.2.0/oradata/iapps/sysaux.dbf' size 25m autoextend on
next 1m maxsize 40m
undo tablespace UNDOTBS1
datafile 'C:/oracle/product/10.2.0/oradata/iapps/undo.dbf' size 10m
default temporary tablespace temp
tempfile 'C:/oracle/product/10.2.0/oradata/iapps/temp.dbf' size 10m

I Hope this will help ever new junior DBA's