Tag Archives: bbed

bbed for win 64

Posted on by

很多朋友反馈在win 64位操作系统之上无法使用bbed(包括9i,10g,11g,12c数据库版本),以前写过一篇文章,完美实现了在win平台的各个版本的数据库版本之上实现使用bbed(在win中运行bbed程序),可惜很遗憾没有注明平台信息,留下了不少疑问,今天在自己的电脑上再次实现此功能,用来证明win 64位的平台之上也可以运行bbed程序(数据库版本包括10g,11g,12c,在10g之前x86架构中无win 64位版本数据库,因此我也无能为力).

操作系统版本64位
本机测试为win 7 64位操作系统
win-64

win-64-2

数据库版本64位
本机测试数据库版本为12.1.0.2 64位版本(因为12c都支持,那对于10g/11g更是不在话下）
db-64

bbed运行情况
这里的bbed只是运行起来,并未加载数据文件,因此这里看到的FILENAME为空,但是不妨碍证明bbed可以在win平台,64位的数据库中运行
bbed-win64

整体证明win 64位平台,64位数据库运行bbed
一图抵上千言万语,让我们使用一幅完整截图来说明,bbed是可以运行在win 64位平台的64位版本的数据库之上(而且这里使用了目前最新的12.1.0.2版本)
oracle-64-bbed-10g-11g-12c

模拟基表事务未提交数据库crash,undo丢失恢复异常恢复

Posted on by

对于基表特别是bootstrap$中的表,如果进行了dml操作,但是没有提交,这个时候数据库crash,而且undo异常.对于类似的场景,都是很多数据库恢复的人都有畏惧,因为_corrupted_rollback_segments参数不能起作用.本实验dml修改seq$表,最终却引起了undo$和I_UNDO1异常(他们都在bootstrap$中)
模拟seq$表事务未提交

SQL> select * from v$version; 

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select sysdate "www.orasos.com" from dual;

www.xifenfei
------------
31-AUG-13

SQL> CREATE SEQUENCE seq_xifenfei INCREMENT BY 1 START WITH 1 CACHE 3;

Sequence created.

SQL> select object_id from dba_objects where object_name='SEQ_XIFENFEI';

 OBJECT_ID
----------
     77282

SQL> SELECT MINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHE from seq$ where obj#=77282;

  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          0          0          3

SQL> update seq$ set  CYCLE#=3 where obj#=77282;

1 row updated.

--另外一个会话abort数据库
SQL> shutdown abort;
ORACLE instance shut down.

--人工rm掉undotbs01.dbf文件

--启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'

--离线数据文件
SQL> alter database datafile 3 offline;

Database altered.

--尝试open数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Process ID: 17864
Session ID: 125 Serial number: 5

alert日志

SMON: enabling cache recovery
Mon Aug 26 17:08:49 2013
ARC3 started with pid=25, OS id=17902
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Archived Log entry 13 added for thread 1 sequence 13 ID 0xfb920b77 dest 1:
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17864.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17864.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Error 704 happened during db open, shutting down database
USER (ospid: 17864): terminating the instance due to error 704
Instance terminated by USER, pid = 17864
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (17864) as a result of ORA-1092

做10046跟踪

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes

SQL> alter database mount;

Database altered.

SQL> alter session set events  '10046 trace name context forever,level 12';

Session altered.

SQL> alter session set db_file_multiblocK_read_count=1;

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Process ID: 17979
Session ID: 125 Serial number: 3

trace文件内容

PARSING IN CURSOR #3064524800 len=142 dep=3 uid=0 oct=3 lid=0 tim=1377508386082495 hv=361892850 ad='265c66d0' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #3064524800:c=2999,e=2162,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=0,tim=1377508386082492
BINDS #3064524800:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b6a8ebd0  bln=22  avl=02  flg=05
  value=27
EXEC #3064524800:c=1999,e=2030,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=906473769,tim=1377508386084833
WAIT #3064524800: nam='db file sequential read' ela= 65 file#=1 block#=321 blocks=1 obj#=34 tim=1377508386085083
WAIT #3064524800: nam='db file sequential read' ela= 21 file#=1 block#=225 blocks=1 obj#=15 tim=1377508386085208
FETCH #3064524800:c=0,e=375,p=2,cr=2,cu=0,mis=0,r=1,dep=3,og=3,plh=906473769,tim=1377508386085319
STAT #3064524800 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=362 us)'
STAT #3064524800 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=223 us)'
CLOSE #3064524800:c=0,e=12,dep=3,type=0,tim=1377508386085657
WAIT #3064511164: nam='control file sequential read' ela= 33 file#=0 block#=1 blocks=1 obj#=15 tim=1377508386085816
WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=15 blocks=1 obj#=15 tim=1377508386085889
WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=17 blocks=1 obj#=15 tim=1377508386085994
WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=23 blocks=1 obj#=15 tim=1377508386086058
WAIT #3064511164: nam='control file sequential read' ela= 17 file#=0 block#=1 blocks=1 obj#=15 tim=1377508386086135
WAIT #3064511164: nam='control file sequential read' ela= 15 file#=0 block#=15 blocks=1 obj#=15 tim=1377508386086196
WAIT #3064511164: nam='control file sequential read' ela= 15 file#=0 block#=17 blocks=1 obj#=15 tim=1377508386086255
WAIT #3064511164: nam='control file sequential read' ela= 18 file#=0 block#=32 blocks=1 obj#=15 tim=1377508386086317
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
FETCH #3064511164:c=8998,e=9239,p=5,cr=6,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1377508386088336
STAT #3064511164 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=82 us)'
STAT #3064511164 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=3 pw=0 time=1166 us)'
=====================
PARSE ERROR #3064512008:len=60 dep=1 uid=0 oct=3 lid=0 tim=1377508386088729 err=604
SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR'
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'

_corrupted_rollback_segments无法打开数据库

SQL> show parameter _corrupted_rollback_segments;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_corrupted_rollback_segments         string      _SYSSMU22_1366820418$, _SYSSMU
                                                 23_2165404374$, _SYSSMU24_1842
                                                 376483$, _SYSSMU25_2373003110$
                                                 , _SYSSMU26_3635237253$, _SYSS
                                                 MU27_1730246918$, _SYSSMU28_31
                                                 59173804$, _SYSSMU29_141006113
                                                 4$, _SYSSMU30_2299069988$, _SY
                                                 SSMU31_290584066$
SQL> startup pfile='/tmp/pfile' mount
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Process ID: 17979
Session ID: 125 Serial number: 3

dump block225

Block header dump:  0x004000e1
 Object id on Block? Y
 seg/obj: 0xf  csc: 0xb8c.3c0c297b  itc: 1  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba        Flag  Lck        Scn/Fsc
0x01   0x0000.001.00000034  0x00400221.0034.08  --U-    1  fsc 0x0000.3c0c297c
bdba: 0x004000e1
data_block_dump,data header at 0xb6b8a644

bbed 提交225事务

[oracle@xifenfei ~]$ bbed password=blockedit filename='/u01/oracle/oradata/ora11g/system01.dbf' blocksize=8192

BBED: Release 2.0.0.0.0 - Limited Production on Mon Aug 26 17:31:09 2013

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

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

BBED> set block 225
        BLOCK#          225

BBED> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 48 bytes                     @20      

 struct kdbh, 14 bytes                      @68      

 struct kdbt[1], 4 bytes                    @82      

 sb2 kdbr[32]                               @86      

 ub1 freespace[59]                          @150     

 ub1 rowdata[7979]                          @209     

 ub4 tailchk                                @8188    

BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000000f
      ub4 ktbbhod1                          @24       0x0000000f
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x3c0c297b
      ub2 kscnwrp                           @32       0x0b8c
   b2 ktbbhict                              @36      -2047
   ub1 ktbbhflg                             @38       0x03 (KTBFONFL)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0000
         ub2 kxidslt                        @46       0x0001
         ub4 kxidsqn                        @48       0x00000034
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00400221
         ub2 kubaseq                        @56       0x0034
         ub1 kubarec                        @58       0x08
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x3c0c297c

BBED> set mode edit
        MODE            Edit

BBED> m /x 0180 offset 60
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225              Offsets:   60 to   91           Dba:0x00000000
------------------------------------------------------------------------
 01800000 7c290c3c 00012000 ffff5200 8d003017 30170000 20008e1f 4106f805 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 225:
current = 0x55db, required = 0x55db

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 225

Block Checking: DBA = 4194529, Block Type = KTB-managed data block
data header at 0xb6773244
kdbchk: row locked by non-existent transaction
        table=0   slot=20
        lockid=1   ktbbhitc=1
Block 225 failed with check code 6101

DBVERIFY - Verification complete

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

BBED> p *kdbr[20]
rowdata[608]
------------
ub1 rowdata[608]                            @817      0x2c

BBED> dump
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225              Offsets:  817 to  848           Dba:0x00000000
------------------------------------------------------------------------
 2c011102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 

 <32 bytes per line>

BBED> m /x 002c
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225              Offsets:  817 to  848           Dba:0x00000000
------------------------------------------------------------------------
 002c1102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 225:
current = 0x79f6, required = 0x79f6

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 225

Block Checking: DBA = 4194529, Block Type = KTB-managed data block
data header at 0xb6773244
kdbchk: row locked by non-existent transaction
        table=0   slot=20
        lockid=44   ktbbhitc=1
Block 225 failed with check code 6101

DBVERIFY - Verification complete

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

BBED> p *kdbr[20]
rowdata[608]
------------
ub1 rowdata[608]                            @817      0x00

BBED> d
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225              Offsets:  817 to  848           Dba:0x00000000
------------------------------------------------------------------------
 002c1102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 

 <32 bytes per line>

BBED> m /x 2c00
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 225              Offsets:  817 to  848           Dba:0x00000000
------------------------------------------------------------------------
 2c001102 c115145f 53595353 4d553230 5f343035 34303430 31322402 c10202c1 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 225:
current = 0x55da, required = 0x55da

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 225


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
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

dump 321 block

SQL> alter system dump datafile '/u01/oracle/oradata/ora11g/system01.dbf' block 321;

System altered.

SQL> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_19704.trc

--trace文件
Block header dump:  0x00400141
 Object id on Block? Y
 seg/obj: 0x22  csc: 0xb8c.3c0c28ec  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.038.00000033  0x0040021e.0033.3d  --U-    1  fsc 0x0000.3c0c28ed

bbed提交321 block 事务

BBED> set block 321
        BLOCK#          321

BBED> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 321                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdxle, 32 bytes                     @92      

 b2 kd_off[32]                              @124     

 ub1 freespace[7581]                        @188     

 ub1 rowdata[351]                           @7769    

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x02 (KDDBTINDEX)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000022
      ub4 ktbbhod1                          @24       0x00000022
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0x3c0c28ec
      ub2 kscnwrp                           @32       0x0b8c
   b2 ktbbhict                              @36      -2046
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0000
         ub2 kxidslt                        @46       0x0000
         ub4 kxidsqn                        @48       0x00000000
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00000000
         ub2 kubaseq                        @56       0x0000
         ub1 kubarec                        @58       0x00
      ub2 ktbitflg                          @60       0x0000 (NONE)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x00000000
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x0000
         ub2 kxidslt                        @70       0x0038
         ub4 kxidsqn                        @72       0x00000033
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0040021e
         ub2 kubaseq                        @80       0x0033
         ub1 kubarec                        @82       0x3d
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x3c0c28ed


BBED> m /x 0180 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 321              Offsets:   84 to  595           Dba:0x00000000
------------------------------------------------------------------------
 01800000 ed280c3c 00008001 00000000 20006400 011e9d1d 00000000 00000000 
 00000000 06000000 601f0000 561f4b1f 401f351f 2a1f1f1f 141f091f fe1ef31e 
 e81edd1e d21ec71e bc1eb11e a61e9b1e 901e851e 7a1e6f1e 641e591e 4e1e431e 
 381e2d1e 221e171e 0c1e011e 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 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 321:
current = 0x990e, required = 0x990e

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 321

Block Checking: DBA = 4194625, Block Type = KTB-managed data block
**** actual rows locked by itl 2  = 1 != # in trans. header = 0
---- end index block validation
Block 321 failed with check code 6401

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): 1
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> p kdxle
struct kdxle, 32 bytes                      @92      
   struct kdxlexco, 16 bytes                @92      
      ub1 kdxcolev                          @92       0x00
      ub1 kdxcolok                          @93       0x00
      ub1 kdxcoopc                          @94       0x80
      ub1 kdxconco                          @95       0x01
      ub4 kdxcosdc                          @96       0x00000000
      sb2 kdxconro                          @100      32
      b2 kdxcofbo                           @102      100
      b2 kdxcofeo                           @104      7681
      b2 kdxcoavs                           @106      7581
   b2 kdxlespl                              @108      0
   sb2 kdxlende                             @110      0
   ub4 kdxlenxt                             @112      0x00000000
   ub4 kdxleprv                             @116      0x00000000
   ub1 kdxledsz                             @120      0x06
   ub1 kdxleunuse                           @121      0x00

