資料庫升級造成的X_$BH狀態異常問題
同事對一個測試庫執行了升級操作，將9204資料庫升級到了10201，升級後發現X_$BH和X_$KCBWDS檢視狀態不正確。
由於升級的時候沒有參考Metalink的文件，而是直接使用DBUA升級造成了X_$BH和X_$KCBWDS的狀態異常。其實造成這個問題的原因還是DBUA造成的。
先看一下問題：
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND STATUS = 'INVALID';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS X_$BH VIEW
SYS X_$KCBWDS VIEW
創始直接編譯物件：
SQL> ALTER VIEW X_$BH COMPILE;
警告: 更改的檢視帶有編譯錯誤。
SQL> SHOW ERR
沒有錯誤。
也沒有錯誤資訊，檢查一下檢視的定義，並對比X$BH表結構，發現了異常：
SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'X_$BH';
TEXT
--------------------------------------------------------------------------------
SELECT "ADDR","INDX","INST_ID","HLADDR","BLSIZ","NXT_HASH","PRV_HASH","NXT_REPL"
,"PRV_REPL","FLAG","LRU_FLAG","TS#","FILE#","DBARFIL","DBABLK","CLASS","STATE","
MODE_HELD","CHANGES","CSTATE","X_TO_NULL","FORCED_READS","FORCED_WRITES","LE_ADD
R","DIRTY_QUEUE","SET_DS","OBJ","BA","CR_SCN_BAS","CR_SCN_WRP","CR_XID_USN","CR_
XID_SLT","CR_XID_SQN","CR_UBA_FIL","CR_UBA_BLK","CR_UBA_SEQ","CR_UBA_REC","CR_SF
L","LRBA_SEQ","LRBA_BNO","HSCN_BAS","HSCN_WRP","HSUB_SCN","RRBA_SEQ","RRBA_BNO",
"US_NXT","US_PRV","WA_NXT","WA_PRV","TCH","TIM" FROM X$BH
SQL> DESC X$BH
名稱 是否為空? 型別
----------------------------- -------- -----------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8)
BLSIZ NUMBER
NXT_HASH RAW(8)
PRV_HASH RAW(8)
NXT_REPL RAW(8)
PRV_REPL RAW(8)
FLAG NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER
TS# NUMBER
FILE# NUMBER
DBARFIL NUMBER
DBABLK NUMBER
CLASS NUMBER
STATE NUMBER
MODE_HELD NUMBER
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(8)
DIRTY_QUEUE NUMBER
SET_DS RAW(8)
OBJ NUMBER
BA RAW(8)
CR_SCN_BAS NUMBER
CR_SCN_WRP NUMBER
CR_XID_USN NUMBER
CR_XID_SLT NUMBER
CR_XID_SQN NUMBER
CR_UBA_FIL NUMBER
CR_UBA_BLK NUMBER
CR_UBA_SEQ NUMBER
CR_UBA_REC NUMBER
CR_SFL NUMBER
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER
LRBA_BNO NUMBER
HSCN_BAS NUMBER
HSCN_WRP NUMBER
HSUB_SCN NUMBER
US_NXT RAW(8)
US_PRV RAW(8)
WA_NXT RAW(8)
WA_PRV RAW(8)
OBJ_FLAG NUMBER
TCH NUMBER
TIM NUMBER
從表結果上可以看到10g的X_$BH欄位已經發生了變化，原來9i的某些欄位在10g中已經不存在了，比如：X_TO_NULL、FORCED_READS、FORCED_WRITES等。
查詢metalink文件，發現需要在升級前將這些檢視刪除掉，否則會造成資料字典異常。
而Oracle提供的解決方法是刪除問題檢視。先宣告一點下面的操作Oracle要求必須在技術支援的指導下來完成。本人不對下面的操作負責，不要在沒有Oracle技術支援的情況下對正式環境執行下面的操作：
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 12月 26 13:35:14 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SHUTDOWN IMMEDIATE
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> STARTUP RESTRICT
ORACLE 例程已經啟動。
Total System Global Area 5083496448 bytes
Fixed Size 2079736 bytes
Variable Size 872416264 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14696448 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> DELETE FROM DEPENDENCY$
2 WHERE D_OBJ# IN
3 (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('X_$BH', 'X_$KCBWDS') AND WNER = 'SYS');
已刪除6行。
SQL> COMMIT;
提交完成。
SQL> DROP VIEW X_$BH;
檢視已刪除。
SQL> DROP VIEW X_$KCBWDS;
檢視已刪除。
SQL> SHUTDOWN IMMEDIATE
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> STARTUP
ORACLE 例程已經啟動。
Total System Global Area 5083496448 bytes
Fixed Size 2079736 bytes
Variable Size 872416264 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14696448 bytes
資料庫裝載完畢。
資料庫已經開啟。
Oracle的metalink文件至此就結果了，重啟後發現問題確實已經“解決”：
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS
2 WHERE WNER = 'SYS'
3 AND STATUS = 'INVALID';
未選定行
但是馬上發現，問題雖然沒有了，但是兩個檢視也沒有了：
SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME = 'X_$BH';
未選定行
有問題就刪除掉，這種解決問題的方法倒是也方便，此時，除了root使用者外，其他使用者訪問X$BH都會報錯：
SQL> SELECT COUNT(*) FROM X$BH;
COUNT(*)
----------
3035
SQL> SELECT COUNT(*) FROM SYS.X_$BH;
SELECT COUNT(*) FROM SYS.X_$BH
*
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在
SQL> CONN NDMAIN
輸入口令:
已連線。
SQL> SELECT COUNT(*) FROM X$BH;
SELECT COUNT(*) FROM X$BH
*
第 1 行出現錯誤:
ORA-00980: 同義詞轉換不再有效
穩妥起見，還是手工重建這兩個刪除掉的檢視：
SQL> CONN / AS SYSDBA
已連線。
SQL> CREATE OR REPLACE FORCE VIEW
2 "SYS"."X_$BH" ("ADDR", "INDX", "INST_ID", "HLADDR", "BLSIZ", "NXT_HASH", "PRV_HASH",
3 "NXT_REPL", "PRV_REPL", "FLAG", "RFLAG", "SFLAG", "LRU_FLAG", "TS#", "FILE#",
4 "DBARFIL", "DBABLK", "CLASS", "STATE", "MODE_HELD", "CHANGES", "CSTATE", "LE_ADDR",
5 "DIRTY_QUEUE", "SET_DS", "OBJ", "BA", "CR_SCN_BAS", "CR_SCN_WRP", "CR_XID_USN",
6 "CR_XID_SLT", "CR_XID_SQN", "CR_UBA_FIL", "CR_UBA_BLK", "CR_UBA_SEQ", "CR_UBA_REC",
7 "CR_SFL", "CR_CLS_BAS", "CR_CLS_WRP", "LRBA_SEQ", "LRBA_BNO", "HSCN_BAS", "HSCN_WRP",
8 "HSUB_SCN", "US_NXT", "US_PRV", "WA_NXT", "WA_PRV", "OBJ_FLAG", "TCH","TIM")
9 AS SELECT "ADDR","INDX","INST_ID","HLADDR","BLSIZ","NXT_HASH","PRV_HASH","NXT_REPL",
10 "PRV_REPL","FLAG","RFLAG","SFLAG","LRU_FLAG","TS#","FILE#","DBARFIL","DBABLK","CLASS",
11 "STATE","MODE_HELD","CHANGES","CSTATE","LE_ADDR","DIRTY_QUEUE","SET_DS","OBJ","BA",
12 "CR_SCN_BAS","CR_SCN_WRP","CR_XID_USN","CR_XID_SLT","CR_XID_SQN","CR_UBA_FIL",
13 "CR_UBA_BLK","CR_UBA_SEQ","CR_UBA_REC","CR_SFL","CR_CLS_BAS","CR_CLS_WRP","LRBA_SEQ",
14 "LRBA_BNO","HSCN_BAS","HSCN_WRP","HSUB_SCN","US_NXT","US_PRV","WA_NXT","WA_PRV",
15 "OBJ_FLAG","TCH","TIM" FROM X$BH
16 ;
檢視已建立。
SQL> CREATE OR REPLACE FORCE VIEW "SYS"."X_$KCBWDS"
2 ("ADDR", "INDX", "INST_ID", "SET_ID", "DBWR_NUM", "BLK_SIZE", "PROC_GROUP",
3 "CNUM_SET", "FLAG", "CKPT_LATCH", "CKPT_LATCH1", "SET_LATCH", "NXT_REPL",
4 "PRV_REPL", "NXT_REPLAX", "PRV_REPLAX", "CNUM_REPL", "ANUM_REPL", "COLD_HD",
5 "HBMAX", "HBUFS", "NXT_WRITE", "PRV_WRITE", "NXT_WRITEAX", "PRV_WRITEAX",
6 "CNUM_WRITE", "ANUM_WRITE", "NXT_XOBJ", "PRV_XOBJ", "NXT_XOBJAX", "PRV_XOBJAX",
7 "CNUM_XOBJ", "ANUM_XOBJ", "NXT_XRNG", "PRV_XRNG", "NXT_XRNGAX", "PRV_XRNGAX",
8 "CNUM_XRNG", "ANUM_XRNG", "NXT_REQ", "PRV_REQ", "NXT_REQAX", "PRV_REQAX",
9 "CNUM_REQ", "ANUM_REQ", "BUF_GOT", "SUM_WRT", "SUM_SCN", "FBWAIT", "WCWAIT",
10 "BBWAIT", "FBINSP","DBINSP", "PNINSP", "HOTMVS", "DBBCHG", "DBBGET", "CONGET",
11 "PREAD", "PWRITE", "FGSDEPTH", "TGTCLEAN", "PWBCNT", "PROTCNT")
12 AS SELECT "ADDR","INDX","INST_ID","SET_ID","DBWR_NUM","BLK_SIZE","PROC_GROUP",
13 "CNUM_SET","FLAG","CKPT_LATCH","CKPT_LATCH1","SET_LATCH","NXT_REPL","PRV_REPL",
14 "NXT_REPLAX","PRV_REPLAX","CNUM_REPL","ANUM_REPL","COLD_HD","HBMAX","HBUFS",
15 "NXT_WRITE","PRV_WRITE","NXT_WRITEAX","PRV_WRITEAX","CNUM_WRITE","ANUM_WRITE",
16 "NXT_XOBJ","PRV_XOBJ","NXT_XOBJAX","PRV_XOBJAX","CNUM_XOBJ","ANUM_XOBJ","NXT_XRNG",
17 "PRV_XRNG","NXT_XRNGAX","PRV_XRNGAX","CNUM_XRNG","ANUM_XRNG","NXT_REQ","PRV_REQ",
18 "NXT_REQAX","PRV_REQAX","CNUM_REQ","ANUM_REQ","BUF_GOT","SUM_WRT","SUM_SCN",
19 "FBWAIT","WCWAIT","BBWAIT","FBINSP","DBINSP","PNINSP","HOTMVS","DBBCHG","DBBGET",
20 "CONGET","PREAD","PWRITE","FGSDEPTH","TGTCLEAN","PWBCNT","PROTCNT" FROM X$KCBWDS;
檢視已建立。
至此問題解決。再次強調，不要嘗試對產品系統進行類似的操作，且進行這種操作前應先對資料庫進行備份。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-152682/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Realm 升級資料庫，為表新增新的Field遇到的異常記錄資料庫
- Oracle資料庫日常問題-歸檔異常增長Oracle資料庫
- 資料庫常見問題資料庫
- MyBatis版本升級導致OffsetDateTime入參解析異常問題覆盤MyBatis
- TR2021_0000偶發資料庫連線異常問題排查資料庫
- 10個常見的資料庫安全問題資料庫
- el-table 多表格彈窗巢狀資料顯示異常錯亂問題巢狀
- 【YashanDB資料庫】yasboot查詢資料庫狀態時顯示資料庫狀態為off資料庫boot
- synchronized四種鎖狀態的升級synchronized
- 資料庫升級之-Dataguard滾動升級資料庫
- DataIntegrityViolationException異常：java利用mymatis連線資料庫異常AIExceptionJava資料庫
- 資料庫升級之-資料泵資料庫
- MySQLNonTransientConnectionException資料庫連線異常MySqlException資料庫
- Java應用異常狀態監測Java
- TCP連線狀態異常記錄TCP
- 資料庫升級之-XTTS資料庫TTS
- 資料庫升級和工具資料庫
- android資料庫如何進行版本升級？架構之資料庫框架升級Android資料庫架構框架
- SQL Server資料庫恢復常見問題SQLServer資料庫
- 資料庫面試時常見的26個問題資料庫面試
- openguass 資料庫狀態查詢資料庫
- 達夢8資料庫的狀態模式資料庫模式
- 例項演示oracle資料塊狀態檢視v$bh的用法一 獲取oracle物件所佔用的資料塊Oracle物件
- 靜默方式安裝、升級oracle(三): 升級資料庫軟體及資料庫Oracle資料庫
- 最全weblogic升級與遷移改造常見問題Web
- 微服務異常問題微服務
- 異常問題排查之旅
- iOS12測試版升級常見問題 iOS12值得升級嗎？iOS
- 第50問：從連線判斷應用訪問資料庫的異常行為資料庫
- Oracle DG資料庫狀態轉換Oracle資料庫
- 伺服器異常狀態怎麼解決伺服器
- Spring系列之不同資料庫異常如何抽象的？Spring資料庫抽象
- [20210722]資料庫異常關閉的處理.txt資料庫
- 資料庫連線異常處理思路資料庫
- 資料庫異常智慧分析與診斷資料庫
- 資料庫連線異常故障報告資料庫
- 專案升級到.Net8.0 Autofac引發詭異的問題
- Python異常 ValueError的問題詳解PythonError
- .net異常處理的效能問題