Oracle DSI系列 01 DSI初识BBED

DSI是Data Server Internals的缩写,是Oracle公司内部用来培训Oracle售后工程师使用的教材。

1 bbed工具使用
BBED工具介绍
BBED stands for Block Browser and EDitor.
BBED只是一款工具，类似于ultraEdit，单纯的会用BBED来修改数据没有任何意义！关键是要知道为什么要这么改！
在充分了解Block格式和Oracle的各种机制的基础上广泛使用BBED, 用它来帮你构造测试案例，
用它来帮你验证测试结果，用它来帮你深入理解Oracle！

本系列测试版本

oracle：11.2.0.4.0

os：centos 6.5

sid：orcl

bbed的安装--(Oracle Block Brower and EDitor Tool)
用来直接查看和修改数据文件数据的一个工具，是Oracle一款内部工具
oracle 11g中缺bbed包,oracle11g bbed install and example

1.上传（sbbdpt.o ssbbded.o bbedus.msb，该三个文件拷贝oracle的linux64版本的）文件，11g是没有这3个文件，在10g里面有

$ORACLE_HOME/rdbms/lib/ssbbded.o
$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb

1[root@DSI opt]# cp bbedus.msb sbbdpt.o ssbbded.o $ORACLE_HOME/rdbms/lib/.
2[root@DSI opt]# chown -R oracle:oinstall $ORACLE_HOME/rdbms/lib/
3[oracle@DSI ~]$ ll $ORACLE_HOME/rdbms/lib/ssbbded.o
4-rw-r--r-- 1 oracle oinstall 1191 Apr 22 10:35 /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ssbbded.o

执行如下命令：

