One of the index table FND_CONCURRENT_REQUESTS_N9’s block is corrupted.
Causes of issue:
Abnormal server down
Steps to solve this issue:
1.Find table name of corrupted block
SELECT k.owner, k.segment_type, k.segment_name, k.partition_name, v.file#
, greatest(k.block_id, v.block#) corr_start_block#
, least(k.block_id+k.blocks-1, v.block#+v.blocks-1) corr_end_block#
, least(k.block_id+k.blocks-1, v.block#+v.blocks-1)
– greatest(k.block_id, v.block#) + 1 blocks_corrupted
, null description
FROM dba_extents k, v$database_block_corruption v
WHERE k.file_id = v.file#
AND k.block_id <= v.block# + v.blocks – 1
AND k.block_id + k.blocks – 1 >= v.block#
UNION
SELECT h.owner, h.segment_type, h.segment_name, h.partition_name, v.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, ‘Segment Header’ description
FROM dba_segments h, v$database_block_corruption v
WHERE h.header_file = v.file#
AND h.header_block between v.block# and v.block# + v.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, v.file#
, greatest(l.block_id, v.block#) corr_start_block#
, least(l.block_id+l.blocks-1, v.block#+v.blocks-1) corr_end_block#
, least(l.block_id+l.blocks-1, v.block#+v.blocks-1)
– greatest(l.block_id, v.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space l, v$database_block_corruption v
WHERE l.file_id = v.file#
AND l.block_id <= v.block# + v.blocks – 1
AND l.block_id + l.blocks – 1 >= v.block#
ORDER BY file#, corr_start_block#;
2.Validate the corrupted index table using below command
Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N9 validate structure online;
3.Get ddl of corrupted index table and save the result using below command.
Select dbms_metadata.get_ddl(‘INDEX’,’FND_CONCURRENT_REQUESTS_N9′,’APP SYS’) from dual;
ddl result:
CREATE INDEX “APPLSYS”.”FND_CONCURRENT_REQUESTS_N9″ ON “APPLSYS”.”FND_CONCURRENT_REQUESTS” (“REQUESTED_BY”, “ACTUAL_COMPLETION_DATE”)
PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) TABLESPACE “APPS_TS_TX_IDX”
4.Drop the corrupted index table and commit.
drop index APPLSYS.FND_CONCURRENT_REQUESTS_N9;
commit ;
5.Rebuild index table by running below command.
CREATE INDEX “APPLSYS”.”FND_CONCURRENT_REQUESTS_N9″ ON FND_CONCURRENT_REQUESTS” (“REQUESTED_BY”, “ACTUAL_COMPLETION_DATE”) PCTFREE 0 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT) TABLESPACE “APPS_TS_TX_IDX”
6.Finally Validate the index table
Analyze Index APPLSYS.FND_CONCURRENT_REQUESTS_N9 validate structure online;
