首页 » ORACLE 9i-23ai » Troubleshooting Oracle ORA-1410 & ORA-01499 & ORA-08103 block corrupted
Troubleshooting Oracle ORA-1410 & 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;
ORA-1410 “invalid ROWID”
A rowid is a structure that allows direct access to a row. The rowid contains information on the object number, the datafile it is located in, the block number, and the slot number within the block.
Oracle 8和更高的版本有下面格式的ROWID：
OOOOOOFFFBBBBBBSSS O=Data Object Number (length=6) F=Relative File Number (length=3) B=Block Number (length=6) S=Slot Number (length=3)
The ORA-01410 is generated when queries that the information was based on, changed during the running process. Meaning that the issue can occur when a index block delete is not completed as ROWID’s are found in the index block leaf.
产生ORA-1410错误原因：
- 如果文件号和块号是有效的，只是行号有问题，那么就返回”no rows selected”
- 如果rowid的任何其他部分有问题，那么返回ORA-1410错误
- ORA-1410可能指出ROWID为BLOCK不在这个表的一部分
- 损坏索引的rowid是 获取表数据时
- SQL语句里手动输入不正确的rowid,或者有错误逻辑的PL/SQL过程产生一个错误rowid.
- bug
尝试
alter system flush shared_pool; alter system flush buffer_cache;
如果问题依旧发生 alter system set events '1410 trace name ERRORSTACK level 3'; alter session set events '10236 trace name context forever, level 1'; alter session set tracefile_identifier='ORA1410'; -- Then reproduce the error.
验证表和索引
SQL> analyze table . validate structure ONLINE; --For each index on the table, run a validate also SQL> analyze index . validate structure ONLINE.
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
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;
解决方法
如果确认是某个索引，可以把损坏的索引drop 重建，而如果确认损坏的是table，可以使用bbed或dul工具编辑block或抽数，简单的方法可以使用Plsql基于rowid抽数，这里存在两个情况，如果有pk索引，可以基于index中的rowid，去遍历table, 如果没有索引，可以使用dbms_rowid包基于dba_extent 创建rowid的方法。
SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE (Doc ID 1527738.1)
1, Extract table data base PK index rowid:
基于pk 索引
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;
/
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS (Doc ID 422547.1)
2, Extract table base dbms_rowid build rowid from dba_extents
1. Determine the average rows per block by using any method. dba_tables.avg_row_len might be a starting point.
An overhead of 100% or more may be added to that average to ensure that no rows will be missed from the blocks. i.e. if the calculated value is 80 rows per block, then 160 may be appropriate. This value will be used in variable ROWSPERBLOCK below.
2. Create the new table where the data will be savaged. Example:
set concat off create table &&table_owner.&&new_table as select * from &&table_owner.&&table_name where 1 = 2;
3. Modify and run the next script:
connect / as sysdba
set serveroutput on
set concat off
DECLARE
nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK:=[VALUE CALCULATED IN STEP 1];
nrows:=0;
select data_object_id into dobj
from dba_objects
where owner = '&&table_owner'
and object_name = '&&table_name'
-- and subobject_name = '[table partition]' Add this condition if table is partitioned
;
for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = '&&table_owner'
and segment_name = '&&table_name'
-- and partition_name = '[table partition]' Add this condition if table is partitioned
-- and file_id != [OFFLINED DATAFILE]This condition is only used if a datafile needs to be skipped due to ORA-376 (A)
order by extent_id)
loop
for br in i.block_id..i.totblocks loop
for j in 1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insert into &&table_owner.&&new_table ([columns here])
select /*+ ROWID(A) */ [columns here]
from &&table_owner.&&table_name A
where rowid = rid;
if sql%rowcount = 1 then nrows:=nrows+1; end if;
if (mod(nrows,10000)=0) then commit; end if;
exception when others then null;
end;
end loop;
end loop;
end loop;
COMMIT;
dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/
3, 脚本
/*
This script prompts for an owner and a tbale name. It then created two new tables
- orignal_table_name_BAD and orignal_table_name_SAVED. If either if these tables exist,
they are dropped and then recreated. The script then gets a list of extents and blocks
associated with the table and loops through each block, attempting to select records by
the rowid and inserts them into the orignical_table_name_SAVED table. Errors are inserted
into the original_table_name_BAD table.
Kerry Osborne - Enkitec
*/
prompt
Prompt WARNING: This script may issue a DROP TABLE command. Do not execute it unless you have read through it
Prompt and are comfortable you know what it does.
Prompt
accept a prompt "Ready? (hit ctl-C to quit) "
set serveroutput on format wrapped
declare
debug_flag varchar2(1) :='N';
x number := 0;
y number := 0;
v_maxrows number := 200; -- 200 is maximum number of rows per block
v_old_rowid varchar2(30);
v_owner_name varchar2(30) := upper('&owner_name');
v_table_name varchar2(30) := upper('&table_name');
v_bad_table varchar2(40);
v_saved_table varchar2(40);
v_full_table_name varchar2(30);
v_object_id number := 0;
v_sql_insert_saved varchar(256);
v_temp number;
v_last_block number := 0;
e_invalid_rowid exception;
pragma exception_init(e_invalid_rowid,-1410);
e_missing_file exception;
pragma exception_init(e_missing_file,-376);
e_obj_no_longer_exists exception;
pragma exception_init(e_obj_no_longer_exists,-8103);
cursor v_sql_get_blocks is
select file_id, block_id as start_block, (block_id + blocks - 1) as end_block
from dba_extents where upper(owner) = v_owner_name and segment_name = v_table_name
order by file_id, block_id
;
begin
v_bad_table := v_table_name||'_BAD';
v_saved_table := v_table_name||'_SAVED';
select object_id into v_object_id
from dba_objects
where owner = v_owner_name
and object_name = v_table_name
;
begin -- block to create saved table
select 1 into v_temp
from dba_tables
where owner = v_owner_name
and table_name = v_saved_table;
if debug_flag = 'Y' then dbms_output.put_line('dropping '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'drop table '||v_owner_name||'.'||v_table_name||'_SAVED';
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_SAVED as select * from '||v_owner_name||'.'||v_table_name||
' where 1=2';
exception when no_data_found then
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_saved_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_SAVED as select * from '||v_owner_name||'.'||v_table_name||
' where 1=2';
end;
begin -- block to create bad table
select 1 into v_temp
from dba_tables
where owner = v_owner_name
and table_name = v_bad_table;
if debug_flag = 'Y' then dbms_output.put_line('dropping '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'drop table '||v_owner_name||'.'||v_table_name||'_BAD';
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_BAD (old_rowid varchar2(30), '||
' old_file number, old_object number, old_block number, old_row number, error_message varchar2(300))';
exception when no_data_found then
if debug_flag = 'Y' then dbms_output.put_line('creating '||v_owner_name||'.'||v_bad_table); end if;
execute immediate 'create table '||v_owner_name||'.'||v_table_name||'_BAD (old_rowid varchar2(30), '||
' old_file number, old_object number, old_block number, old_row number, error_message varchar2(300))';
end;
v_full_table_name := v_owner_name||'.'||v_table_name;
v_sql_insert_saved := 'insert into '||v_owner_name||'.'||v_saved_table||' select * from '||v_full_table_name||
' where ROWID = dbms_rowid.rowid_create(1, :b_obj, :b_file, :b_blk, :v_row)';
for v_uetrec in v_sql_get_blocks loop
for v_blk in v_uetrec.start_block..v_uetrec.end_block loop
if debug_flag = 'Y' then dbms_output.put_line(v_uetrec.file_id||'.'||v_blk); end if;
for v_row in 0..v_maxrows loop
v_old_rowid := v_uetrec.file_id||'.'||v_blk||'.'||v_row;
begin
execute immediate v_sql_insert_saved
using v_object_id, v_uetrec.file_id, v_blk, v_row;
if debug_flag = 'Y' then
dbms_output.put_line(v_old_rowid);
end if;
exception when e_missing_file then
if debug_flag = 'Y' then
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK||' '||v_old_rowid);
else
if v_blk != v_last_block then -- only insert one bad record per block
execute immediate
'insert into '||v_owner_name||'.'||v_bad_table||' values'||
'(:b_rowid,:b_obj,:b_file,:b_blk,:b_row,:b_error)'
using v_old_rowid, v_object_id, v_uetrec.file_id, v_blk, v_row, DBMS_UTILITY.FORMAT_ERROR_STACK;
y := y+1;
v_last_block := v_blk;
end if;
end if;
when e_invalid_rowid then null;
when e_obj_no_longer_exists then null;
when no_data_found then null;
when others then
if debug_flag = 'Y' then
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_STACK||' '||v_old_rowid);
else
execute immediate
'insert into '||v_owner_name||'.'||v_bad_table||' values'||
'(:b_rowid,:b_obj,:b_file,:b_blk,:b_row,:b_error)'
using v_old_rowid, v_object_id, v_uetrec.file_id, v_blk, v_row, DBMS_UTILITY.FORMAT_ERROR_STACK;
y := y+1;
end if;
end;
end loop; -- end of row-loop
commit; -- save after each block
end loop; -- end of block-loop
end loop; -- end of uet-loop
execute immediate
'select count(*) from '||v_owner_name||'.'||v_saved_table
into x;
dbms_output.put_line(chr(0));
dbms_output.new_line;
dbms_output.put_line('Saved '||x||' records in '||v_saved_table||'.');
dbms_output.put_line(y||' bads records in '||v_bad_table||'.');
end;
/
undef a
references : note.1499.1 note.8103.1
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。