1[oracle@DSI ~]$ cd $ORACLE_HOME/rdbms/lib
2[oracle@DSI lib]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
3
4Linking BBED utility (bbed)
5rm -f /u01/app/oracle/product/11.2.0/db_1/bin/bbed
6gcc -o /u01/app/oracle/product/11.2.0/db_1/bin/bbed -m64 -z noexecstack -L/u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ -L/u01/app/oracle/product/11.2.0/db_1/lib/ -L/u01/app/oracle/product/11.2.0/db_1/lib/stubs/  /u01/app/oracle/product/11.2.0/db_1/lib/s0main.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ssbbded.o /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/sbbdpt.o `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 -lclntsh  `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/app/oracle/product/11.2.0/db_1/lib/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /u01/app/oracle/product/11.2.0/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/11.2.0/db_1/lib -lm    `cat /u01/app/oracle/product/11.2.0/db_1/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/11.2.0/db_1/lib

2.进入BBED
bbed
blockedit ##默认密码

1[oracle@DSI lib]$ bbed
2Message 112 not found; No message file for product=RDBMS, facility=BBED
3BBED-00113: file not found
4
5[oracle@DSI lib]$ ll /u01/app/oracle/product/11.2.0/db_1/bin/bbed
6-rwxr-xr-x 1 oracle oinstall 255078 Apr 22 10:37 /u01/app/oracle/product/11.2.0/db_1/bin/bbed
7[oracle@DSI lib]$ cp bbedus.msb /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/
8[oracle@DSI lib]$ bbed
9Password: 
10
11BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 22 10:42:37 2019
12
13Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
14
15************* !!! For Oracle Internal Use only !!! ***************
16BBED>

进行bbed参数文件配置

1SQL> select file#||chr(9)||name||chr(9)||bytes from v$datafile;
2
3FILE#||CHR(9)||NAME||CHR(9)||BYTES
4--------------------------------------------------------------------------------
51    /u01/app/oracle/oradata/orcl/system01.dbf    775946240
62    /u01/app/oracle/oradata/orcl/sysaux01.dbf    629145600
73    /u01/app/oracle/oradata/orcl/undotbs01.dbf    1184890880
84    /u01/app/oracle/oradata/orcl/users01.dbf    5242880
9
10[oracle@DSI ~]$ vim par.txt
11blocksize=8192
12listfile=filelist.txt
13mode=edit
14[oracle@DSI ~]$ vim filelist.txt
151    /u01/app/oracle/oradata/orcl/system01.dbf    775946240
162    /u01/app/oracle/oradata/orcl/sysaux01.dbf    629145600
173    /u01/app/oracle/oradata/orcl/undotbs01.dbf    1184890880
184    /u01/app/oracle/oradata/orcl/users01.dbf    5242880
19[oracle@DSI ~]$ pwd
20/home/oracle
21[oracle@DSI ~]$ vim .bash_profile 
22[oracle@DSI ~]$ source .bash_profile 
23[oracle@DSI ~]$ more .bash_profile 
24alias bbed='bbed parfile=par.txt password=blockedit'
25[oracle@DSI ~]$ bbed
26
27BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 22 16:07:15 2019
28
29Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
30
31************* !!! For Oracle Internal Use only !!! ***************
32
33BBED> info
34 File#  Name                                                        Size(blks)
35 -----  ----                                                        ----------
36     1  /u01/app/oracle/oradata/orcl/system01.dbf                        94720
37     2  /u01/app/oracle/oradata/orcl/sysaux01.dbf                        76800
38     3  /u01/app/oracle/oradata/orcl/undotbs01.dbf                      144640
39     4  /u01/app/oracle/oradata/orcl/users01.dbf                           640

初次查看

1BBED> set file 4 block 1
2    FILE#              4
3    BLOCK#             1
4
5BBED> map /v
6 File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
7 Block: 1                                     Dba:0x01000001
8------------------------------------------------------------
9 Data File Header
10
11 struct kcvfh, 860 bytes                    @0       ##头部
12    struct kcvfhbfh, 20 bytes               @0       
13    struct kcvfhhdr, 76 bytes               @20      
14    ub4 kcvfhrdb                            @96      
15    struct kcvfhcrs, 8 bytes                @100     
16    ub4 kcvfhcrt                            @108     
17    ub4 kcvfhrlc                            @112     
18    struct kcvfhrls, 8 bytes                @116     
19    ub4 kcvfhbti                            @124     
20    struct kcvfhbsc, 8 bytes                @128     
21    ub2 kcvfhbth                            @136     
22    ub2 kcvfhsta                            @138     
23    struct kcvfhckp, 36 bytes               @484     
24    ub4 kcvfhcpc                            @140     
25    ub4 kcvfhrts                            @144     
26    ub4 kcvfhccc                            @148     
27    struct kcvfhbcp, 36 bytes               @152     
28    ub4 kcvfhbhz                            @312     
29    struct kcvfhxcd, 16 bytes               @316     
30    sword kcvfhtsn                          @332     
31    ub2 kcvfhtln                            @336     
32    text kcvfhtnm[30]                       @338     
33    ub4 kcvfhrfn                            @368     
34    struct kcvfhrfs, 8 bytes                @372     
35    ub4 kcvfhrft                            @380     
36    struct kcvfhafs, 8 bytes                @384     
37    ub4 kcvfhbbc                            @392     
38    ub4 kcvfhncb                            @396     
39    ub4 kcvfhmcb                            @400     
40    ub4 kcvfhlcb                            @404     
41    ub4 kcvfhbcs                            @408     
42    ub2 kcvfhofb                            @412     
43    ub2 kcvfhnfb                            @414     
44    ub4 kcvfhprc                            @416     
45    struct kcvfhprs, 8 bytes                @420     
46    struct kcvfhprfs, 8 bytes               @428     
47    ub4 kcvfhtrt                            @444     
48
49 ub4 tailchk                                @8188

3.常用命令：set、 find、 dump、 modify 、sum apply、examine、map 、print、 veritfy 

1(1)set file 4 block 32
2    set dba 0x01000020
3    set offset 0           --０表示第一个字节开始
4    set block  1           --1表示第一个块开始
5    set count 8192         --默认是显示512字节
6 (2)find /x 05d67g         --查指定的字符串在指定数据块中的具体位置
7    f                      --find的简写，表示继续从当前位置开始往下查询字符串05d67g
8 (3)dump                   --十六进制查看block
9    dump /v                --查看十六进制内容的同时以文本方式“翻译”十六进制显示的内容，相当于对当前block执行strings命令
10 (4)modify /x d43          --修改指定block,指定offset的数据块块内记录的内容
11 (5)sum apply              --计算修改后的数据块的checksum值，然后写入数据块的offset为16-17的位置
12 (6) map                   --会通过偏移量来显示block里的详细信息，
13      map /v               --可以查看更详细的信息
14      p kcbh
15      p ktbbh
16      p kdbh
17      p kdbt
18      p kcvfh
19      p kcvfh.kcvfhrfn
20      p kcvfh.kcvfhckp
21      p *kdbr[0]  --第一行记录
22      x /rnc      -examine /read number char  用16进制翻译成我们能看懂的文件
23这个输出结果默认是16进制的。我们可以将其修改成其他格式
24/x  Hex
25/d  signed decimal
26/u  unsigned decimal
27/o  Octal
28/c  Character
29/n  Oracle Number
30/t  Oracle Date
31/i  Oracle ROWID

参考博客

https://blog.csdn.net/tianlesoftware/article/details/5006580

https://www.cnblogs.com/polestar/p/4243646.html

1几个常用的
2set 设定当前的环境
3show 查看当前的环境参数，跟sqlplus的同名命令类似。
4dump 列出指定block的内容，dump命令可以将block 的内从显示到屏幕。 每次显示的bytes由count 控制，默认是512 bytes。 使用 /v 选项，可以显示更多详细信息
5find 在指定的block中查找指定的字符串，结果是显示出字符串，及其偏移量--offset，偏移量就是在block中的字节数
6modify 修改指定block的指定偏移量的值，可以在线修改。
7--BBED> modify /c dmm dba 1, 115362 offset 8155
8copy 把一个block的内容copy到另一个block中
9verify 检查当前环境是否有坏块
10--BBED> verify dba 6,15
11sum 计算block的checksum，modify之后block就被标识为坏块，current checksum与reqired checksum不一致，sum命令可以计算出新的checksum并应用到当前块。
12--BBED> sum dba 1,115362 apply
13undo 回滚当前的修改操作，如果手误做错了，undo一下就ok了，回到原来的状态。undo命令是回滚最后一次的操作
14--BBED> undo
15revert 回滚所有之前的修改操作，意思就是 undo all
16--BBED> revert dba 1,115362
17corrupt 将一个block 标记为corrupt，这样db 在操作时就会跳过该block，从而避免错误
18--BBED> corrupt dba 6,15
19--注意： undo 命令不能undo 一个corruption，但是revert 命令却可以
20
21find
22create table yhq(name1 varchar2(100));
23insert into yhq values('yhq is DBA!');
24insert into yhq values('yhq is mysql DBA!');
25
26select
27rowid,
28dbms_rowid.rowid_relative_fno(rowid) rel_fno,
29dbms_rowid.rowid_block_number(rowid) blockno,
30dbms_rowid.rowid_row_number(rowid) rowno
31from yhq;
32----查看block 情况
33SQL> select
34rowid,
35dbms_rowid.rowid_relative_fno(rowid) rel_fno,
36dbms_rowid.rowid_block_number(rowid) blockno,
37dbms_rowid.rowid_row_number(rowid) rowno
38from yhq;  2    3    4    5    6  
39
40ROWID              REL_FNO     BLOCKNO      ROWNO
41------------------ ---------- ---------- ----------
42AAAVRbAABAAAWahAAA        1       91809      0
43AAAVRbAABAAAWahAAB        1       91809      1
44BBED> set file 1 block 91809
45    FILE#              1
46    BLOCK#             91809
47
48BBED> find /c yhq top
49 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
50 Block: 91809            Offsets: 8156 to 8159           Dba:0x004166a1
51------------------------------------------------------------------------
52 79687120 
53
54 <32 bytes per line>
55 --bbed 显示在offset 8156的为位置，我们dump 该offset 看看,如果我们要继续搜索yhq，那么只需要按下f 就可以了，不需要跟参数
56 BBED> dump /v dba 1,91809 offset 8156 count 128
57 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
58 Block: 91809   Offsets: 8156 to 8191  Dba:0x004166a1
59-------------------------------------------------------
60 79687120 6973206d 7973716c 20444241 l yhq is mysql DBA
61 212c0101 0b796871 20697320 44424121 l !,...yhq is DBA!
62 0106f2a6                            l ..
63
64 <16 bytes per line>

BBED解析DB_NAME
DB_NAME数据库名，长度不能超过8个字符，记录在datafile、 redolog和control file中

1BBED> p kcvfh  ##指定具体的元素进行print
2struct kcvfh, 860 bytes                     @0       
3   struct kcvfhbfh, 20 bytes                @0       
4      ub1 type_kcbh                         @0        0x0b
5      ub1 frmt_kcbh                         @1        0xa2
6      ub1 spare1_kcbh                       @2        0x00
7      ub1 spare2_kcbh                       @3        0x00
8      ub4 rdba_kcbh                         @4        0x01000001
9      ub4 bas_kcbh                          @8        0x00000000
10      ub2 wrp_kcbh                          @12       0x0000
11      ub1 seq_kcbh                          @14       0x01
12      ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
13      ub2 chkval_kcbh                       @16       0x1220
14      ub2 spare3_kcbh                       @18       0x0000
15   struct kcvfhhdr, 76 bytes                @20      
16      ub4 kccfhswv                          @20       0x00000000
17      ub4 kccfhcvn                          @24       0x0b200400
18      ub4 kccfhdbi                          @28       0x5b6f76cf
19      text kccfhdbn[0]                      @32      O ##dbname
20      text kccfhdbn[1]                      @33      R
21      text kccfhdbn[2]                      @34      C
22      text kccfhdbn[3]                      @35      L
23
24BBED> dump
25 File: /u01/app/oracle/oradata/orcl/users01.dbf (4)
26 Block: 1                Offsets:    0 to  511           Dba:0x01000001
27------------------------------------------------------------------------
28 0ba20000 01000001 00000000 00000104 20120000 00000000 0004200b cf766f5b 
29 4f52434c 00000000 ee010000 80020000 00200000 04000300 00000000 00000000 
30 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
31 00000000 443e0000 00000000 582bfa3b 4f2bfa3b 01000000 00000000 00000000 
32 00000000 00000000 00000400 4a000000 00000000 49000000 00000000 00000000 
33 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
34 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
35 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
36 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
37 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
38 00000000 00000000 00000000 04000000 05005553 45525300 00000000 00000000 
39 00000000 00000000 00000000 00000000 04000000 00000000 00000000 00000000 
40 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
41 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
42 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
43 00000000 26f60d00 00000000 d22ffa3b 01000000 49000000 8b7d0000 10000000 
44
45 <32 bytes per line>
46
47
48SQL> show parameter db_name
49
50NAME                     TYPE     VALUE
51------------------------------------ ----------- ------------------------------
52db_name                  string     orcl
53
54SQL> select chr(to_number(substr('4f52434c00000000',rownum*2-1,2),'xxxxxxxx')) from 
55dba_objects where rownum<=9;  2  
56
57CHR(
58----
59O
60R
61C
62L

诊断Trace files
alter session set events 'immediate trace name controlf level N'; ##控制文件
alter session set events 'immediate trace name file_hdrs level N';##文件头部信息
alter session set events 'immediate trace name redohdr level N';##日志文件头部信息
alter system dump undo header "_SYSSMU17_3012809736$"; ##undo文件
alter system dump datafile 3 block 256;
alter system dump logfile '/u01/app/oracle/oradata/ocm/redo01.log';
alter session set events 'immediate trace name treedump level 64952 ';

Recovery算法

Oracle为了在任何情况下都能顺利执行recovery操作，采用了一 系列的算法/设计来保证recovery的成功实施：
Page Fix
Write-Ahead-Log
Log-Force-at-Commit
Online-Log Switch Management
Checkpointing
Thread-Open Flag
Incremental Checkpointing
Two-Pass Recovery

Recovery方法
Recovery for a thread starts at last checkpoint
Recovery progresses, redo record by redo record, until all available redo has been applied
Find the block
Block time > Redo record time: Skip block
Block time = Redo record time: Apply changes
Block time < Redo record time: Stuck recovery (ORA-600 [3020])
No generation of redo

解决思路
查看告警日志
Dump控制文件和数据文件头
BBED修复
正常shutdown immediate

1[oracle@DSI trace]$ pwd
2/u01/app/oracle/diag/rdbms/orcl/orcl/trace
3[oracle@DSI trace]$ tail -n 100 alert_orcl.log
4
5检查点
6      ub1 kcvcpetb[7]                       @519      0x00
7   ub4 kcvfhcpc                             @140      0x0000004a ##检查点
8   ub4 kcvfhrts                             @144      0x00000000
9   ub4 kcvfhccc                             @148      0x00000049 ##控制文件
10   struct kcvfhbcp, 36 bytes                @152
11
12BBED> p kcvfhcpc
13ub4 kcvfhcpc                                @140      0x0000004a
14
15BBED> 
16BBED> p kcvfhccc
17ub4 kcvfhccc                                @148      0x00000049
18##文件头kcvfhcpc的检查点计数器的值（0x0000004a）比控制文件kcvfhccc计数器的值小（0x00000049）的话，数据库打开和关闭就会有问题
19，需要修改值
20
21
22当前控制文件中的SCN号
23SQL> col name format a65
24SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
25
26     FILE# TO_CHAR(CHECK
27---------- -------------
28     1      929146
29     2      929146
30     3      929146
31     4      929146
32数据文件的状态
33SQL> set pagesize 9999
34SQL> set linesize 9999
35
36SQL> select file#,status,name from v$datafile;
37
38     FILE# STATUS  NAME
39---------- ------- -----------------------------------------------------------------
40     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
41     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
42     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
43     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
44添加测试数据文件表空间
45SQL> CREATE TABLESPACE test DATAFILE '/u01/app/oracle/oradata/orcl/test01.dbf' SIZE 50M;
46
47Tablespace created.
48
49SQL> select file#,status,name from v$datafile;
50
51     FILE# STATUS  NAME
52---------- ------- -----------------------------------------------------------------
53     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
54     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
55     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
56     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
57     5 ONLINE  /u01/app/oracle/oradata/orcl/test01.dbf

模拟故障，
https://www.cnblogs.com/polestar/p/4243646.html

1 将datafile 5 online

1SQL> alter database datafile 5 offline;
2alter database datafile 5 offline
3*
4ERROR at line 1:
5ORA-01145: offline immediate disallowed unless media recovery enabled ##数据库处于归档模式才能将数据文件offline
6SQL> select name,log_mode from v$database;
7
8NAME                                  LOG_MODE
9----------------------------------------------------------------- ------------
10ORCL                                  NOARCHIVELOG
11
12SQL> shutdown immediate;
13Database closed.
14Database dismounted.
15ORACLE instance shut down.
16SQL> startup mount;
17ORACLE instance started.
18
19Total System Global Area  784998400 bytes
20Fixed Size            2257352 bytes
21Variable Size          511708728 bytes
22Database Buffers      264241152 bytes
23Redo Buffers            6791168 bytes
24Database mounted.
25SQL> alter database archivelog; ##修改为归档模式
26
27Database altered.
28
29SQL> alter database open;
30
31Database altered.
32
33SQL> select name,log_mode from v$database;
34
35NAME                                  LOG_MODE
36----------------------------------------------------------------- ------------
37ORCL                                  ARCHIVELOG
38
39SQL> alter database datafile 5 offline;
40
41Database altered.
42
43SQL> select file#,status,name from v$datafile;
44
45     FILE# STATUS  NAME
46---------- ------- -----------------------------------------------------------------
47     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
48     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
49     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
50     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
51     5 RECOVER /u01/app/oracle/oradata/orcl/test01.dbf ##文件状态已经被修改

2 更改文件的scn号

1SQL> alter system switch logfile;
2
3System altered.
4
5SQL> alter system switch logfile;
6
7System altered.
8
9SQL> select file#,checkpoint_change# from v$datafile;
10
11     FILE# CHECKPOINT_CHANGE#
12---------- ------------------
13     1           930121
14     2           930121
15     3           930121
16     4           930121
17     5           930121
18
19SQL> alter system switch logfile;
20
21System altered.
22
23SQL> alter system switch logfile;
24
25System altered.
26
27SQL> alter system switch logfile;
28
29System altered.
30
31SQL> select file#,checkpoint_change# from v$datafile;
32
33     FILE# CHECKPOINT_CHANGE#
34---------- ------------------
35     1           930517
36     2           930517
37     3           930517
38     4           930517
39     5           930121 ##多执行几次，这里已经不一样了
40##查看需要恢复datafile的scn
41SQL> select file#,online_status,change# from v$recover_file;
42
43     FILE# ONLINE_    CHANGE#
44---------- ------- ----------
45     5 OFFLINE     930121

3 把datafile设置为online

1SQL> alter database datafile 5 online;
2alter database datafile 5 online
3*
4ERROR at line 1:
5ORA-01113: file 5 needs media recovery
6ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test01.dbf'
7##提示需要进行数据恢复
8##/u01/app/oracle/diag/rdbms/orcl/orcl/trace/
9Tue Apr 23 09:31:31 2019
10alter database datafile 5 online
11ORA-1113 signalled during: alter database datafile 5 online...
12
13
14利用bbed修改数据文件5号的scn号
15SQL> shutdown immediate;
16Database closed.
17Database dismounted.
18ORACLE instance shut down.

利用bbed查看文件1和文件5的scn号
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[oracle@DSI ~]$ bbed
2
3BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 22 18:05:42 2019
4
5Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
6
7************* !!! For Oracle Internal Use only !!! ***************
8
9BBED> info
10 File#  Name                                                        Size(blks)
11 -----  ----                                                        ----------
12     1  /u01/app/oracle/oradata/orcl/system01.dbf                        94720
13     2  /u01/app/oracle/oradata/orcl/sysaux01.dbf                        76800
14     3  /u01/app/oracle/oradata/orcl/undotbs01.dbf                      144640
15     4  /u01/app/oracle/oradata/orcl/users01.dbf                           640
16
17BBED> set file 1 block 1
18    FILE#              1
19    BLOCK#             1
20
21BBED> map /v
22 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
23 Block: 1                                     Dba:0x00400001
24------------------------------------------------------------
25 Data File Header
26
27 struct kcvfh, 860 bytes                    @0       
28    struct kcvfhbfh, 20 bytes               @0       
29    struct kcvfhhdr, 76 bytes               @20      
30    ub4 kcvfhrdb                            @96      
31    struct kcvfhcrs, 8 bytes                @100     
32    ub4 kcvfhcrt                            @108     
33    ub4 kcvfhrlc                            @112     
34    struct kcvfhrls, 8 bytes                @116     
35    ub4 kcvfhbti                            @124     
36    struct kcvfhbsc, 8 bytes                @128     
37    ub2 kcvfhbth                            @136     
38    ub2 kcvfhsta                            @138     
39    struct kcvfhckp, 36 bytes               @484     
40    ub4 kcvfhcpc                            @140     
41    ub4 kcvfhrts                            @144     
42    ub4 kcvfhccc                            @148     
43    struct kcvfhbcp, 36 bytes               @152     
44    ub4 kcvfhbhz                            @312     
45    struct kcvfhxcd, 16 bytes               @316     
46    sword kcvfhtsn                          @332     
47    ub2 kcvfhtln                            @336     
48    text kcvfhtnm[30]                       @338     
49    ub4 kcvfhrfn                            @368     
50    struct kcvfhrfs, 8 bytes                @372     
51    ub4 kcvfhrft                            @380     
52    struct kcvfhafs, 8 bytes                @384     
53    ub4 kcvfhbbc                            @392     
54    ub4 kcvfhncb                            @396     
55    ub4 kcvfhmcb                            @400     
56    ub4 kcvfhlcb                            @404     
57    ub4 kcvfhbcs                            @408     
58    ub2 kcvfhofb                            @412     
59    ub2 kcvfhnfb                            @414     
60    ub4 kcvfhprc                            @416     
61    struct kcvfhprs, 8 bytes                @420     
62    struct kcvfhprfs, 8 bytes               @428     
63    ub4 kcvfhtrt                            @444     
64
65 ub4 tailchk                                @8188  
66
67BBED> p kcvfhckp
68struct kcvfhckp, 36 bytes                   @484     
69   struct kcvcpscn, 8 bytes                 @484     
70      ub4 kscnbas                           @484      0x000e38a5
71      ub2 kscnwrp                           @488      0x0000
72   ub4 kcvcptim                             @492      0x3bfa9b54
73   ub2 kcvcpthr                             @496      0x0001
74   union u, 12 bytes                        @500     
75      struct kcvcprba, 12 bytes             @500     
76         ub4 kcrbaseq                       @500      0x0000004e
77         ub4 kcrbabno                       @504      0x00000b88
78         ub2 kcrbabof                       @508      0x0010
79   ub1 kcvcpetb[0]                          @512      0x02
80   ub1 kcvcpetb[1]                          @513      0x00
81   ub1 kcvcpetb[2]                          @514      0x00
82   ub1 kcvcpetb[3]                          @515      0x00
83   ub1 kcvcpetb[4]                          @516      0x00
84   ub1 kcvcpetb[5]                          @517      0x00
85   ub1 kcvcpetb[6]                          @518      0x00
86   ub1 kcvcpetb[7]                          @519      0x00
87
88BBED> p kcvfhcpc
89ub4 kcvfhcpc                                @140      0x00000053
90
91BBED> p kcvfhccc
92ub4 kcvfhccc                                @148      0x00000052
93##这里kcvfhcpc比控制文件kcvfhccc的检查点的值要大,小的话肯定要报错，启动或关闭不了
94
95--修改kscnbas
96BBED> modify /x 9972fb3b dba 5,1 offset 492
97BBED-00209: invalid number (9972fb3b)
98--报无法修改，看看两个文件的484至487的4个字节值的异同
99BBED> dump /v dba 1,1 offset 484 count 32
100 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
101 Block: 1       Offsets:  484 to  515  Dba:0x00400001
102-------------------------------------------------------
103 8d8d0e00 00000000 9972fb3b 01000000 l .........r񬬮.
104 4f000000 02000000 10000000 02000000 l O...............
105
106 <16 bytes per line>
107
108BBED> dump /v dba 5,1 offset 484 count 32
109 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
110 Block: 1       Offsets:  484 to  515  Dba:0x01400001
111-------------------------------------------------------
112 49310e00 00000000 fb8ffa3b 01000000 l I1......󸹮...
113 49000000 6de80000 10000000 02000000 l I...m欮........
114
115 <16 bytes per line>
116
117--观察文件1和文件5的前四个字节的异同，只修改484，485前两字节
118##modify /x 8d8d dba 5,1 offset 484
119BBED> modify /x 8d8d dba 5,1 offset 484
120 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
121 Block: 1                Offsets:  484 to  515           Dba:0x01400001
122------------------------------------------------------------------------
123 8d8d0e00 00000000 fb8ffa3b 01000000 49000000 6de80000 10000000 02000000 
124
125 <32 bytes per line>
126-修改kcvcptim
127BBED> dump /v dba 1,1 offset 492 count 4
128 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
129 Block: 1       Offsets:  492 to  495  Dba:0x00400001
130-------------------------------------------------------
131 9972fb3b                            l .r
132
133 <16 bytes per line>
134
135BBED> dump /v dba 5,1 offset 492 count 4
136 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
137 Block: 1       Offsets:  492 to  495  Dba:0x01400001
138-------------------------------------------------------
139 fb8ffa3b                            l 
140
141 <16 bytes per line>
142##modify /x 9972 dba 5,1 offset 492
143BBED> modify /x 9972 dba 5,1 offset 492
144 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
145 Block: 1                Offsets:  492 to  495           Dba:0x01400001
146------------------------------------------------------------------------
147 9972fa3b 
148
149 <32 bytes per line>
150--修改kcvfhcpc
151BBED> dump /v dba 1,1 offset 140 count 4
152 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
153 Block: 1       Offsets:  140 to  143  Dba:0x00400001
154-------------------------------------------------------
155 57000000                            l W...
156
157 <16 bytes per line>
158
159BBED> dump /v dba 5,1 offset 140 count 4
160 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
161 Block: 1       Offsets:  140 to  143  Dba:0x01400001
162-------------------------------------------------------
163 04000000                            l ....
164
165 <16 bytes per line>
166##modify /x 5700 dba 5,1 offset 140
167BBED> modify /x 5700 dba 5,1 offset 140
168 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
169 Block: 1                Offsets:  140 to  143           Dba:0x01400001
170------------------------------------------------------------------------
171 57000000 
172
173 <32 bytes per line>
174--修改kcvfhccc
175BBED> dump /v dba 1,1 offset 148 count 4
176 File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
177 Block: 1       Offsets:  148 to  151  Dba:0x00400001
178-------------------------------------------------------
179 56000000                            l V...
180
181 <16 bytes per line>
182
183BBED> dump /v dba 5,1 offset 148 count 4
184 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
185 Block: 1       Offsets:  148 to  151  Dba:0x01400001
186-------------------------------------------------------
187 03000000                            l ....
188
189 <16 bytes per line>
190##modify /x 5600 dba 5,1 offset 148
191BBED> modify /x 5600 dba 5,1 offset 148
192 File: /u01/app/oracle/oradata/orcl/test01.dbf (5)
193 Block: 1                Offsets:  148 to  151           Dba:0x01400001
194------------------------------------------------------------------------
195 56000000 
196
197 <32 bytes per line>
198
199应用修改
200BBED> sum apply
201Check value for File 5, Block 1:
202current = 0x1c88, required = 0x1c88
203
204SQL> alter database datafile 5 online; 
205alter database datafile 5 online
206*
207ERROR at line 1:
208ORA-01113: file 5 needs media recovery
209ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test01.dbf'
210alter database datafile 5 online
211ORA-1113 signalled during: alter database datafile 5 online...
212Tue Apr 23 09:49:04 2019
213Checker run found 1 new persistent data failures

##测试到这里了，对bbed修改oracle块的还是差一点意思，为了后面的测试继续，这里只好使用rman先恢复

1RMAN> restore datafile 5;
2RMAN> recover datafile 5;
3
4SQL> alter database datafile 5 online;
5
6Database altered.
7SQL> alter system switch logfile;
8
9System altered.
10
11SQL> select file#,checkpoint_change# from v$datafile;
12
13     FILE# CHECKPOINT_CHANGE#
14---------- ------------------
15     1           958624
16     2           958624
17     3           958624
18     4           958624
19     5           958624