月归档：三月 2016

insert into aud$引起高版本问题导致ORA-600[17059]

发表于 2016 年 3 月 20 日 由 惜分飞

昨天晚上有朋友咨询我,他数据库(win2008 11.2.0.1 单机)出现大量ORA-00600[17059],如下错误,让帮忙分析原因
alert日志报错

Sat Mar 19 21:31:02 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3336.trc  (incident=45304):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:06 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4168.trc  (incident=45166):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
Sat Mar 19 21:31:09 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2928.trc  (incident=45342):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
Sat Mar 19 21:31:12 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5812.trc  (incident=45399):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:17 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5884.trc  (incident=45255):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:21 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2976.trc  (incident=45305):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:24 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6068.trc  (incident=45256):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:28 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_6044.trc  (incident=45351):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-28001: 口令已经失效
Sat Mar 19 21:31:32 2016
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_2928.trc  (incident=45343):
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []
ORA-02002: 写入审计线索时出错

这里虽然报了ORA-00600[17059],ORA-28001,ORA-02002但是根据经验感觉很可能是由于ORA-00600[17059]错误导致后面的其他两个错误.

trace文件信息

Dump continued from file: d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5484.trc
ORA-00600: 内部错误代码, 参数: [17059], [0x49BE74CB0], [0x49BE75548], [0x4A9A0FFD0], [], [], [], [], [], [], [], []

========= Dump for incident 45253 (ORA 600 [17059]) ========

*** 2016-03-19 21:28:34.244
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=4vs91dcv7u1p6) -----
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode,
 obj$creator,obj$name,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, 
logoff$lwrite,logoff$dead,comment$text,spare1,spare2,  priv$used,clientid,sessioncpu,proxy$sid,user$guid, 
instance#,process#,xid,scn,auditid,  sqlbind,sqltext,obj$edition,dbid)  values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),   
  :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)

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009173DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexProcessError()  CALL???  dbgexPhaseII()       021160570 02116D448 00000B0C5
+2563                                              000000002
dbgeExecuteForError  CALL???  dbgexProcessError()  021160570 021167540 000000001
()+65                                              000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  025455460 00000000C 000000001
1726                          ()                   4A9A0FFD0
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()   025455460 024690040 000000258
75                                                 7F883243A05D
kgeade()+560         CALL???  dbkePostKGE_kgsf()   000000001 000000000 00A684E58
                                                   00A5553E2
kgeriv_int()+111     CALL???  kgeade()             024444C49 000000000 000000000
                                                   000000000
kgeriv()+29          CALL???  kgeriv_int()         000000000 000000001 02B474DD0
                                                   000000001
kgesiv()+105         CALL???  kgeriv()             461A8AD00 7F883243A05D
                                                   025455460 000000001
kgesic3()+60         CALL???  kgesiv()             401122000 000000000 009B4310C
                                                   4A9A0EC80
kgltba()+739         CALL???  kgesic3()            000000200 6236313231656434
                                                   4000042A3 000000002
kglhdgc()+384        CALL???  kgltba()             4A095BC50 461A31858 02B476418
                                                   000000001
kglLock()+3063       CALL???  kglhdgc()            000000000 000020C68 4ADE08E18
                                                   000000008
kglget()+403         CALL???  kglLock()            02B476008 02B475360 02B475360
                                                   0004D112F
kxsGetLookupLock()+  CALL???  kglget()             025455460 02B4756A0 000000001
327                                                000000003
kksfbc()+14464       CALL???  kxsGetLookupLock()   4AC4EDFB0 0004FD4CA 0246CC6B8
                                                   4AC4EDFB0
kkspsc0()+2117       CALL???  kksfbc()             0246CC6B8 000000003 000000008
                                                   0070D7F80
kksParseCursor()+18  CALL???  kkspsc0()            0246B19B8 0070D7F80 000000256
1                                                  000000003
opiosq0()+2538       CALL???  kksParseCursor()     000000000 025454EA0 000000000
                                                   0033917DF
opiosq()+23          CALL???  opiosq0()            000000003 00000000F 02B478A28
                                                   025450000
opiodr()+1662        CALL???  opiosq()             0746E6172 000000002 06567656C
                                                   000000000
rpidrus()+862        CALL???  opiodr()             00000004A 00000000F 02B478A28
                                                   000000001
rpidru()+154         CALL???  rpidrus()            02B478028 000000001 000000000
                                                   000400000
