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>