oraclebbed恢复删除数据实例
2023-06-13 09:15:11 时间
恢复己删除数据
一、创建模拟环境
SQL>createtablehr.xifenfei(idnumber,namevarchar2(20))tablespacexff;
Tablecreated.
SQL>insertintohr.xifenfeivalues(1,"xifenfei");
1rowcreated.
SQL>insertintohr.xifenfeivalues(2,"xff");
1rowcreated.
SQL>commit;
Commitcomplete.
SQL>select*fromhr.xifenfei;
IDNAME
———-——————?
1xifenfei
2xff
SQL>selectrowid,
2dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3dbms_rowid.rowid_block_number(rowid)blockno,
4dbms_rowid.rowid_row_number(rowid)rowno
5fromhr.xifenfei;
ROWIDREL_FNOBLOCKNOROWNO
—————————-———-———-
AAAHy3AACAAAAISAAA25300
AAAHy3AACAAAAISAAB25301
查询file#，block，后面恢复要用
SQL>deletefromhr.xifenfeiwhereid=2;
1rowdeleted.
SQL>commit;
Commitcomplete.
SQL>select*fromhr.xifenfei;
IDNAME
———-——————?
1xifenfei
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
二、bbed恢复删除数据
[oracle@localhost~]$bbedparfile=/tmp/parfile.cnf
Password:
BBED:Release2.0.0.0.0?LimitedProductiononMonAug2201:52:522011
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.
*************!!!ForOracleInternalUseonly!!!***************
BBED>showall
FILE#2
BLOCK#1
OFFSET0
DBA0×00800001(83886092,1)
FILENAME/opt/oracle/oradata/xifenfei/xff01.dbf
BIFILEbifile.bbd
LISTFILE/tmp/list
BLOCKSIZE8192
MODEEdit
EDITUnrecoverable
IBASEDec
OBASEDec
WIDTH80
COUNT512
LOGFILElog.bbd
SPOOLNo
BBED>setdba2,530
DBA0×00800212(83891382,530)
BBED>find/cxff
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8170to8191Dba:0×00800212
————————————————————————
7866662c000202c10208786966656e666569010680e2
<32bytesperline>
BBED>dump/v
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8170to8191Dba:0×00800212
——————————————————-
7866662c000202c10208786966656e66lxff,……xifenf
6569010680e2lei….
<16bytesperline>
BBED>dump/voffset8160
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8160to8191Dba:0×00800212
——————————————————-
0000003c020202c103037866662c0002l…<......xff,..
02c10208786966656e666569010680e2l....xifenfei....
<16bytesperline>
BBED>dump/voffset8164
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8164to8191Dba:0×00800212
——————————————————-
020202c103037866662c000202c10208l……xff,……
786966656e666569010680e2lxifenfei….
<16bytesperline>
BBED>dump/voffset8162
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8162to8191Dba:0×00800212
——————————————————-
003c020202c103037866662c000202c1l.<......xff,....
0208786966656e666569010680e2l..xifenfei....
<16bytesperline>
BBED>dump/voffset8163
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8163to8191Dba:0×00800212
——————————————————-
3c020202c103037866662c000202c102l<......xff,.....
08786966656e666569010680e2l.xifenfei....
<16bytesperline>
通过尝试，推断出来3c的offset
BBED>modify/x2c
Warning:contentsofpreviousBIFILEwillbelost.Proceed?(Y/N)y
File:/opt/oracle/oradata/xifenfei/xff01.dbf(2)
Block:530Offsets:8163to8191Dba:0×00800212
————————————————————————
2c020202c103037866662c000202c10208786966656e666569010680e2
<32bytesperline>
修改3c为2c
BBED>sumapply
CheckvalueforFile2,Block530:
current=0xb1b9,required=0xb1b9
三、核对结果
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea236000356bytes
FixedSize451684bytes
VariableSize201326592bytes
DatabaseBuffers33554432bytes
RedoBuffers667648bytes
Databasemounted.
Databaseopened.
SQL>select*fromhr.xifenfei;
IDNAME
———-——————?
1xifenfei
2xff
说明：
1）如果数据未删除：rowflag的值为32+8+4=44或者0x2c
2）如果数据被删除：rowflag的值为32+16+8+4=60或者0x3c
找回被删除数据
创建模拟表数据
SQL>createtablet_xifenfei(idnumber,namevarchar2(10));
Tablecreated.
SQL>insertintot_xifenfeivalues(1,"xifenfei");
1rowcreated.
SQL>insertintot_xifenfeivalues(2,"XIFENFEI");
1rowcreated.
SQL>commit;
Commitcomplete.
dump数据块
SQL>altersystemflushBUFFER_CACHE;
Systemaltered.
SQL>select rowid,id,name,
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 fromchf.t_xifenfei;
ROWID IDNAME REL_FNO BLOCKNO ROWNO
--------------------------------------------------------------------
AAASdmAAEAAAACvAAA 1xifenfei 4 175 0
AAASdmAAEAAAACvAAB 2XIFENFEI 4 175 1
SQL>altersystemdumpdatafile4block175;
Systemaltered.
dump文件内容
block_row_dump:
tab0,row0,@0x1f89
tl:15fb:--H-FL--lb:0x1 cc:2
col 0:[2] c102
col 1:[8] 786966656e666569
tab0,row1,@0x1f7a
tl:15fb:--H-FL--lb:0x1 cc:2
col 0:[2] c103
col 1:[8] 584946454e464549
end_of_block_dump
2012-05-0105:09:29.287714:kjbmbassert[0xaf.4]
Enddumpdatablockstsn:4file#:4minblk175maxblk175
删除表数据
SQL>deletefromt_xifenfei;
2rowsdeleted.
SQL>commit;
Commitcomplete.
SQL>altersystemflushBUFFER_CACHE;
Systemaltered.
SQL>altersystemdumpdatafile4block175;
Systemaltered.
dump文件内容
block_row_dump:
tab0,row0,@0x1f89
tl:2fb:--HDFL--lb:0x2
tab0,row1,@0x1f7a
tl:2fb:--HDFL--lb:0x2
end_of_block_dump
2012-05-0105:13:35.214357:kjbmbassert[0xaf.4]
Enddumpdatablockstsn:4file#:4minblk175maxblk175
通过对比这两次的dump文件发现
1.数据内容被删除,并不是真正删除,而是给其增加了一个标识位(fd:---D----)
2.fb:--H-FL--(headofrowpiece+firstdatapiece+lastdatapiece)
其有8个选项每个选项的值分别对应bitmask即32+8+4=44or0x2c
3.如果一个row被delete了,那么rowflag就会更新,bitmask里的deleted被设置为16.
此时rowflag为：32+16+8+4=60or0x3c.
4.如果我们要找回来被删除的数据,只需要把3c改为2c即可
关闭数据库
SQL>select*fromchf.t_xifenfei;
norowsselected
SQL>selectnamefromv$datafilewherefile#=4;
NAME
------------------------------------------------
/tmp/user01.dbf
SQL>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
bbed修改数据
BBED>setfilename"/tmp/user01.dbf"
FILENAME /tmp/user01.dbf
BBED>setblock175
BLOCK# 175
BBED>setblocksize8192
BLOCKSIZE 8192
BBED>setmodeedit
MODE Edit
BBED>map
File:/tmp/user01.dbf(0)
Block:175 Dba:0x00000000
------------------------------------------------------------
KTBDataBlock(Table/Cluster)
structkcbh,20bytes @0
structktbbh,72bytes @20
structkdbh,14bytes @100
structkdbt[1],4bytes @114
sb2kdbr[2] @118
ub1freespace[8036] @122
ub1rowdata[30] @8158
ub4tailchk @8188
BBED>p*kdbr[0]
rowdata[15]
-----------
ub1rowdata[15] @8173 0x3c
BBED>p*kdbr[1]
rowdata[0]
----------
ub1rowdata[0] @8158 0x3c
BBED>m/x2coffset8158
File:/tmp/user01.dbf(0)
Block:175 Offsets:8158to8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202c10308584946454e46454932630202c10208786966656e6665690106
b47e
<32bytesperline>
BBED> m/x2coffset8173
File:/tmp/user01.dbf(0)
Block:175 Offsets:8173to8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202c10208786966656e6665690106b47e
<32bytesperline>
BBED>sumapply
CheckvalueforFile0,Block175:
current=0x4d13,required=0x4d13
启动数据库验证
SQL>startup
ORACLEinstancestarted.
TotalSystemGlobalArea 535662592bytes
FixedSize 1346140bytes
VariableSize 411043236bytes
DatabaseBuffers 117440512bytes
RedoBuffers 5832704bytes
Databasemounted.
Databaseopened.
SQL>select*fromchf.t_xifenfei;
IDNAME
--------------------
1xifenfei
2XIFENFEI
相关文章
- coco数据集语义分割_实例分割模型
- 实例分割算法_实例分割数据集制作
- 大数据应用实例[通俗易懂]
- 超全sql注入实用语句_sql注入语句实例大全
- 【视频】风险价值VaR原理与Python蒙特卡罗Monte Carlo模拟计算投资组合实例|附代码数据
- 【视频】风险价值VaR原理与Python蒙特卡罗Monte Carlo模拟计算投资组合实例|附代码数据
- SQL开发知识：SQLserver中cube：多维数据集实例详解
- oracle数据匹配merge into的实例详解
- Kafka性能测试实例详解大数据
- python子类如何继承父类的实例变量详解编程语言
- Redis应用实例——让您快速提升数据存储和查询效率（redis应用实例）
- 查看 Oracle 实例名：一步一步教程（查看oracle实例名）
- Oracle数据库1亿数据精简式清理实例（oracle1亿数据清理）
- php编程实现获取excel文档内容的代码实例
- androidLinearLayout布局实例代码
- jqgrid表格数据导出实例
- JqueryAjax解析XML数据(同步及异步调用)简单实例
- ThinkPHP3.1之D方法实例详解
- python3生成随机数实例
- Android中生成、使用Json数据实例
- thinkphp的URL路由规则与配置实例
- Nodejs中读取中文文件编码问题、发送邮件和定时任务实例
- Ruby中常用的字符串处理函数使用实例
- PHP实现AES256加密算法实例
- php实现的返回数据格式化类实例