用户反映应用软件在保存相关数据时报错,并提供了如下报错截图:
通过直接查找alert日志中的错误对应TRACE文件以及通过设置errorstack方式从TRACE文件中分析相应的应用软件报错时对应的数据库对象信息:
ORA-00600 25027错误
[oracle@orcl01 trace]$ more /oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_166102.trc
Trace file /oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_166102.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/product/11.2.0.4
System name: Linux
Node name: orcl01
Release: 2.6.32-642.el6.x86_64
Version: #1 SMP Wed Apr 13 00:51:26 EDT 2016
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 195
Unix process pid: 166102, image: oracle@orcl01
*** 2018-08-15 23:04:45.223
*** SESSION ID:(220.437) 2018-08-15 23:04:45.223
*** CLIENT ID:() 2018-08-15 23:04:45.223
*** SERVICE NAME:(orcl) 2018-08-15 23:04:45.223
*** MODULE NAME:(w3wp.exe) 2018-08-15 23:04:45.223
*** ACTION NAME:() 2018-08-15 23:04:45.223
DDE: Problem Key 'ORA 600 [25027]' was flood controlled (0x4) (incident: 145727)
ORA-00600: Ś²¿´´²ϊ027], [28], [0], [], [], [], [], [], [], [], [], []
*** 2018-08-15 23:09:54.341
DDE: Problem Key 'ORA 600 [25027]' was flood controlled (0x4) (incident: 145728)
ORA-00600: Ś²¿´´²ϊ027], [28], [0], [], [], [], [], [], [], [], [], []
…………
*** 2018-08-15 23:52:11.953
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00600: Ś²¿´´²ϊ027], [28], [0], [], [], [], [], [], [], [], [], []
----- Current SQL Statement for this session (sql_id=4uy64tf4a6fb8) -----
UPDATE ZY_DOC_BINGLIJLHTML_V4 SET HTMLVALUE=:CLOB WHERE BINGLIJLID='b9c8cb06-748d-48e1-a0aa-9a8b7affa016'
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000000 ?
7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbkdaKsdActDriver() call ksedmp() 000000003 ? 000000000 ?
+1960 7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F65AD42A730 ? 7FFEAF292720 ?
)+1065 7FFEAF28B4A0 ? 7FFEAF28B578 ?
7FFEAF290020 ? 000000002 ?
dbgdaRunAction()+81 call dbgdaExecuteAction( 7F65AD42A730 ? 00A6006C0 ?
5 ) 0020C0003 ? 7FFEAF292720 ?
000000001 ? 000000002 ?
dbgdRunActions()+64 call dbgdaRunAction() 7F65AD42A730 ? 000000005 ?
0020C0003 ? 7FFEAF292720 ?
ORA-00600 splbfmb-dblfree
Wed Aug 15 18:22:31 2018
Errors in file /oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_44580.trc (incident=144026):
ORA-00600: Ś²¿´´²ϊsplbfmb-dblfree], [0], [264601152], [264600928], [224], [0], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
*** 2018-08-15 18:15:52.536
Opcodes *.*
DBAs (file#, block#):
(63, 359297) .
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
**NOTE: Only Dumping Redo less then 12 hours**
Times: 08/15/2018 06:15:52 thru eternity
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
对LOB段错误的处理
RMAN校验无物理坏块,移动或导出相应LOB段时有ORA-01555报错。最终使用如下方式进行修复:
SQL> exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);
BEGIN DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsp_check_repair_blk:kcbz_objdchk], [0], [0], [1], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 335
ORA-06512: at line 1
SQL> alter table EMR3.ZY_DOC_BINGLIJLXML_V4 modify lob(XMLVALUE)(pctversion 40);
Table altered.
SQL> set linesize 180 pagesize 200
col file_id for a5
col file_name for a50
col tablespace_name for a20
select a.tablespace_name tablespace_name,a.total AS "Total(M)",
nvl(ceil((1 - b.free / a.total) * 100),100) "usage_of_tablespace%",
nvl(b.free,SQL> SQL> SQL> SQL> 2 3 0) "left_space(M)",
c.EXTENT_MANAGEMENT "Extent_management"
from (select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(nvl(bytes, 0)) 4 5 6 7 8 / 1024 / 1024 free
from dba_free_space
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name = c.tablespace_name
and c.tablespace_name = b.tablespace_name(+)
and a.tablespace_name='USERS'
order by (1 - b. 9 10 11 12 13 14 15 free / a.total) * 100 desc;
TABLESPACE_NAME Total(M) usage_of_tablespace% left_space(M) Extent_management
-------------------- ---------- -------------------- ------------- ------------------------------
USERS 1935 62 749.9375 LOCAL
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);
Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS)
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'ZY_DOC_BINGLIJLXML_V4' and owner='EMR3';
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PCTVERSION RETENTION
---------- ----------
XMLVALUE
40
SQL> col column_name for a30
SQL> /
COLUMN_NAME PCTVERSION RETENTION
------------------------------ ---------- ----------
XMLVALUE 40
SQL> alter table EMR3.ZY_DOC_BINGLIJLXML_V4 modify lob(XMLVALUE)(retention);
Table altered.
SQL> select column_name, pctversion, retention from dba_lobs where table_name = 'ZY_DOC_BINGLIJLXML_V4' and owner='EMR3';
COLUMN_NAME PCTVERSION RETENTION
------------------------------ ---------- ----------
XMLVALUE 10800
SQL>
SQL>
SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);
Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS)
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
SQL> select count(*) from corrupt_lobs20180815;
COUNT(*)
----------
0
SQL> declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
num number;
begin
for cursor_lob in (select rowid r, XM 2 3 4 5 6 7 8 9 10 LVALUE from EMR3.ZY_DOC_BINGLIJLXML_V4) loop
begin
num := dbms_lob.instr (cursor_lob.XMLVALUE, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs20180815 values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corr 11 12 13 14 15 16 17 18 upt_lobs20180815 values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs20180815 values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/ 19 20 21 22 23 24 25 26
PL/SQL procedure successfully completed.
SQL> select * from corrupt_lobs20180815;
CORRUPT_ROWID ERR_NUM
------------------ ----------
AAAuG1AA/AABQA0AAB 1555
AAAuG1AA/AABQA0AAF 1555
AAAuG1AA/AABQA0AAG 1555
AAAuG1AA/AABQC0AAA 1555
AAAuG1AA/AABQV3AAB 1555
AAAuG1AA/AABQV3AAC 1555
AAAuG1AA/AABQV3AAD 1555
7 rows selected.
SQL> update EMR3.ZY_DOC_BINGLIJLXML_V4
set XMLVALUE = empty_clob()
where rowid in (select corrupt_rowid from SYS.corrupt_lobs20180815);
commit; 2 3
7 rows updated.
SQL>
Commit complete.
SQL> commit;
Commit complete.
SQL> Alter table EMR3.ZY_DOC_BINGLIJLXML_V4 move lob(XMLVALUE) store as (tablespace USERS);
Table altered.
SQL> exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('EMR3','SYS_LOB0000188094C00003$$','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,status,table_name from dba_indexes where owner='EMR3' and TABLE_NAME in(upper('ZY_DOC_BINGLIJLXML_V4'));
OWNER
--------------------------------------------------------------------------------
INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
TABLE_NAME
--------------------------------------------------------------------------------
EMR3
PK_ZY_DOC_BINGLIJLXML_V4
UNUSABLE
ZY_DOC_BINGLIJLXML_V4
OWNER
--------------------------------------------------------------------------------
INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
TABLE_NAME
--------------------------------------------------------------------------------
EMR3
SYS_IL0000188853C00002$$
VALID
ZY_DOC_BINGLIJLXML_V4
SQL> col owner for a15
col COLUMN_NAME for a20SQL>
SQL>
SQL> /
OWNER
---------------
INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
TABLE_NAME
--------------------------------------------------------------------------------
EMR3
PK_ZY_DOC_BINGLIJLXML_V4
UNUSABLE
ZY_DOC_BINGLIJLXML_V4
OWNER
---------------
INDEX_NAME
--------------------------------------------------------------------------------
STATUS
------------------------
TABLE_NAME
--------------------------------------------------------------------------------
EMR3
SYS_IL0000188853C00002$$
VALID
ZY_DOC_BINGLIJLXML_V4
SQL>
SQL>
SQL> alter index EMR3.PK_ZY_DOC_BINGLIJLXML_V4 rebulid online;
alter index EMR3.PK_ZY_DOC_BINGLIJLXML_V4 rebulid online
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SQL> alter index EMR3.PK_ZY_DOC_BINGLIJLXML_V4 rebuild online;
Index altered.