rpiswu2()+2757       CALL???  rpidru()             02B4788B0 000000000
                                                   1D181E32DAE2234 000000000
rpidrv()+6105        CALL???  rpiswu2()            4AC4EC300 000000000 02B478680
                                                   000000002
rpisplu()+1607       CALL???  rpidrv()             400000001 7F880000004A
                                                   02B478A28 000000008
audins()+2562        CALL???  rpisplu()            000000001 000000000 000000000
                                                   000000000
audlon()+1286        CALL???  audins()             000005028 0070D9274 0070D9280
                                                   000000036
auddft()+2140        CALL???  audlon()             006F79DE0 0092ACAF1 000000000
                                                   000000000
kpolnb()+4007        CALL???  auddft()             0246AF458 000000064 000000000
                                                   000000000
kpolon()+237         CALL???  kpolnb()             1D181E300000051 02B47AF20
                                                   000000000 000026161
opiodr()+1662        CALL???  kpolon()             000000001 000000000 000000000
                                                   00A42F224
ttcpip()+1325        CALL???  opiodr()             4800000000000051 40000001A
                                                   02B47E100 000000000
opitsk()+2040        CALL???  ttcpip()             02546F180 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             000000000 000000000 000000000
                                                   02B47F9F8
opiodr()+1662        CALL???  opiino()             00000003C 000000004 02B47FAB0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 02B47FAB0
                                                   615C3A6400000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 02B47FAB0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              1D181E32DAE2234 000000000
                                                   150013000307E0 601B80009001C
opimai()+191         CALL???  opimai_real()        000000000 02B47FC68 01D3200A0
                                                   02B47FC68
OracleThreadStart()  CALL???  opimai()             000000000 006DF0B34 0000000E0
+724                                               0000027CC
0000000076E1652D     CALL???  OracleThreadStart()  02957FF18 000000000 000000000
                                                   000000000
0000000076F4C521     CALL???  0000000076E16520     000000000 000000000 000000000
                                                   000000000
 

--------------------- Binary Stack Dump ---------------------

LibraryObject:  Address=9be74cb0 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000] 
  ChildTable:  size='32768' 
    Child:  id='0' Table=9be75b60 Reference=9be75600 Handle=a9a0eb20 
    Child:  id='1' Table=9be75b60 Reference=9be61ed8 Handle=a9a06700 
    Child:  id='2' Table=9be75b60 Reference=9be621f0 Handle=a9a06500 
    Child:  id='3' Table=9be75b60 Reference=9be62528 Handle=a9997200 
    Child:  id='4' Table=9be75b60 Reference=9be62818 Handle=a99597a8 
    Child:  id='5' Table=9be75b60 Reference=9be62b50 Handle=a99cd210 
    Child:  id='6' Table=9be75b60 Reference=9bd0a418 Handle=a994a308 
    Child:  id='7' Table=9be75b60 Reference=9bd0a750 Handle=a9920980 
    Child:  id='8' Table=9be75b60 Reference=9bd0aa40 Handle=a99a6d48 
    Child:  id='9' Table=9be75b60 Reference=9bd0ad78 Handle=a99a6918 
    Child:  id='10' Table=9be75b60 Reference=9bd0b068 Handle=a9993388 
    …………
    Child:  id='32764' Table=becbd118 Reference=bec882d8 Handle=522c1118 
    Child:  id='32765' Table=becbd118 Reference=bec7c708 Handle=52319608 
    Child:  id='32766' Table=becbd118 Reference=bec7c9f8 Handle=522c0f18 
    Child:  id='32767' Table=becbd118 Reference=bec7cd30 Handle=522a7858

结合trace信息,我们可以发现高版本数量已经到了32768,理论最大值.因此数据库报了ORA-600[17059]错误(以前写过类似文章:因为高版本引起ORA-00600[17059]),并且引起了其他的ORA-错误.通过查询数据库高版本信息,在重启一会儿的库中,发现
insert-aud$

过几分钟后
insert-aud$-高版本

通过这里,进一步诊断,引起高版本的sql，是由于aud$表相关的插入时绑定参数导致（如果需要可以进一步分析是由于什么原因导致了高版本）.这里根据经验具体原因已经不再重要，对于11.2.0.1版本,本身bug比较多,且暂时无法升级
处理方法
1.因为本库的本地审计意义不大直接从数据库层面关闭

audit_trail='none'

2.在11g的初始版本中,acs特性导致子游标过多,建议关闭

