Oracle bbed 实用示例-----File Header Reset

一.查看当前环境

1.1 当前控制文件中的SCN号

[oracle@ora10 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 17 10:11:34 2015

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

SQL> conn /as sysdba

Connected.

SQL> col name format a65

SQL> select file#,to_char(checkpoint_change#,'') from v$datafile;

     FILE# TO_CHAR(CHECK

---------- -------------

         1        490652

         2        490652

         3        490652

         4        490652

         5        490652

SQL>

1.2 当前数据文件的状态

SQL> select file#,status,name from v$datafile;

     FILE# STATUS  NAME

---------- ------- -----------------------------------------------------------------

         1 SYSTEM  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf

         2 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf

         3 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf

         4 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf

         5 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf

SQL>

二.模拟故障场景

2.1 将datafile 5 offline

SQL> alter database datafile 5 offline;

Database altered.

SQL> select file#,status,name from v$datafile;

     FILE# STATUS  NAME

---------- ------- -----------------------------------------------------------------

         1 SYSTEM  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf

         2 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.db

                   f

         3 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf

         4 ONLINE  /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf

         5 RECOVER /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf  ---文件5的状态已经被改变

SQL>

2.2 更改文件的SCN号

--做日志切换，多执行几次

SQL> alter system switch logfile;

.........

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

---------- ------------------

         1             491337

         2             491337

         3             491337

         4             491337

         5             490652  --SCN号未变

SQL> 

---查看需要恢复datafile 的SCN:

SQL> select file#,online_status,change# from v$recover_file;

     FILE# ONLINE_    CHANGE#

---------- ------- ----------

         5 OFFLINE     490652

SQL>

2.3 将datafile 5 online

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

SQL>

三.利用bbed修改文件5的scn号

3.1 再利用bbed前，将数据库关闭

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

3.2 利用bbed查看文件1和文件5的scn号

----查看文件的scn号之前，先了解文件中的scn号的4个属性值

　 Oracle根据4个属性来判断datafile 是否和其他的datafile 一致，如果都一致，可以正常操作，如果不一致，那么会报ORA-01113错误。

kscnbas (at offset 484) - SCN of last change to the datafile.
kcvcptim (at offset 492) -Time of the last change to the datafile.
kcvfhcpc (at offset 140) - Checkpoint count.
kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count

这四个属性值存在File header中，即文件的第一个数据块中

---查看文件1的4个SCN属性值

BBED> set file 1

        FILE#           1

BBED> set block 1

        BLOCK#          1

BBED> map /v

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1                                     Dba:0x00400001

------------------------------------------------------------

 Data File Header

 struct kcvfh, 676 bytes                    @0

    struct kcvfhbfh, 20 bytes               @0

    struct kcvfhhdr, 76 bytes               @20

    ub4 kcvfhrdb                            @96

    struct kcvfhcrs, 8 bytes                @100

    ub4 kcvfhcrt                            @108

    ub4 kcvfhrlc                            @112

    struct kcvfhrls, 8 bytes                @116

    ub4 kcvfhbti                            @124

    struct kcvfhbsc, 8 bytes                @128

    ub2 kcvfhbth                            @136

    ub2 kcvfhsta                            @138

    struct kcvfhckp, 36 bytes               @484

    ub4 kcvfhcpc                            @140

    ub4 kcvfhrts                            @144

    ub4 kcvfhccc                            @148

    struct kcvfhbcp, 36 bytes               @152

    ub4 kcvfhbhz                            @312

    struct kcvfhxcd, 16 bytes               @316

    word kcvfhtsn                           @332

    ub2 kcvfhtln                            @336

    text kcvfhtnm[30]                       @338

    ub4 kcvfhrfn                            @368

    struct kcvfhrfs, 8 bytes                @372

    ub4 kcvfhrft                            @380

    struct kcvfhafs, 8 bytes                @384

    ub4 kcvfhbbc                            @392

    ub4 kcvfhncb                            @396

    ub4 kcvfhmcb                            @400

    ub4 kcvfhlcb                            @404

    ub4 kcvfhbcs                            @408

    ub2 kcvfhofb                            @412

    ub2 kcvfhnfb                            @414

    ub4 kcvfhprc                            @416

    struct kcvfhprs, 8 bytes                @420

    struct kcvfhprfs, 8 bytes               @428

    ub4 kcvfhtrt                            @444     

 ub4 tailchk                                @8188    

BBED> print kcvfhckp

struct kcvfhckp, 36 bytes                   @484

   struct kcvcpscn, 8 bytes                 @484

      ub4 kscnbas                           @484      0x00077fd0

      ub2 kscnwrp                           @488      0x0000

   ub4 kcvcptim                             @492      0x33cf444d

   ub2 kcvcpthr                             @496      0x0001

   union u, 12 bytes                        @500

      struct kcvcprba, 12 bytes             @500

         ub4 kcrbaseq                       @500      0x00000010

         ub4 kcrbabno                       @504      0x000000f3

         ub2 kcrbabof                       @508      0x0010

   ub1 kcvcpetb[0]                          @512      0x02

   ub1 kcvcpetb[1]                          @513      0x00

   ub1 kcvcpetb[2]                          @514      0x00

   ub1 kcvcpetb[3]                          @515      0x00

   ub1 kcvcpetb[4]                          @516      0x00

   ub1 kcvcpetb[5]                          @517      0x00

   ub1 kcvcpetb[6]                          @518      0x00

   ub1 kcvcpetb[7]                          @519      0x00

BBED> print kcvfhcpc

ub4 kcvfhcpc                                @140      0x0000004e

BBED> print kcvfhccc

ub4 kcvfhccc                                @148      0x0000004d

BBED>

注意：print打印出来的值和在文件中存储的值可能不是完全一致。在intel 的little endian是低位先存储，即顺序与我们看到的是相反的.利用

dump出数据实际存储值：

BBED> dump /v dba 1,1 offset 484 count 32

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  484 to  515  Dba:0x00400001

-------------------------------------------------------

 d07f0700 00000000 4d44cf33 01000a00 l ......MD....

 10000000 f3000000 1000ffbf 02000000 l ............

 <16 bytes per line>

BBED> dump /v dba 1,1 offset 140 count 32

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  140 to  171  Dba:0x00400001

-------------------------------------------------------

 4e000000 1ecfce33 4d000000 00000000 l N....衔3M.......

 00000000 00000000 00000000 00000000 l ................

 <16 bytes per line>

BBED> 

ub4 kscnbas            @484      0x00077fd0 -->d07f0700 这四个值才是我们要写入文件5中的

ub4 kcvcptim           @492      0x33cf444d -->4d44cf33

ub4 kcvfhcpc           @140      0x0000004e -->4e000000

ub4 kcvfhccc           @148      0x0000004d -->4d000000

3.3 修改文件5的4个SCN值

----修改kscnbas

BBED> modify /x d07f0700 dba 5,1 offset 484

BBED-00209: invalid number (d07f0700)

--报无法修改，看看两个文件的484至487的4个字节值的异同：

BBED> dump /v dba 1,1 offset 484 count 32

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  484 to  515  Dba:0x00400001

-------------------------------------------------------

 d07f0700 00000000 4d44cf33 01000a00 l ......MD....

 10000000 f3000000 1000ffbf 02000000 l ............

 <16 bytes per line>

BBED>

BBED> dump /v dba 5,1 offset 484 count 32

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1       Offsets:  484 to  515  Dba:0x01400001

-------------------------------------------------------

 9c7c0700 0000870a 1d3fcf33 01000000 l .|.......?....

 09000000 02000000 1000ffbf 02000000 l ..............

 <16 bytes per line>

BBED>

--观察文件1和文件5的前四个字节的异同，只修改484，485前两字节

BBED> modify /x d07f dba 5,1 offset 484

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1                Offsets:  484 to  515           Dba:0x01400001

------------------------------------------------------------------------

 d07f0700 0000870a 1d3fcf33 01000000 09000000 02000000 1000ffbf 02000000 

 <32 bytes per line>

BBED>

----修改kcvcptim

----观察文件1和文件5的前四个字节的异同

BBED> dump /v dba 1,1 offset 492 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  492 to  495  Dba:0x00400001

-------------------------------------------------------

 4d44cf33                            l MD

 <16 bytes per line>

BBED> dump /v dba 5,1 offset 492 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1       Offsets:  492 to  495  Dba:0x01400001

-------------------------------------------------------

 1d3fcf33                            l .?

 <16 bytes per line>

BBED>

同理，只需修改492，493两字节的值

BBED> modify /x 4d44 dba 5,1 offset 492

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1                Offsets:  492 to  495           Dba:0x01400001

------------------------------------------------------------------------

 4d44cf33 

 <32 bytes per line>

BBED>

----修改kcvfhcpc

----观察前四个字节的异同

BBED> dump /v dba 1,1 offset 140 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  140 to  143  Dba:0x00400001

-------------------------------------------------------

 4e000000                            l N...

 <16 bytes per line>

BBED> dump /v dba 5,1 offset 140 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1       Offsets:  140 to  143  Dba:0x01400001

-------------------------------------------------------

 49000000                            l I...

 <16 bytes per line>

BBED> 

--只需修改前两个字节

BBED> modify /x 4e00 dba 5,1 offset 140

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1                Offsets:  140 to  143           Dba:0x01400001

------------------------------------------------------------------------

 4e000000 

 <32 bytes per line>

BBED>

----修改kcvfhccc

----观察前四个字节的异同

BBED> dump /v dba 1,1 offset 148 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf (1)

 Block: 1       Offsets:  148 to  151  Dba:0x00400001

-------------------------------------------------------

 4d000000                            l M...

 <16 bytes per line>

BBED> dump /v dba 5,1 offset 148 count 4

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1       Offsets:  148 to  151  Dba:0x01400001

-------------------------------------------------------

 48000000                            l H...

 <16 bytes per line>

BBED>

----只需修改前两个字节

BBED> modify /x 4d00 dba 5,1 offset 148

 File: /u01/app/oracle/oradata/ORCL/datafile/test_01.dbf (5)

 Block: 1                Offsets:  148 to  151           Dba:0x01400001

------------------------------------------------------------------------

 4d000000 

 <32 bytes per line>

BBED>

3.4 应用修改

BBED> sum apply

Check value for File 5, Block 1:

current = 0xf719, required = 0xf719

BBED>

四.创建控制文件

　　虽然我们修改了datafile header里的几个值，但是v$datafile里的scn并没有改变，因为v$datafile里的scn是从控制文件里读取的。而BBED 不能修改控制文件，所以，还需要重建控制文件。

4.1 用带*._allow_resetlogs_corruption=TRUE的pfile启库到nomount状态

SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

SQL> 

注：如果没有此参数后期open resetlogs 时会报：

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 5 needs more recovery to be consistent

ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

4.2 创建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/redo01.log'  SIZE 50M,

  9    GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/redo02.log'  SIZE 50M,

 10    GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/redo03.log'  SIZE 50M

 11  DATAFILE

 12     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf',

 13     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf',

 14     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf',

 15     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf',

 16     '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

 17  CHARACTER SET AL32UTF8

 18  ;

CREATE CONTROLFILE REUSE DATABASE "orcl" NORESETLOGS  ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01229: data file 5 is inconsistent with logs

ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

SQL>

因为ORA-01229: data file 5 is inconsistent with logs，索引不能用NORESETLOGS创建控制文件，只能用RESETLOGS

SQL> CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/redo01.log'  SIZE 50M,

  9    GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/redo02.log'  SIZE 50M,

 10    GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/redo03.log'  SIZE 50M

 11  DATAFILE

 12     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_bc7lf4bd_.dbf',

 13     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_bc7ld3sf_.dbf',

 14     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_bc7ldql4_.dbf',

 15     '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_bc7lflno_.dbf',

 16     '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

 17  CHARACTER SET AL32UTF8

 18  ;

Control file created.

SQL>

4.3 恢复数据库

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 491472 generated at 01/17/2015 10:24:13 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_01_17/o1_mf_1_9_%u_.arc

ORA-00280: change 491472 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00328: archived log ends at change 491322, need later change 491472

ORA-00334: archived log: '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2015_01_17/o1_mf_1_9_bcnzchrb_.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 5 needs more recovery to be consistent

ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/test_01.dbf'

SQL> alter database open resetlogs;  --

Database altered.

SQL>

至此，数据库打开，不过并不是完全恢复

秒客网

Oracle bbed 实用示例-----File Header Reset

一.查看当前环境

二.模拟故障场景

三.利用bbed修改文件5的scn号

四.创建控制文件

相关文章