ORA-08103 物件不在存在..弔詭問題－assistant engineer

>>08/30 發生一件發生弔詭事情...有客戶反應要對某個表格做index 會發生ORA-08103

其實不難發現看字眼就覺得是....表格已經刪除了或者...跟錯user帳號...但是這件事情並不是這樣

--------------------------------------------------------------------------------------------

####特別處理

CREATE INDEX Jiade.jiade_tem_idex ON Jiade.jiade_tem
(SEQNO, WO_CODE) TABLESPACE JIADE_DATA;
CREATE INDEX TMSNAF.PRDRPTIT_INDEX_SW ON TMSNAF.PRODUCTION_DAILY_REPORT_ITEM
*
ERROR at line 1:
ORA-08103: object no longer exists

<<以為是表格不存在....但是...

SQL> desc Jiade.jiade_tem
Name Null? Type
----------------------------------------- -------- ----------------------------
REPORT_ITEM_ID NOT NULL VARCHAR2(32)
REPORT_ID VARCHAR2(32)
REPORT_CODE VARCHAR2(20)
REPORT_SEQNO NUMBER
WO_CODE VARCHAR2(20)
SEQNO NUMBER
OP_TYPE_CODE VARCHAR2(20)
SUB_OP_TYPE_CODE VARCHAR2(20)
DUTY_OP_TYPE_CODE VARCHAR2(20)
QUESTION_CODE VARCHAR2(20)
ACT_QTY NUMBER
QTY NUMBER
UNIT VARCHAR2(32)

表結構是正常的.....表示表格內有一個壞塊造成原因

>>在創建索引時候ORACLE會對表格每個塊去掃描,當掃描到有問題塊時候就會跳出並出錯ora-08103...

>>所以這邊有嘗試使用SELECT COUNT(1)方式也是一樣結果 --COUNT也是全部掃描

>>這邊有嘗試過很多方是來做但是都不行

>>匯入匯出

>>對表格做複製 CREATE TABLE XXX AS SELECT * FROM JIADE.JIADE_ITEM;

---------------------------------以下以原廠文件來解決也不行------------------------------------------------------------

以Oracle給的文檔來處理 ( Doc ID 8103.1 )

我運行以下結果都不行解決我的問題,以下紅色為語句
>>analyze table JIADE.JIADE_ITEM validate structure cascade online;
If this returns ORA-8103
Flush the buffer cache.
It might be a corruption only in the SGA memory (Buffer cache):
>>alter session set events 'immediate trace name flush_cache level 1';
In a RAC system, flushing the buffer cache may be needed in the additional rac instances.
If the error persists, continue with the next steps:
Flush the shared pool.
In some cases the problem can be caused by a cursor invalidation problem for which flushing the shared_pool can be a workaround:
>>alter system flush shared_pool;

>>>以下是Oracle透過跳開壞塊來insert into 到新表,但是也不行

SQL> create table REPORT_ITEM08
as
select *
from PRODUCTION_DAILY_REPORT_ITEM
where 1=2; 2 3 4 5

Table created.

SQL> create table bad_rows (row_id rowid, oracle_error_code number);

set serveroutput on
Table created.

SQL> SQL>
SQL> DECLARE
2 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3
4 CURSOR c1 IS select rowid
5 from PRODUCTION_DAILY_REPORT_ITEM tab1
6 order by rowid;
7
8 r RowIDTab;
9 rows NATURAL := 20000;
10 bad_rows number := 0 ;
11 errors number;
12 error_code number;
13 myrowid rowid;
14 BEGIN
15 OPEN c1;
16 LOOP
17 FETCH c1 BULK COLLECT INTO r LIMIT rows;
18 EXIT WHEN r.count=0;
19 BEGIN
20 FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
21 insert into REPORT_ITEM08
22 select /*+ ROWID(A) */ A.*
23 from PRODUCTION_DAILY_REPORT_ITEM A where rowid = r(i);
24 EXCEPTION
25 when OTHERS then
26 BEGIN
27 errors := SQL%BULK_EXCEPTIONS.COUNT;
FOR err1 IN 1..errors LOOP
28 29 error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
30 if error_code in (1410, 8103, 1578) then
31 myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
32 bad_rows := bad_rows + 1;
33 insert into bad_rows values(myrowid, error_code);
34 else
35 raise;
36 end if;
37 END LOOP;
38 END;
39 END;
40 commit;
41 END LOOP;
42 commit;
43 CLOSE c1;
44 dbms_output.put_line('Total Bad Rows: '||bad_rows);
45 END;
46 /
DECLARE
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 17

