【故障處理】ORA-30012的解決過程
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① ORA-30012的解決過程
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代碼、相關軟件、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若網頁文章代碼格式有錯亂，請下載pdf格式的文檔來閱讀。
④ 在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。其中，需要特別關注的地方我都用灰色背景和粉紅色字體來表示，比如在下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XLHRD_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
甲方在做災備切換演練的時候，有一套數據庫不能正常啟動，基本的報錯都是：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
[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
[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
可以看到UNDO表空間的名稱和從控制文件中查到的名稱不一致，下面修改SPFILE文件中有關UNDO表空間的部分，如下所示：
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的值不一致的情況，這個可以從告警日志中去查詢，這裡就不深究了，畢竟是個測試庫，也許很多年都沒人使用了吧。
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於數據庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2127949/
● 本文博客園地址：http://www.cnblogs.com/lhrbest/p/6040312.html
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 聯系我請加QQ好友(642808185)，注明添加緣由
● 於 2016-11-06 23:00 ~ 2016-11-07 12:00 在中行完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的數據庫技術。
