truncate IDL_UB1$导致数据库open hang---惜分飞

在一次数据库恢复中,发现IDL_UB1$表被truncate,然后数据库在open过程中会hang住,而且不报任何错误,这里通过试验进行重现.对于这类问题,以前有过类似处理测试：truncate IDL_UB1$恢复
试验数据库版本

SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE    11.2.0.4.0      Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SQL> select object_name,object_type,owner,object_id,data_object_id, 2   last_ddl_time from dba_objects where object_name like 'IDL_UB%'; OBJECT_NAME   OBJECT_TYPE         OWNER     OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIM ------------- ------------------- -------- ---------- -------------- ------------ IDL_UB1$      TABLE               SYS             225            225 24-AUG-13 IDL_UB2$      TABLE               SYS             227            227 24-AUG-13

truncate IDL_UB1$表

SQL> truncate table IDL_UB1$; truncate table IDL_UB1$ * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 1090 Session ID: 125 Serial number: 5

alert日志报错信息

Sat May 25 20:23:11 2024 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98DA2B7, hshuid()+273] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_1090.trc  (incident=60156): ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98DA2B7] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/incident/incdir_60156/xifenfei_ora_1090_i60156.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details.

检查确认IDL_UB1$表被truncate成功

SQL> select count(1) from IDL_UB1$;   COUNT(1) ----------          0 SQL> select object_id,data_object_id,last_ddl_time from dba_objects where object_name like 'IDL_UB%';  OBJECT_ID DATA_OBJECT_ID ---------- --------------        225          87377

重启数据库hang住
 

---

新会话查询信息
数据库open状态,但是无法执行ddl,无法执行exp/expdp等导出操作