_optimizer_adaptive_cursor_sharing=false 
_optimizer_extended_cursor_sharing=none 
_optimizer_extended_cursor_sharing_rel=none

3.限制数据库sql游标数量，当游标超过该数量直接抛弃重新硬解析

_cursor_features_enabled=34
event='106001 trace name context forever,level 100'

通过以上配置，重启数据库之后,运行一天alert日志未再出现任何错误,通过查询mos匹配Bug 10196339 : ORA600[17059] OCCURS DUE TO BIND_MISMATCH

发表在 Oracle Bug | 标签为 , , , | 2 条评论

file 1 block 128 corrupted/坏块恢复—system rollback坏块修复

发表于 2016 年 3 月 17 日 由 惜分飞

有个数据库file 1 block 128 坏块导致数据库无法启动报错如下
file-1-block-128

该数据库版本是11.2.0.1,根据我们的经验该block是system rollback 的segment header,以下为我在正常哭查询结果

SQL> select file_id,block_id,blocks from dba_extents where segment_name='SYSTEM';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1        128          8
         1        136          8
         1        528          8
         1        536          8
         1        544          8
         1        552          8

6 rows selected.

dump file 1 block 128 结果

Dump all the blocks in range:
buffer tsn: 0 rdba: 0x00400080 (1/128)
scn: 0x0000.00000000 seq: 0xff flg: 0x04 tail: 0x00000eff
frmt: 0x02 chkval: 0x1387 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Hex dump of block: st=0, typ_found=1

这里可以看到block scn为0×0000.00000000，而且数据块已经被标记为坏块
dbv检查坏块结果
dbv

从这里可以看出来主要错误是由于Controlscn: 0×0004.119fe191 greater than blockscn: 0×0000.00000000,拷贝system文件到本地，使用bbed修改

bbed修复坏块

H:\temp\SYSTEM01>bbed password=blockedit filename=system01.dbf blocksize=8192

BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 17 00:23:49 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        system01.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

BBED> set block 129
        BLOCK#          129

BBED> map
 File: system01.dbf (0)
 Block: 129                                   Dba:0x00000000
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0

 struct ktech, 72 bytes                     @20

 struct ktemh, 16 bytes                     @92

 struct ktetb[6], 48 bytes                  @108

 struct ktuxc, 104 bytes                    @4148

 struct ktuxe[204], 8160 bytes              @4252

 ub4 tailchk                                @8188


BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000

BBED> set mode edit
        MODE            Edit

BBED> d offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff0e0000

 <32 bytes per line>

BBED> m /x 01 offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e0000

 <32 bytes per line>

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> sum apply
Check value for File 0, Block 129:
current = 0x1387, required = 0x1387

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> exit


H:\temp\SYSTEM01>dbv file=SYSTEM01.DBF

DBVERIFY: Release 12.1.0.2.0 - Production on 星期四 3月 17 00:26:26 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\TEMP\SYSTEM01\SYSTEM01.DBF
Controlscn: 0x0004.119fe191 greater than blockscn: 0x0000.00000000
页 128 失败, 校验代码为 14509
页 128 失败, 校验代码为 14509


DBVERIFY - 验证完成

检查的页总数: 209920
处理的页总数 (数据): 132380
失败的页总数 (数据): 0
处理的页总数 (索引): 57168
失败的页总数 (索引): 0
处理的页总数 (其他): 3112
处理的总页数 (段)  : 1
失败的总页数 (段)  : 1
空的页总数: 17260
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 188826853 (5.188826853)


H:\temp\SYSTEM01>bbed password=blockedit filename=system01.dbf

BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 17 00:26:59 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************


BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 129
        BLOCK#          129

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0


BBED> map
 File: system01.dbf (0)
 Block: 129                                   Dba:0x00000000
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0

 struct ktech, 72 bytes                     @20

 struct ktemh, 16 bytes                     @92

 struct ktetb[6], 48 bytes                  @108

 struct ktuxc, 104 bytes                    @4148

 struct ktuxe[204], 8160 bytes              @4252

 ub4 tailchk                                @8188


BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000


BBED> set mode edit
        MODE            Edit

BBED> m /x 0400 offset 12
 File: system01.dbf (0)
 Block: 129              Offsets:   12 to  523           Dba:0x00000000
------------------------------------------------------------------------
 04000104 87130000 00000000 00000000 00000000 00000000 06000000 2f000000
 20100000 05000000 05000000 08000000 2d024000 00000000 05000000 00000000
 00000000 00000000 00000000 00000000 06000000 00000000 00000000 00000040
 81004000 07000000 88004000 08000000 10024000 08000000 18024000 08000000
 20024000 08000000 28024000 08000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0004
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000

