ORA-08102&ORA-00701
某客户的数据库告警日志中不断提示：
Thu Jul 02 10:26:36 2020
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
Thu Jul 02 10:26:53 2020
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
Thu Jul 02 10:27:12 2020
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_j001_106746.trc:
可以看出是个scheduler job出错。查看trace， 可以看到有ORA-08102错误
2020-07-02 10:26:44.189755*:800C254E:sql_mon_query:keswx.c@3681:keswxWriteEndInfoToStream(): done writing error info: code=8102 fac=ORA msg=ORA-08102: δՒµ½˷ҽ¹ؼüז, ¶ԏ423, ΄¼þ 1, ¿頱71704 (2)
msglen=63
*** 2020-07-02 10:25:15.995
*** SESSION ID:(2131.5) 2020-07-02 10:25:15.995
*** CLIENT ID:() 2020-07-02 10:25:15.995
*** SERVICE NAME:(SYS$USERS) 2020-07-02 10:25:15.995
*** MODULE NAME:() 2020-07-02 10:25:15.995
*** ACTION NAME:() 2020-07-02 10:25:15.995
oer 8102.2 - obj# 423, rdba: 0x0042a50f(afn 1, blk# 173327)
kdk key 8102.2:
ncol: 3, len: 15
key: (15): 04 c3 08 50 4e 02 c1 09 06 00 42 a1 fc 00 03
mask: (4096):
查询所属对象：
SQL> col object_name format a40
SQL> set linesize 800
SQL> select object_type, object_name, owner from dba_objects where object_id = 423;
OBJECT_TYPE OBJECT_NAME OWNER
------------------- ---------------------------------------- ------------------------------
INDEX I_H_OBJ#_COL# SYS
SQL>
SQL> select table_name from dba_indexes where index_name = 'I_H_OBJ#_COL#';
TABLE_NAME
------------------------------
HISTGRM$
查看数据， 发现索引与数据不一致：
SQL> select count(*) from HISTGRM$;
COUNT(*)
----------
192329
SQL> select /*+full(a) */ count(*) from HISTGRM$ a;
COUNT(*)
----------
192194
通过索引检索多了数据，很多index得key在表中没有。
尝试rebuild, 报错：
SQL> alter index I_H_OBJ#_COL# rebuild online;
alter index I_H_OBJ#_COL# rebuild online
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
将数据库启动到start migrate，再进行rebuild
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 3187673960 bytes
Database Buffers 1.3892E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;
Index altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size 2270360 bytes
Variable Size 3187673960 bytes
Database Buffers 1.3892E+10 bytes
Redo Buffers 21684224 bytes
Database mounted.
Database opened.
SQL> select count(*) from HISTGRM$;
COUNT(*)
----------
192194
SQL> select /*+full(a) */ count(*) from HISTGRM$ a;
COUNT(*)
----------
192194
后续告警日志中类似错误再未出现。
参考： https://www.eygle.com/archives/2007/02/ora_00701_warmstarting.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8520577/viewspace-2701886/ ，如需转载，请注明出处，否则将追究法律责任。
