存储断电故障导致的常见错误
2023-09-20T22:00:39.865797+08:00
jycDB(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-09-20T22:00:39.866243+08:00
Errors in file /oracle/app/oracle/diag/rdbms/jyc/jyc1/trace/jyc1_j000_12601.trc:
ORA-00600: internal error code, arguments: [6017], [0], [0], [], [], [], [], [], [], [], [], []
2023-09-20T22:00:39.868109+08:00
opidrv aborting process J000 ospid (12601) as a result of ORA-600
2023-09-20T22:00:39.892215+08:00
Errors in file /oracle/app/oracle/diag/rdbms/jyc/jyc1/trace/jyc1_ora_39028.trc (incident=2177567) (PDBNAME=jycDB):
ORA-00600: 内部错误代码, 参数: [2866], [110], [3218711], [1310720], [1310720], [], [], [], [], [], [], []
jycDB(3):Incident details in: /oracle/app/oracle/diag/rdbms/jyc/jyc1/incident/incdir_2177567/jyc1_ora_39028_i2177567.trc
2023-09-21T09:54:58.264922+08:00
jycDB(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-09-21T09:54:58.640171+08:00
Dumping diagnostic data in directory=[cdmp_20230921095458], requested by (instance=1, osid=39028), summary=[incident=2177567].
2023-09-21T10:07:46.421007+08:00
Errors in file /oracle/app/oracle/diag/rdbms/jyc/jyc1/trace/jyc1_ora_48456.trc (incident=2177537) (PDBNAME=jycDB):
ORA-00600: 内部错误代码, 参数: [kdsgrp1-qetlbr], [7], [650446436], [6], [], [], [], [], [], [], [], []
jycDB(3):Incident details in: /oracle/app/oracle/diag/rdbms/jyc/jyc1/incident/incdir_2177537/jyc1_ora_48456_i2177537.trc
jycDB(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-09-21T10:07:46.531415+08:00
2023-09-21T19:26:07.416472+08:00
Errors in file /oracle/app/oracle/diag/rdbms/jyc/jyc1/trace/jyc1_ora_32827.trc (incident=2240183) (PDBNAME=JYCDB):
ORA-00600: internal error code, arguments: [kdliReadPos], [27887], [0], [0], [82153], [111726], [7], [616766913], [0], [], [], []
JYCDB(3):Use ADRCI or Support Workbench to package the incident.
以上错误基本是表和索引故障，根据trc内容查看对应的对象具体处理即可。
用到的方式：
根据trc:
* kdsgrp1-1: where 4 *************************************
row 0x13071306.37 no continuation RID
row likely from index rdba: 0x13864e9d, tsn: 7
rfile# 76 block# 463622 slot 55 not found (dscnt: 0)
查找对象：
Select owner, segment_name, segment_type, partition_name,tablespace_name
From dba_extents
Where relative_fno = 76
And 463622 between block_id and (block_id+blocks-1);
SQL> Analyze table JYC.TEST_TABLE validate structure;
Table analyzed.--不报错，说明表正常。处理重建表上的索引问题即可。
SQL> select index_name from dba_indexes where owner='JYC' and table_name='TEST_TABLE';
INDEX_NAME
--------------------------------------------------------------------------------
IDX_TEST_TABLEPAYID
IDX_TEST_RECNUM
P_PK_PSI_FEE_PAYM
SQL> alter index JYC.P_PK_PSI_FEE_PAYM rebuild;
在trc根据关键字obj:查对象obj:
cat /oracle/app/oracle/diag/rdbms/jyc/jyc1/incident/incdir_2176727/jyc1_j000_12601_i2176727.trc|grep "obj:"
dbwrid: 3 obj: 169176 objn: 83140 tsn: [3/7] afn: 105 hint: f
addr: 0xcaf807058 obj: 169176 cls: DATA
seg/obj: 0x294d8 csc: 0x0000000120cd80e3 itc: 2 flg: E typ: 2 - INDEX
dbwrid: 1 obj: 169176 objn: 83140 tsn: [3/7] afn: 169 hint: f
addr: 0xcaf7abb78 obj: 169176 cls: DATA
seg/obj: 0x294d8 csc: 0x0000000120d24aed itc: 4 flg: E typ: 2 - INDEX
dbwrid: 0 obj: 169169 objn: 82545 tsn: [3/7] afn: 38 hint: f
seg/obj: 0x294d1 csc: 0x0000000120e09c11 itc: 2 flg: E typ: 1 - DATA
addr: 0xcaf839b38 obj: 169169 cls: DATA
addr: 0xcaf839b38 obj: 169169 cls: DATA
dbwrid: 3 obj: 169169 objn: 82545 tsn: [3/7] afn: 38 hint: f
seg/obj: 0x294d1 csc: 0x0000000120d8154f itc: 2 flg: E typ: 1 - DATA
addr: 0xcaf8c5db8 obj: 169169 cls: DATA
addr: 0xcaf8c5db8 obj: 169169 cls: DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
seg/obj: 0xbe8500 csc: 0x0000000120f26247 itc: 2 flg: O typ: 1 - DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
seg/obj: 0xbe8500 csc: 0x0000000120f25f89 itc: 2 flg: O typ: 1 - DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
seg/obj: 0xbe8500 csc: 0x0000000120f25f76 itc: 2 flg: O typ: 1 - DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
seg/obj: 0xbe8500 csc: 0x0000000120f25f5a itc: 2 flg: O typ: 1 - DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
seg/obj: 0xbe8500 csc: 0x0000000120f25f49 itc: 2 flg: O typ: 1 - DATA
dbwrid: 3 obj: 12485888 objn: 77314 tsn: [3/3] afn: 1004 hint: f
dbwrid: 0 obj: 169176 objn: 83140 tsn: [3/7] afn: 169 hint: f
seg/obj: 0x294d8 csc: 0x0000000120cdc59b itc: 1 flg: E typ: 2 - INDEX
addr: 0xcaf778a18 obj: 169176 cls: DATA
addr: 0xcaf778a18 obj: 169176 cls: DATA
dbwrid: 1 obj: 169176 objn: 83140 tsn: [3/7] afn: 169 hint: f
seg/obj: 0x294d8 csc: 0x0000000120d24aed itc: 4 flg: E typ: 2 - INDEX
addr: 0xcaf7abb78 obj: 169176 cls: DATA
addr: 0xcaf7abb78 obj: 169176 cls: DATA
dbwrid: 2 obj: 169176 objn: 83140 tsn: [3/7] afn: 110 hint: f
seg/obj: 0x294d8 csc: 0x0000000120e59c8f itc: 4 flg: E typ: 2 - INDEX
addr: 0xcaf7d91d8 obj: 169176 cls: DATA
addr: 0xcaf7d91d8 obj: 169176 cls: DATA
dbwrid: 3 obj: 169176 objn: 83140 tsn: [3/7] afn: 105 hint: f
seg/obj: 0x294d8 csc: 0x0000000120cd80e3 itc: 2 flg: E typ: 2 - INDEX
addr: 0xcaf807058 obj: 169176 cls: DATA
addr: 0xcaf807058 obj: 169176 cls: DATA
SQL> select object_name,object_type,owner from dba_objects where object_id=169176;
no rows selected
Elapsed: 00:00:00.07
SQL> c/169176/83140
1* select object_name,object_type,owner from dba_objects where object_id=83140
SQL> r
1* select object_name,object_type,owner from dba_objects where object_id=83140
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
----------------------------------------------
OWNER
--------------------------------------------------------------------------------
IDX_TEST_REGNO
INDEX
jyc
Elapsed: 00:00:00.07
SQL> c/83140/77314
1* select object_name,object_type,owner from dba_objects where object_id=77314
SQL> r
1* select object_name,object_type,owner from dba_objects where object_id=77314
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
----------------------------------------------
OWNER
--------------------------------------------------------------------------------
WRI$_ADV_SEGADV_SEGROW
TABLE
SYS
Elapsed: 00:00:00.07
SQL> truncate table WRI$_ADV_SEGADV_SEGROW;
Table truncated.
Elapsed: 00:00:00.11
SQL> alter index jyc.IDX_TEST_REGNO rebuild;
Index altered.
Elapsed: 00:00:00.09
SQL> select object_name,object_type,owner from dba_objects where object_id=169169;
no rows selected
Elapsed: 00:00:00.07
===================kdliReadPos===============
TEST表的RECORDSINFO为CLOB字段类型
检查损坏的rowid
drop table bad_rows;
create table bad_rows (row_id ROWID
,oracle_error_code number);
set concat off
set serveroutput on
declare
n number;
error_code number;
bad_rows number := 0;
ora1578 EXCEPTION;
ora600 EXCEPTION;
PRAGMA EXCEPTION_INIT(ora1578, -1578);
PRAGMA EXCEPTION_INIT(ora600, -600);
begin
for cursor_lob in (select rowid rid, &&lob_column
from &&table_owner.&&table_with_lob) loop
begin
n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
exception
when ora1578 then
bad_rows := bad_rows + 1;
insert into bad_rows values(cursor_lob.rid,1578);
commit;
when ora600 then
bad_rows := bad_rows + 1;
insert into bad_rows values(cursor_lob.rid,600);
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values(cursor_lob.rid,error_code);
commit;
end;
end loop;
dbms_output.put_line('Total Rows identified with errors in LOB column: '||
bad_rows);
end;
/
Enter value for lob_column: RECORDSINFO
old 11: for cursor_lob in (select rowid rid, &&lob_column
new 11: for cursor_lob in (select rowid rid, RECORDSINFO
Enter value for table_owner: JYC
Enter value for table_with_lob: TEST
old 12: from &&table_owner.&&table_with_lob) loop
new 12: from jyc.TEST) loop
old 14: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
new 14: n:=dbms_lob.instr(cursor_lob.RECORDSINFO,hextoraw('889911')) ;
Total Rows identified with errors in LOB column: 17
PL/SQL procedure successfully completed.
Elapsed: 03:40:01.55
========查看执行的进度========
正在执行的sql查询：
SELECT d.spid,a.sid,e.event,b.status,C.SQL_TEXT,B.USERNAME,B.OSUSER,B.MACHINE,B.PROGRAM
FROM V$SESS_IO A,V$SESSION B,V$SQL C,V$PROCESS D,v$session_wait e
WHERE A.SID=B.SID AND B.SQL_HASH_VALUE=C.HASH_VALUE and B.sid=E.sid
AND B.PADDR=D.ADDR and b.status='ACTIVE'
查看查询消耗的时间：
select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork from v$session_longops where sofar!=totalwork;
=============================
SQL> select * from bad_rows
ROW_ID ORACLE_ERROR_CODE
------------------ -----------------
AAAbRvABmAAAryKAAA 600
AAAbRvABmAAAsDHAAB 600
AAAbRvABlAAAr9aAAG 600
AAAbRvABlAAAsBPAAH 600
AAAbRvABlAAAsFJAAE 600
AAAbRvABkAAAqfAAAB 600
AAAbRvABkAAAqfAAAC 600
AAAbRvABkAAAqgAAAA 600
AAAbRvABkAAAqmIAAE 600
AAAbRvABkAAArMiAAA 600
AAAbRvABkAAArMiAAD 600
AAAbRvABkAAArPxAAA 600
AAAbRvABjAAAq8qAAA 600
AAAbRvABjAAArIuAAD 600
AAAbRvABjAAArPAAAC 600
AAAbRvABPAAGgFZAAA 600
AAAbRvABPAAGgFpAAB 600
17 rows selected.
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEST';
SUM(BYTES)/1024/1024/1024
-------------------------
29.0625
Elapsed: 00:00:00.53
20:51:19 SQL> SELECT count(*) FROM jyc.TEST;
COUNT(*)
----------
11543973
Elapsed: 00:00:00.81
20:52:11 SQL> SELECT count(*) FROM jyc.TEST where rowid not in(select row_id from bad_rows);
COUNT(*)
----------
11543957
SQL>alter session set workarea_size_policy=manual;
alter session set sort_area_size=1073741820;
alter session set sort_area_retained_size=1073741820;
alter session set db_file_multiblock_read_count=128;
CREATE TABLE jyc.TEST_20230921
parallel (degree 16)
nologging as
SELECT * FROM jyc.TEST where rowid not in(select row_id from bad_rows);
Table created.
Elapsed: 04:16:18.60 --未并行的消耗时间
SQL>-- alter table jyc.TEST_20230921 NOPARALLEL;
SQL>-- alter table jyc.TEST_20230921 LOGGING;
SQL> select count(*) from jyc.TEST_20230921 ;
COUNT(*)
----------
11543748
Elapsed: 00:01:03.61
SQL> alter session set workarea_size_policy=manual;
alter session set sort_area_size=1073741820;
alter session set sort_area_retained_size=1073741820;
alter session set db_file_multiblock_read_count=128;
CREATE TABLE jyc.TEST_20230922
parallel (degree 16)
nologging as
SELECT * FROM jyc.TEST_20230921 WHERE CREATE_DATE >=TO_DATE('2023-08-29 01:00:00','yyyy-MM-dd hh24:mi:ss');
Table created.
Elapsed: 00:00:29.57
SQL>-- alter table jyc.TEST_20230922 NOPARALLEL;
SQL>-- alter table jyc.TEST_20230922 LOGGING;
SQL> select count(*) from jyc.TEST_20230922;
COUNT(*)
----------
6910
找出丢失信息的记录：
SELECT ID,CREATE_DATE,REGISTER_NUMBER,IDENTITY_CODE,PATIENT_NAME FROM jyc.TEST_noclob
MINUS
SELECT ID,CREATE_DATE,REGISTER_NUMBER,IDENTITY_CODE,PATIENT_NAME FROM jyc.TEST_20230922;
参考文档：ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)
ORA-16433
https://www.modb.pro/db/31928
http://blog.itpub.net/70004783/viewspace-2990334/
最后修改时间：2023-10-23 16:30:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
