Category Archives: Oracle备份恢复

Solaris rm datafile recovery

Posted on by

今天早上接到有客户恢复请求，他一不小心在solaris系统中使用rm -rf oradata命令把一个分区下面的所有数据文件全部删除了。现在不知道怎么办,请求我们给予支持.上去检查发现比较幸运,数据库没有crash,看来直接考虑使用句柄进行恢复

root@CNISORCLSVR # uname -a
SunOS CNISORCLSVR 5.9 Generic_112233-08 sun4u sparc SUNW,Sun-Fire-880
root@CNISORCLSVR # ps -ef|grep lgwr   
  oracle   597     1  0   Mar 05 ?       17:14 ora_lgwr_xifenfei
    root 28069 28043  0 18:51:17 pts/2    0:00 grep lgwr
root@CNISORCLSVR # ls -ltr
total 189348454
-r--r--r--   1 oracle   dba       657920 Apr 26  2002 12
c---------   1 root     sys       13, 12 Mar 27  2004 8
c---------   1 root     sys       13, 12 Mar 27  2004 10
-rw-r-----   0 oracle   dba      34359730176 Nov 12  2013 291
-rw-r-----   0 oracle   dba      1073750016 Nov 13  2013 293
D---------   1 root     root           0 Mar  5 19:31 11
-rw-r-----   1 oracle   dba         1758 Mar  5 22:04 9
-rw-rw----   1 oracle   dba           24 Mar  5 22:04 13
s---------   0 root     root           0 Mar  8 00:45 14
-rw-r-----   1 oracle   dba      1887444992 Mar 12 03:27 289
-rw-r-----   1 oracle   dba      943726592 Mar 12 11:17 290
-rw-r-----   0 oracle   dba      4294975488 Mar 13 00:09 292
-rw-r-----   0 oracle   dba      268443648 Mar 13 01:33 288
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 279
-rw-r-----   1 oracle   dba      134225920 Mar 13 01:33 278
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 269
-rw-r-----   1 oracle   dba      268443648 Mar 13 01:33 267
-rw-r-----   1 oracle   dba      148119552 Mar 13 01:33 266
-rw-r-----   1 oracle   dba      10493952 Mar 13 01:33 265
-rw-r-----   1 oracle   dba      26222592 Mar 13 01:33 264
-rw-r-----   1 oracle   dba      62922752 Mar 13 01:33 263
-rw-r-----   1 oracle   dba      20979712 Mar 13 01:33 262
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 287
-rw-r-----   1 oracle   dba      209723392 Mar 13 01:33 285
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 283
-rw-r-----   1 oracle   dba      67117056 Mar 13 01:33 282
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 281
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 280
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 276
-rw-r-----   0 oracle   dba      1073750016 Mar 13 01:33 275
-rw-r-----   0 oracle   dba      2214600704 Mar 13 01:33 274
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 273
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 272
c---------   1 root     sys       13,  2 Mar 13 02:00 5
c---------   1 root     sys       13,  2 Mar 13 02:00 4
c---------   1 root     sys       13,  2 Mar 13 02:00 3
c---------   1 root     sys       13,  2 Mar 13 02:00 2
c---------   1 root     sys       13,  2 Mar 13 02:00 1
c---------   1 root     sys       13,  2 Mar 13 02:00 0
--w-------   1 oracle   dba      4640842 Mar 13 04:43 7
--w-------   1 oracle   dba      4640842 Mar 13 04:43 6
-rw-r-----   0 oracle   dba      1207967744 Mar 13 18:21 271
-rw-r-----   0 oracle   dba      15929974784 Mar 13 18:39 284
-rw-r-----   0 oracle   dba      134225920 Mar 13 18:45 277
-rw-r-----   0 oracle   dba      2122326016 Mar 13 18:46 286
-rw-r-----   0 oracle   dba      9261031424 Mar 13 18:47 270
-rw-r-----   0 oracle   dba      18253619200 Mar 13 18:47 268
-rw-r-----   1 oracle   dba      134225920 Mar 13 18:51 261
-rw-r-----   1 oracle   dba      524296192 Mar 13 18:51 260
-rw-r-----   1 oracle   dba      104858112 Mar 13 18:52 259
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 258
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 257
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 256

