本案例来自西区某客户，数据库版本为11.2.0.1，由于redo损坏需要通过隐藏参数“_allow_resetlogs_corruption”=true来强制open数据库，但是在启动过程中，报错ORA-07445。具体报错为：
|
1 2 3 4 5 6 7 |
SMON: enabling cache recovery Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x2297750, kgegpa()+40] [flags: 0x0, count: 1] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x229597B, kgebse()+279] [flags: 0x2, count: 2] Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x229597B, kgebse()+279] [flags: 0x2, count: 2] Fri Aug 05 23:16:29 2022 PMON (ospid: 23557): terminating the instance due to error 397 Instance terminated by PMON, pid = 23557 |
通过10046跟踪open过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
PARSING IN CURSOR #4 len=148 dep=1 uid=0 oct=6 lid=0 tim=1659715679553876 hv=3540833987 ad='7f91db10' sqlid='5ansr7r9htpq3' update undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1 END OF STMT PARSE #4:c=10998,e=10789,p=7,cr=53,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1659715679553875 BINDS #4: Bind#0 oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0 kxsbbbfp=7f91e56a bln=32 avl=19 flg=09 value="_SYSSMU1_547241589$" 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=7fb700badea0 bln=24 avl=02 flg=05 value=3 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=7fb700bade70 bln=24 avl=03 flg=05 value=128 Bind#3 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=7fb700bade40 bln=24 avl=02 flg=05 value=2 Bind#4 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=7fb700bade10 bln=24 avl=02 flg=05 value=1 Bind#5 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=7fb700badde0 bln=24 avl=03 flg=05 value=4571 Bind#6 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=7fb700baddb0 bln=24 avl=05 flg=05 value=1849380 Bind#7 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=7fb700badd80 bln=24 avl=06 flg=05 value=2782784391 Bind#8 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=7fb700badd50 bln=24 avl=03 flg=05 value=3980 Bind#9 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=7fb700badd20 bln=24 avl=01 flg=05 value=0 Bind#10 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=7fb700bc5fe8 bln=24 avl=02 flg=05 value=2 Bind#11 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=7fb700bc5fb8 bln=24 avl=02 flg=05 value=2 Bind#12 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=7fb700baded0 bln=22 avl=02 flg=05 value=1 WAIT #4: nam='db file sequential read' ela= 18 file#=1 block#=541 blocks=1 obj#=0 tim=1659715679555587 *** 2022-08-06 00:07:59.555 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x2297750, kgegpa()+40] [flags: 0x0, count: 1] DDE previous invocation failed before phase II DDE was called in a 'No Invocation Mode' ----- Start Diag Diagnostic Dump ----- Diag diagnostic dump is performed due to an error in the diagfw code during error handling. DDE is switched to protected mode during the diagnostic dump to prevent recursive errors in the error hadnling code. Dump error and call stack for the diagnostic dump: *** 2022-08-06 00:07:59.563 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0) ----- Error Stack Dump ----- *** 2022-08-06 00:07:59.563 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x229597B, kgebse()+279] [flags: 0x2, count: 2] Not recording an ORA-7445 (previous attmpet failed) Warning! DDE is invoked in protected mode. DDE call is aborted. Warning! DDE is invoked in protected mode. DDE call is aborted. *** 2022-08-06 00:07:59.565 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x41CE1224] [PC:0x229597B, kgebse()+279] [flags: 0x2, count: 2] Not recording an ORA-7445 (previous attmpet failed) ssexhd: crashing the process... Shadow_Core_Dump = partial |
发现报错的sql为update undo$，在读取1号文件541号文件之后开始报错。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
BBED> set block 541 BLOCK# 541 BBED> map /v File: /data/oracle/VOC/VOC/system01.dbf (0) Block: 541 Dba:0x00000000 ------------------------------------------------------------ Undo Data struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktubh, 74 bytes @20 struct ktubhxid, 8 bytes @20 ub2 ktubhseq @28 ub1 ktubhcnt @30 ub1 ktubhirb @31 ub1 ktubhicl @32 ub1 ktubhflg @33 ub2 ktubhidx[30] @34 ub1 freespace[174] @94 ub1 undodata[7920] @268 ub4 tailchk @8188 |
1号文件的block 541是undo block，我们知道system表空间只有一个undo段为system。该block属于system undo段的一个undo块，那么update undo$为什么会去访问这个undo块呢？
第一种可能性是需要undo块构造一致性读，但是用bbed读取了几个块，发现在推进了scn的情况下并不需要undo块来构造一致性读，那么这很可能与为事务分配undo块有关，当事务绑定undo段之后，会从undo段头块的free block pool中去获取undo块。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
BBED> set block 128 BLOCK# 128 BBED> map /v File: /data/oracle/VOC/VOC/system01.dbf (0) Block: 128 Dba:0x00000000 ------------------------------------------------------------ Unlimited Undo Segment Header struct kcbh, 20 bytes @0 ub1 type_kcbh @0 ub1 frmt_kcbh @1 ub1 spare1_kcbh @2 ub1 spare2_kcbh @3 ub4 rdba_kcbh @4 ub4 bas_kcbh @8 ub2 wrp_kcbh @12 ub1 seq_kcbh @14 ub1 flg_kcbh @15 ub2 chkval_kcbh @16 ub2 spare3_kcbh @18 struct ktech, 72 bytes @20 ub4 spare1_ktech @20 sword tsn_ktech @24 ub4 lastmap_ktech @28 ub4 mapcount_ktech @32 ub4 extents_ktech @36 ub4 blocks_ktech @40 ub2 mapend_ktech @44 struct hwmark_ktech, 32 bytes @48 struct locker_ktech, 8 bytes @80 ub4 flag_ktech @88 struct ktemh, 16 bytes @92 ub4 count_ktemh @92 ub4 next_ktemh @96 ub4 obj_ktemh @100 ub4 flag_ktemh @104 struct ktetb[6], 48 bytes @108 ub4 ktetbdba @108 ub4 ktetbnbk @112 struct ktuxc, 104 bytes @4148 struct ktuxcscn, 8 bytes @4148 struct ktuxcuba, 8 bytes @4156 sb2 ktuxcflg @4164 ub2 ktuxcseq @4166 sb2 ktuxcnfb @4168 ub4 ktuxcinc @4172 sb2 ktuxcchd @4176 sb2 ktuxcctl @4178 ub2 ktuxcmgc @4180 ub4 ktuxcopt @4188 struct ktuxcfbp[5], 60 bytes @4192 struct ktuxe[255], 10200 bytes @4252 ub4 ktuxexid @4252 ub4 ktuxebrb @4256 struct ktuxescn, 8 bytes @4260 sb4 ktuxesta @4268 ub1 ktuxecfl @4269 sb2 ktuxeuel @4270 ub4 tailchk @8188 BBED> p ktuxcfbp struct ktuxcfbp[0], 12 bytes @4192 struct ktufbuba, 8 bytes @4192 ub4 kubadba @4192 0x0040021d ub2 kubaseq @4196 0x0069 ub1 kubarec @4198 0x10 sb2 ktufbext @4200 3 sb2 ktufbspc @4202 3734 struct ktuxcfbp[1], 12 bytes @4204 struct ktufbuba, 8 bytes @4204 ub4 kubadba @4204 0x00000000 ub2 kubaseq @4208 0x0032 ub1 kubarec @4210 0x07 sb2 ktufbext @4212 2 sb2 ktufbspc @4214 7124 struct ktuxcfbp[2], 12 bytes @4216 struct ktufbuba, 8 bytes @4216 ub4 kubadba @4216 0x00000000 ub2 kubaseq @4220 0x0032 ub1 kubarec @4222 0x22 sb2 ktufbext @4224 2 sb2 ktufbspc @4226 2418 struct ktuxcfbp[3], 12 bytes @4228 struct ktufbuba, 8 bytes @4228 ub4 kubadba @4228 0x00000000 ub2 kubaseq @4232 0x0029 ub1 kubarec @4234 0x0b sb2 ktufbext @4236 5 sb2 ktufbspc @4238 6944 struct ktuxcfbp[4], 12 bytes @4240 struct ktufbuba, 8 bytes @4240 ub4 kubadba @4240 0x00000000 ub2 kubaseq @4244 0x0000 ub1 kubarec @4246 0x00 sb2 ktufbext @4248 0 sb2 ktufbspc @4250 0 |
可以看到system undo段的free block pool中存在1个块，rdba为0x0040021d，正好就是1号文件541号块，那么处理该问题就非常简单了，清空free block pool，让system undo段重新分配undo块即可。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
BBED> m /x 00000000 offset 4192 File: /data/oracle/VOC/VOC/system01.dbf (0) Block: 128 Offsets: 4192 to 4703 Dba:0x00000000 ------------------------------------------------------------------------ 00000000 69001000 0300960e 00000000 32000700 0200d41b 00000000 32002200 02007209 00000000 29000b00 0500201b 00000000 00000000 00000000 ce000000 1b024000 a76139cf 8c0f0000 09000800 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 da654acf 8c0f0000 09000c00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 da2138cf 8c0f0000 09001100 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 4ea45acf 8c0f0000 09001200 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 d93038cf 8c0f0000 09000d00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 1a2c4dcf 8c0f0000 09000300 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 288e2bcf 8c0f0000 09000900 00000000 00000000 00000000 01000000 00000000 ce000000 1c024000 d61e5bcf 8c0f0000 09001b00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 a86139cf 8c0f0000 09001800 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 2a8e2bcf 8c0f0000 09000200 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 8e9f27cf 8c0f0000 09006000 00000000 00000000 00000000 01000000 00000000 ce000000 1c024000 031b5ccf <32 bytes per line> BBED> sum apply Check value for File 0, Block 128: current = 0x80f2, required = 0x80f2 BBED> p ktuxcnfb sb2 ktuxcnfb @4168 1 BBED> m /x 00 offset 4168 File: /data/oracle/VOC/VOC/system01.dbf (0) Block: 128 Offsets: 4168 to 4679 Dba:0x00000000 ------------------------------------------------------------------------ 00000000 00000000 3d003600 02800100 68000000 feffff7f 00000000 69001000 0300960e 00000000 32000700 0200d41b 00000000 32002200 02007209 00000000 29000b00 0500201b 00000000 00000000 00000000 ce000000 1b024000 a76139cf 8c0f0000 09000800 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 da654acf 8c0f0000 09000c00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 da2138cf 8c0f0000 09001100 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 4ea45acf 8c0f0000 09001200 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 d93038cf 8c0f0000 09000d00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 1a2c4dcf 8c0f0000 09000300 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 288e2bcf 8c0f0000 09000900 00000000 00000000 00000000 01000000 00000000 ce000000 1c024000 d61e5bcf 8c0f0000 09001b00 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 a86139cf 8c0f0000 09001800 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 2a8e2bcf 8c0f0000 09000200 00000000 00000000 00000000 01000000 00000000 ce000000 1b024000 8e9f27cf 8c0f0000 09006000 00000000 00000000 <32 bytes per line> BBED> sum apply Check value for File 0, Block 128: current = 0x80f3, required = 0x80f3 |
修改之后重新启动数据库成功。
over！！！