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 the tablespace then create the tablespace.
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.