首页 » ORACLE » ora-01499 & ora-08103 caused by block corrupted or write loss a case

ora-01499 & ora-08103 caused by block corrupted or write loss a case

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'; 

Trace file keyworld:
“OBJD MISMATCH”
“dump suspect buffer”
“KTRVAC”
“Consistent read started for block ”
“but could not be dumped”

case e.g.

*** 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。

 

references : note.1499.1  note.8103.1

打赏

,

对不起,这篇文章暂时关闭评论。