SQL> select file#,name from v$datafile wehre name like '/disk%';

     FILE# NAME
---------- --------------------------------------------------
         9 /disk/oradata/xifenfei/xifenfei.dbf
        10 /disk/oradata/xifenfei/CSSN.dbf
        11 /disk/oradata/xifenfei/NCSSN.dbf
        12 /disk/oradata/xifenfei/CSIC_RDS.dbf
        13 /disk/oradata/xifenfei/CSIC_CSSN.dbf
        14 /disk/oradata/xifenfei/CNIS_I.dbf
        15 /disk/oradata/xifenfei/CNIS.dbf
        16 /disk/oradata/xifenfei/TRSWCM6_CSSN.dbf
        17 /disk/oradata/xifenfei/TRSWCM6_CSSN_PLUGINS.dbf
        18 /disk/oradata/xifenfei/DIGIREF.dbf
        20 /disk/oradata/xifenfei/TRSWCM.dbf
        21 /disk/oradata/xifenfei/TRSWCM52_NSLC.dbf
        22 /disk/oradata/xifenfei/TRSWCM52_PLUGINS_NSLC.dbf
        24 /disk/oradata/xifenfei/TRSWCM_PLUGINS.dbf
        25 /disk/oradata/xifenfei/CNIS_ALL.dbf
        27 /disk/oradata/xifenfei/undotbs01.dbf
        28 /disk/oradata/xifenfei/TRS_IDS02.dbf
        29 /disk/oradata/xifenfei/xdb02.dbf

在solaris中比较郁闷,虽然进入了fd目录,但是无法知道哪些文件句柄是删除,哪些是正常的,因此没有办法，只能使用lsof进一步分析

root@CNISORCLSVR # pkgadd -d lsof-4.80-sol9-sparc-local

The following packages are available:
  1  IBMlsof     lsof
                 (sparc) 4.80

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: all

Processing package instance <IBMlsof> from </tmp/lsof-4.80-sol9-sparc-local>

lsof
(sparc) 4.80
Vic Abell
Using </usr/local> as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.

The following files are already installed on the system and are being
used by another package:
* /usr/local/bin <attribute change only>

* - conflict with a file which does not belong to any package.

Do you want to install these conflicting files [y,n,?,q] y
## Checking for setuid/setgid programs.

The following files are being installed with setuid and/or setgid
permissions:
 /usr/local/bin/lsof <setgid sys>
 /usr/local/bin/sparcv7/lsof <setgid sys>
 /usr/local/bin/sparcv9/lsof <setgid sys>

Do you want to install these as setuid/setgid files [y,n,?,q] y

Installing lsof as <IBMlsof>

## Installing part 1 of 1.
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof
/usr/local/doc/lsof/00.README.FIRST
/usr/local/doc/lsof/00CREDITS
/usr/local/doc/lsof/00DCACHE
/usr/local/doc/lsof/00DIALECTS
/usr/local/doc/lsof/00DIST
/usr/local/doc/lsof/00FAQ
/usr/local/doc/lsof/00LSOF-L
/usr/local/doc/lsof/00MANIFEST
/usr/local/doc/lsof/00PORTING
/usr/local/doc/lsof/00QUICKSTART
/usr/local/doc/lsof/00README
/usr/local/doc/lsof/00TEST
/usr/local/doc/lsof/00XCONFIG
/usr/local/doc/lsof/lsof.man
/usr/local/man/man8/lsof.8
[ verifying class <none> ]

Installation of <IBMlsof> was successful.

