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

Error:

SQL> startup

ORACLE instance started.

Total System Global Area 2221395968 bytes

Fixed Size                                2230272 bytes

Variable Size                      1325402112 bytes

Database Buffers               889192448 bytes

Redo Buffers                         4571136 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

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

Process ID: 23812

Session ID: 3 Serial number: 3

Background:

This error appeared after adding undo tablespace for second RAC node.

DB was getting mounted but OPEN DATABASE was throwing ORA-30012

Cause/Possible Solution:

1. Did not used DBCA?

Database created without dbca may face this issue , so if it is feasible then try to create database instance using dbca than using create database command or through restoration

2. Undo tablespace missing

Undo tablespace itself is missing in database.

In RAC , every instance has its own undo tablespace

In Open mode from other RAC instance,

SQL>select TABLESPACE_NAME ,BLOCK_SIZE,INITIAL_EXTENT,MAX_EXTENTS,STATUS,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where TABLESPACE_NAME like 'UNDO%';

TABLESPACE_NAME       BLOCK_SIZE INITIAL_EXTENT MAX_EXTENTS STATUS    CONTENTS  EXTENT_MAN SEGMEN

----------------                        -------------- ---------- -------------- ----------- --------- --------- ---------- ------

UNDOTBS1                        8192          65536  2147483645 ONLINE    UNDO      LOCAL      MANUAL

UNDOTBS2                         8192          65536  2147483645 ONLINE    UNDO      LOCAL      MANUAL

From Mount mode,

SQL>select * from v$tablespace where NAME like 'UNDO%';

       TS# NAME                           INC BIG FLA ENC

    ---------- ------------------------------ --- --- --- ---

         2 UNDOTBS1                       YES NO  YES

         7 UNDOTBS2                       YES NO  YES

3. Undo tablespace created is Not UNDO

You have created UNDO tablespace without “undo” clause mentioned like below , which will be similar as other users tablespace with CONTENTS as PERMANENT than UNDO

Wrong Type:

SQL>create tablespace UNDOTBS2 datafile  '+DATA1_RPT' size 200M;

SQL> select * from dba_tablespaces  where TABLESPACE_NAME like 'UNDO%'  ;

TABLESPACE_NAME              BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS       CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR

------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- --- ------- --- ------------

UNDOTBS1                                              8192     65536                        1  2147483645 2147483645                       65536 ONLINE    UNDO     LOGGING   NO  LOCAL      SYSTEM    NO  MANUAL DISABLED NOGUARANTEE NO  HOST    NO

UNDOTBS2                                              8192     65536                        1  2147483645 2147483645                       65536 ONLINE    PERMANENT LOGGING   NO  LOCAL      SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO  HOST    NO

Correct Type:

SQL> create undo tablespace UNDOTBS2 datafile '+DATA1_RPT' SIZE 200M;

SQL> select * from dba_tablespaces  where TABLESPACE_NAME like 'UNDO%';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG PREDICA ENC COMPRESS_FOR

------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- --- ------- --- ------------

UNDOTBS1                             8192          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO  LOCAL      SYSTEM    NO  MANUAL DISABLED NOGUARANTEE NO  HOST    NO

UNDOTBS2                             8192          65536                       1  2147483645 2147483645                   65536 ONLINE    UNDO      LOGGING   NO  LOCAL      SYSTEM    NO  MANUAL DISABLED NOGUARANTEE NO  HOST    NO

4. Set undo_management to MANUAL

This is just a workaround if all above solutions does not work; it will help to open the database but plan to set it AUTO in future

SQL>Alter system set undo_management=’MANUAL’;