Skip to content

How to Resolve ORA-30012: undo tablespace does not exist or of wrong type

  • by

ORA-30012

Tried to restart a normal, typical database after changing the default undo tablespace, but it failed with ORA-30012.

SQL> startup
ORACLE instance started.
...
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type
Process ID: 3537
Session ID: 1145 Serial number: 49897

In the last section, I'll talk about ORA-30012 in pluggable database (PDB), you may directly go there.

ORA-30012 means that the default UNDO tablespace you specified in SPFILE or PFILE does not exist, or it's not an UNDO tablespace essentially. Therefore, the database fails to startup.

Most likely, you mistyped the tablespace name when changed the default undo tablespace parameter UNDO_TABLESPACE.

Let's see how we solve it.

Solution

We should make sure the correct undo tablespace name.

1. Startup to Mount

To check some information about tablespaces, we need it mounted.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
...
Database mounted.

2. Check Tablespace

We have to list all valid tablespaces in order to choose the correct one.

SQL> select name from v$tablespace order by 1;

NAME
------------------------------
SYSAUX
SYSTEM
TEMP
UNDOTBS1
UNDO_2
USERS

6 rows selected.

For multitenant databases, you should use the statement as below to check the root container.

SQL> select name from v$tablespace where con_id = 1 order by 1;

3. Set Correct UNDO_TABLESPACE

If there's any mistyping or misspelling, you should correct it now. In this case, the tablespace name is correct. So it must be another issue, we have to switch UNDO_TABLESPACE back to the original one with SCOPE=SPFILE.

SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

System altered.

4. Restart Database

To verify that the parameter has been set correctly, we should restart it.

SQL> shutdown immediate;
...
SQL> startup
...

For mistyping or misspelling cases, you are safe and you can stop now if it opens normally. For rest cases, there're more jobs to do.

In some rare cases, you could have created a normal tablespace, not an undo tablespace. So we need to check their types.

5. Check Tablespace Type

SQL> select tablespace_name, contents from dba_tablespaces order by 1;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------------------
SYSAUX                         PERMANENT
SYSTEM                         PERMANENT
TEMP                           TEMPORARY
UNDOTBS1                       UNDO
UNDO_2                         PERMANENT
USERS                          PERMANENT

As we can see, the tablespace UNDO_2 has been created as a normal database, not an UNDO one. This is the source of problem.

To correct the problem, we should re-create the tablespace, then set a correct undo tablespace for UNDO_TABLESPACE.

6. Re-create Tablespace as UNDO

Drop then create it.

SQL> drop tablespace undo_2 including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace undo_2 datafile '/u01/app/oracle/oradata/ORCLCDB/undo_2.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Let's check its type.

SQL> select tablespace_name, contents from dba_tablespaces order by 1;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------------------
SYSAUX                         PERMANENT
SYSTEM                         PERMANENT
TEMP                           TEMPORARY
UNDOTBS1                       UNDO
UNDO_2                         UNDO
USERS                          PERMANENT

6 rows selected.

It's an UNDO now.

7. Set Correct UNDO_TABLESPACE

If you do like the newly created UNDO tablespace to be the default one, you can set it with SCOPE=SPFILE.

SQL> alter system set undo_tablespace=UNDO_2 scope=spfile;

System altered.

8. Restart Database

To verify the parameter is correctly set, we should restart it.

SQL> shutdown immediate;
...
SQL> startup
...

We're good.

ORA-30012 in Pluggable Database (PDB)

We open a PDB after we changed the default undo tablespace of it in SPFILE.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        MOUNTED
SQL> show con_name

CON_NAME
------------------------------
ORCLPDB
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type

Since the PDB is already mounted, it's easier to solve it than in a CDB or a non-CDB.

Set Correct UNDO_TABLESPACE

SQL> alter system set undo_tablespace=UNDOTBS1 container=current scope=spfile;

System altered.

Open PDB

SQL> alter database open;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        READ WRITE NO

We solved ORA-30012.

Leave a Reply

Your email address will not be published. Required fields are marked *