root@CNISORCLSVR # ./lsof -p 597
COMMAND PID   USER   FD   TYPE        DEVICE    SIZE/OFF   NODE NAME
oracle  597 oracle  cwd   VDIR          85,5        2048 106299 /export/home/oracle/app/product/9.2.0/dbs
oracle  597 oracle  txt   VREG          85,5    61272672   2332 /export/home/oracle/app/product/9.2.0/bin/oracle
…………
oracle  597 oracle  260u  VREG          85,5   524296192 106517 /export/home/oracle/oradata/xifenfei/system01.dbf
oracle  597 oracle  261u  VREG          85,5   134225920 106518 /export/home/oracle/oradata/xifenfei/undotbs01.dbf
…………
oracle  597 oracle  268u  VREG        118,70 18253619200  109 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle  597 oracle  269u  VREG        118,70   134225920  110 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle  597 oracle  270u  VREG        118,70  9261031424  111 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
…………
oracle  597 oracle  293u  VREG        118,70  1073750016   14 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)

到这一步,基本上定位/disk部分是我们需要恢复的数据,从而可以定位到句柄,然后结合数据文件信息,直接使用cp命令恢复出来文件.然后数据库层面recover并且online.

cd /proc/597/fd
cp 269 /disk/oradata/cnisora2/CSSN.dbf
chown oracle:dba /disk/oradata/xifenfei/CSSN.dbf

SQL> recover datafile 10;
ORA-00283: 恢复会话因错误而取消
ORA-01124: 无法恢复数据文件 10 - 文件在使用中或在恢复中
ORA-01110: 数据文件 10: '/disk/oradata/xifenfei/CSSN.dbf'


SQL> alter database datafile 10 offline;

数据库已更改。

SQL> recover datafile 10;
完成介质恢复。
SQL> alter database datafile 10 online;

数据库已更改。

至此基本上恢复完成,万幸是数据库没有crash,遇到此类问题,千万不要盲目关闭数据库.另外数据库备份重于一切

file 1 block 128 corrupted system回滚段坏块修复

Posted on by

有个数据库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更加严格

ORA-01172 ORA-01151 故障恢复

Posted on by

有客户存储异常断电,导致数据库启动报ORA-01172错,导致数据库无法open
数据库启动报ORA-01172错误

Wed Mar 23 14:16:23 2016
ALTER DATABASE OPEN
Wed Mar 23 14:16:24 2016
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Wed Mar 23 14:16:24 2016
Started redo scan
Wed Mar 23 14:16:25 2016
Completed redo scan
 62588 redo blocks read, 15 data blocks need recovery
Wed Mar 23 14:16:25 2016
Started redo application at
 Thread 1: logseq 15050, block 2, scn 2439828667
Wed Mar 23 14:16:25 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 15050 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/orcl/redo01.log
Wed Mar 23 14:16:25 2016
Completed redo application
Wed Mar 23 14:16:25 2016
RECOVERY OF THREAD 1 STUCK AT BLOCK 26185 OF FILE 3
Wed Mar 23 14:16:25 2016
RECOVERY OF THREAD 1 STUCK AT BLOCK 69385 OF FILE 3
Wed Mar 23 14:16:25 2016
RECOVERY OF THREAD 1 STUCK AT BLOCK 566 OF FILE 2
Wed Mar 23 14:16:25 2016
RECOVERY OF THREAD 1 STUCK AT BLOCK 89 OF FILE 2
Wed Mar 23 14:16:25 2016
RECOVERY OF THREAD 1 STUCK AT BLOCK 53769 OF FILE 3
Wed Mar 23 14:16:26 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p012_6540.trc:
ORA-01172: recovery of thread 1 stuck at block 566 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
Wed Mar 23 14:16:26 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p008_6532.trc:
ORA-01172: recovery of thread 1 stuck at block 53769 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Wed Mar 23 14:16:26 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p011_6538.trc:
ORA-01172: recovery of thread 1 stuck at block 69385 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Wed Mar 23 14:16:26 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p005_6526.trc:
ORA-01172: recovery of thread 1 stuck at block 26185 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Wed Mar 23 14:16:27 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p014_6544.trc:
ORA-01172: recovery of thread 1 stuck at block 89 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
Wed Mar 23 14:16:27 2016
Aborting crash recovery due to slave death, attempting serial crash recovery
Wed Mar 23 14:16:27 2016
Beginning crash recovery of 1 threads
Wed Mar 23 14:16:27 2016
Started redo scan
Wed Mar 23 14:16:27 2016
Completed redo scan
 62588 redo blocks read, 15 data blocks need recovery
