同事对一个测试库执行了升级操作，将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 OWNER = '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 OWNER = '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 OWNER = '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;

视图已创建。

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

升级完成后，检查失效对象的时候发现的。
同事升级后没有检查数据库的对象，等到打完补丁才发现。

之所以没有列出来DOC ID，是因为那篇文档似乎是INTERNAL的东西。
btw，这6条记录似乎在其他版本中也看到了，应该是这两个视图本身的依赖信息。

强。还没有直接升级过