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’;


No comments:

Post a Comment

My Popular Posts