BBED> m /x 00 offset 95
 File: /u01/oracle/oradata/ora11g/system01.dbf (0)
 Block: 321              Offsets:   95 to  606           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00200064 00011e9d 1d000000 00000000 00000000 00060000 00601f00 
 00561f4b 1f401f35 1f2a1f1f 1f141f09 1ffe1ef3 1ee81edd 1ed21ec7 1ebc1eb1 
 1ea61e9b 1e901e85 1e7a1e6f 1e641e59 1e4e1e43 1e381e2d 1e221e17 1e0c1e01 
 1e000000 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 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 321:
current = 0x380e, required = 0x380e

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 321


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提交事务open依旧报错

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oracle/oradata/ora11g/undotbs01.dbf'
Process ID: 20229
Session ID: 125 Serial number: 5

做10046的trace文件错误依旧

=====================
PARSING IN CURSOR #3065238148 len=142 dep=3 uid=0 oct=3 lid=0 tim=1377519352653391 hv=361892850 ad='269a8064' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #3065238148:c=2999,e=3152,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=0,tim=1377519352653388
BINDS #3065238148:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b6b32268  bln=22  avl=02  flg=05
  value=27
EXEC #3065238148:c=2999,e=3182,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=906473769,tim=1377519352657030
WAIT #3065238148: nam='db file sequential read' ela= 169 file#=1 block#=321 blocks=1 obj#=34 tim=1377519352657400
WAIT #3065238148: nam='db file sequential read' ela= 25 file#=1 block#=225 blocks=1 obj#=15 tim=1377519352657546
FETCH #3065238148:c=1000,e=578,p=2,cr=2,cu=0,mis=0,r=1,dep=3,og=3,plh=906473769,tim=1377519352657738
STAT #3065238148 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=2 pw=0 time=538 us)'
STAT #3065238148 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=1 pw=0 time=338 us)'
CLOSE #3065238148:c=0,e=13,dep=3,type=0,tim=1377519352657942
WAIT #3065201888: nam='control file sequential read' ela= 111 file#=0 block#=1 blocks=1 obj#=15 tim=1377519352658170
WAIT #3065201888: nam='control file sequential read' ela= 19 file#=0 block#=16 blocks=1 obj#=15 tim=1377519352658269
WAIT #3065201888: nam='control file sequential read' ela= 18 file#=0 block#=18 blocks=1 obj#=15 tim=1377519352658329
WAIT #3065201888: nam='control file sequential read' ela= 19 file#=0 block#=24 blocks=1 obj#=15 tim=1377519352658391
WAIT #3065201888: nam='control file sequential read' ela= 17 file#=0 block#=1 blocks=1 obj#=15 tim=1377519352658469
WAIT #3065201888: nam='control file sequential read' ela= 16 file#=0 block#=16 blocks=1 obj#=15 tim=1377519352658528
WAIT #3065201888: nam='control file sequential read' ela= 28 file#=0 block#=18 blocks=1 obj#=15 tim=1377519352658787
WAIT #3065201888: nam='control file sequential read' ela= 20 file#=0 block#=32 blocks=1 obj#=15 tim=1377519352658883
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
FETCH #3065201888:c=11998,e=12032,p=5,cr=6,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1377519352661044
STAT #3065201888 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=19 us)'
STAT #3065201888 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=3 pw=0 time=1001 us)'

