Troubleshooting ora-01499 & ora-08103 block corrupted
ORA-1499: MOS
ORA-1499 is produced by statement “ANALIZE TABLE|CLUSTER VALIDATE STRUCTURE CASCADE” to report an inconsistency between a table or a cluster and its index where an index key value is not found in the index or vice versa.
Trace file keyword:
“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”
tsn: Tablespace Number where the INDEX is stored.
rdba: Relative data block address of the INDEX segment header.
e.g.
Table/Index row count mismatch
table 8152 : index 9571, 1407
Index root = tsn: 1 rdba: 0x0080750e
–OR
row not found in index tsn: 42 rdba: 0xce94ecba
TO identify index
SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# = dbms_utility.data_block_address_file(to_number('&rdba','XXXXXXXX'))
AND ts#= &tsn)
AND header_block = dbms_utility.data_block_address_block(to_number('&rdba','XXXXXXXX'));
Solution:
1, Drop index segment currpted, recreate index or index partition (not rebuild)
2, Recreating the table via export/import and then creating the indexes
# for local partition index
ALTER TABLE . MODIFY PARTITION UNUSABLE LOCAL INDEXES; ALTER TABLE . MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES;
analyze many indexes for table
drop table analyze_log purge;
create table analyze_log(atime date,owner varchar2(50),table_name varchar2(50), index_name varchar2(50),flag number(1));
alter session set db_file_multiblock_read_count=512;
alter session set events '10231 trace name context forever, level 10';
set serveroutput on
-- eg
exec analyze_quick('anbob','tt');
select * from analyze_log;
create or replace procedure analyze_quick(owner_table varchar2, name_table varchar2, name_index varchar2 default null)
is
--
--
-- write log to table analyze_log, flag(1: index need to rebuild; 0: ignore)
--
s varchar2(30000);
v_sql varchar2(4000);
num_indexes number := 0;
sum_hash number;
begin
for i in (select a.owner, a.index_name, b.column_name
from dba_indexes a, dba_ind_columns b
where a.table_owner = upper(owner_table)
and a.table_name = upper(name_table)
and (a.index_name = upper(name_index) or name_index is null)
and a.index_type not in ('IOT - TOP'
,'LOB'
,'FUNCTION-BASED NORMAL'
,'FUNCTION-BASED DOMAIN'
,'CLUSTER')
and a.owner = b.index_owner
and a.index_name = b.index_name
and a.table_name = b.table_name
and b.column_position = 1) loop
num_indexes := num_indexes+1;
s := 'select /*+ full(t1) parallel(t1 32) */ sum(ora_hash(rowid)) from ';
s := s || owner_table || '.' || name_table || ' t1 where ' || i.column_name ||' is not null MINUS ';
s := s || 'select /*+ index_ffs(t '|| i.index_name||') */ sum(ora_hash(rowid)) from ';
s := s || owner_table || '.' || name_table || ' t where ' || i.column_name ||' is not null';
begin
-- index begin to analyze
v_sql:='insert into analyze_log values(sysdate,:town,:tnm,:inm,:flg)';
execute immediate v_sql using owner_table,name_table,i.index_name,0;
commit;
execute immediate s into sum_hash;
dbms_output.put_line('TABLE/INDEX beging to analyze... Table: '
||upper(owner_table)||'.'||upper(name_table)
||' Index: '
||upper(i.index_name));
if sum_hash > 0 then
-- raise_application_error(-20220,'TABLE/INDEX MISMATCH detected!! Table: '
-- ||upper(owner_table)||'.'||upper(name_table)
-- ||' Index: '
-- ||upper(i.index_name));
dbms_output.put_line('TABLE/INDEX MISMATCH detected!! Table: '
||upper(owner_table)||'.'||upper(name_table)
||' Index: '
||upper(i.index_name));
-- index inconsistent
execute immediate v_sql using owner_table,name_table,i.index_name,1;
commit;
end if;
exception
when no_data_found then null; -- no_data_found means that there is not inconsistency
end;
end loop;
if num_indexes = 0 and name_index is not null then
raise_application_error(-20221,'Check was not executed. Index '||upper(name_index)||' does not exist for table '||upper(name_table)|| ' or table does not exist');
elsif num_indexes = 0 then
raise_application_error(-20222,'Check was not executed. No INDEXES with index_type=NORMAL found for table '||upper(name_table)|| ' or table does not exist');
end if;
end;
/
ORA-8103 MOS
ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type=6) was expected but the actual block information is not a data block (Type!=6).
ORA-8103 is also caused by an unexpected data_object_id where it is changing for the involved objects while the affected SQL statement is executed.
analyze method:
alter session set max_dump_file_size=unlimited; alter session set db_file_multiblock_read_count=1; alter session set events '10200 trace name context forever, level 1'; alter session set events '8103 trace name errorstack level 3'; alter session set events '10236 trace name context forever, level 1'; alter session set tracefile_identifier='ORA8103'; -- OR -- alter system set events '8103 trace name errorstack level 3'; -- check db alert log file
Trace file keyworld:
“OBJD MISMATCH”
“dump suspect buffer”
“KTRVAC”
“Consistent read started for block ”
“but could not be dumped”
“ORA-8103”
Find errors
sed -n '/Aug 17/,$p' alert*.log |grep -B 1 "ORA-08103" |egrep '^Errors in file'|awk '{print $4}'|sed 's/://'|while read LINE ; do echo $LINE; grep "ORA-08103" -A 2 $LINE; done;
case 1
*** SESSION ID:(647.425) 2016-10-26 14:47:36.789 OBJD MISMATCH typ=6, seg.obj=1697516001, diskobj=2744649, dsflg=0, dsobj=2713804, tid=2713804, cls=8 *** 2016-10-26 14:47:36.831 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-08103: object no longer exists ----- Current SQL Statement for this session (sql_id=gvxmvbcwdh2qv) ----- select /*+ full(t1) parallel(t1 32) */ sum(ora_hash(rowid)) from ANBOB.HIST201609 t1 where B_HOMEZIP is not null MINUS select /*+ index_ffs(t HIST201609_B_HOMEZIP) */ sum(ora_hash(rowid)) from ANBOB.HIST201609 t where B_HOMEZIP is not null ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 70000315c01b9c0 41 procedure SYS.ANALYZE_QUICK 70000319c294830 1 anonymous block ... The buffer with tsn: 33 rdba: 0x54766d01 was pinned, but could not be dumped
TIP:
Collection google out and found that the problem is:
diskobj object data object ID
dsobj is the object id
From the above we can tell the following:
On disk DATA_OBJECT_ID (diskobj) = 2744649
In memory buffer DATA_OBJECT_ID (dsobj) = 2713804
select object_id, data_object_id, owner, object_name,subobject_name, object_type from dba_objects where data_object_id in (2744649, 2713804) or object_id in (2744649, 2713804,1697516001); OBJECT_ID DATA_OBJECT_ID OWNER OBJECT_TYPE SUBOBJECT_NAME OBJECT_NAME --------- -------------- ------- ---------- -------------- ----------- 2713804 2713804 ANBOB TAB PARTITION P20160922_23 HIST201609 2665067 2744649 ANBOB IND PARTITION P20160825_13 HIST201608_LACCI select count(*) from ANBOB.HIST201609 partition(P20160922_23); faild ora-8103
extract table data base rowid:
REM Create a new table based on the table that is producing errors with no rows:
create table ANBOB.HIST201609_new
as
select *
from ANBOB.HIST201609 partition(P20160923_00)
where 1=2;
REM Create the table to keep track of ROWIDs pointing to affected rows:
create table bad_rows (row_id rowid
,oracle_error_code number);
set serveroutput on
DECLARE
TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
CURSOR c1 IS select /*+ index(tab1) */ rowid
from ANBOB.HIST201609 partition(P20160922_23) tab1
where IMEI is NOT NULL;
r RowIDTab;
rows NATURAL := 20000;
bad_rows number := 0 ;
errors number;
error_code number;
myrowid rowid;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO r LIMIT rows;
EXIT WHEN r.count=0;
BEGIN
FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
insert into ANBOB.HIST201609_new
select /*+ ROWID(A) */ *
from ANBOB.HIST201609 partition(P20160922_23) A where rowid = r(i);
EXCEPTION
when OTHERS then
BEGIN
errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
if error_code in (1410, 8103, 1578) then
myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
bad_rows := bad_rows + 1;
insert into bad_rows values(myrowid, error_code);
else
raise;
end if;
END LOOP;
END;
END;
commit;
END LOOP;
commit;
CLOSE c1;
dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
Solution：
alter table ANBOB.HIST201609 truncate partition P20160922_23; -- faild ora-8103 alter table ANBOB.HIST201609 exchange partition P20160922_23 with table ANBOB.HIST201609_new; complete.
in the case show:
The buffer with tsn: 33 rdba: 0x54766d01 was pinned
rdba 0x54766d01,the block type: 0x20=FIRST LEVEL BITMAP BLOCK , and object id is another table,But confirmed the late confirmation of the table no problem。
case 2
Note:
objn: -1 means it’s had droped, you can use flashback query on obj$ like the following：
select * from sys.obj$ as of timestamp sysdate-2/24 where obj#8817570;
references : note.1499.1 note.8103.1
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。