Wed Mar 23 14:16:27 2016
Started redo application at
 Thread 1: logseq 15050, block 2, scn 2439828667
Wed Mar 23 14:16:27 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 15050 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/orcl/redo01.log
RECOVERY OF THREAD 1 STUCK AT BLOCK 566 OF FILE 2
Wed Mar 23 14:16:27 2016
Aborting crash recovery due to error 1172
Wed Mar 23 14:16:27 2016
Errors in file /oracle/admin/orcl/udump/orcl_ora_6514.trc:
ORA-01172: recovery of thread 1 stuck at block 566 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

ALTER DATABASE RECOVER datafile 1 报错
尝试recover datafile 1之后报ORA-600 kcbrapply_4,ORA-600 kcfrbd_3,ORA-600 kcbrapply_12等错误,从报错信息看,出现这些错误的原因,是由于断电导致坏块引起.

Thu Mar 24 21:50:18 2016
ALTER DATABASE RECOVER  datafile 1  
Thu Mar 24 21:50:18 2016
Media Recovery Start
 parallel recovery started with 15 processes
Thu Mar 24 21:50:18 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 15050 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/orcl/redo01.log
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p004_13391.trc:
ORA-00600: internal error code, arguments: [kcbrapply_4], [2], [], [], [], [], [], []
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p010_13403.trc:
ORA-00600: internal error code, arguments: [kcbrapply_4], [0], [], [], [], [], [], []
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p000_13383.trc:
ORA-00600: internal error code, arguments: [kcbrapply_4], [0], [], [], [], [], [], []
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p009_13401.trc:
ORA-00600: internal error code, arguments: [kcbrapply_4], [3], [], [], [], [], [], []
Thu Mar 24 21:50:19 2016
Hex dump of (file 1, block 61562) in trace file /oracle/admin/orcl/bdump/orcl_p001_13385.trc
Corrupt block relative dba: 0x0040f07a (file 1, block 61562)
Bad header found during media recovery
Data in bad block:
 type: 0 format: 0 rdba: 0xf07a0000
 last change scn: 0x916c.dc4b0040 seq: 0x0 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0xb088
 consistency value in tail: 0x06010fc1
 check value in block header: 0x601
 block checksum disabled
Thu Mar 24 21:50:19 2016
Hex dump of (file 1, block 55706) in trace file /oracle/admin/orcl/bdump/orcl_p014_13411.trc
Corrupt block relative dba: 0x0040d99a (file 1, block 55706)
Bad header found during media recovery
Data in bad block:
 type: 0 format: 0 rdba: 0xd99a0000
 last change scn: 0x916c.e1ad0040 seq: 0x0 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0xa520
 consistency value in tail: 0x06012222
 check value in block header: 0x601
 block checksum disabled
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p006_13395.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [3342335], [1], [0], [64000], [], []
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p003_13389.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [3932159], [1], [0], [64000], [], []
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p002_13387.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [2293759], [1], [0], [64000], [], []
Reread of rdba: 0x0040d99a (file 1, block 55706) found valid data
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p014_13411.trc:
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], []
Thu Mar 24 21:50:19 2016
Reread of rdba: 0x0040f07a (file 1, block 61562) found valid data
Thu Mar 24 21:50:19 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p001_13385.trc:
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p014_13411.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9782BF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p006_13395.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9C82BF4] [] []
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [3342335], [1], [0], [64000], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p009_13401.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9A02BF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_4], [3], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p003_13389.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9F02AF4] [] []
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [3932159], [1], [0], [64000], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p004_13391.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xBA182AF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_4], [2], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p010_13403.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xBA402AF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_4], [0], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p000_13383.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9282AF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_4], [0], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p001_13385.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+450] [SIGSEGV] [Address not mapped to object] [0xB9C82AF4] [] []
ORA-00600: internal error code, arguments: [kcbrapply_12], [], [], [], [], [], [], []
Thu Mar 24 21:50:23 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p002_13387.trc:
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 11042)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/oracle/oradata/orcl/system01.dbf'
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kcfrbd_3], [1], [2293759], [1], [0], [64000], [], []

