The Problem
A new instance of an existing RAC database was created – not using DBCA. The new database instance will not start. Attempting to start the new instance throws ORA-30012 on startup.
ORA-30012: undo tablespace 'UNDOTBS_02' does not exist or of wrong type
The Solution
The new database instance will not start because a new UNDO tablespace was not created and assigned to it. Each instance in a RAC database must have its own UNDO tablespace, specified in the pfile/spfile.
When a new instance is added using DBCA, DBCA should automatically create an UNDO tablespace for the new instance. If a new instance is added manually (not recommended), then the new UNDO tablespace must also be manually created and manually assigned to the new instance. The recommended way to add a new RAC instance is to use DBCA.
Undo tablespaces in the Oracle RAC database are assigned by specifying a different value for the UNDO_TABLESPACE parameter for each instance in the SPFILE or in individual PFILEs. It is not necessary to shut down the other instances in order to fix this problem. Each instance has a different undo tablespace, so a new one can be created and assigned to the new instance without affecting the running instances.
1.Use CREATE UNDO TABLESPACE statement to create a new undo tablespace for the new instance. For example:
SQL> CREATE UNDO TABLESPACE undotbs_02 DATAFILE '+DATADG1' size 1000M autoextend on;
2. Use ALTER SYSTEM to set UNDO_TABLESPACE to the new tablespace for the new SID. For example:
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02 SCOPE=BOTH SID='PROD2';
The new instance should have SID.UNDO_TABLESPACE set to the name of the new undo tablespace just created. Each of the other instances of the database should keep its undo tablespace as the same as what it was before.
3. The new instance should now start without throwing ORA-30012.