[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat May 25 20:29:01 2024 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE --操作hang住 SQL> create table t1 as select * from dba_objects; --exp操作hang住 [oracle@xifenfei ~]$ exp "'/ as sysdba'" tables=obj$ Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:03 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. --expdp操作hang住 [oracle@xifenfei ~]$ expdp "'/ as sysdba'" tables=obj$ Export: Release 11.2.0.4.0 - Production on Sat May 25 20:39:35 2024 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

数据库在open 被hang的过程中等待事件信息
一直卡在db file sequential read等待事件上，而且等待的block信息一直不变，读取对象为IDL_UB2$

SQL> select username,program,event,P1,P2,P3 from v$session where wait_class#<>6 and username='SYS'   2  AND SID NOT IN(SELECT DISTINCT SID FROM V$MYSTAT); USERNAME                       PROGRAM ------------------------------ ------------------------------------------------ EVENT                                                                    P1 ---------------------------------------------------------------- ----------         P2         P3 ---------- ---------- SYS                            sqlplus@xifenfei (TNS V1-V3) db file sequential read                                                   1       1531          1 SQL> / USERNAME                       PROGRAM ------------------------------ ------------------------------------------------ EVENT                                                                    P1 ---------------------------------------------------------------- ----------         P2         P3 ---------- ---------- SYS                            sqlplus@xifenfei (TNS V1-V3) db file sequential read                                                   1       1531          1 SQL> / USERNAME                       PROGRAM ------------------------------ ------------------------------------------------ EVENT                                                                    P1 ---------------------------------------------------------------- ----------         P2         P3 ---------- ---------- SYS                            sqlplus@xifenfei (TNS V1-V3) db file sequential read                                                   1       1531          1 --读取对象 SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME   2    FROM DBA_EXTENTS A   3   WHERE FILE_ID = &FILE_ID   4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; Enter value for file_id: 1 old   3:  WHERE FILE_ID = &FILE_ID new   3:  WHERE FILE_ID = 1 Enter value for block_id: 1531 old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1 new   4:    AND 1531 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1 OWNER ------------------------------ SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME ------------------ ------------------------------ ------------------------------ SYS IDL_UB2$ TABLE              SYSTEM

trace数据库启动过程发现卡在select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# 语句部分.

PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552642714 hv=4260389146 ad='80963c78' sqlid='cvn54b7yz0s8u' select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# END OF STMT PARSE #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642713 BINDS #140737276517064:  Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05   value=1310  Bind#1   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05   value=0  Bind#2   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05   value=184549376 EXEC #140737276517064:c=72,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642819 FETCH #140737276517064:c=4,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1716640552642832 STAT #140737276517064 id=1 cnt=0 pid=0 pos=1 obj=225 op='TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=1 pr=0 pw=0 time=5 us cost=3 size=44 card=2)' STAT #140737276517064 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=4 us cost=2 size=0 card=2)' CLOSE #140737276517064:c=2,e=2,dep=2,type=0,tim=1716640552642858 ===================== PARSING IN CURSOR #140737276517064 len=135 dep=2 uid=0 oct=3 lid=0 tim=1716640552642876 hv=1115215392 ad='808c4c68' sqlid='c6awqs517jpj0' select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# END OF STMT PARSE #140737276517064:c=12,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642875 BINDS #140737276517064:  Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05   value=1310  Bind#1   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05   value=0  Bind#2   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05   value=184549376 EXEC #140737276517064:c=73,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552642977 WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1610 blocks=1 obj#=237 tim=1716640552643001 WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1522 blocks=1 obj#=226 tim=1716640552643027 WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=1521 blocks=1 obj#=226 tim=1716640552643051 FETCH #140737276517064:c=81,e=80,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643065 WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1524 blocks=1 obj#=226 tim=1716640552643124 WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=1523 blocks=1 obj#=226 tim=1716640552643143 FETCH #140737276517064:c=40,e=40,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643150 WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1526 blocks=1 obj#=226 tim=1716640552643216 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1525 blocks=1 obj#=226 tim=1716640552643232 FETCH #140737276517064:c=36,e=36,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643239 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8200 blocks=1 obj#=226 tim=1716640552643405 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1527 blocks=1 obj#=226 tim=1716640552643420 FETCH #140737276517064:c=31,e=31,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643427 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8202 blocks=1 obj#=226 tim=1716640552643583 WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8201 blocks=1 obj#=226 tim=1716640552643600 FETCH #140737276517064:c=34,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643608 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8204 blocks=1 obj#=226 tim=1716640552643766 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8203 blocks=1 obj#=226 tim=1716640552643785 FETCH #140737276517064:c=35,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552643792 WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8206 blocks=1 obj#=226 tim=1716640552644038 WAIT #140737276517064: nam='db file sequential read' ela= 8 file#=1 block#=8205 blocks=1 obj#=226 tim=1716640552644062 FETCH #140737276517064:c=49,e=49,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644070 WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8208 blocks=1 obj#=226 tim=1716640552644296 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=8207 blocks=1 obj#=226 tim=1716640552644313 FETCH #140737276517064:c=36,e=35,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644320 WAIT #140737276517064: nam='db file sequential read' ela= 7 file#=1 block#=8209 blocks=1 obj#=226 tim=1716640552644479 FETCH #140737276517064:c=20,e=20,p=1,cr=3,cu=0,mis=0,r=1,dep=2,og=4,plh=1319326155,tim=1716640552644487 FETCH #140737276517064:c=2,e=1,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=1319326155,tim=1716640552644545 STAT #140737276517064 id=1 cnt=9 pid=0 pos=1 obj=226 op='TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=20 pr=10 pw=0 time=53 us cost=3 size=21 card=1)' STAT #140737276517064 id=2 cnt=9 pid=1 pos=1 obj=237 op='INDEX RANGE SCAN I_IDL_CHAR1 (cr=11 pr=1 pw=0 time=46 us cost=2 size=0 card=1)' CLOSE #140737276517064:c=3,e=3,dep=2,type=0,tim=1716640552644579 ===================== PARSING IN CURSOR #140737276517064 len=132 dep=2 uid=0 oct=3 lid=0 tim=1716640552644609 hv=1684122946 ad='80924938' sqlid='39m4sx9k63ba2' select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# END OF STMT PARSE #140737276517064:c=17,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644609 BINDS #140737276517064:  Bind#0   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f73a0  bln=22  avl=03  flg=05   value=1310  Bind#1   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7370  bln=24  avl=01  flg=05   value=0  Bind#2   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00   oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7ffff35f7340  bln=24  avl=06  flg=05   value=184549376 EXEC #140737276517064:c=92,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2317816222,tim=1716640552644735 WAIT #140737276517064: nam='db file sequential read' ela= 9 file#=1 block#=1618 blocks=1 obj#=238 tim=1716640552644761 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1530 blocks=1 obj#=227 tim=1716640552644778 WAIT #140737276517064: nam='db file sequential read' ela= 10 file#=1 block#=1529 blocks=1 obj#=227 tim=1716640552644799 FETCH #140737276517064:c=60,e=60,p=3,cr=5,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644807 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1532 blocks=1 obj#=227 tim=1716640552644924 WAIT #140737276517064: nam='db file sequential read' ela= 6 file#=1 block#=1531 blocks=1 obj#=227 tim=1716640552644940 FETCH #140737276517064:c=33,e=33,p=2,cr=4,cu=0,mis=0,r=1,dep=2,og=4,plh=2317816222,tim=1716640552644947