通过观察每次的10046文件,发现总是在查询select /*+ rule */ name,file#, block#,status$,user#,undosqn,xactsqn, scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1并且总是us#=27号回滚段上过不去,通过使用bbed或者dul修改数据文件,让数据库启动时候不再扫描27号回滚段(主要是修改undo$.status$=1,block位置可以通过10046确定,也可以通过odu来确定,亦或者找个相同版本的库查看)

继续open数据库

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

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.
SQL> alter database open;

Database altered.

删除有问题undo,重建新undo

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified


SQL> !oerr ora 01561
01561, 00000, "failed to remove all objects in the tablespace specified"
// *Cause: Failed to remove all objects when dropping a tablespace
// *Action: Retry the drop tablespace until all objects are dropped

SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified


SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

-------------------------------------------------
因为前面使用了bbed/dul之类的工具，修改了undo$表，
导致undo$中的标记27号回滚段与seg$中不一致
解决方法使得修改undo$.status$为3或者2
-------------------------------------------------

SQL> update undo$ set status$=3 where us#=27;

1 row updated.

SQL> commit;

Commit complete.

SQL> drop tablespace undotbs1 ;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified


SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area  175775744 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                6656000 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 ;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/ora11g/undotbs1.dbf' size 10M;

Tablespace created.