ALTER DATABASE RECOVER datafile 3 报错
该文件恢复主要报ORA-600 kcbrsearchflist_2,ORA-600 kdxlin:psno out of range,ORA-600 kcbs_dump_adv_state等错误

Thu Mar 24 21:52:04 2016
ALTER DATABASE RECOVER  datafile 3  
Thu Mar 24 21:52:04 2016
Media Recovery Start
 parallel recovery started with 15 processes
Thu Mar 24 21:52:04 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 15050 Reading mem 0
  Mem# 0 errs 0: /oracle/oradata/orcl/redo01.log
Thu Mar 24 21:52:05 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p007_13462.trc:
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], []
Thu Mar 24 21:52:05 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p001_13450.trc:
ORA-00600: internal error code, arguments: [kcbrsearchflist_2], [], [], [], [], [], [], []
Thu Mar 24 21:52:05 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p007_13462.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9F076F4] [] []
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], []
Thu Mar 24 21:52:05 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p001_13450.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9C874F4] [] []
ORA-00600: internal error code, arguments: [kcbrsearchflist_2], [], [], [], [], [], [], []
Thu Mar 24 21:52:06 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p007_13462.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9F066F4] [] []
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9F076F4] [] []
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], []
Thu Mar 24 21:52:06 2016
Errors in file /oracle/admin/orcl/bdump/orcl_p001_13450.trc:
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9C864F4] [] []
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+464] [SIGSEGV] [Address not mapped to object] [0xB9C874F4] [] []
ORA-00600: internal error code, arguments: [kcbrsearchflist_2], [], [], [], [], [], [], []

恢复过程

SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size          1220432 bytes
Variable Size             369098928 bytes
Database Buffers         1761607680 bytes
Redo Buffers               15556608 bytes
Database mounted.
SQL> select file# from v$datafile;

     FILE#
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> recover datafile 1;
ORA-03113: end-of-file on communication channel

SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size          1220432 bytes
Variable Size             369098928 bytes
Database Buffers         1761607680 bytes
Redo Buffers               15556608 bytes
Database mounted.

SQL> recover datafile 3;
ORA-03113: end-of-file on communication channel

SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size          1220432 bytes
Variable Size             369098928 bytes
Database Buffers         1761607680 bytes
Redo Buffers               15556608 bytes
Database mounted.
SQL> recover datafile 5;
Media recovery complete.
SQL> recover datafile 6;
Media recovery complete.
SQL> recover datafile 4;
Media recovery complete.
SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [57], [11], [], [], [], [],
[]

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

这次运气不错,system坏的是mon_mods$,undo异常可以重建,基本上可以说没有数据丢失,数据库恢复完成.
重要的库,通过open过程报错信息,分析可能的坏块所属对象,然后确定处理方法,以免造成永久性数据块损坏.

csc(0x0000.1f8adab3) higher than block scn(0x0000.00000000)

Posted on by

数据库虽然正常open了，但是由于system有坏块，导致数据库导出有部分表报错，客户希望通过修复坏块完美解决该问题
exp-ORA-1578

bbed检查system报坏块

C:\Users\FAL>dbv file=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on 星期六 5月 14 15:40:55 2016

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

