如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
主要内容
应用于
产品:
- 10.1 至 12.1版的Oracle 数据库
要收集什么以及为什么？
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.
Comment