Skip to content
Advertisements

ORA-30012: undo tablespace ‘UNDOTBS02’ does not exist or of wrong type

When i try startup my test db , the following error will be occur.

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type
Process ID: 14328
Session ID: 125 Serial number: 5

Actually what i’am wrong here, I created new undo tablespace for existing tablespace & then I dropped existing undo tablespace. I startup using pfile and worked, later i shutdown database without creating spfile for existing db. SO I just startup using spfile, the above error will be shown.

Solution :

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
20 ADDME YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
9 RCRMAN YES NO YES
10 USERS_TBS YES NO YES
5 UNDOTBS2 YES NO YES
19 TESTTBS YES NO YES
14 TESTTB YES NO YES

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
15 TESTDB YES NO YES

12 rows selected.

SQL> create pfile='d:\backupnew\initazardb.ora' from spfile;

File created.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Edit Pfile UNDO_TABLESPACE PARAMETER
SQL> startup pfile='d:\backupnew\initazardb.ora';
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile from pfile;

File created.

SQL> create spfile from pfile='d:\backupnew\initazardb.ora';

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 331350480 bytes
Database Buffers 197132288 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.
SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
Advertisements
12 Comments Post a comment
  1. Synicasm #

    Thank you very much for this .. Fixed !

    April 6, 2011
  2. Obrigado pela ajuda! Salvou o meu emprego! 🙂

    November 7, 2011
  3. Travis Johnson #

    Thanks for the article…I had the exact same problem, only another dba didn’t tell me he made a different undo tablespace. This article walked me right through how to fix it!

    February 21, 2012
  4. Ahmed #

    Hi Mohammed Azar,
    Asalamu alaikkum, Your artical is very good. I got fixed one error from your artical password profile.
    What is your mail id.please mail me
    Regards,
    Ahmed

    February 24, 2012
  5. Mohammed Abdul Mannan #

    Dear Azar,

    Really appreciate your efforts for posting. Your blog helped me when I was struggling the most. May ALLAH give u best rewards for never ending help.

    Regards.
    Abdul Mannan

    June 1, 2012
  6. james #

    Thanks Azar, this was pretty useful.

    July 17, 2012
  7. john #

    Thank you!!

    January 10, 2013
  8. Florian #

    Thank you so much – you rescued my day!

    February 25, 2013
  9. Carlos #

    The Hero of the Day!

    Thank you so much!!

    February 19, 2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: