Oracleデータベースの深層（２）−BBEDを利用したデータブロックの障害＆復旧シミュレーション

注意：この方法はテスト検証用のみのため、実際の運用データファイルで試さないで下さい！！

１、サンプル表を作成する

SQL> create table bbed tablespace users as select * from dba_tables;

Table created.

SQL> select count(*) from bbed;

  COUNT(*)
----------
      1650

SQL> col segment_name form a10;
SQL> select segment_name,file_id,block_id from dba_extents where segment_name='BBED';

SEGMENT_NA    FILE_ID   BLOCK_ID
---------- ---------- ----------
BBED                4        401
BBED                4        409
BBED                4        417
BBED                4        425
BBED                4        433
BBED                4        441
BBED                4        449

7 rows selected.

２、bbedの設定ファイルを作成

sys@ORA10G> select file#,name,bytes from v$datafile;

FILE# NAME                                              BYTES
----- -------------------------------------------------------------
    1 /u01/oracle/app/oradata/ora10g/system01.dbf       534773760
    2 /u01/oracle/app/oradata/ora10g/undotbs01.dbf      188743680
    3 /u01/oracle/app/oradata/ora10g/sysaux01.dbf       429916160
    4 /u01/oracle/app/oradata/ora10g/users01.dbf          5242880
    5 /u01/oracle/app/oradata/ora10g/example01.dbf      104857600
    6 /u01/oracle/app/oradata/ora10g/dbstatspack01.dbf  152043520

6 rows selected.

[oracle@sst bbed]$ more filelist.txt
1 /u01/oracle/app/oradata/ora10g/system01.dbf       534773760
2 /u01/oracle/app/oradata/ora10g/undotbs01.dbf      188743680
3 /u01/oracle/app/oradata/ora10g/sysaux01.dbf       429916160
4 /u01/oracle/app/oradata/ora10g/users01.dbf          5242880
5 /u01/oracle/app/oradata/ora10g/example01.dbf      104857600
6 /u01/oracle/app/oradata/ora10g/dbstatspack01.dbf  152043520

[oracle@sst bbed]$ more par.bbd
blocksize=8192
listfile=filelist.txt
mode=edit

３、データベースをSHUTDOWNし、データファイルのバックアップを取る

４、bbedを実行する

[oracle@sst bbed]$ bbed parfile=par.bbd
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Oct 26 13:24:20 2009

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

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

BBED> set file 4
        FILE#           4

BBED> show
        FILE#           4
        BLOCK#          1
        OFFSET          0
        DBA             0x01000001 (16777217 4,1)
        FILENAME        /u01/oracle/app/oradata/ora10g/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE        filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

５、もし操作が誤ったらをロールバックする

BBED> modify /x 0x00c00011
 File: /u01/oracle/app/oradata/ora10g/users01.dbf (4)
 Block: 1                Offsets:    0 to  511           Dba:0x01000001
------------------------------------------------------------------------
 00c00011 01000001 00000000 00000104 43550000 00000000 0001200a b25893ee
 4f524131 30470000 ac2e0000 80020000 00200000 04000300 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 46290000 00000000 b0ef8421 c7ce7a29 61c84000 00000000 63d37a29
 b3cf4000 00000000 01000000 30010000 fe86cc29 2f010000 b3cf4000 00000000
 63d37a29 01000000 02000000 02000000 10004c00 02000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 80020000 00000000
 00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000
 00000000 00000000 00000000 00000000 04000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 7660c128 7bce0600 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 89746700 00000000 a389cc29 01000a00 4b000000 c4e40000 1000d2bf

 <32 bytes per line>

BBED> revert
All changes made in this session will be rolled back. Proceed? (Y/N) y
Reverted file '/u01/oracle/app/oradata/ora10g/users01.dbf', block 1
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

６、仮にデータブロックを破壊する
BBED> modify 1000 file 4 block 17
 File: /u01/oracle/app/oradata/ora10g/users01.dbf (4)
 Block: 17               Offsets:    0 to  511           Dba:0x01000011
------------------------------------------------------------------------
 03e80000 11000001 afbf0600 00000104 b6410000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 02000000 ffffffff 04000000 03000000 08000000
 01000100 00000000 01000000 00000000 00000000 03000000 ddaec442 ddaec442
 00000000 00000000 00000000 00000000 12000001 00000000 00000000 04000000
 08000000 15000001 00000000 00000000 00000000 01000000 00000000 01000000
 cbc70000 00000000 00000000 11000001 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 57000000 00000000 00000000 00000000 00000000
 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>

確認してみると、先ほど破壊したBlock17に問題がある

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/app/oradata/ora10g/users01.dbf
BLOCK = 17

Block 17 is corrupt
Corrupt block relative dba: 0x01000011 (file 0, block 17)
Bad header found during verification
Data in bad block:
 type: 3 format: 0 rdba: 0x01000011
 last change scn: 0x0000.0006bfaf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xbfaf2001
 check value in block header: 0x41b6
 computed block checksum: 0x4a23


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> quit

下記のOracleツールで確認する結果と同じ：

[oracle@sst bbed]$ dbv file=/u01/oracle/app/oradata/ora10g/users01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Mon Oct 26 13:33:18 2009

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

DBVERIFY - Verification starting : FILE = /u01/oracle/app/oradata/ora10g/users01.dbf
Page 17 is marked corrupt
Corrupt block relative dba: 0x01000011 (file 4, block 17)
Bad header found during dbv:
Data in bad block:
 type: 3 format: 0 rdba: 0x01000011
 last change scn: 0x0000.0006bfaf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xbfaf2001
 check value in block header: 0x41b6
 computed block checksum: 0x4a23



DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 98
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 144
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 334
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 6779312 (0.6779312)

もちろん、この表領域を利用したフルテーブル走査した場合、Blockエラーが出ます。

７、バックアップしたデータファイルを利用して修復する

まず、バックアップしたデータファイルを一覧に追加する
[oracle@sst bbed]$ more filelist.txt
1 /u01/oracle/app/oradata/ora10g/system01.dbf       534773760
2 /u01/oracle/app/oradata/ora10g/undotbs01.dbf      188743680
3 /u01/oracle/app/oradata/ora10g/sysaux01.dbf       429916160
4 /u01/oracle/app/oradata/ora10g/users01.dbf          5242880
5 /u01/oracle/app/oradata/ora10g/example01.dbf      104857600
6 /u01/oracle/app/oradata/ora10g/dbstatspack01.dbf  152043520
7 /u01/oracle/app/oradata/ora10g.bak/users01.dbf          5242880

設定後、bbedを起動する
[oracle@sst bbed]$ bbed parfile=par.bbd
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Mon Oct 26 13:39:06 2009

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

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

BBED> set file 7
        FILE#           7

BBED> show
        FILE#           7
        BLOCK#          1
        OFFSET          0
        DBA             0x01c00001 (29360129 7,1)
        FILENAME        /u01/oracle/app/oradata/ora10g.bak/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE        filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

バックアップファイルから正常ブロックをコピーする。

BBED> copy file 7 block 17 to file 4 block 17;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/app/oradata/ora10g/users01.dbf (4)
 Block: 17               Offsets:    0 to  511           Dba:0x01000011
------------------------------------------------------------------------
 20a20000 11000001 afbf0600 00000104 b6410000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 02000000 ffffffff 04000000 03000000 08000000
 01000100 00000000 01000000 00000000 00000000 03000000 ddaec442 ddaec442
 00000000 00000000 00000000 00000000 12000001 00000000 00000000 04000000
 08000000 15000001 00000000 00000000 00000000 01000000 00000000 01000000
 cbc70000 00000000 00000000 11000001 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 57000000 00000000 00000000 00000000 00000000
 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> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/app/oradata/ora10g/users01.dbf
BLOCK = 17


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

実際のフルテーブル走査してみる（エラーなし）

[oracle@sst bbed]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 26 13:43:11 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size             109053544 bytes
Database Buffers          167772160 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> select count(*) from bbed;

  COUNT(*)
----------
      1650

bbedを利用することで、ブロック障害のシミュレーションやブロック修復に大変役立ちます。