DBVERIFY - 开始验证: FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
csc(0x0000.1f8adab3) higher than block scn(0x0000.00000000)
页 122146 失败, 校验代码为 6054


DBVERIFY - 验证完成

检查的页总数: 252160
处理的页总数 (数据): 178921
失败的页总数 (数据): 0
处理的页总数 (索引): 52576
失败的页总数 (索引): 1
处理的页总数 (其他): 3201
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17462
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 529420540 (0.529420540)

bbed修复坏块

C:\Users\FAL>bbed password=blockedit filename=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

BBED: Release 2.0.0.0.0 - Limited Production on Sat May 14 15:37:01 2016

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

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

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 2
        BLOCK#          2

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0

 ub4 tailchk                                @8188


BBED> set block 122147
        BLOCK#          122147

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 3720 bytes                   @20

 struct kdxle, 32 bytes                     @3740

 b2 kd_off[188]                             @3772

 ub1 freespace[1534]                        @4148

 ub1 rowdata[2442]                          @5682

 ub4 tailchk                                @8188


BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0041dd22
   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       0x6cee
   ub2 spare3_kcbh                          @18       0x0000

BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146

Block Checking: DBA = 4316450, Block Type = KTB-managed data block
Found block already marked corrupted

DBVERIFY - Verification complete

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

BBED> set offset 8188
        OFFSET          8188

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 3720 bytes                   @20

 struct kdxle, 32 bytes                     @3740

 b2 kd_off[188]                             @3772

 ub1 freespace[1534]                        @4148

 ub1 rowdata[2442]                          @5682

 ub4 tailchk                                @8188


BBED> d
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff060000

 <32 bytes per line>


BBED> set mode edit
        MODE            Edit

BBED> m /x 01 offset 14
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets:   14 to  525           Dba:0x00000000
------------------------------------------------------------------------
 0104ee6c 00000200 00003500 0000b3da 8a1f0000 00009a00 030021dd 41000900
 0700443d 0600531c c000628b 010000c0 00002c3f 5a1f0300 0000ca5e 05002b0a
 c0008685 01000080 000073d7 8a1f0400 200057a2 0500d530 c000a88e 2c000080
 0000b3d7 8a1f0a00 01002848 08001101 c00084be 29000080 0000e3d7 8a1f0800
 0d00d612 06008f0a c000258d 3c000080 000026d8 8a1f0100 1f0063c1 04006007
 c0002887 4b000080 000046d8 8a1f0700 1e005403 0600a707 c000bd8e 40000080
 00006bd8 8a1f0a00 0b000c48 08001301 c00084be 41000080 0000a2d8 8a1f0600
 1b0055e6 0500c508 c0000f8b 23000080 0000fad8 8a1f0300 0600ca5e 0500220a
 c0008685 34000080 000040d9 8a1f0800 1300d412 0600900a c000258d 2e000080
 000064d9 8a1f0600 0b0092e6 0500c608 c0000f8b 1c000080 000089d9 8a1f0a00
 05001d48 08001401 c00084be 05000080 0000aed9 8a1f0400 100030a2 0500d730
 c000a88e 0e000080 000016da 8a1f0800 1700c712 0600910a c000258d 33000080
 000022da 8a1f0200 1100450d 0500930c c000c085 28000080 00002fda 8a1f0200
 0800610d 0500940c c000c085 20000080 00004eda 8a1f0700 12003303 0600aa07
 c000bd8e 0a000120 0f00b7da 8a1f0a00 13000048 08001501 c00084be 07000220
 1e00f5da 8a1f0900 07000b44 0600650f c000968d 05000120 0f0004db 8a1f0300

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 122147:
current = 0x6cee, required = 0x6cee

BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146


DBVERIFY - Verification complete

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


