資料庫升級造成的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資料庫
- 同事處理異常斷電資料庫狀態變為SUSPECT過程資料庫
- rac 升級crs 升級資料庫軟體，升級資料庫資料庫
- 資料庫升級到mysql5.6遇到的資料表ENGINE問題資料庫MySql
- 資料庫常見問題資料庫
- Oracle資料庫異機升級（10.2.0.5 --> 11.2.0.4）Oracle資料庫
- oracle9207 rac 升級到9208異常問題Oracle
- 資料庫升級資料庫
- ♀♀資料庫升級♀♀資料庫
- 資料升級到5.1所遇到的問題
- 資料庫11g升級中一次奇怪的問題資料庫
- 資料庫連線沒有釋放造成的奇怪問題資料庫
- MyBatis版本升級導致OffsetDateTime入參解析異常問題覆盤MyBatis
- oracle資料庫狀態Oracle資料庫
- synchronized四種鎖狀態的升級synchronized
- 網路問題或客戶端異常可能導致資料庫會話處於僵死狀態客戶端資料庫會話
- Oracle 資料庫升級Oracle資料庫
- 10個常見的資料庫安全問題資料庫
- 資料庫升級之-Dataguard滾動升級資料庫
- TCP連線狀態異常記錄TCP
- Java應用異常狀態監測Java
- 資料庫升級之-資料泵資料庫
- Oracle資料庫的靜默狀態和掛起狀態Oracle資料庫
- 資料庫訂單狀態資料庫
- 資料庫升級和工具資料庫
- 資料庫升級之-XTTS資料庫TTS
- 資料庫升級報錯資料庫
- 五個常見 PHP 資料庫問題PHP資料庫
- 資料庫啟動的不同狀態資料庫
- 檢視映象資料庫的狀態資料庫
- TR2021_0000偶發資料庫連線異常問題排查資料庫
- 資料庫的升級及遷移資料庫
- 資料庫面試時常見的26個問題資料庫面試
- 屬性問題造成ATO料號銷貨後訂單LINE狀態不變的處理
- Android版本升級同時Sqlite資料庫的升級及之前資料的保留AndroidSQLite資料庫
- oracle資料庫升級11.2.0.3升級到11.2.0.4Oracle資料庫