ORA-30012 about undo tablespace configuration error

The undo tablespace is an important part of the Oracle architecture. The reason why we can roll back is that we have it. Any data modification in the database will generate the pre-image in the undo tablespace. One is rollback, and the other is concurrency and consistent query. Therefore, undo is an essential part of Oracle Database creation and parameter configuration. This article describes how to solve the fault after incorrectly configuring the undo tablespace.

For basic undo tablespace knowledge, refer:
Oracle ROLLBACK and UNDO)
Check and set reasonable undo tablespace
Shrink undo tablespace

 

1. undo Exception error prompt

oracle@DevDB04:~> export ORACLE_SID=BODB3oracle@DevDB04:~> sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Apr 23 10:19:27 2014Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL> startup pfile=/u02/database/BODB3/initBODB3.ora;ORACLE instance started.Total System Global Area  536870912 bytesFixed Size                  2097624 bytesVariable Size             411045416 bytesDatabase Buffers          117440512 bytesRedo Buffers                6287360 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forced

2. Fault Analysis
# Alert Log Information
# We receive the error message: ORA-30012, UNDOTBS1 does not exist or Type Error
Wed Apr 23 10:19:49 HKT 2014
Errors in file/u02/database/BODB3/udump/bodb3_ora_819.trc:
ORA-30012: undo tablespace 'undotbs1 'does not exist or of wrong type
Wed Apr 23 10:19:49 HKT 2014
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012.
Instance terminated by USER, pid = 819
ORA-1092 signalled during: alter database open...

# Further viewing the trace file
Oracle @ DevDB04:/u02/database/BODB3/bdump> more/u02/database/BODB3/udump/bodb3_ora_819.trc
/U02/database/BODB3/udump/bodb3_ora_819.trc
Oracle Database 10g Release 10.2.0.5.0-64bit Production
ORACLE_HOME =/users/oracle/OraHome10g
System name: Linux
Node name: DevDB04
Release: 2.6.16.46-0.12-smp
Version: #1 SMP Thu May 17 14:00:09 UTC 2007
Machine: x86_64
Instance name: BODB3
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 819, image: oracle @ DevDB04 (TNS V1-V3)

* ** Action name :() 10:19:49. 076
... The middle part is omitted .....
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
ORA-30012: undo tablespace 'undotbs1 'does not exist or of wrong type
# It is also a problem related to undo. UNDOTBS1 does not exist or the type is incorrect.
# That is, the undo parameter is not set correctly.

 

3. troubleshooting

SQL> startup mount pfile =/u02/database/BODB3/initBODB3.ora; ORACLE instance started. -- Note: If the undo parameter is not correctly set, it can be mounted. -- in the mount stage, the control file is read to obtain the name and status information of the data file and the redo log file, but do not check the existence and consistency of data and log files -- Author: Leshami -- Blog: response> show parameter undoNAME type value =----------- response undo_management string comment integer comment string UNDOTBS1SQL> ho grep undo/u02/database/BODB3/comment _ management = AUTOundo_tablespace = UNDOTBS1 # comment = 86400 # undo_retention = 172800 -- undo information recorded in the data dictionary, there is no undotbs1, which is the problem where SQL> select name from v $ tablespace where name like '% UNDO %'; NAME------------------------------UNDOTBS2UNDOTBS -- view the data file corresponding to undo below, there are 2, the preceding two undo tablespaces correspond to SQL> ho ls-hltr/u02/database/BODB3/undototal 301M-rw-r ----- 1 oracle oinstall 201 M 2014-04-23 19 undotbsBODB33.dbf-rw-r ----- 1 oracle oinstall 101 M undotbsBODB32.dbfSQL> shutdown abort; ORACLE instance shut down. -- modify the undo configuration to any undo tablespace SQL> ho vi/u02/database/BODB3/tablespace> ho grep undo/u02/database/BODB3/initBODB3.oraundo _ management = AUTOundo_tablespace = UNDOTBS # undo_retention = 86400 # undo_retention = 172800 -- it can be opened normally after restart again -- if you are using spfile, you can directly use alter system to modify SQL> startup pfile =/u02/database/BODB3/initBODB3.oraORACLE instance started. total System Global Area 536870912 bytesFixed Size 2097624 bytesVariable Size 411045416 bytesDatabase Buffers 117440512 bytesRedo Buffers 6287360 bytesDatabase mounted. database opened. SQL> show parameter db_nameNAME TYPE VALUE values ----------- mongodb_name string BODB3SQL> select open_mode from v $ database; OPEN_MODE----------READ WRITESQL> show parameter undoNAME TYPE VALUE =----------- export undo_management string comment integer comment string UNDOTBS

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in oracle rac (listener. ora tnsnames. ora)
Oracle rac listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle LISTENER password (LISTENER)
Configure the ORACLE client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
SYSTEM tablespace management and Backup Recovery
SYSAUX tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the ORACLE architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle ONLINE redo LOG FILE)
Oracle Control File)
Oracle archiving logs
Oracle ROLLBACK and UNDO)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)