system坏块 – Database SOS

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

有个数据库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为0x0000.00000000，而且数据块已经被标记为坏块
dbv检查坏块结果
dbv

从这里可以看出来主要错误是由于Controlscn: 0x0004.119fe191 greater than blockscn: 0x0000.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更加严格

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次system表空间坏块(ORA-01578)数据库恢复

半夜朋友打来求救电话,说xx医院his系统因为存储异常导致system坏块无法正常启动,因为是win平台无法使用bbed,无法修复system 坏块,请求技术支持
dbv检查system文件报坏块

对应具体地址为:file 1 block 39041和66738

判断控制文件异常
通过数据库恢复检查脚本(Oracle Database Recovery Check)脚本检测数据库发现控制文件明显异常(checkpoint scn)

尝试恢复数据库

因此对该库进行了不完全恢复,然后尝试resetlogs打开数据库,数据库报ORA-600 2662错误

Fri Aug 29 02:35:08 2014
alter database open resetlogs
Fri Aug 29 02:35:11 2014
RESETLOGS after complete recovery through change 451371288
Resetting resetlogs activation ID 1232269761 (0x4972f1c1)
Fri Aug 29 02:35:15 2014
Setting recovery target incarnation to 3
Fri Aug 29 02:35:15 2014
Assigning activation ID 1384652231 (0x52881dc7)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=17, OS id=1084
Fri Aug 29 02:35:15 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=18, OS id=2836
Fri Aug 29 02:35:15 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: Z:\ORACLE\PRODUCT\10.2.0\ORCL\REDO01.LOG
Successful open of redo thread 1
Fri Aug 29 02:35:15 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Aug 29 02:35:15 2014
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Fri Aug 29 02:35:15 2014
ARC0: Becoming the heartbeat ARCH
Fri Aug 29 02:35:15 2014
SMON: enabling cache recovery
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []
Fri Aug 29 02:35:16 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4824.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [451371311], [0], [451374534], [8388977], [], []
Fri Aug 29 02:35:16 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Fri Aug 29 02:35:17 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_2928.trc:
ORA-00600: ??????, ??: [], [], [], [], [], [], [], []
Instance terminated by USER, pid = 4824
ORA-1092 signalled during: alter database open resetlogs...

ORA-600 2662 该错误解决思路很明显,推进scn,数据库报ORA-01578

Fri Aug 29 02:42:47 2014
SMON: enabling cache recovery
Fri Aug 29 02:42:47 2014
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Fri Aug 29 02:42:49 2014
SMON: enabling tx recovery
Fri Aug 29 02:42:49 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Aug 29 02:42:50 2014
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_4804.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 39041)
ORA-01110: 数据文件 1: 'Z:\ORACLE\PRODUCT\10.2.0\ORCL\SYSTEM01.DBF'
Fri Aug 29 02:42:50 2014
LOGSTDBY: Validating controlfile with logical metadata
Fri Aug 29 02:42:51 2014
LOGSTDBY: Validation complete
ORA-604 signalled during: alter database open...

使用event跳过坏块，启动数据库成功

Fri Aug 29 02:48:59 2014
SMON: enabling cache recovery
Fri Aug 29 02:49:00 2014
Successfully onlined Undo Tablespace 1.
Fri Aug 29 02:49:00 2014
SMON: enabling tx recovery
Fri Aug 29 02:49:00 2014
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=34, OS id=3096
Fri Aug 29 02:49:01 2014
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Aug 29 02:49:01 2014
Completed: alter database open

查询坏块对象

因为这些对象均不是核心对象,直接进行truncate然后插入老数据

后续还有大量错误修复

ORA-12012: error on auto execute of job 1
ORA-08102: index key not found, obj# 239, file 1, block 1674 (2)
ORA-00600: 内部错误代码, 参数: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []
ORA-00600: internal error code, arguments: [6749], [3], [12606796], [173], [], [], [], []
ORA-00600: 内部错误代码, 参数: [13013], [52898], [52895], [38288618], [44], [38288618], [17], []
ORA-00600: 内部错误代码, 参数: [13013], [5001], [52895], [38286476], [5], [38286476], [17], []

再次说明,很多时候数据库恢复不要看成多神秘,就是几个参数搞定,更加不要神化有坏块就bbed修复,当然非常极端,使用N中工具,N种尝试的也存在.做好备份重于一切

标签归档：system坏块