BBED> m /x c1da8a1f offset 8
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 c1da8a1f 00000104 ee6c0000 02000000 35000000 b3da8a1f 00000000 9a000300
 21dd4100 09000700 443d0600 531cc000 628b0100 00c00000 2c3f5a1f 03000000
 ca5e0500 2b0ac000 86850100 00800000 73d78a1f 04002000 57a20500 d530c000
 a88e2c00 00800000 b3d78a1f 0a000100 28480800 1101c000 84be2900 00800000
 e3d78a1f 08000d00 d6120600 8f0ac000 258d3c00 00800000 26d88a1f 01001f00
 63c10400 6007c000 28874b00 00800000 46d88a1f 07001e00 54030600 a707c000
 bd8e4000 00800000 6bd88a1f 0a000b00 0c480800 1301c000 84be4100 00800000
 a2d88a1f 06001b00 55e60500 c508c000 0f8b2300 00800000 fad88a1f 03000600
 ca5e0500 220ac000 86853400 00800000 40d98a1f 08001300 d4120600 900ac000
 258d2e00 00800000 64d98a1f 06000b00 92e60500 c608c000 0f8b1c00 00800000
 89d98a1f 0a000500 1d480800 1401c000 84be0500 00800000 aed98a1f 04001000
 30a20500 d730c000 a88e0e00 00800000 16da8a1f 08001700 c7120600 910ac000
 258d3300 00800000 22da8a1f 02001100 450d0500 930cc000 c0852800 00800000
 2fda8a1f 02000800 610d0500 940cc000 c0852000 00800000 4eda8a1f 07001200
 33030600 aa07c000 bd8e0a00 01200f00 b7da8a1f 0a001300 00480800 1501c000
 84be0700 02201e00 f5da8a1f 09000700 0b440600 650fc000 968d0500 01200f00

 <32 bytes per line>

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


BBED> sum apply
Check value for File 0, Block 122147:
current = 0x7364, required = 0x7364


BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146


DBVERIFY - Verification complete

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

dbv再次验证，坏块完美修复

C:\Users\FAL>dbv file=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on 星期六 5月 14 22:00:06 2016

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

DBVERIFY - 开始验证: FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 259840
处理的页总数 (数据): 181934
失败的页总数 (数据): 0
处理的页总数 (索引): 57381
失败的页总数 (索引): 0
处理的页总数 (其他): 3218
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17307
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 530218225 (0.530218225)

通过修复之后重新导出数据
exp-ok

参考相似篇章:file 1 block 128 corrupted/坏块恢复—system rollback坏块修复

存储异常导致数据库不能启动恢复

Posted on by

xx医院存储突然掉线,导致数据库异常,现场工程师折腾了一天,问题依旧没有解决,无奈之下找到我们,希望我们能够帮忙恢复数据库.
启动报ORA-00600[2131]错误

Fri Nov 06 14:50:59 2015
ALTER DATABASE   MOUNT
This instance was first to mount
Fri Nov 06 14:50:59 2015
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.4)(PORT=1521))' SCOPE=MEMORY SID='xifenfei1';
NOTE: Loaded library: System 
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13221.trc  (incident=191085):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
Incident details in: /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_191085/xifenfei1_ora_13221_i191085.trc
Fri Nov 06 14:51:10 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE   MOUNT...

出现该错误的原因是由于：We are attempting to write a controlfile checkpoint progress record, but find we do not have the progress record generating this exception.由于控制文件异常导致,出现此类情况,我们一般使用单个控制文件一次尝试,如果都不可以考虑重建控制文件

由于坏块(逻辑/物理)导致数据库实例恢复无法进行

Beginning crash recovery of 2 threads
Started redo scan
kcrfr_rnenq: use log nab 393216
kcrfr_rnenq: use log nab 2
Completed redo scan
 read 4427 KB redo, 500 data blocks need recovery
