Первая часть немного палевная. Русское и английское описание. Причем неполное.
Часть 2 посвящена восстановлению удаленной строки. Удаляем в SQL, а восстанавливаем в файле.
Выполним SQL скрипт. Нам нужен список файлов с их номерами и размером.
SQL> select file#||' '||name||' '||bytes from v$datafile;
FILE#||''||NAME||''||BYTES
--------------------------------------------------------------------------------
1 /home/oracle/oracle/product/10.2.0/oradata/pahom/system01.dbf 503316480
2 /home/oracle/oracle/product/10.2.0/oradata/pahom/undotbs01.dbf 36700160
3 /home/oracle/oracle/product/10.2.0/oradata/pahom/sysaux01.dbf 241172480
4 /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf 5242880
5 /home/oracle/oracle/product/10.2.0/oradata/pahom/example01.dbf 104857600
Сохраняем список в файл list.txt. И подключаем к BBED.
BBED> set list '/home/oracle/oracle/product/10.2.0/bbed/list.txt'
LISTFILE /home/oracle/oracle/product/10.2.0/bbed/list.txt
Смотрим данные над которыми будем издеваться. Определяем ROWID строки. ROWID позволит определить номер файла и номер блока.
SQL> select * from scott.emp where ename='FORD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
SQL> select rowid from scott.emp where ename='FORD';
ROWID
------------------
AAAMfMAAEAAAAAgAAM
SQL> select dbms_rowid.rowid_relative_fno('AAAMfMAAEAAAAAgAAM') from dual;
DBMS_ROWID.ROWID_RELATIVE_FNO('AAAMFMAAEAAAAAGAAM')
---------------------------------------------------
4
SQL> select dbms_rowid.rowid_block_number('AAAMfMAAEAAAAAgAAM') from dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAMFMAAEAAAAAGAAM')
---------------------------------------------------
32
SQL> select * from scott.emp where ename='FARD';
no rows selected
SQL> select * from scott.emp where ename='FORD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
Делаем активным файл 4(см.список фалов) и блок 32, которые определили SQL скриптами.
BBED> set dba 4,32
DBA 0x01000020 (16777248 4,32)
Поиск в файле данных которые мы определили как мучеников науки.
BBED> find /c FORD
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7668 to 8179 Dba:0x01000020
------------------------------------------------------------------------
464f5244 07414e41 4c595354 03c24c43 0777b50c 03010101 02c21fff 02c1152c
010802c2 50054a41 4d455305 434c4552 4b03c24d 630777b5 0c030101 0103c20a
33ff02c1 1f2c0108 03c24f4d 05414441 4d530543 4c45524b 03c24e59 0777bb05
17010101 02c20cff 02c1152c 010803c2 4f2d0654 55524e45 52085341 4c45534d
414e03c2 4d630777 b5090801 010102c2 10018002 c11f2c01 0803c24f 28044b49
4e470950 52455349 44454e54 ff0777b5 0b110101 0102c233 ff02c10b 2c010803
c24e5905 53434f54 5407414e 414c5953 5403c24c 430777bb 04130101 0102c21f
ff02c115 2c010803 c24e5305 434c4152 4b074d41 4e414745 5203c24f 280777b5
06090101 0103c219 33ff02c1 0b2c0108 03c24d63 05424c41 4b45074d 414e4147
455203c2 4f280777 b5050101 010103c2 1d33ff02 c11f2c01 0803c24d 37064d41
5254494e 0853414c 45534d41 4e03c24d 630777b5 091c0101 0103c20d 3302c20f
02c11f2c 010803c2 4c43054a 4f4e4553 074d414e 41474552 03c24f28 0777b504
02010101 03c21e4c ff02c115 2c010803 c24c1604 57415244 0853414c 45534d41
4e03c24d 630777b5 02160101 0103c20d 3302c206 02c11f2c 010803c2 4b640541
4c4c454e 0853414c 45534d41 4e03c24d 630777b5 02140101 0102c211 02c20402
c11f2c01 0803c24a 4605534d 49544805 434c4552 4b03c250 030777b4 0c110101
<32 bytes per line>
Выведем дамп, чтобы убедиться что мы нашли что искали.
BBED> dump /v dba 4,32 offset 7668 count 64
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7668 to 7731 Dba:0x01000020
-------------------------------------------------------
464f5244 07414e41 4c595354 03c24c43 l FORD.ANALYST. LC
0777b50c 03010101 02c21fff 02c1152c l .w ...... ... .,
010802c2 50054a41 4d455305 434c4552 l ... P.JAMES.CLER
4b03c24d 630777b5 0c030101 0103c20a l K. Mc.w ...... .
<16 bytes per line>
Пытаемся изменить данные напрямую в файле. У нас ошибка :( Читай хелп по BBED.
BBED> modify /c FARD dba 4,32 offset 7668
BBED-00215: editing not allowed in BROWSE mode
Включаем режим редактирования. Специально не убрал ошибку. Часто забываю включить этот режим.
BBED> set mode edit
MODE Edit
Меняем значение. Когда поменял FORD на PAHOM мой блок сказал что он с ошибкой. Потому аккуратнее. Меняем на равное количество символов.
BBED> modify /c FARD dba 4,32 offset 7668
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7668 to 7731 Dba:0x01000020
------------------------------------------------------------------------
46415244 07414e41 4c595354 03c24c43 0777b50c 03010101 02c21fff 02c1152c
010802c2 50054a41 4d455305 434c4552 4b03c24d 630777b5 0c030101 0103c20a
<32 bytes per line>
Мы могли не менять значение, просто чтоб видеть что и такое можно сделать. Смотрим что наменяли.
BBED> dump /v dba 4,32 offset 7668 count 64
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7668 to 7731 Dba:0x01000020
-------------------------------------------------------
46415244 07414e41 4c595354 03c24c43 l FARD.ANALYST. LC
0777b50c 03010101 02c21fff 02c1152c l .w ...... ... .,
010802c2 50054a41 4d455305 434c4552 l ... P.JAMES.CLER
4b03c24d 630777b5 0c030101 0103c20a l K. Mc.w ...... .
<16 bytes per line>
Так как мы наглые и меняем значение сразу в файле. Контрольная сумма у нас не сростается.
BBED> sum dba 4,32
Check value for File 4, Block 32:
current = 0x26a0, required = 0x28a0
Правим контрольную сумму.
BBED> sum dba 4,32 apply
Check value for File 4, Block 32:
current = 0x28a0, required = 0x28a0
Чтобы увидеть изменения в запросе нужно или перестартануть базу или сбросить буфер.
SQL> alter system flush buffer_cache;
System altered.
Проверяем что так оно и есть.
SQL> select * from scott.emp where ename='FORD';
no rows selected
SQL> select * from scott.emp where ename='FARD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FARD ANALYST 7566 03-DEC-81 3000
20
Берем дамп блока файла. Чтобы сравнить что происходит до и после нашего опыта с восстановлением удаленной строки.
SQL> alter system dump datafile 4 block 32;
System altered.
/home/oracle/oracle/product/10.2.0/db_1/admin/pahom/udump/pahom_ora_7134.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-78.0.0.0.1.ELsmp
Version: #1 SMP Fri Jul 25 14:41:56 EDT 2008
Machine: i686
Instance name: pahom
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 7134, image: oracle@localhost.localdomain (TNS V1-V3)
*** SERVICE NAME:() 2009-06-02 10:50:22.287
*** SESSION ID:(159.3) 2009-06-02 10:50:22.287
Thread 1 checkpoint: logseq 3, block 2, scn 502677
cache-low rba: logseq 3, block 3
on-disk rba: logseq 3, block 240, scn 502967
start recovery at logseq 3, block 3, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 118Kb in 0.80s => 0.14 Mb/sec
Total physical reads: 4096Kb
Longest record: 8Kb, moves: 0/210 (0%)
Longest LWN: 9Kb, moves: 0/69 (0%), moved: 0Mb
Last redo scn: 0x0000.0007acb6 (502966)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 69/69 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 591/660 = 0.9
----------------------------------------------
*** 2009-06-02 10:50:23.123
KCRA: start recovery claims for 69 data blocks
*** 2009-06-02 10:50:24.019
KCRA: blocks processed = 69/69, claimed = 69, eliminated = 0
*** 2009-06-02 10:50:24.021
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 69/69 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 660/660 = 1.0
----------------------------------------------
*** 2009-06-02 11:16:11.328
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.0006bfdb seq: 0x10 flg: 0x06 tail: 0xbfdb0610
frmt: 0x02 chkval: 0x28a0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7220E00 to 0xB7222E00
B7220E00 0000A206 01000020 0006BFDB 06100000 [.... ...........]
B7220E10 000028A0 00180001 0000C7CC 0006BFD9 [.(..............]
B7220E20 1FE80000 00321F02 01000019 00110003 [......2.........]
B7220E30 000000F2 00805794 004900C8 0000200E [.....W....I.. ..]
B7220E40 0006BFDB 00000000 00000000 00000000 [................]
B7220E50 00000000 00000000 00000000 00000000 [................]
B7220E60 00000000 000E0100 002EFFFF 1D331D61 [............a.3.]
B7220E70 00001D33 1F72000E 1F1C1F47 1EC61EF3 [3.....r.G.......]
B7220E80 1E741E9D 1E261E4C 1DD51DFB 1D881DAF [..t.L.&.........]
B7220E90 00001D61 00000000 00000000 00000000 [a...............]
B7220EA0 00000000 00000000 00000000 00000000 [................]
Repeat 465 times
B7222BC0 00000000 08012C00 2350C203 4C494D06 [.....,....P#.MIL]
B7222BD0 0552454C 52454C43 4EC2034B B6770753 [LER.CLERK..NS.w.]
B7222BE0 01011701 0EC20201 0BC102FF 0308012C [............,...]
B7222BF0 040350C2 44524146 414E4107 5453594C [.P..FARD.ANALYST]
B7222C00 434CC203 0CB57707 01010103 FF1FC202 [..LC.w..........]
B7222C10 2C15C102 C2020801 414A0550 0553454D [...,....P.JAMES.]
B7222C20 52454C43 4DC2034B B5770763 0101030C [CLERK..Mc.w.....]
B7222C30 0AC20301 C102FF33 08012C1F 4D4FC203 [....3....,....OM]
B7222C40 41444105 4305534D 4B52454C 594EC203 [.ADAMS.CLERK..NY]
B7222C50 05BB7707 01010117 FF0CC202 2C15C102 [.w.............,]
B7222C60 C2030801 54062D4F 454E5255 41530852 [....O-.TURNER.SA]
B7222C70 4D53454C C2034E41 7707634D 010809B5 [LESMAN..Mc.w....]
B7222C80 C2020101 02800110 012C1FC1 4FC20308 [..........,....O]
B7222C90 494B0428 5009474E 49534552 544E4544 [(.KING.PRESIDENT]
B7222CA0 B57707FF 0101110B 33C20201 0BC102FF [..w........3....]
B7222CB0 0308012C 05594EC2 544F4353 4E410754 [,....NY.SCOTT.AN]
B7222CC0 53594C41 4CC20354 BB770743 01011304 [ALYST..LC.w.....]
B7222CD0 1FC20201 15C102FF 0308012C 05534EC2 [........,....NS.]
B7222CE0 52414C43 414D074B 4547414E 4FC20352 [CLARK.MANAGER..O]
B7222CF0 B5770728 01010906 19C20301 C102FF33 [(.w.........3...]
B7222D00 08012C0B 634DC203 414C4205 4D07454B [.,....Mc.BLAKE.M]
B7222D10 47414E41 C2035245 7707284F 010105B5 [ANAGER..O(.w....]
B7222D20 C2030101 02FF331D 012C1FC1 4DC20308 [.....3....,....M]
B7222D30 414D0637 4E495452 4C415308 414D5345 [7.MARTIN.SALESMA]
B7222D40 4DC2034E B5770763 01011C09 0DC20301 [N..Mc.w.........]
B7222D50 0FC20233 2C1FC102 C2030801 4A05434C [3......,....LC.J]
B7222D60 53454E4F 4E414D07 52454741 284FC203 [ONES.MANAGER..O(]
B7222D70 04B57707 01010102 4C1EC203 15C102FF [.w.........L....]
B7222D80 0308012C 04164CC2 44524157 4C415308 [,....L..WARD.SAL]
B7222D90 414D5345 4DC2034E B5770763 01011602 [ESMAN..Mc.w.....]
B7222DA0 0DC20301 06C20233 2C1FC102 C2030801 [....3......,....]
B7222DB0 4105644B 4E454C4C 4C415308 414D5345 [Kd.ALLEN.SALESMA]
B7222DC0 4DC2034E B5770763 01011402 11C20201 [N..Mc.w.........]
B7222DD0 0204C202 012C1FC1 4AC20308 4D530546 [......,....JF.SM]
B7222DE0 05485449 52454C43 50C2034B B4770703 [ITH.CLERK..P..w.]
B7222DF0 0101110C 09C20201 15C102FF BFDB0610 [................]
Block header dump: 0x01000020
Object id on Block? Y
seg/obj: 0xc7cc csc: 0x00.6bfd9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.011.000000f2 0x00805794.00c8.49 --U- 14 fsc 0x0000.0006bfdb
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xb7220e64
===============
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0xb7220e64
bdba: 0x01000020
76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d61
avsp=0x1d33
tosp=0x1d33
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1f72
0x14:pri[1] offs=0x1f47
0x16:pri[2] offs=0x1f1c
0x18:pri[3] offs=0x1ef3
0x1a:pri[4] offs=0x1ec6
0x1c:pri[5] offs=0x1e9d
0x1e:pri[6] offs=0x1e74
0x20:pri[7] offs=0x1e4c
0x22:pri[8] offs=0x1e26
0x24:pri[9] offs=0x1dfb
0x26:pri[10] offs=0x1dd5
0x28:pri[11] offs=0x1daf
0x2a:pri[12] offs=0x1d88
0x2c:pri[13] offs=0x1d61
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1f47
tl: 43 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 2] c2 11
col 6: [ 2] c2 04
col 7: [ 2] c1 1f
tab 0, row 2, @0x1f1c
tl: 43 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 06
col 7: [ 2] c1 1f
tab 0, row 3, @0x1ef3
tl: 41 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4c 43
col 1: [ 5] 4a 4f 4e 45 53
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 04 02 01 01 01
col 5: [ 3] c2 1e 4c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 4, @0x1ec6
tl: 45 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4d 37
col 1: [ 6] 4d 41 52 54 49 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 1c 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 0f
col 7: [ 2] c1 1f
tab 0, row 5, @0x1e9d
tl: 41 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 3] c2 1d 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 6, @0x1e74
tl: 41 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 1f
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 8, @0x1e26
tl: 38 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 9, @0x1dfb
tl: 43 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4f 2d
col 1: [ 6] 54 55 52 4e 45 52
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 08 01 01 01
col 5: [ 2] c2 10
col 6: [ 1] 80
col 7: [ 2] c1 1f
tab 0, row 10, @0x1dd5
tl: 38 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 4f 4d
col 1: [ 5] 41 44 41 4d 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 59
col 4: [ 7] 77 bb 05 17 01 01 01
col 5: [ 2] c2 0c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 11, @0x1daf
tl: 38 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 2] c2 50
col 1: [ 5] 4a 41 4d 45 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 3] c2 0a 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 12, @0x1d88
tl: 39 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 50 03
col 1: [ 4] 46 41 52 44
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 2] c2 1f
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 13, @0x1d61
tl: 39 fb: --H-FL-- lb: 0x1 cc: 8
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
col 6: *NULL*
col 7: [ 2] c1 0b
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
Убили-и!!
SQL> delete from scott.emp where ename='FARD';
1 row deleted.
SQL> commit;
Commit complete.
Смотрим опять дамп блока файла
SQL> alter system dump datafile 4 block 32;
System altered.
*** 2009-06-02 11:33:18.294
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.0008064a seq: 0x02 flg: 0x02 tail: 0x064a0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB7220E00 to 0xB7222E00
B7220E00 0000A206 01000020 0008064A 02020000 [.... ...J.......]
B7220E10 00000000 00180001 0000C7CC 00080648 [............H...]
B7220E20 1FE80000 00321F02 01000019 00110003 [......2.........]
B7220E30 000000F2 00805794 004900C8 00008000 [.....W....I.....]
B7220E40 0006BFDB 001D0001 000000CA 00800212 [................]
B7220E50 001E00A3 00252001 0008064A 00000000 [..... %.J.......]
B7220E60 00000000 000E0100 002EFFFF 1D331D61 [............a.3.]
B7220E70 00001D5A 1F72000E 1F1C1F47 1EC61EF3 [Z.....r.G.......]
B7220E80 1E741E9D 1E261E4C 1DD51DFB 1D881DAF [..t.L.&.........]
B7220E90 00001D61 00000000 00000000 00000000 [a...............]
B7220EA0 00000000 00000000 00000000 00000000 [................]
Repeat 465 times
B7222BC0 00000000 08002C00 2350C203 4C494D06 [.....,....P#.MIL]
B7222BD0 0552454C 52454C43 4EC2034B B6770753 [LER.CLERK..NS.w.]
B7222BE0 01011701 0EC20201 0BC102FF 0308023C [............<...]
B7222BF0 040350C2 44524146 414E4107 5453594C [.P..FARD.ANALYST]
B7222C00 434CC203 0CB57707 01010103 FF1FC202 [..LC.w..........]
B7222C10 2C15C102 C2020800 414A0550 0553454D [...,....P.JAMES.]
B7222C20 52454C43 4DC2034B B5770763 0101030C [CLERK..Mc.w.....]
B7222C30 0AC20301 C102FF33 08002C1F 4D4FC203 [....3....,....OM]
B7222C40 41444105 4305534D 4B52454C 594EC203 [.ADAMS.CLERK..NY]
B7222C50 05BB7707 01010117 FF0CC202 2C15C102 [.w.............,]
B7222C60 C2030800 54062D4F 454E5255 41530852 [....O-.TURNER.SA]
B7222C70 4D53454C C2034E41 7707634D 010809B5 [LESMAN..Mc.w....]
B7222C80 C2020101 02800110 002C1FC1 4FC20308 [..........,....O]
B7222C90 494B0428 5009474E 49534552 544E4544 [(.KING.PRESIDENT]
B7222CA0 B57707FF 0101110B 33C20201 0BC102FF [..w........3....]
B7222CB0 0308002C 05594EC2 544F4353 4E410754 [,....NY.SCOTT.AN]
B7222CC0 53594C41 4CC20354 BB770743 01011304 [ALYST..LC.w.....]
B7222CD0 1FC20201 15C102FF 0308002C 05534EC2 [........,....NS.]
B7222CE0 52414C43 414D074B 4547414E 4FC20352 [CLARK.MANAGER..O]
B7222CF0 B5770728 01010906 19C20301 C102FF33 [(.w.........3...]
B7222D00 08002C0B 634DC203 414C4205 4D07454B [.,....Mc.BLAKE.M]
B7222D10 47414E41 C2035245 7707284F 010105B5 [ANAGER..O(.w....]
B7222D20 C2030101 02FF331D 002C1FC1 4DC20308 [.....3....,....M]
B7222D30 414D0637 4E495452 4C415308 414D5345 [7.MARTIN.SALESMA]
B7222D40 4DC2034E B5770763 01011C09 0DC20301 [N..Mc.w.........]
B7222D50 0FC20233 2C1FC102 C2030800 4A05434C [3......,....LC.J]
B7222D60 53454E4F 4E414D07 52454741 284FC203 [ONES.MANAGER..O(]
B7222D70 04B57707 01010102 4C1EC203 15C102FF [.w.........L....]
B7222D80 0308002C 04164CC2 44524157 4C415308 [,....L..WARD.SAL]
B7222D90 414D5345 4DC2034E B5770763 01011602 [ESMAN..Mc.w.....]
B7222DA0 0DC20301 06C20233 2C1FC102 C2030800 [....3......,....]
B7222DB0 4105644B 4E454C4C 4C415308 414D5345 [Kd.ALLEN.SALESMA]
B7222DC0 4DC2034E B5770763 01011402 11C20201 [N..Mc.w.........]
B7222DD0 0204C202 002C1FC1 4AC20308 4D530546 [......,....JF.SM]
B7222DE0 05485449 52454C43 50C2034B B4770703 [ITH.CLERK..P..w.]
B7222DF0 0101110C 09C20201 15C102FF 064A0602 [..............J.]
Block header dump: 0x01000020
Object id on Block? Y
seg/obj: 0xc7cc csc: 0x00.80648 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.011.000000f2 0x00805794.00c8.49 C--- 0 scn 0x0000.0006bfdb
0x02 0x0001.01d.000000ca 0x00800212.00a3.1e --U- 1 fsc 0x0025.0008064a
data_block_dump,data header at 0xb7220e64
===============
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0xb7220e64
bdba: 0x01000020
76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d61
avsp=0x1d33
tosp=0x1d5a
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1f72
0x14:pri[1] offs=0x1f47
0x16:pri[2] offs=0x1f1c
0x18:pri[3] offs=0x1ef3
0x1a:pri[4] offs=0x1ec6
0x1c:pri[5] offs=0x1e9d
0x1e:pri[6] offs=0x1e74
0x20:pri[7] offs=0x1e4c
0x22:pri[8] offs=0x1e26
0x24:pri[9] offs=0x1dfb
0x26:pri[10] offs=0x1dd5
0x28:pri[11] offs=0x1daf
0x2a:pri[12] offs=0x1d88
0x2c:pri[13] offs=0x1d61
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1f47
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 2] c2 11
col 6: [ 2] c2 04
col 7: [ 2] c1 1f
tab 0, row 2, @0x1f1c
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 06
col 7: [ 2] c1 1f
tab 0, row 3, @0x1ef3
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 43
col 1: [ 5] 4a 4f 4e 45 53
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 04 02 01 01 01
col 5: [ 3] c2 1e 4c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 4, @0x1ec6
tl: 45 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 37
col 1: [ 6] 4d 41 52 54 49 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 1c 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 0f
col 7: [ 2] c1 1f
tab 0, row 5, @0x1e9d
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 3] c2 1d 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 6, @0x1e74
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 1f
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 8, @0x1e26
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 9, @0x1dfb
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 2d
col 1: [ 6] 54 55 52 4e 45 52
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 08 01 01 01
col 5: [ 2] c2 10
col 6: [ 1] 80
col 7: [ 2] c1 1f
tab 0, row 10, @0x1dd5
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 4d
col 1: [ 5] 41 44 41 4d 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 59
col 4: [ 7] 77 bb 05 17 01 01 01
col 5: [ 2] c2 0c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 11, @0x1daf
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 2] c2 50
col 1: [ 5] 4a 41 4d 45 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 3] c2 0a 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 12, @0x1d88
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 13, @0x1d61
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
col 6: *NULL*
col 7: [ 2] c1 0b
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
Берем дамп со смещением в начало блока.
BBED> dump /v dba 4,32 offset 7652 count 64
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7652 to 7715 Dba:0x01000020
-------------------------------------------------------
0102c20e ff02c10b 3c020803 c2500304 l .. ... .<... P..
46415244 07414e41 4c595354 03c24c43 l FARD.ANALYST. LC
0777b50c 03010101 02c21fff 02c1152c l .w ...... ... .,
000802c2 50054a41 4d455305 434c4552 l ... P.JAMES.CLER
<16 bytes per line>
По дампу мы видим что строка 12 со смещением от начала блока @0x1d88. Смотрим с чего начинается строка.
BBED> p *kdbr[12]
rowdata[39]
-----------
ub1 rowdata[39] @7660 0x3c
Ну это я привел дамп на начало строки.
BBED> dump /v dba 4,32 offset 7660 count 64
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7660 to 7723 Dba:0x01000020
-------------------------------------------------------
3c020803 c2500304 46415244 07414e41 l <... P..FARD.ANA
4c595354 03c24c43 0777b50c 03010101 l LYST. LC.w .....
02c21fff 02c1152c 000802c2 50054a41 l . ... .,... P.JA
4d455305 434c4552 4b03c24d 630777b5 l MES.CLERK. Mc.w
<16 bytes per line>
Надо бы разобраться подробнее почему вычесть 16(десятичн) нужно от значения 1 байта строки. Пока возьму как аксиому.
BBED> modify /x 2c offset 7660
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7660 to 7723 Dba:0x01000020
------------------------------------------------------------------------
2c020803 c2500304 46415244 07414e41 4c595354 03c24c43 0777b50c 03010101
02c21fff 02c1152c 000802c2 50054a41 4d455305 434c4552 4b03c24d 630777b5
<32 bytes per line>
Для наглядности смотрим чего произошло.
BBED> dump /v dba 4,32 offset 7660 count 64
File: /home/oracle/oracle/product/10.2.0/oradata/pahom/users01.dbf (4)
Block: 32 Offsets: 7660 to 7723 Dba:0x01000020
-------------------------------------------------------
2c020803 c2500304 46415244 07414e41 l ,... P..FARD.ANA
4c595354 03c24c43 0777b50c 03010101 l LYST. LC.w .....
02c21fff 02c1152c 000802c2 50054a41 l . ... .,... P.JA
4d455305 434c4552 4b03c24d 630777b5 l MES.CLERK. Mc.w
<16 bytes per line>
Правим контрольную сумму.
BBED> sum dba 4,32
Check value for File 4, Block 32:
current = 0x16d8, required = 0x16c8
BBED> sum dba 4,32 apply
Check value for File 4, Block 32:
current = 0x16c8, required = 0x16c8
Сбрасываем буфер.
SQL> alter system flush buffer_cache;
System altered.
Проверяем что у нас все вернулось.
SQL> select * from scott.emp where ename='FARD';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FARD ANALYST 7566 03-DEC-81 3000
20
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FARD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
Блин какой я умный. Пойду попью пивка.