直接修改基表,bbed/dul修改数据文件等操作都是危险级别非常高的操作,而且oracle not support,在没有十足把握之前,一定不要在生产环境中执行,如果数据库挂了需要类似方法恢复,也请一定保留现场,如果你搞不定能够还原现场
部分操作可以参考:通过bbed修改回滚段状态解决ORA-00704故障

bbed处理ORA-01200故障

Posted on by

一个朋友的测试库出现ORA-01200错误,正好周末比较空闲,随手帮他使用bbed进行了恢复,给广大朋友提供一种解决该问题的方法
数据库启动报错

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:09:11 2013

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

SQL> connect/as sysdba
已连接到空闲例程。
SQL> startup force
ORACLE 例程已经启动。

Total System Global Area 1071333376 bytes
Fixed Size                  1334380 bytes
Variable Size             318768020 bytes
Database Buffers          746586112 bytes
Redo Buffers                4644864 bytes
数据库装载完毕。
ORA-01122: 数据库文件 1 验证失败
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01200: 87946 的实际文件大小小于 88320 块的正确大小

这里的错误很明显是因为file 1的数据文件头记录block大小为88320个block,而该数据文件的实际大小只有87946个block,所以出现该问题.

dbv检测文件

D:\app\Administrator\oradata\orcl>dbv file=SYSTEM01.DBF