Started redo application at
 Thread 1: logseq 5731, block 391398
 Thread 2: logseq 4252, block 520815
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5731 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_2.266.835331047
Recovery of Online Redo Log: Thread 2 Group 8 Seq 4252 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_8.331.835330421
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197486):
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197486/xifenfei1_ora_13770_i197486.trc
Fri Nov 06 15:03:09 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc  (incident=197487):
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_197487/xifenfei1_ora_13770_i197487.trc
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 65207)
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 81045
ORA-00600: internal error code, arguments: [kdxlin:psno out of range], [], [], [], [], [], [], [], [], [], [], []
Recovery of Online Redo Log: Thread 2 Group 3 Seq 4253 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_3.332.835330505
Hex dump of (file 14, block 62536) in trace file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc
Reading datafile '+DATA/xifenfei/datafile/ht01.dbf' for corruption at rdba: 0x0380f448 (file 14, block 62536)
Reread (file 14, block 62536) found same corrupt data (logically corrupt)
RECOVERY OF THREAD 1 STUCK AT BLOCK 62536 OF FILE 14
Fri Nov 06 15:03:13 2015
Abort recovery for domain 0
Aborting crash recovery due to error 1172
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
Abort recovery for domain 0
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13770.trc:
ORA-01172: recovery of thread 1 stuck at block 62536 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

查看资料发现和Bug 14301592 – Several errors by corrupt blocks shifted by 2 bytes in buffer cache during recovery caused by INDEX redo apply，可以通过ALLOW 1 CORRUPTION临时解决

使用ALLOW 1 CORRUPTION进行恢复,出现ORA-07445[kdxlin]错误

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_3.332.835330505     
ORA-00279: change 700860458 generated at 11/05/2015 21:20:15 needed for thread
1
ORA-00289: suggestion : +ARCHIVE/xifenfei/xifenfei_1_5731_835324843.arc
ORA-00280: change 700860458 for thread 1 is in sequence #5731


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/xifenfei/onlinelog/group_2.266.835331047
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
70104)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATA/xifenfei/datafile/sysaux.257.835324753'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 82289
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB572] [Address not mapped to object] []


ORA-01112: media recovery not started

ORA-07445[kdxlin()+4088]未找到类似说明,到了这一步,无法简单的恢复成功,只能通过设置隐含参数跳过实例恢复,尝试resetlog库

通过设置_allow_resetlogs_corruption参数继续恢复

SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.

Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927],
[0], [700860464], [], [], [], [], [], [], []
Process ID: 13563
Session ID: 157 Serial number: 3

alert日志报错

Fri Nov 06 19:26:39 2015
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc  (incident=319140):
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Incident details in:/home/app/oracle/diag/rdbms/xifenfei/xifenfei1/incident/incdir_319140/xifenfei1_ora_13563_i319140.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Redo thread 2 internally disabled at seq 1 (CKPT)
ARC1: Archiving disabled thread 2 sequence 1
Archived Log entry 9956 added for thread 2 sequence 1 ID 0x0 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Errors in file /home/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_13563.trc:
ORA-00600: internal error code, arguments: [kclchkblk_4], [0], [700869927], [0], [700860464], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 13563): terminating the instance due to error 600
Fri Nov 06 19:26:42 2015
Instance terminated by USER, pid = 13563
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (13563) as a result of ORA-1092
Fri Nov 06 19:26:42 2015
ORA-1092 : opitsk aborting process

这里是比较熟悉的ora-600[kclchkblk_4]错误,和ora-600[2662]错误类似,需要调整scn,由于数据库版本为11.2.0.4,无法使用常规方法调整scn,在修改控制文件,oradebug,bbed方法可供选择

使用oradebug方法处理
因为是asm环境,其他方法处理起来都相对麻烦

[oracle@wisetop1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 6 19:30:59 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.

Total System Global Area 7315603456 bytes
Fixed Size                  2267384 bytes
Variable Size            2566915848 bytes
Database Buffers         4731174912 bytes
Redo Buffers               15245312 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x06001AE70 4 0x2FAF0800
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 2FAF0800
SQL> alter database open;

Database altered.

至此数据库open成功,后续就是处理一些坏块的工作,并建议客户逻辑重建库.