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

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

I have 3 node RAC system. I am trying to convert from single to the RAC system. I have created undo tablespace for instance 2 & 3. I am having thrown the below error messages.

SQL> startup

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size                  2227824 bytes

Variable Size             620757392 bytes

Database Buffers          587202560 bytes

Redo Buffers                9146368 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

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

Process ID: 6920

Session ID: 67 Serial number: 5

Note: you might the get this error message when you have specified the wrong undo tablespace for the particular instance in the rac environment or single instance environment. In that case, you have to create a pfile and you have modify the undo_tablespace value.

I have login to the Instance-1 and try to drop the undo tablespace which has been created for instance-2 & instance-3

SQL> drop tablespace UNDO_2 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace UNDO_3 including contents and datafiles;

Tablespace dropped.

Then I have used the CREATE UNDO TABLESPACE option to create the tablespace for Instance – 2 & 3.

An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

SQL> create undo tablespace UNDO_2 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

SQL> create undo tablespace UNDO_3 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

I have modified the undo tablespace for the Instance 2 & 3 in the spfile

SQL>  alter system set undo_tablespace='UNDO_2' scope=spfile sid='BHU_2';

System altered.

SQL> alter system set undo_tablespace='UNDO_3' scope=spfile sid='BHU_3';

System altered.

After the modification, I am trying to open the instance-2 & 3. it opens with out any issues.

SQL> startup

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size                  2227824 bytes

Variable Size             620757392 bytes

Database Buffers          587202560 bytes

Redo Buffers                9146368 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

_in_memory_undo                      boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer         900

undo_tablespace                      string        UNDO_2

SQL>

I hope this resolve your issue and happy learning!!!