BBED> m /x 625a60d0 offset 8
 File: system01.dbf (0)
 Block: 129              Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 625a60d0 10000104 87130000 00000000 00000000 00000000 00000000 06000000
 2f000000 20100000 05000000 05000000 08000000 2d024000 00000000 05000000
 00000000 00000000 00000000 00000000 00000000 06000000 00000000 00000000
 00000040 81004000 07000000 88004000 08000000 10024000 08000000 18024000
 08000000 20024000 08000000 28024000 08000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0xd0605a62
   ub2 wrp_kcbh                             @12       0x0010
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000

BBED> d offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e0000

 <32 bytes per line>

BBED> m /x 010e625a
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e625a

 <32 bytes per line>

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128

Block 128 is corrupt
***
Corrupt block relative dba: 0x00400080 (file 0, block 128)
Bad check value found during verification
Data in bad block -
 type: 14 format: 2 rdba: 0x00400080
 last change scn: 0x0010.34605a62 seq: 0x1 flg: 0x04
 consistency value in tail: 0x5a620e01
 check value in block header: 0x1387, computed block checksum: 0x3470
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0

BBED> sum apply
Check value for File 0, Block 129:
current = 0x27f7, required = 0x27f7

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> exit


H:\temp\SYSTEM01>dbv file=SYSTEM01.DBF

DBVERIFY: Release 12.1.0.2.0 - Production on 星期四 3月 17 00:40:38 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\TEMP\SYSTEM01\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 209920
处理的页总数 (数据): 132380
失败的页总数 (数据): 0
处理的页总数 (索引): 57168
失败的页总数 (索引): 0
处理的页总数 (其他): 3112
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17260
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 188826853 (5.188826853)

这里发现当我们bbed验证非坏块之时,使用dbv检测依旧报坏块,可以看出来dbv的验证比bbed更加严格

发表在 非常规恢复 | 标签为 , , , , , , | 2 条评论

分区无法识别导致asm diskgroup无法mount

发表于 2016 年 3 月 16 日 由 惜分飞

有客户咨询由于主机重启之后，导致四个磁盘组中的data2磁盘组无法mount(报ORA-15032,ORA-15017,ORA-15063),数据库无法open,让我们帮忙分析解决

