诗檀软件专业数据库修复团队
主要内容
应用于
产品:
要收集什么以及为什么？
SRDC 要收集分析ORA-08102所报告的块损坏所需的信息。
操作计划
- 确定受影响的索引
该错误以及ORA-08102 的追踪文件为受影响的索引提供了obj# (object_id) 。
SQL*Plus 会话收到该错误的例子：
SQL> DELETE dept WHERE deptno=10;
DELETE dept WHERE deptno=10
*
ERROR at line 1:
ORA-08102: index key not found, obj# 46115, file 5, block 90 (2)
追踪文件:
oer 8102.2 - obj# 46115, rdba: 0x02c0005a(afn 5, blk# 90)
kdk key 8102.2:
ncol: 3, len: 16
key: (16):
06 c5 02 01 01 27 02 04 c3 02 32 33 06 02 c0 00 4a 00 05
运行下一个查询，确认索引；该例中obj_number 是 46115：
select *
from dba_objects
where object_id = &obj_number;
- 在基表上执行ANALYZE VALIDATE STRUCTURE CASCADE
确认受影响索引的基表，并在基表上运行分析VALIDATE STRUCTURE CASCADE:
ANALYZE TABLE <table name> VALIDATE STRUCTURE CASCADE ONLINE;
如果是分区表，则会生成错误ORA-14508;那么请按照Note 111990.1操作。
如果ANALYZE 生成了错误 ORA-01499, 请上传跟踪文件。注意跟踪文件不会有错误ORA-01499，但可能会有一下信息：
"row not found in index"
"Table/Index row count mismatch"
"row mismatch in index dba"
"Table row count/Bitmap index bit count mismatch"
"kdavls: kdcchk returns %d when checking cluster dba 0x%08lx objn %d\n"
- 确定所有受影响的关键值
选择性确定所有受影响的关键值，可以用索引扫描执行全表扫描:
表中不属于索引的行：
spool tablenotindex.log
SELECT /*+ FULL(t1) */ rowid, <indexed column list>
FROM <Table name> t1
MINUS
SELECT /*+ index(t <Index name>) */ rowid, <indexed column list>
FROM <Table name> t;
spool off
例:
Table name = DEPT, Index name = I_DEPT1, Indexed columns in index I_DEPT1 are: DEPTNO, DNAME.
spool tablenotindex.log
SELECT /*+ FULL(t1) */ rowid, deptno, dname
FROM dept t1
MINUS
SELECT /*+ index(t I_DEPT1) */ rowid, deptno, dname
FROM dept t;
spool off
确保用于查询的执行计划使用受影响的索引; 例如索引I_DEPT1在执行计划中有显示。查询不使用索引的一个原因是该列被定义为允许空值，这样的话就添加一个WHERE子句，例如：
where deptno is not null。
- 上传跟踪文件，警报日志，OS日志文件和历史纪录
请上传:
- The trace file for the ORA-08102 error.
- The trace file for the ORA-01499 (Only if ANALYZE produced ORA-01499 error)
- Alert.log file from all instances and incident trace files related to the error:
Use TFA Collector as described in Note:1676101.1 as that will collect all required files
- The output of query executed in step 1 to identify the INDEX.
- Upload the DDL statement of the Affected Objects. An export of the base table with rows=N may be useful as it contains table/index definition (see above example of exp with row=N).
- File tablenotindex.log created in step 2 above
- Provide the description of how the index was last created. eg was it rebuild with the ONLINE option?. Was the PARALLEL clause used?, etc.
- Information about how the records are inserted in the Base Table. eg. Is DIRECT Load applied in the base table? Is the MERGE statement used?, Is Parallel DML used?, etc
- Get the timestamp of last time when the INDEX was rebuilt/created before reporting the ORA-01499; look for dba_objects.LAST_DDL_TIME on the INDEX.
- Thorough history of events that led to the corruption; find out if there were any noticeable changes to the environment such as hardware changes, OS upgrades/patches, recent File System configuration (addition of new disks, etc), new application code, new init.ora parameters, Oracle patches, Oracle upgrade, and OS upgrade.
- 保留数据库和存档日志备份
保留其他信息；以防进一步调查需要:
- Keep a Database Backup from when the error was produced.
- Keep at least 6 hours of Archived Log Files from before the error started until the error was reported.