>>文件寫道透過跳過壞塊也不行

---------------------------------------------------------------------

透過Oracle文章來用根本不行......我表上也沒索引可以使用...只能使用後續方式來解決

後續透過追蹤發現有壞塊出現

alter session set max_dump_file_size=unlimited;
alter session set db_file_multiblock_read_count=1;
alter session set events 'immediate trace name trace_buffer_on level 1048576';
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';

run the query that produces the error ORA-8103

alter session set events 'immediate trace name trace_buffer_off';
exit

-------------------------------------------------------------------

查看文件紅圈處...發現 file=6 block=2207571 出現unknow情況

ktrget2(): started for block <0x0007 : 0x01a1af53> objd: 0x000258a1 <<<<<<
env [0xfffffd7ffc7f96cc]: (scn: 0x0adb.9ad85beb xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0adb.922f406e flg: 0x00000660)
ktrexf(): returning 9 on: d06be90 cr-scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0xffff.ffffffff sfl: 0
KTRVAC: path typ=58, rdba=1a1af53
KTRGET: pobj=0, dsobj=153761, dsd=1a1af53, sd=182ea20

Dump of buffer cache at level 10 for tsn=7 rdba=27373395
BH (0x12bfa2438) file#: 6 rdba: 0x01a1af53 (6/2207571) class: 1 ba: 0x12b5ee000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,19
dbwrid: 0 obj: 153761 objn: 153761 tsn: 7 afn: 6 hint: f
hash: [0x36517a0c0,0x36517a0c0] lru: [0xdbf8a0d0,0x141f3a440]
lru-flags: hot_buffer
ckptq: [NULL] fileq: [NULL] objq: [0x35fa11588,0x14fee7be8] objaq: [0x35fa11578,0x14fee7bf8]
use: [NULL] wait: [NULL] fast-cr-pins: 1
st: XCURRENT md: NULL fpin: 'ktswh72: ktsbget' tch: 196
flags:
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 7 rdba: 0x01a1af53 (6/2207571)
scn: 0x0ada.6a5cf51e seq: 0x01 flg: 0x04 tail: 0xf51e3a01
frmt: 0x02 chkval: 0x524e type: 0x3a=unknown <<<<<<< type: 0x3a=unknown
Hex dump of block: st=0, typ_found=0
Dump of memory from 0x000000012B5EE000 to 0x000000012B5F0000
12B5EE000 0000A23A 01A1AF53 6A5CF51E 04010ADA [:...S.....\j....]
12B5EE010 0000524E 00000000 00000000 00000000 [NR..............]
12B5EE020 00000000 00000000 00000000 00000000 [................]

---------------只能透過以下方式來將問題解決

>> 1.sqlplus user/passwd
>> create table maob_error_info (A varchar2(4000));
>> create table JIADE.JIADE_ITEM_bak tablespace JIADE_DATA as select * from JIADE.JIADE_ITEM where 1=2; >>建構表欄位

2.run following script to extract normal data and insert backup table.

DECLARE
MY_ROWID ROWID;
vs_error varchar2(200);
vn_block_id number;
cursor cur1 is select relative_fno,block_id, blocks from
dba_extents where segment_name='JIADE_ITEM' and owner='JIADE' ;
c_row cur1%rowtype;

begin
open cur1;
loop
fetch cur1 into c_row;
exit when cur1%notfound;

for j in 0 .. c_row.blocks-1 loop
vn_block_id:=c_row.block_id+j;

FOR i in 0..500 loop
begin

my_rowid := DBMS_ROWID.ROWID_CREATE(1, <issue table data_object_id>, c_row.relative_fno, vn_block_id, i); <<<<select data_object_id from dba_objects where object_name='<your issue table>';
--dbms_lock.sleep(10);
insert into JIADE.JIADE_ITEM_bak select * from JIADE.JIADE_ITEM where rowid=my_rowid;

commit;
exception
when others then
vs_error:=substr(sqlerrm,1,30);
insert into maob_error_info values(my_rowid||'Error info:'||vs_error||' FILE_NO='||c_row.relative_fno||' BLOCKID='||vn_block_id ||' rownum='||i);

end;
end loop; --FOR i in 0..1000 loop
end loop;
commit;
end loop;
end;
/

3.rename issue table to other name.
alter table JIADE.JIADE_ITEM RENAME TO JIADE_ERR

4.rename backup table to issue table name.
ALTER TABLE JIADE.JIADE_ITEM_BAK RENAME TO JIADE.JIADE_ITEM

5.rebuild indexes.