Wed Mar 09 18:10:53 2016
NOTE: Assigning number (1,1) to disk (/dev/oracleasm/disks/VOL011)
Wed Mar 09 18:10:53 2016
ERROR: no read quorum in group: required 1, found 0 disks
NOTE: cache dismounting (clean) group 1/0xBD42B778 (DATA2) 
NOTE: messaging CKPT to quiesce pins Unix process pid: 45093, image: oracle@BA (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0xBD42B778 (DATA2) 
NOTE: cache ending mount (fail) of group DATA2 number=1 incarn=0xbd42b778
NOTE: cache deleting context for group DATA2 1/0xbd42b778
GMON dismounting group 1 at 16 for pid 18, osid 45093
NOTE: Disk DATA2_0001 in mode 0x9 marked for de-assignment
ERROR: diskgroup DATA2 was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA2" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"
ERROR: ALTER DISKGROUP DATA2 MOUNT  /* asm agent *//* {0:0:431} */

这里很明显由于缺少asm disk导致data2无法正常mount,进一步分析发现data2是有两块磁盘组成

Mon Sep 14 13:14:35 2015
SQL> create diskgroup data2 external redundancy  disk '/dev/oracleasm/disks/VOL010','/dev/oracleasm/disks/VOL011' 
NOTE: Assigning number (4,0) to disk (/dev/oracleasm/disks/VOL010)
NOTE: Assigning number (4,1) to disk (/dev/oracleasm/disks/VOL011)
NOTE: initializing header on grp 4 disk DATA2_0000
NOTE: initializing header on grp 4 disk DATA2_0001
NOTE: initiating PST update: grp = 4
Mon Sep 14 13:14:35 2015
GMON updating group 4 at 29 for pid 26, osid 51535
NOTE: group DATA2: initial PST location: disk 0000 (PST copy 0)
NOTE: PST update grp = 4 completed successfully 
NOTE: cache registered group DATA2 number=4 incarn=0xea085f62
NOTE: cache began mount (first) of group DATA2 number=4 incarn=0xea085f62
NOTE: cache opening disk 0 of grp 4: DATA2_0000 path:/dev/oracleasm/disks/VOL010
NOTE: cache opening disk 1 of grp 4: DATA2_0001 path:/dev/oracleasm/disks/VOL011
NOTE: cache creating group 4/0xEA085F62 (DATA2)
NOTE: cache mounting group 4/0xEA085F62 (DATA2) succeeded
NOTE: allocating F1X0 on grp 4 disk DATA2_0000
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting (clean) group 4/0xEA085F62 (DATA2) 
NOTE: messaging CKPT to quiesce pins Unix process pid: 51535, image: oracle@BA (TNS V1-V3)
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 4/0xEA085F62 (DATA2) 
GMON dismounting group 4 at 30 for pid 26, osid 51535
GMON dismounting group 4 at 31 for pid 26, osid 51535
NOTE: Disk DATA2_0000 in mode 0x7e marked for de-assignment
NOTE: Disk DATA2_0001 in mode 0x7e marked for de-assignment
SUCCESS: diskgroup DATA2 was created

结合这部分信息,我们可以确定data2磁盘组是由两个磁盘组构成,分别为VOL010和VOL011,现在由于只发现了VOL011,因此data2磁盘组无法正常mount.观察发现该系统使用的是asmlib,通过oracleasm querydisk命令结合fdisk的盘符,
oracleasm-querydisk

基本上可以确定VOL010丢失应该在mpathb盘(由于只有该盘和分区未被使用,其他盘和分区已经全部被现在可以查询到的asmlib使用作为asmdisk)之上

Disk /dev/mapper/mpathb: 3846.7 GB, 3846677987328 bytes
255 heads, 63 sectors/track, 467665 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1               1      267350  2147483647+  ee  GPT

Disk /dev/mapper/mpathbp1: 3846.7 GB, 3846675890176 bytes
255 heads, 63 sectors/track, 467665 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xb84bb99a

                Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1p1               1      200513  1610620641   83  Linux
/dev/mapper/mpathbp1p2          200514      267349   536860170   83  Linux
/dev/mapper/mpathbp1p3          267350      467665  1609038270   83  Linux

这里我们发现奇怪现象:mpathb盘先使用parted分为一个mapthbp1分区,然后又使用fdisk分了三个p1p1,p1p2,p1p3三个子分区.然后我们查看/dev/mapper/中的设备情况
mpathb

发现没有p1p1,p1p2,p1p3这三个本该属于mapthb上的子分区.现在基本上明确,是由于对mapthb先使用了parted分区,然后再使用fdisk分区,在操作系统重启之后,无法正常识别相关子分区导致该问题.到此解决该问题的思路有三种.
1. 因为磁盘分区表信息是正常的,就是分区表信息没有同步到操作系统之上,想办法同步过去即可,os部分内容,此处忽略
2. 使用数据文件重组的方式直接对data2这两个asm disk进行重组,这里因为三个子分区未发现,直接对mapthbp1分区进行扫描即可,参考:asm disk header 彻底损坏恢复
3. 因为分区对于asm disk来说主要就是设置了磁盘的偏移量和大小,如果找到磁盘的偏移量,然后确定asm disk大小,直接通过dd命令把该部分dd到新的磁盘设备之上,然后直接mount磁盘组即可,这里重点讲解第三种方法恢复处理
使用dd出来mapthp1的磁盘头,然后使用bbed找出来偏移量,主要依据是第一次出现01820101信息的部分

BBED> d
 File: bp1 (0)
 Block: 64               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 01820101 00000000 00000080 bc60223c 00000000 00000000 00000000 00000000
 4f52434c 4449534b 564f4c30 31300000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> show all
        FILE#           0
        BLOCK#          64
        OFFSET          0
        DBA             0x00000000 (0 0,64)
        FILENAME        bp1
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       512
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           64
        LOGFILE         log.bbd
        SPOOL           No

这里基本上可以定位到asm disk header对于mapthbp1的偏移量为32256,dd出来asm disk header分析
dd

使用kfed查看磁盘头信息
kfed
kfed2
现在基本上可以确定,asm disk大小为1572871M,磁盘的偏移量为32256,然后使用dd命令把这部分dd到新的磁盘设备上,然后oracleasm scandisks后
asm-mount
asmcd-lsdg
oracle-open
data2 mount成功,数据库正常open,此数据库完美恢复
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

发表在 非常规恢复 | 标签为 , , , , , , , | 评论关闭