DBVERIFY: Release 11.1.0.6.0 - Production on 星期日 5月 12 22:30:29 2013

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

DBVERIFY - 开始验证: FILE = SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 87040
处理的页总数 (数据): 62870
失败的页总数 (数据): 0
处理的页总数 (索引): 11055
失败的页总数 (索引): 0
处理的页总数 (其它): 2437
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 10678
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 980055 (0.980055)

检查发现该数据文件未发现坏块,减小了该数据文件通过bbed恢复异常的风险,数据库最怕就是system中出现很多坏块

使用bbed修改kccfhfsz
因为win的bbed问题,所以拷贝到我的电脑上进行修改

C:\Users\XIFENFEI\Desktop\temp>bbed filename=system01.dbf blocksize=8192
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sun May 12 23:27:26 2013

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

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

BBED> set block 2
        BLOCK#          2
--从一台机器中拷贝到另外的机器，实际中的block可能发生改变，因为含block 0

BBED> map
 File: system01.dbf (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0

 ub4 tailchk                                @8188


BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a

--通过ORA-01200错误报出来的文件头记录大小88320实际就是0x0001578a


BBED> set mode edit
        MODE            Edit

BBED> set count 32
        COUNT           32

BBED> d
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
00590100 00200000 01000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

BBED> m /x 8A570100
 File: system01.dbf (0)
 Block: 2                Offsets:   44 to   75           Dba:0x00000000
------------------------------------------------------------------------
 8a570100 00200000 01000300 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>
--通过ORA-01200错误报出来的数据文件实际大小,来修改该文件头的kcvfhhdr.kccfhfsz值,也可以通过文件实际大小计算出来


BBED> p kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       0x0001578a

BBED> sum apply
Check value for File 0, Block 2:
current = 0x0f79, required = 0x0f79

BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 1


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

打开数据库

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

数据库已更改。

bbed模拟提交事务二之屏蔽smon回滚事务

Posted on by

在上一篇修改datablock itl(bbed模拟提交事务一之修改itl)的基础之上，本篇实现修改undo segment header中的相关事务槽信息,从而屏蔽数据库在重启或者进程异常的时候,smon的回滚操作,从而比较完美的实现了手工提交数据库事务
update table and uncommit(session 1)

SQL> select distinct
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;   

   REL_FNO   BLOCK_NO
---------- ----------
         4         28

SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 xifenfei.com
        44 xifenfei.com
        28 xifenfei.com
        15 xifenfei.com
        29 xifenfei.com
         3 xifenfei.com
        25 xifenfei.com
        39 xifenfei.com
        51 xifenfei.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

SQL> update chf.t_xifenfei set object_name='orasos.com' where rownum<10;

9 rows updated.

SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 orasos.com
        44 orasos.com
        28 orasos.com
        15 orasos.com
        29 orasos.com
         3 orasos.com
        25 orasos.com
        39 orasos.com
        51 orasos.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump undo header(session 2)

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         13        367          2       1126        362         32

SQL>  alter system dump undo header "_SYSSMU1$";

System altered.


index  state cflags  wrap#    uel         scn            dba     parent-xid          nub       stmt_num       cmt
------------------------------------------------------------------------------------------------------------------
   …………
0x0c    9    0x00  0x016e  0x0029  0x0b2c.c02d1f6a  0x00800464  0x0000.000.00000000  0x00000001   0x00000000  1358813163
0x0d   10    0x80  0x016f  0x0002  0x0b2c.c02d7b15  0x00800466  0x0000.000.00000000  0x00000001   0x00000000  0
0x0e    9    0x00  0x016f  0x000f  0x0b2c.c02d2ae2  0x00800466  0x0000.000.00000000  0x00000001   0x00000000  1358820065
   …………

通过结合dump undo header 中的TRN TBL的state为10的为active事务,然后结合scn/dba等信息,
来确定是哪条记录是需要我们修改.然后通过find命令快速定位到0x0d这条记录,然后进行修改

通过结合bbed的dump命令得出16进制数据分析得出如下结论

--index 0x0c
6e01        0000 64048000      6a1f2dc0 2c0b0000      09       00          2900      
0000000000000000            00000000          01000000     ebd7fd50(1358813163注意存储顺序)

--index 0x0d
6f01  wrap# 0000 66048000 dba  157b2dc0 2c0b0000 scn  0a state 80  cflags  0200 uel  
0000000000000000 parent-xid 00000000 stmt_num 01000000 nub 00000000 cmt

--index 0x0e
6f01        0000 66048000      e22a2dc0 2c0b0000      09       00          0f00      
0000000000000000            00000000          01000000     e1f2fd50(1358820065)

bbed modify undo segment header(session 2)

BBED> f /x 0a80
 File: /u01/oracle/oradata/XFF/undotbs01.dbf (0)
 Block: 9                Offsets: 6736 to 6751           Dba:0x00000000
------------------------------------------------------------------------
 0a800200 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 0900
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/undotbs01.dbf (0)
 Block: 9                Offsets: 6736 to 6751           Dba:0x00000000
------------------------------------------------------------------------
 09000200 00000000 00000000 00000000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 9:
current = 0xecdd, required = 0xecdd

bbed modify data block itl(session 2)

struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0001
         ub2 kxidslt                        @46       0x000d
         ub4 kxidsqn                        @48       0x0000016f
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00800466
         ub2 kubaseq                        @56       0x016a
         ub1 kubarec                        @58       0x20
      ub2 ktbitflg                          @60       0x0009 (NONE)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       18
         ub2 _ktbitwrp                      @62       0x0012
      ub4 ktbitbas                          @64       0x00000000

BBED> m /x 0080 offset 60
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 28               Offsets:   60 to  571           Dba:0x00000000
------------------------------------------------------------------------
 00801200 00000000 09002a00 36020000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 28:
current = 0xccf1, required = 0xccf1

restart db and select table(session 3)

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1267164 bytes
Variable Size             109054500 bytes
Database Buffers          188743680 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> col object_name for a20
SQL> set pages 100
SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 orasos.com
        44 orasos.com
        28 orasos.com
        15 orasos.com
        29 orasos.com
         3 orasos.com
        25 orasos.com
        39 orasos.com
        51 orasos.com
        26 xifenfei.com
        17 xifenfei.com
        13 xifenfei.com
         9 xifenfei.com
        41 xifenfei.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

到此证明,通过修改undo segment header中的state和cflags实现数据库启动不回滚未提交事务；通过修改datablock itl实现数据库在访问未提交数据块时候不访问undo。从而整体上较完美的实现了手工提交一个事务(数据库提交一个事务涉及的方方面面较为复杂，这里只是通过修改最核心的两部分来大致模拟提交事务)

bbed模拟提交事务一之修改itl

Posted on by

我们都知道，根据oracle 事务的一致性,当我们在session 1中进行dml操作,如果未提交在其他会话中是无法看到修改后的值(只能看到修改前的值).这里通过bbed模拟部分提交事务从而实现在其他会话中查看到另外会话未提交事务(本质已经部分模拟提交,还有undo segment header中信息未清理,下篇补充)
create table(session 1)

SQL> create table chf.t_xifenfei
  2  as
  3  select object_id,object_name from dba_objects where rownum<20;

Table created.

SQL> select rowid,
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;

ROWID                 REL_FNO   BLOCK_NO
------------------ ---------- ----------
AAANL3AAEAAAAAcAAA          4         28
AAANL3AAEAAAAAcAAB          4         28
AAANL3AAEAAAAAcAAC          4         28
AAANL3AAEAAAAAcAAD          4         28
AAANL3AAEAAAAAcAAE          4         28
AAANL3AAEAAAAAcAAF          4         28
AAANL3AAEAAAAAcAAG          4         28
AAANL3AAEAAAAAcAAH          4         28
AAANL3AAEAAAAAcAAI          4         28
AAANL3AAEAAAAAcAAJ          4         28
AAANL3AAEAAAAAcAAK          4         28
AAANL3AAEAAAAAcAAL          4         28
AAANL3AAEAAAAAcAAM          4         28
AAANL3AAEAAAAAcAAN          4         28
AAANL3AAEAAAAAcAAO          4         28
AAANL3AAEAAAAAcAAP          4         28
AAANL3AAEAAAAAcAAQ          4         28
AAANL3AAEAAAAAcAAR          4         28
AAANL3AAEAAAAAcAAS          4         28

19 rows selected.

SQL>  select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        39 I_IND1
        51 I_CDEF2
        26 I_PROXY_ROLE_DATA$_1
        17 FILE$
        13 UET$
         9 I_FILE#_BLOCK#
        41 I_FILE1
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

update record(session 1)

SQL> update chf.t_xifenfei set object_name ='www.orasos.com' where rownum<15;

14 rows updated.

SQL> col object_name for a20
SQL> select * from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 www.orasos.com
        44 www.orasos.com
        28 www.orasos.com
        15 www.orasos.com
        29 www.orasos.com
         3 www.orasos.com
        25 www.orasos.com
        39 www.orasos.com
        51 www.orasos.com
        26 www.orasos.com
        17 www.orasos.com
        13 www.orasos.com
         9 www.orasos.com
        41 www.orasos.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

dump block(session 2)

SQL> alter system checkpoint;

System altered.

--注意flush buffer_cache(不然后面bbed修改会被不能通过select显示,而且会被覆盖)
SQL> alter system flush buffer_cache;

System altered.

SQL>  alter system flush shared_pool;

System altered.

SQL> alter system dump datafile 4 block 28;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x000a.00a.0000017e  0x0081ffc7.01a2.22  ----   14  fsc 0x0000.00000000  <--注意Lck 14
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

bbed commit Transaction(session 3)

BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x0000d2f7
      ub4 ktbbhod1                          @24       0x0000d2f7
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xc02d1aec
      ub2 kscnwrp                           @32       0x0b2c
   b2 ktbbhict                              @36       3
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01000019
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0xffff
         ub2 kxidslt                        @46       0x0000
         ub4 kxidsqn                        @48       0x00000000
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x00000000
         ub2 kubaseq                        @56       0x0000
         ub1 kubarec                        @58       0x00
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       2860
         ub2 _ktbitwrp                      @62       0x0b2c
      ub4 ktbitbas                          @64       0xc02d1987
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000a
         ub4 kxidsqn                        @72       0x0000017e
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0081ffc7
         ub2 kubaseq                        @80       0x01a2
         ub1 kubarec                        @82       0x22
      ub2 ktbitflg                          @84       0x000e (NONE) <--修改要改为8000
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
   struct ktbbhitl[2], 24 bytes             @92      
      struct ktbitxid, 8 bytes              @92      
         ub2 kxidusn                        @92       0x0000
         ub2 kxidslt                        @94       0x0000
         ub4 kxidsqn                        @96       0x00000000
      struct ktbituba, 8 bytes              @100     
         ub4 kubadba                        @100      0x00000000
         ub2 kubaseq                        @104      0x0000
         ub1 kubarec                        @106      0x00
      ub2 ktbitflg                          @108      0x0000 (NONE)
      union _ktbitun, 2 bytes               @110     
         b2 _ktbitfsc                       @110      0
         ub2 _ktbitwrp                      @110      0x0000
      ub4 ktbitbas                          @112      0x00000000

BBED> m /x 0080 offset 84
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 28               Offsets:   84 to  115           Dba:0x00000000
------------------------------------------------------------------------
 00800000 00000000 00000000 00000000  

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 28:
current = 0x03dc, required = 0x03dc


BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
  …………
   struct ktbbhitl[1], 24 bytes             @68      
      struct ktbitxid, 8 bytes              @68      
         ub2 kxidusn                        @68       0x000a
         ub2 kxidslt                        @70       0x000a
         ub4 kxidsqn                        @72       0x0000017e
      struct ktbituba, 8 bytes              @76      
         ub4 kubadba                        @76       0x0081ffc7
         ub2 kubaseq                        @80       0x01a2
         ub1 kubarec                        @82       0x22
      ub2 ktbitflg                          @84       0x8000 (KTBFCOM) <--修改值
      union _ktbitun, 2 bytes               @86      
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x00000000
  …………

dump block(session 2)

SQL> alter system dump datafile 4 block 28;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0b2c.c02d1987
0x02   0x000a.00a.0000017e  0x0081ffc7.01a2.22  C---    0  scn 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

select data(session 4)

SQL> select object_id,object_name from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- --------------------
        20 www.orasos.com
        44 www.orasos.com
        28 www.orasos.com
        15 www.orasos.com
        29 www.orasos.com
         3 www.orasos.com
        25 www.orasos.com
        39 www.orasos.com
        51 www.orasos.com
        26 www.orasos.com
        17 www.orasos.com
        13 www.orasos.com
         9 www.orasos.com
        41 www.orasos.com
        48 I_CON1
        38 I_OBJ3
         7 I_TS#
        53 I_CDEF4
        19 IND$

19 rows selected.

这里可以看到,已经模拟出来在其他session中可以访问数据库为commit的记录(在该block级别已经模拟了commit)