Oracle报错ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
👉 本文共约583个字，系统预计阅读时间或需3分钟。
本文简介
甲方在做灾备切换演练的时候，有一套数据库不能正常启动，基本的报错都是：ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type 。下面记录一下其解决过程。
故障分析及解决过程
故障环境介绍
| 项目 | source db |
|---|---|
| db 类型 | RAC |
| db version | 11.2.0.3.0 |
| db 存储 | ASM |
| OS版本及kernel版本 | AIX 64位 7.1.0.0 |
故障发生现象及报错信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | [ZFLHRDB1:root]:/>crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA1.dg ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.LISTENER.lsnr ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.asm ONLINE ONLINE zflhrdb1 Started ONLINE ONLINE zflhrdb2 Started ora.gsd OFFLINE OFFLINE zflhrdb1 OFFLINE OFFLINE zflhrdb2 ora.net1.network ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.ons ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.registry.acfs ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE zflhrdb1 ora.cvu 1 ONLINE ONLINE zflhrdb1 ora.oc4j 1 ONLINE ONLINE zflhrdb1 ora.oralhr.db 1 ONLINE OFFLINE Instance Shutdown 2 ONLINE OFFLINE Instance Shutdown ora.scan1.vip 1 ONLINE ONLINE zflhrdb1 ora.zflhrdb1.vip 1 ONLINE ONLINE zflhrdb1 ora.zflhrdb2.vip 1 ONLINE ONLINE zflhrdb2 [ZFLHRDB1:root]:/>srvctl start db -d oralhr PRCR-1079 : Failed to start resource ora.oralhr.db CRS-5017: The resource action "ora.oralhr.db start" encountered the following error: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type Process ID: 12976284 Session ID: 1421 Serial number: 1 . For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/zflhrdb1/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.oralhr.db' on 'zflhrdb1' failed CRS-2632: There are no more servers to try to place resource 'ora.oralhr.db' on that would satisfy its placement policy CRS-5017: The resource action "ora.oralhr.db start" encountered the following error: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type Process ID: 14614730 Session ID: 1421 Serial number: 1 . For details refer to "(:CLSN00107:)" in "/oracle/app/11.2.0/grid/log/zflhrdb2/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.oralhr.db' on 'zflhrdb2' failed |
故障分析及解决过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [ZFLHRDB1:root]:/>oerr ora 30012 30012, 00000, "undo tablespace '%s' does not exist or of wrong type" // *Cause: the specified undo tablespace does not exist or of the // wrong type. // *Action: Correct the tablespace name and reissue the statement. [ZFLHRDB1:root]:/>su - oracle [ZFLHRDB1:oracle]:/oracle>sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 6 23:46:05 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SYS@oraLHR1> startup mount ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2225776 bytes Variable Size 1409288592 bytes Database Buffers 1778384896 bytes Redo Buffers 16936960 bytes Database mounted. SYS@oraLHR1> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 3 TEMP NO NO YES 5 USERS YES NO YES 6 ILHRDATA YES NO YES 7 ILHRIND YES NO YES 8 ILHRLOG YES NO YES 9 UNDO1 YES NO YES 10 UNDO2 YES NO YES 9 rows selected. SYS@oraLHR1> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA1/oralhr/spfileoralhr.ora SYS@oraLHR1> SELECT a.SID, a.VALUE FROM v$spparameter a WHERE a.NAME LIKE '%undo_tablespace%' ; SID VALUE -------------------- ----------- oraLHR2 UNDOTBS2 oraLHR1 UNDOTBS1 * UNDO2 |
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
可以看到UNDO表空间的名称和从控制文件中查到的名称不一致，下面修改SPFILE文件中有关UNDO表空间的部分，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | SYS@oraLHR1> ALTER SYSTEM SET undo_tablespace='UNDO1' SID='oraLHR1'; System altered. SYS@oraLHR1> ALTER SYSTEM SET undo_tablespace='UNDO2' SID='oraLHR2'; System altered. SYS@oraLHR1> SELECT a.SID, a.VALUE FROM v$spparameter a WHERE a.NAME LIKE '%undo_tablespace%'; SID VALUE --------------- ------ oraLHR1 UNDO1 oraLHR2 UNDO2 <<<<<<<<<------- 已经和控制文件中记录的一致了，下面启动数据库 SYS@oraLHR1> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [ZFLHRDB1:oracle]:/oracle>srvctl stop db -d oralhr -o abort [ZFLHRDB1:oracle]:/oracle>srvctl start db -d oralhr [ZFLHRDB1:oracle]:/oracle>crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA1.dg ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.LISTENER.lsnr ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.asm ONLINE ONLINE zflhrdb1 Started ONLINE ONLINE zflhrdb2 Started ora.gsd OFFLINE OFFLINE zflhrdb1 OFFLINE OFFLINE zflhrdb2 ora.net1.network ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.ons ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 ora.registry.acfs ONLINE ONLINE zflhrdb1 ONLINE ONLINE zflhrdb2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE zflhrdb1 ora.cvu 1 ONLINE ONLINE zflhrdb1 ora.oc4j 1 ONLINE ONLINE zflhrdb1 ora.oralhr.db 1 ONLINE ONLINE zflhrdb1 Open 2 ONLINE ONLINE zflhrdb2 Open ora.scan1.vip 1 ONLINE ONLINE zflhrdb1 ora.zflhrdb1.vip 1 ONLINE ONLINE zflhrdb1 ora.zflhrdb2.vip 1 ONLINE ONLINE zflhrdb2 [ZFLHRDB1:oracle]:/oracle>srvctl status db -d oralhr Instance oraLHR1 is running on node zflhrdb1 Instance oraLHR2 is running on node zflhrdb2 |
故障处理总结
ORA-30012基本是由于参数文件中的undo_tablespace的值和控制文件中记录的值不一致导致的。可以使用ALTER SYSTEM修改或者直接修改参数文件中有关undo_tablespace的部分和控制文件中的一致即可。
关于为何会出现参数文件和控制文件中undo_tablespace的值不一致的情况，这个可以从告警日志中去查询，这里就不深究了，毕竟是个测试库，也许很多年都没人使用了吧。
