Oracle bbed 实用示例-----File Header Reset - PoleStar_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数
每页帖数
1/1页
返回列表
发起投票
查看: 2950 | 回复: 0
主题： Oracle bbed 实用示例-----File Header Reset - PoleStar
注册用户
等级：中士
经验：207
发帖：80
精华：0
注册：2011-10-19
状态：离线
发送短消息息给tk
加好友发送短消息息给tk
发消息
发表于：
2015-4-20 9:56:06 | [全部帖] [楼主帖]
楼主
一.查看当前环境

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#,'999999999999') 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 onlineSQL> 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 -->4d0000003.3 修改文件5的4个SCN值----修改kscnbasBBED> 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 ............
----修改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>至此，数据库打开，不过并不是完全恢复