标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,679)
- DB2 (22)
- MySQL (73)
- Oracle (1,541)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (563)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
标签归档:open hang
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
新会话查询信息
数据库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