ORA-08102所需的诊断数据收集

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

主要内容

应用于

产品:

  • 10.1 至 12.1版的Oracle 数据库

要收集什么以及为什么?

SRDC 要收集分析ORA-08102所报告的块损坏所需的信息。

 

操作计划

  1. 确定受影响的索引

该错误以及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;

 

  1. 在基表上执行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”

 

  1. 确定所有受影响的关键值

选择性确定所有受影响的关键值,可以用索引扫描执行全表扫描:

表中不属于索引的行:

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

 

  1. 上传跟踪文件,警报日志,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.

 

  1. 保留数据库和存档日志备份

保留其他信息;以防进一步调查需要:

  • 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.

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569