-
加我公众号(orasos)
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,884)
- DB2 (22)
- MySQL (82)
- Oracle (1,709)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (662)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁（RUs+OJVM）-19.33
- 由于con$.con#值达到极限，触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
分类目录归档：数据库
bbed 恢复 GLOBAL_NAME 为空故障
看到dbsnake关于UPDATE GLOBAL_NAME为空之后的恢复写的不是很完整,自己通过试验和对bbed的研究,完善他的blog内容(泄露一点内部的东西)
模拟错误
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 * from global_name ; GLOBAL_NAME -------------------------------------------------------------------- ORA11G SQL> update global_name set global_name=''; 1 row updated. SQL> COMMIT; Commit complete. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 260049780 bytes Database Buffers 46137344 bytes Redo Buffers 6328320 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Process ID: 28306 Session ID: 125 Serial number: 5
alert日志
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc (incident=20556): ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_20556/ora11g_ora_28306_i20556.trc Wed Aug 08 23:21:48 2012 Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc: ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 28306): terminating the instance due to error 600 Instance terminated by USER, pid = 28306 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (28306) as a result of ORA-1092 Wed Aug 08 23:21:48 2012 ORA-1092 : opitsk aborting process
找出global_name相关信息(另外库中)
SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','GLOBAL_NAME','SYS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'
SQL> select * from props$ where value$='XIFENFEI';
NAME VALUE$
------------------------------ ----------------------------------------
COMMENT$
--------------------------------------------------------------------------
GLOBAL_DB_NAME XIFENFEI
Global database name
SQL> select dump('GLOBAL_DB_NAME','16') from dual;
DUMP('GLOBAL_DB_NAME','16')
--------------------------------------------------------
Typ=96 Len=14: 47,4c,4f,42,41,4c,5f,44,42,5f,4e,41,4d,45
--得出GLOBAL_DB_NAME存储字16进制字符串为:0e474c4f42414c5f44425f4e414d45
bbed操作
--通过第三方工具定位props$表中的GLOBAL_DB_NAME列在数据块0x00400321的31行上
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 00:26:12 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/oracle/oradata/ora11g/system01.dbf 0
2 /u01/oracle/oradata/ora11g/sysaux01.dbf 0
3 /u01/oracle/oradata/ora11g/undotbs01.dbf 0
4 /u01/oracle/oradata/ora11g/users01.dbf 0
BBED> set block 801
BLOCK# 801
BBED> map
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Dba:0x00400321
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[37] @110
ub1 freespace[5771] @184
ub1 rowdata[2233] @5955
ub4 tailchk @8188
BBED> p *kdbr[31]
rowdata[0]
----------
ub1 rowdata[0] @5955 0x2c
BBED> x /rccc
rowdata[0] @5955
----------
flag@5955: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5956: 0x02
cols@5957: 3
col 0[14] @5958: GLOBAL_DB_NAME
col 1[0] @5973: *NULL*
col 2[20] @5974: Global database name
BBED> set count 64
COUNT 64
BBED> f /x 0e474c4f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5958 to 6021 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d45ff 14476c6f 62616c20 64617461 62617365
206e616d 652c0003 0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447
<32 bytes per line>
BBED> f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5998 to 6061 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447 6c6f6261 6c206461
74616261 7365206e 616d652c 00030a44 4254494d 455a4f4e 45053030 3a30300c
<32 bytes per line>
BBED> f
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 6460 to 6523 Dba:0x00400321
------------------------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4508 53454544 44415441 14476c6f 62616c20
64617461 62617365 206e616d 652c0003 114e4c53 5f524442 4d535f56 45525349
<32 bytes per line>
BBED> f
BBED-00212: search string not found
BBED> d /v offset 5958
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5958 to 6021 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d45ff l .GLOBAL_DB_NAME.
14476c6f 62616c20 64617461 62617365 l .Global database
206e616d 652c0003 0e474c4f 42414c5f l name,...GLOBAL_
44425f4e 414d4506 4f524131 31471447 l DB_NAME.ORA11G.G
<16 bytes per line>
BBED> d /v offset 5998
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5998 to 6061 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4506 l .GLOBAL_DB_NAME.
4f524131 31471447 6c6f6261 6c206461 l ORA11G.Global da
74616261 7365206e 616d652c 00030a44 l tabase name,...D
4254494d 455a4f4e 45053030 3a30300c l BTIMEZONE.00:00.
<16 bytes per line>
BBED> d /v offset 6460
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 6460 to 6523 Dba:0x00400321
-------------------------------------------------------
0e474c4f 42414c5f 44425f4e 414d4508 l .GLOBAL_DB_NAME.
53454544 44415441 14476c6f 62616c20 l SEEDDATA.Global
64617461 62617365 206e616d 652c0003 l database name,..
114e4c53 5f524442 4d535f56 45525349 l .NLS_RDBMS_VERSI
<16 bytes per line>
--通过时上面的查找可以知道数据库对GLOBAL_DB_NAME有进行两次update操作
--GLOBAL_DB_NAME初始化值SEEDDATA,第一次更新为ORA11G,第二次更新为null
BBED> p kdbr[31]
sb2 kdbr[31] @172 5863
--这里可以发现我们看到offset 5995开始有值,但是row directory却指向了5863
BBED> d offset 5863 count 128
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5863 to 5990 Dba:0x00400321
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c02030e
474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520
<32 bytes per line>
--通过dump看到row directory指向的值和实际的数据相差(5995-5863),都是0
BBED> d /v offset 5900
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5900 to 6027 Dba:0x00400321
-------------------------------------------------------
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 0000002c 02030e47 4c4f4241 l .......,...GLOBA
4c5f4442 5f4e414d 45ff1447 6c6f6261 l L_DB_NAME..Globa
6c206461 74616261 7365206e 616d652c l l database name,
00030e47 4c4f4241 4c5f4442 5f4e414d l ...GLOBAL_DB_NAM
45064f52 41313147 14476c6f 62616c20 l E.ORA11G.Global
<16 bytes per line>
--需要指定的新值前面也存在同样的0,所以模仿的处理方法,让row directory同样向前偏移92
BBED> m /x 0f17 offset 172
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 172 to 299 Dba:0x00400321
------------------------------------------------------------------------
0f176018 16189a17 5e173d17 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> p kdbr[31]
sb2 kdbr[31] @172 5903
BBED> p *kdbr[31]
rowdata[40]
-----------
ub1 rowdata[40] @5995 0x2c
BBED> x /rccc
rowdata[40] @5995
-----------
flag@5995: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5996: 0x00
cols@5997: 3
col 0[14] @5998: GLOBAL_DB_NAME
col 1[6] @6013: ORA11G
col 2[20] @6020: Global database name
修改lock信息
BBED> m /x 02 offset 5996
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5996 to 6123 Dba:0x00400321
------------------------------------------------------------------------
02030e47 4c4f4241 4c5f4442 5f4e414d 45064f52 41313147 14476c6f 62616c20
64617461 62617365 206e616d 652c0003 0a444254 494d455a 4f4e4505 30303a30
300c4442 2074696d 65207a6f 6e652c00 02174e4f 5f555345 5249445f 56455249
46494552 5f53414c 54203633 39364335 38414231 37414530 30374539 41373238
<32 bytes per line>
BBED> d offset 5955
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5955 to 6082 Dba:0x00400321
------------------------------------------------------------------------
2c02030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162
61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131
4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45
5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553
<32 bytes per line>
BBED> m /x 2c00
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 5955 to 6082 Dba:0x00400321
------------------------------------------------------------------------
2c00030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162
61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131
4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45
5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553
<32 bytes per line>
--验证块
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: the amount of space used is not equal to block size
used=2056 fsc=6 avsp=6040 dtl=8096
Block 801 failed with check code 6110
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
--修改_ktbitfsc信息，让其通过块验证
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0004
ub2 kxidslt @46 0x000e
ub4 kxidsqn @48 0x000001d4
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c00a93
ub2 kubaseq @56 0x0083
ub1 kubarec @58 0x33
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x000c78fe
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x000a
ub2 kxidslt @70 0x0016
ub4 kxidsqn @72 0x000001eb
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c0015b
ub2 kubaseq @80 0x008f
ub1 kubarec @82 0x0d
ub2 ktbitflg @84 0x0001 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 6
ub2 _ktbitwrp @86 0x0006
ub4 ktbitbas @88 0x00000000
BBED> m /x 00 offset 86
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 86 to 213 Dba:0x00400321
------------------------------------------------------------------------
00000000 00000001 25000600 5c00e716 98179e17 00002500 701f571e 92189c1e
101efb1d ffff981d 551d0f1d d91cb71c 941c731c 4a1c1a1c ef1bc51b 9c1b701b
471b191b d61a931a 3c1a101a ee19bc19 86194e19 0d190f17 60181618 9a175e17
3d170000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe830, required = 0xe830
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: space available on commit is incorrect
tosp=6046 fsc=0 stb=0 avsp=6040
Block 801 failed with check code 6111
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
--修改kdbhtosp信息
BBED> p kdbh
struct kdbh, 14 bytes @92
ub1 kdbhflag @92 0x00 (NONE)
b1 kdbhntab @93 1
b2 kdbhnrow @94 37
sb2 kdbhfrre @96 6
sb2 kdbhfsbo @98 92
sb2 kdbhfseo @100 5863
b2 kdbhavsp @102 6040
b2 kdbhtosp @104 6046
BBED> d offset 102
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 102 to 229 Dba:0x00400321
------------------------------------------------------------------------
98179e17 00002500 701f571e 92189c1e 101efb1d ffff981d 551d0f1d d91cb71c
941c731c 4a1c1a1c ef1bc51b 9c1b701b 471b191b d61a931a 3c1a101a ee19bc19
86194e19 0d190f17 60181618 9a175e17 3d170000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> d offset 104
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 104 to 231 Dba:0x00400321
------------------------------------------------------------------------
9e170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c
731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619
4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 98
File: /u01/oracle/oradata/ora11g/system01.dbf (1)
Block: 801 Offsets: 104 to 231 Dba:0x00400321
------------------------------------------------------------------------
98170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c
731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619
4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
--至此修改row directory指针完成
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801
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
重新启动数据库
SQL> startup ORACLE instance started. Total System Global Area 313860096 bytes Fixed Size 1344652 bytes Variable Size 260049780 bytes Database Buffers 46137344 bytes Redo Buffers 6328320 bytes Database mounted. Database opened. SQL> select * from global_name ; GLOBAL_NAME -------------------------------------------------------- ORA11G SQL>
利用bbed找回ORACLE更新前值
模拟数据块更新
SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
ID ROWID REL_FNO BLOCKNO ROWNO
---------- ------------------ ---------- ---------- ----------
1 AAASc+AAEAAAACvAAA 4 175 0
2 AAASc+AAEAAAACvAAB 4 175 1
SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46
SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
SQL> alter system checkpoint;
System altered.
SQL> select * from t_xifenfei;
ID NAME
---------- ----------
1 XIFENFEI
2 CHF
这里我们对数据库进行了一次更新操作,并且dump出来对应值,为了方便定位到相应记录
bbed查看相关值
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 8 20:50:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 175
FILE# 4
BLOCK# 175
BBED> map
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Dba:0x010000af
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8031] @122
ub1 rowdata[35] @8153
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @118 8053
sb2 kdbr[1] @120 8068
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15] @8168 0x2c
BBED> x /rnc
rowdata[15] @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170: 2
col 0[2] @8171: 2
col 1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8153 0x2c
BBED> x /rnc
rowdata[0] @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155: 2
col 0[2] @8156: 1
col 1[8] @8159: XIFENFEI
BBED> set count 64
COUNT 64
<32 bytes per line>
BBED> d /v
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8153 to 8191 Dba:0x010000af
-------------------------------------------------------
2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
000202c1 03034348 462c0002 02c10203 l ......CHF,......
58464602 068de8 l XFF....
<16 bytes per line>
使用bbed找回历史值
--准备工作,通过dump出来的值,推算出来第一条记录的起点02c10203584646,
--在这个值的基础上offset-3得到offset值为8078
BBED> p kdbr
sb2 kdbr[0] @118 8053
sb2 kdbr[1] @120 8068
--修改row directory指针位置
BBED> m /x 8e1f
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 118 to 181 Dba:0x010000af
------------------------------------------------------------------------
8e1f841f 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> p kdbr
sb2 kdbr[0] @118 8078
sb2 kdbr[1] @120 8068
BBED> sum apply
Check value for File 4, Block 175:
current = 0xdff8, required = 0xdff8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: xaction header lock count mismatch
trans=2 ilk=1 nlo=0 --提示事务锁错误
Block 175 failed with check code 6108
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
Message 531 not found; product=RDBMS; facility=BBED
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25] @8178 0x2c
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8178 to 8191 Dba:0x010000af
------------------------------------------------------------------------
2c000202 c1020358 46460206 8de8
<32 bytes per line>
BBED> x /rnc
rowdata[25] @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x00 --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2
cols@8180: 2
col 0[2] @8181: 1
col 1[3] @8184: XFF
--修改事务锁标识为2
BBED> m /x 02 offset 8179
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8179 to 8191 Dba:0x010000af
------------------------------------------------------------------------
020202c1 02035846 4602068d e8
<32 bytes per line>
BBED> set offset 8153
OFFSET 8153
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8153 to 8191 Dba:0x010000af
------------------------------------------------------------------------
2c020202 c1020858 4946454e 4645492c 000202c1 03034348 462c0202 02c10203
58464602 068de8
<32 bytes per line>
--把更新后值的事务锁标识改为0
BBED> m /x 00 offset +1
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8154 to 8191 Dba:0x010000af
------------------------------------------------------------------------
000202c1 02085849 46454e46 45492c00 0202c103 03434846 2c020202 c1020358
46460206 8de8
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddfa, required = 0xddfa
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
used=42 fsc=0 avsp=8041 dtl=8088 -->提示块的空间使用不正确
Block 175 failed with check code 6110
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
Message 531 not found; product=RDBMS; facility=BBED
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x0001273e
ub4 ktbbhod1 @24 0x0001273e
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x0000e88a
ub2 kscnwrp @32 0x0002
sb2 ktbbhict @36 2
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x010000a8
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0006
ub2 kxidslt @46 0x001e
ub4 kxidsqn @48 0x000002c6
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00c000d9
ub2 kubaseq @56 0x0086
ub1 kubarec @58 0x2a
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 2
ub2 _ktbitwrp @62 0x0002
ub4 ktbitbas @64 0x0000e550
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0006
ub2 kxidslt @70 0x0008
ub4 kxidsqn @72 0x000002c7
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c000da
ub2 kubaseq @80 0x0086
ub1 kubarec @82 0x12
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x0000e88d
--所有的_ktbitfsc修改为0
BBED> m /x 00 offset 62
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 62 to 125 Dba:0x010000af
------------------------------------------------------------------------
000050e5 00000600 0800c702 0000da00 c0008600 12000120 00008de8 00000000
00000000 00000001 0200ffff 1600751f 691f691f 00000200 8e1f841f 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
used=42 fsc=0 avsp=8041 dtl=8088
Block 175 failed with check code 6110
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
Message 531 not found; product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 1
sb2 kdbhnrow @102 2
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 22
sb2 kdbhfseo @108 8053
sb2 kdbhavsp @110 8045
sb2 kdbhtosp @112 8045
--修改kdbhtosp和kdbhavsp值
BBED> m /x 6e1f offset 112
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 112 to 175 Dba:0x010000af
------------------------------------------------------------------------
6e1f0000 02008e1f 841f0000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 6e1f offset 110
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 110 to 173 Dba:0x010000af
------------------------------------------------------------------------
6e1f6e1f 00000200 8e1f841f 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
--数据块验证通过
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
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
Message 531 not found; product=RDBMS; facility=BBED
重启数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 251661172 bytes
Database Buffers 54525952 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
--找回更新前值
SQL> select * from chf.t_xifenfei;
ID NAME
---------- ----------
1 XFF
2 CHF
ORACLE update 操作内部原理
对于oracle的update操作,在数据块中具体是如何出来,是直接更新原来值,还是通过插入新值修改指针的方法实现.下面通过证明:
模拟表插入数据
SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
ID ROWID REL_FNO BLOCKNO ROWNO
---------- ------------------ ---------- ---------- ----------
1 AAASc+AAEAAAACvAAA 4 175 0
2 AAASc+AAEAAAACvAAB 4 175 1
SQL> alter system dump datafile 4 block 175;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_24625.trc
数据存储对应16进制值
SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46
得出第一条记录对应值为:02c10203584646;第二条记录对应值为:02c10303434846
dump 数据块得到记录
bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f84
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f8e ---->8078
0x14:pri[1] offs=0x1f84 ---->8068
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 3] 58 46 46
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
bbed查看相关记录
BBED> p kdbr sb2 kdbr[0] @118 8078 <--第一条row directory指针位置 sb2 kdbr[1] @120 8068 <--第二条row directory指针位置 BBED> p *kdbr[0] rowdata[10] ----------- ub1 rowdata[10] @8178 0x2c BBED> x /rnc rowdata[10] @8178 ----------- flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 2 col 0[2] @8181: 1 col 1[3] @8184: XFF BBED> p *kdbr[1] rowdata[0] ---------- ub1 rowdata[0] @8168 0x2c BBED> x /rnc rowdata[0] @8168 ---------- flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8169: 0x01 cols@8170: 2 col 0[2] @8171: 2 col 1[3] @8174: CHF BBED> d File: /u01/oracle/oradata/ora11g/users01.dbf (4) Block: 175 Offsets: 8168 to 8191 Dba:0x010000af ------------------------------------------------------------------------ 2c010202 c1030343 48462c01 0202c102 03584646 010650e5 <32 bytes per line>
这里可以得到结论如下:
1.数据是从块的底部开始往上存储
2.在每一条记录的头部分别有flag/lock/cols对应这里的2c0102
3.这里的偏移量和dump出来的数据可以看出来两条记录是连续在一起(偏移量分别为:8168和8178)
更新一条记录
SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
我们可以但看到值有XFF改变为XIFENFEI,存储长度变大
dump数据块信息
bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f75
avsp=0x1f69
tosp=0x1f69
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f75 ---->8053
0x14:pri[1] offs=0x1f84 ---->8068
block_row_dump:
tab 0, row 0, @0x1f75
tl: 15 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 8] 58 49 46 45 4e 46 45 49
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 3] 43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通过对比第一次dump出来的数据块发现:row 0的值和偏移量发生了变化
bbed查看相关记录
BBED> set file 4 block 175
FILE# 4
BLOCK# 175
BBED> map
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Dba:0x010000af
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[2] @118
ub1 freespace[8031] @122
ub1 rowdata[35] @8153
ub4 tailchk @8188
BBED> p kdbr
sb2 kdbr[0] @118 8053 <--第一条row directory指针位置
sb2 kdbr[1] @120 8068 <--第二条row directory指针位置
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15] @8168 0x2c
BBED> x /rnc
rowdata[15] @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170: 2
col 0[2] @8171: 2
col 1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8153 0x2c
BBED> x /r
rowdata[0] @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155: 2
col 0[2] @8156: 0xc1 0x02
col 1[8] @8159: 0x58 0x49 0x46 0x45 0x4e 0x46 0x45 0x49
BBED> set count 64
COUNT 64
<32 bytes per line>
BBED> d /v
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 175 Offsets: 8153 to 8191 Dba:0x010000af
-------------------------------------------------------
2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
000202c1 03034348 462c0002 02c10203 l ......CHF,......
58464602 068de8 l XFF....
<16 bytes per line>
从这里可以看到
1.这里可以看到三个值(XFF,CHF,XIFENFEI)均存在,但是通过p kdbr和dump block不能看到,因为row directory中无指针指定到该值上
2.也是通过row directory指针使得我们从原先看到的第一条记录处于数据块最底部变成了现在相对而言的数据部分最上层,
3.绝大多数情况:数据库更新一条记录,不是直接修改数据值,而是重新插入一条新记录,然后修改row directory指针指定到新的offset上
4.不是直接update,而是insert+指针来实现,这样做的好处:1)如果修改记录update值的长度发生变化(变大或者变小)那么该值之前的数据都要发生变动,对数据库来说成本太高.2)如果直接更新值可能导致其他数据变动,使得其他行受到影响.
5.由于是修改row directory指针,所以该处理方法的rowid值不会发生变化
