If there are ORA-01092 and ORA-30012 errors together

Errors like this

ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type

if there are ORA-01092 and ORA-30012 errors together, here's how to resolve them

[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 5 10:23:32 2019 Copyright (c) 1982, 2011, Oracle.  All rights reserved. SQL> connect sys as sysdba Enter password: Connected to an idle instance. SQL>

SQL> startup mount ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 331350480 bytes Database Buffers 197132288 bytes Redo Buffers 5804032 bytes Database mounted.

SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 9 RCRMAN YES NO YES 10 USERS_TBS YES NO YES 5 UNDOTBS2 YES NO YES 19 TESTTBS YES NO YES 14 TESTTB YES NO YES TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 15 TESTDB YES NO YES 12 rows selected.

SQL> create pfile='/oracle/product/11.2.0/db_1/dbs/initbckorcl.ora' from spfile; File created.

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.

Edit Pfile UNDO_TABLESPACE PARAMETER

ORCL.undo_tablespace='UNDOTBS01' orcl.undo_tablespace='UNDOTBS01' *.undo_tablespace='UNDOTBS02'

Change like this

ORCL.undo_tablespace='UNDOTBS02' orcl.undo_tablespace='UNDOTBS02' *.undo_tablespace='UNDOTBS02'

Startup with new pfile

SQL> startup pfile='/oracle/product/11.2.0/db_1/dbs/initbckorcl.ora'; ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 331350480 bytes Database Buffers 197132288 bytes Redo Buffers 5804032 bytes Database mounted. Database opened. SQL>

SQL> create spfile from pfile; File created. SQL> create spfile from pfile='/oracle/product/11.2.0/db_1/dbs/initbckorcl.ora'; File created.

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1375792 bytes Variable Size 331350480 bytes Database Buffers 197132288 bytes Redo Buffers 5804032 bytes Database mounted. Database opened.

SQL> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2