数据库升级造成的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
}SM-xp0  2  WHERE WNER = 'SYS'ITPUB个人空间M(q*w9B dK6[ w V
  3  AND STATUS = 'INVALID';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
y\!y/]^m
frn Y0------------------------------ ------------------------------ -------------------ITPUB个人空间?fP2KZ
SYS                            X_$BH                          VIEWITPUB个人空间e9g$]-b%C'aY-{D
SYS                            X_$KCBWDS                      VIEW

创始直接编译对象：

SQL> ALTER VIEW X_$BH COMPILE;

警告:更改的视图带有编译错误。

SQL> SHOW ERRITPUB个人空间 C[Thuu t
没有错误。

也没有错误信息，检查一下视图的定义，并对比X$BH表结构，发现了异常：

SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'X_$BH';

TEXTITPUB个人空间f~5}2HEf&p
--------------------------------------------------------------------------------ITPUB个人空间\NW"``
SELECT "ADDR","INDX","INST_ID","HLADDR","BLSIZ","NXT_HASH","PRV_HASH","NXT_REPL"ITPUB个人空间N3y_5]~]@2Hp$F
,"PRV_REPL","FLAG","LRU_FLAG","TS#","FILE#","DBARFIL","DBABLK","CLASS","STATE","
|T(h~2L,e`0MODE_HELD","CHANGES","CSTATE","X_TO_NULL","FORCED_READS","FORCED_WRITES","LE_ADD
}K@1qe(y0R","DIRTY_QUEUE","SET_DS","OBJ","BA","CR_SCN_BAS","CR_SCN_WRP","CR_XID_USN","CR_
PfnmI;S;l3w
{0XID_SLT","CR_XID_SQN","CR_UBA_FIL","CR_UBA_BLK","CR_UBA_SEQ","CR_UBA_REC","CR_SFITPUB个人空间dG3e9i"x
L","LRBA_SEQ","LRBA_BNO","HSCN_BAS","HSCN_WRP","HSUB_SCN","RRBA_SEQ","RRBA_BNO",
(E n&Q0vl6s0"US_NXT","US_PRV","WA_NXT","WA_PRV","TCH","TIM" FROM X$BH

ITPUB个人空间+B-W;|!gT[
SQL> DESC X$BHITPUB个人空间p6N8lz@+z
 名称                         是否为空?类型
/|dj5b)E7L!S0 ----------------------------- -------- -----------ITPUB个人空间j3l`6bW$a
 ADDR                                   RAW(8)ITPUB个人空间PT
{3b,vd4v
 INDX                                   NUMBERITPUB个人空间7Vn Z"ju)`
 INST_ID                                NUMBERITPUB个人空间ZdYS{.W+h1YK
 HLADDR                                 RAW(8)
YuES(A:s+v5P0 BLSIZ                                  NUMBER
3j.o diP)~0 NXT_HASH                               RAW(8)
Y7j!H2Rf `N4Do"M0 PRV_HASH                               RAW(8)
}'Vz\(K9VR0 NXT_REPL                               RAW(8)
*g$M3bU _E&{k0 PRV_REPL                               RAW(8)ITPUB个人空间+rIVJ!['DN
 FLAG                                   NUMBER
UisiI e)US0 RFLAG                                  NUMBERITPUB个人空间#hzg
X)b!~
l _
iH
 SFLAG                                  NUMBERITPUB个人空间'@t,`]bb+U
 LRU_FLAG                               NUMBER
h"C},MR#G"ak0 TS#                                    NUMBER
p;H3h^h;tz0Pw0 FILE#                                  NUMBER
,hh&M-wL(kt0 DBARFIL                                NUMBER
p~|gX$y1B,do.l&vm0 DBABLK                                 NUMBERITPUB个人空间Vgg2b*iFz!S3l
 CLASS                                  NUMBERITPUB个人空间y^2zmL C5T
 STATE                                  NUMBERITPUB个人空间7Z8[ g9J3s y3c,eL
 MODE_HELD                              NUMBERITPUB个人空间D\m3Kb/V
 CHANGES                                NUMBERITPUB个人空间b:\,{
W4R_ik
 CSTATE                                 NUMBER
"jHJ @8_0 LE_ADDR                                RAW(8)
k&j,K:He'}V5T6Y s0 DIRTY_QUEUE                            NUMBER
Bi;[2`(V&ODt!T0 SET_DS                                 RAW(8)ITPUB个人空间kS9e[)jk"fq_
 OBJ                                    NUMBERITPUB个人空间w'p GE6@~
 BA                                     RAW(8)
;R:~x0AA7O/wa Dn WW0 CR_SCN_BAS                             NUMBERITPUB个人空间ec*cT"u
 CR_SCN_WRP                             NUMBERITPUB个人空间 [%uuX0n
N0^ |Dy
D
 CR_XID_USN                             NUMBER
dOs
Xs-r,A0 CR_XID_SLT                             NUMBERITPUB个人空间![jG8|/U
 CR_XID_SQN                             NUMBERITPUB个人空间6}1g.g~'I,zd@
 CR_UBA_FIL                             NUMBER
q+` L5mC*~Z$bq0C'S-}([H0 CR_UBA_BLK                             NUMBERITPUB个人空间-h,qI+?x6T
 CR_UBA_SEQ                             NUMBERITPUB个人空间#K&x6lGik!Q
 CR_UBA_REC                             NUMBER
t6Af#HZj:r;JC0 CR_SFL                                 NUMBERITPUB个人空间a|il3q
 CR_CLS_BAS                             NUMBER
wG9f$XA0FIbJ0 CR_CLS_WRP                             NUMBER
'HX}i+N0 LRBA_SEQ                               NUMBER
tHH\rG0 LRBA_BNO                               NUMBERITPUB个人空间ZgRz:Jd,l
 HSCN_BAS                               NUMBERITPUB个人空间.u"SK0J0EHnF
 HSCN_WRP                               NUMBER
Zl+j%m"up0 HSUB_SCN                               NUMBER
G
l,x1C[f3ARl0 US_NXT                                 RAW(8)ITPUB个人空间(]+R Yh^2L N [
 US_PRV                                 RAW(8)
D`zfOB8dW.X-a0 WA_NXT                                 RAW(8)
.Mj'?&u
l.FW0 WA_PRV                                 RAW(8)ITPUB个人空间M4iNHL}
 OBJ_FLAG                               NUMBERITPUB个人空间8]/X1g7p)Tg^!Y^4}q,uy
 TCH                                    NUMBER
u-GL$Z{T(|'w{'_0 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.

ITPUB个人空间3z.bRi_:N
连接到:ITPUB个人空间/_K})@*Nr P
Oracle Database10gEnterprise Edition Release 10.2.0.3.0 - 64bit Production
O%M2BGdt,\&T0With the Partitioning, OLAP and Data Mining options

SQL> SHUTDOWN IMMEDIATEITPUB个人空间F\Q$J y{E
数据库已经关闭。ITPUB个人空间'GL"x)X D
已经卸载数据库。
j
l:FJ2E e9H!k0ORACLE例程已经关闭。ITPUB个人空间 mQ2m.Z*gK;m9I
SQL> STARTUP RESTRICT 
|!T^V6ruG v"zh5w+H|0ORACLE例程已经启动。

Total System Global Area 5083496448 bytes
yV_(N9{uH0Fixed Size                  2079736 bytes
3u2Wp Z#\0Variable Size             872416264 bytesITPUB个人空间]5_1u5v{j
Database Buffers         4194304000 bytesITPUB个人空间#e$J({:F q#WJ6uF%M0c6H
P*\
Redo Buffers               14696448 bytesITPUB个人空间)Pd+WPM(c5c aM1I
数据库装载完毕。
5YP+X%XW6J0]@0数据库已经打开。
hnV2` ~4r0SQL> DELETE FROM DEPENDENCY$
7?9JM:J,N"y FQr0  2  WHERE D_OBJ# IN
6x ~6s0DB$@0  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 IMMEDIATEITPUB个人空间)x9L(~6~7U|;qh
数据库已经关闭。ITPUB个人空间D#Eq _Ew8y
已经卸载数据库。ITPUB个人空间0st5|0B I)J
ORACLE例程已经关闭。
Gdxt0UQ~B5o0SQL> STARTUPITPUB个人空间&nAKWt
ORACLE例程已经启动。

Total System Global Area 5083496448 bytes
T&k3T0D!H'y N%o0Fixed Size                  2079736 bytes
!E-q#p9L9Bs {0Variable Size             872416264 bytes
2rb Y&[.{0Database Buffers         4194304000 bytes
@K_)M wb0Redo Buffers               14696448 bytesITPUB个人空间%j C:d7if!z*I
数据库装载完毕。
)L8r`6p;R5b0数据库已经打开。

Oracle的metalink文档至此就结果了，重启后发现问题确实已经“解决”：

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS
'\"GfsuX(J]3p0  2  WHERE WNER = 'SYS'ITPUB个人空间3@$a)AC"bs7wM4ub
  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(*)ITPUB个人空间X#H)b[M"r:q
----------ITPUB个人空间;CCa z eG
      3035

SQL> SELECT COUNT(*) FROM SYS.X_$BH;
2Jl%T4~P^[a,}^0SELECT COUNT(*) FROM SYS.X_$BHITPUB个人空间bsKX3I~
                         *ITPUB个人空间2B }C%b!j#`(?@
第1行出现错误:
6hKv6~o{5~q0ORA-00942:表或视图不存在

+Tzv.c[ jXrc0SQL> CONN NDMAINITPUB个人空间v~|:{
Z\.D
输入口令:
$l%f }k6a}0已连接。
JJ#CsC0SQL> SELECT COUNT(*) FROM X$BH;
S9E&as_6_(HO0SELECT COUNT(*) FROM X$BHITPUB个人空间["rz{8s/h
                     *ITPUB个人空间\%a.Kjh7V/dD ]
第1行出现错误:ITPUB个人空间+O2z XD,Y/Bg-m
ORA-00980:同义词转换不再有效

稳妥起见，还是手工重建这两个删除掉的视图：

SQL> CONN / AS SYSDBAITPUB个人空间RwV1f&j9g@kh
已连接。ITPUB个人空间*[/{[i`9P-dL
SQL> CREATE OR REPLACE FORCE VIEWITPUB个人空间H"[wG+x3k0C
  2  "SYS"."X_$BH" ("ADDR", "INDX", "INST_ID", "HLADDR", "BLSIZ", "NXT_HASH", "PRV_HASH",ITPUB个人空间2OD.d8g"m1Pm)V^
  3  "NXT_REPL", "PRV_REPL", "FLAG", "RFLAG", "SFLAG", "LRU_FLAG", "TS#", "FILE#",ITPUB个人空间 |gM${%]Mi?
  4  "DBARFIL", "DBABLK", "CLASS", "STATE", "MODE_HELD", "CHANGES", "CSTATE", "LE_ADDR",
W0Tx?2^!W'U6P0  5  "DIRTY_QUEUE", "SET_DS", "OBJ", "BA", "CR_SCN_BAS", "CR_SCN_WRP", "CR_XID_USN",
)A
O1p'Q.ru*r5M^DFn0  6  "CR_XID_SLT", "CR_XID_SQN", "CR_UBA_FIL", "CR_UBA_BLK", "CR_UBA_SEQ", "CR_UBA_REC",ITPUB个人空间$z1~YH[e,OUg
  7  "CR_SFL", "CR_CLS_BAS", "CR_CLS_WRP", "LRBA_SEQ", "LRBA_BNO", "HSCN_BAS", "HSCN_WRP",ITPUB个人空间BA,s;G
C:[;U
  8  "HSUB_SCN", "US_NXT", "US_PRV", "WA_NXT", "WA_PRV", "OBJ_FLAG", "TCH","TIM")ITPUB个人空间Q+GY]zC"nZW
  9  AS SELECT "ADDR","INDX","INST_ID","HLADDR","BLSIZ","NXT_HASH","PRV_HASH","NXT_REPL",
{
dz!Y4i ^0 10  "PRV_REPL","FLAG","RFLAG","SFLAG","LRU_FLAG","TS#","FILE#","DBARFIL","DBABLK","CLASS",
$I @#pD0qF0 11  "STATE","MODE_HELD","CHANGES","CSTATE","LE_ADDR","DIRTY_QUEUE","SET_DS","OBJ","BA",ITPUB个人空间*v!|,{%Jg;w:\Es
 12  "CR_SCN_BAS","CR_SCN_WRP","CR_XID_USN","CR_XID_SLT","CR_XID_SQN","CR_UBA_FIL",ITPUB个人空间,A T9s7G7u6X^.A
 13  "CR_UBA_BLK","CR_UBA_SEQ","CR_UBA_REC","CR_SFL","CR_CLS_BAS","CR_CLS_WRP","LRBA_SEQ",ITPUB个人空间A o8k M'h7Tk'|$L2R8UE
 14  "LRBA_BNO","HSCN_BAS","HSCN_WRP","HSUB_SCN","US_NXT","US_PRV","WA_NXT","WA_PRV",
C[-TE;x6Uyn3u{0 15  "OBJ_FLAG","TCH","TIM" FROM X$BHITPUB个人空间L+?G%dLd5eT
 16  ;

视图已创建。

SQL> CREATE OR REPLACE FORCE VIEW "SYS"."X_$KCBWDS"
Ca.t,b,^ I3K0  2  ("ADDR", "INDX", "INST_ID", "SET_ID", "DBWR_NUM", "BLK_SIZE", "PROC_GROUP",
,{tv4E%j2Ji0  3  "CNUM_SET", "FLAG", "CKPT_LATCH", "CKPT_LATCH1", "SET_LATCH", "NXT_REPL",
R-w I4vLI5]-r0  4  "PRV_REPL", "NXT_REPLAX", "PRV_REPLAX", "CNUM_REPL", "ANUM_REPL", "COLD_HD",
Tr,k6bG'\9^y9T0  5  "HBMAX", "HBUFS", "NXT_WRITE", "PRV_WRITE", "NXT_WRITEAX", "PRV_WRITEAX",ITPUB个人空间|^f.q6[a7_^n1yz
  6  "CNUM_WRITE", "ANUM_WRITE", "NXT_XOBJ", "PRV_XOBJ", "NXT_XOBJAX", "PRV_XOBJAX",ITPUB个人空间&?0~:[8s,aP4mL@9v
  7  "CNUM_XOBJ", "ANUM_XOBJ", "NXT_XRNG", "PRV_XRNG", "NXT_XRNGAX", "PRV_XRNGAX",ITPUB个人空间0{+^r4v/uf~
  8  "CNUM_XRNG", "ANUM_XRNG", "NXT_REQ", "PRV_REQ", "NXT_REQAX", "PRV_REQAX",
qB[7hfC+h0  9  "CNUM_REQ", "ANUM_REQ", "BUF_GOT", "SUM_WRT", "SUM_SCN", "FBWAIT", "WCWAIT",ITPUB个人空间aT"l%xm,G)d)A'[+e-i
 10  "BBWAIT", "FBINSP","DBINSP", "PNINSP", "HOTMVS", "DBBCHG", "DBBGET", "CONGET",
m&U+D E D\4H$o5w0 11  "PREAD", "PWRITE", "FGSDEPTH", "TGTCLEAN", "PWBCNT", "PROTCNT")
;B.Y E#QV9aA0 12  AS SELECT "ADDR","INDX","INST_ID","SET_ID","DBWR_NUM","BLK_SIZE","PROC_GROUP",ITPUB个人空间d
Z W"V#l,JQ
 13  "CNUM_SET","FLAG","CKPT_LATCH","CKPT_LATCH1","SET_LATCH","NXT_REPL","PRV_REPL",ITPUB个人空间/]1|5U
_Y%N'G
 14  "NXT_REPLAX","PRV_REPLAX","CNUM_REPL","ANUM_REPL","COLD_HD","HBMAX","HBUFS",ITPUB个人空间H1E _(z%v)H/u
 15  "NXT_WRITE","PRV_WRITE","NXT_WRITEAX","PRV_WRITEAX","CNUM_WRITE","ANUM_WRITE",ITPUB个人空间]UX,AZr^W{1z nxL
 16  "NXT_XOBJ","PRV_XOBJ","NXT_XOBJAX","PRV_XOBJAX","CNUM_XOBJ","ANUM_XOBJ","NXT_XRNG",
"zF
O4r.m
{0 17  "PRV_XRNG","NXT_XRNGAX","PRV_XRNGAX","CNUM_XRNG","ANUM_XRNG","NXT_REQ","PRV_REQ",
F.L$_A.d]6Z0 18  "NXT_REQAX","PRV_REQAX","CNUM_REQ","ANUM_REQ","BUF_GOT","SUM_WRT","SUM_SCN",
4ru9\q {/qQQ"x0 19  "FBWAIT","WCWAIT","BBWAIT","FBINSP","DBINSP","PNINSP","HOTMVS","DBBCHG","DBBGET",
?
Gi$~%dP5P4V0 20  "CONGET","PREAD","PWRITE","FGSDEPTH","TGTCLEAN","PWBCNT","PROTCNT" FROM X$KCBWDS;

视图已创建。

至此问题解决。再次强调，不要尝试对产品系统进行类似的操作，且进行这种操作前应先对数据库进行备份。