oracle bbed恢復(fù)刪除數(shù)據(jù)實(shí)例
更新時(shí)間：2013年11月11日 10:10:03 作者：
在oracle中bbed恢復(fù)刪除數(shù)據(jù)實(shí)例
恢復(fù)己刪除數(shù)據(jù)
一、創(chuàng)建模擬環(huán)境
復(fù)制代碼 代碼如下:
SQL> create table hr.xifenfei (id number,name varchar2(20)) tablespace xff;
Table created.
SQL> insert into hr.xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into hr.xifenfei values(2,'xff');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
SQL> select 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 hr.xifenfei;
ROWID REL_FNO BLOCKNO ROWNO
—————— ———- ———- ———-
AAAHy3AACAAAAISAAA 2 530 0
AAAHy3AACAAAAISAAB 2 530 1
查詢file#，block，后面恢復(fù)要用
SQL> delete from hr.xifenfei where id=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
二、bbed恢復(fù)刪除數(shù)據(jù)
復(fù)制代碼 代碼如下:
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 – Limited Production on Mon Aug 22 01:52:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
FILE# 2
BLOCK# 1
OFFSET 0
DBA 0×00800001 (8388609 2,1)
FILENAME /opt/oracle/oradata/xifenfei/xff01.dbf
BIFILE bifile.bbd
LISTFILE /tmp/list
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> set dba 2,530
DBA 0×00800212 (8389138 2,530)
BBED> find /c xff
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
————————————————————————
7866662c 000202c1 02087869 66656e66 65690106 80e2
<32 bytes per line>
BBED> dump /v
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8170 to 8191 Dba:0×00800212
——————————————————-
7866662c 000202c1 02087869 66656e66 l xff,……xifenf
65690106 80e2 l ei….
<16 bytes per line>
BBED> dump /v offset 8160
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8160 to 8191 Dba:0×00800212
——————————————————-
0000003c 020202c1 03037866 662c0002 l …<......xff,..
02c10208 78696665 6e666569 010680e2 l ....xifenfei....
<16 bytes per line>
BBED> dump /v offset 8164
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8164 to 8191 Dba:0×00800212
——————————————————-
020202c1 03037866 662c0002 02c10208 l ……xff,……
78696665 6e666569 010680e2 l xifenfei….
<16 bytes per line>
BBED> dump /v offset 8162
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8162 to 8191 Dba:0×00800212
——————————————————-
003c0202 02c10303 7866662c 000202c1 l .<......xff,....
02087869 66656e66 65690106 80e2 l ..xifenfei....
<16 bytes per line>
BBED> dump /v offset 8163
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
——————————————————-
3c020202 c1030378 66662c00 0202c102 l <......xff,.....
08786966 656e6665 69010680 e2 l .xifenfei....
<16 bytes per line>
通過嘗試，推斷出來3c的offset
BBED> modify /x 2c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/oracle/oradata/xifenfei/xff01.dbf (2)
Block: 530 Offsets: 8163 to 8191 Dba:0×00800212
————————————————————————
2c020202 c1030378 66662c00 0202c102 08786966 656e6665 69010680 e2
<32 bytes per line>
修改3c為2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
三、核對(duì)結(jié)果
復(fù)制代碼 代碼如下:
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from hr.xifenfei;
ID NAME
———- ——————–
1 xifenfei
2 xff
說明：
1）如果數(shù)據(jù)未刪除：row flag的值為 32+8+4=44或者0x2c
2）如果數(shù)據(jù)被刪除：row flag的值為 32+16+8+4=60或者0x3c
找回被刪除數(shù)據(jù)
創(chuàng)建模擬表數(shù)據(jù)
復(fù)制代碼 代碼如下:
SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into t_xifenfei values(2,'XIFENFEI');
1 row created.
SQL> commit;
Commit complete.
dump數(shù)據(jù)塊
SQL> alter system flush BUFFER_CACHE;
System altered.
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 from chf.t_xifenfei;
ROWID ID NAME REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ---------- ---------- ----------
AAASdmAAEAAAACvAAA 1 xifenfei 4 175 0
AAASdmAAEAAAACvAAB 2 XIFENFEI 4 175 1
SQL> alter system dump datafile 4 block 175;
System altered.
dump文件內(nèi)容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 8] 78 69 66 65 6e 66 65 69
tab 0, row 1, @0x1f7a
tl: 15 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 8] 58 49 46 45 4e 46 45 49
end_of_block_dump
2012-05-01 05:09:29.287714 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
刪除表數(shù)據(jù)
復(fù)制代碼 代碼如下:
SQL> delete from t_xifenfei;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
dump文件內(nèi)容
block_row_dump:
tab 0, row 0, @0x1f89
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f7a
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
2012-05-01 05:13:35.214357 : kjbmbassert [0xaf.4]
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175
通過對(duì)比這兩次的dump文件發(fā)現(xiàn)
1.數(shù)據(jù)內(nèi)容被刪除,并不是真正刪除,而是給其增加了一個(gè)標(biāo)識(shí)位(fd:---D----)
2.fb:--H-FL--(head of row piece+first data piece+last data piece )
其有8個(gè)選項(xiàng)每個(gè)選項(xiàng)的值分別對(duì)應(yīng)bitmask即32+8+4=44 or 0x2c
3.如果一個(gè)row被delete了,那么row flag就會(huì)更新,bitmask里的deleted被設(shè)置為16.
此時(shí)row flag為：32+16+8+4 = 60 or 0x3c.
4.如果我們要找回來被刪除的數(shù)據(jù),只需要把3c改為2c即可
關(guān)閉數(shù)據(jù)庫
復(fù)制代碼 代碼如下:
SQL> select * from chf.t_xifenfei;
no rows selected
SQL> select name from v$datafile where file#=4;
NAME
------------------------------------------------
/tmp/user01.dbf
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
bbed修改數(shù)據(jù)
BBED> set filename '/tmp/user01.dbf'
FILENAME /tmp/user01.dbf
BBED> set block 175
BLOCK# 175
BBED> set blocksize 8192
BLOCKSIZE 8192
BBED> set mode edit
MODE Edit
BBED> map
File: /tmp/user01.dbf (0)
Block: 175 Dba:0x00000000
------------------------------------------------------------
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[8036] @122
ub1 rowdata[30] @8158
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[15]
-----------
ub1 rowdata[15] @8173 0x3c
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0] @8158 0x3c
BBED> m /x 2c offset 8158
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8158 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1030858 4946454e 46454932 630202c1 02087869 66656e66 65690106
b47e
<32 bytes per line>
BBED> m /x 2c offset 8173
File: /tmp/user01.dbf (0)
Block: 175 Offsets: 8173 to 8191 Dba:0x00000000
------------------------------------------------------------------------
2c630202 c1020878 6966656e 66656901 06b47e
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 175:
current = 0x4d13, required = 0x4d13
啟動(dòng)數(shù)據(jù)庫驗(yàn)證
復(fù)制代碼 代碼如下:
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1346140 bytes
Variable Size 411043236 bytes
Database Buffers 117440512 bytes
Redo Buffers 5832704 bytes
Database mounted.
Database opened.
SQL> select * from chf.t_xifenfei;
ID NAME
---------- ----------
1 xifenfei
2 XIFENFEI
您可能感興趣的文章:
相關(guān)文章
oracle 優(yōu)化的一點(diǎn)體會(huì)
oracle 優(yōu)化的一點(diǎn)體會(huì)大家可以參考下，提升運(yùn)行效率。2009-10-10
instr函數(shù)為字符查找函數(shù)，其功能是查找一個(gè)字符串在另一個(gè)字符串中首次出現(xiàn)的位置，instr函數(shù)在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置，這篇文章主要介紹了Oracle Instr函數(shù)實(shí)例講解,需要的朋友可以參考下2022-11-11
在數(shù)據(jù)庫管理中,觸發(fā)器是重要的工具,用于實(shí)現(xiàn)自動(dòng)化操作和數(shù)據(jù)完整性維護(hù),本文以一個(gè)實(shí)際案例詳解觸發(fā)器的基本語句和應(yīng)用,在導(dǎo)入個(gè)人信息時(shí),如果已存在該信息且需更新非導(dǎo)入項(xiàng)（如疫情信息）,觸發(fā)器通過比較:new與:old屬性值實(shí)現(xiàn)自動(dòng)同步2024-10-10
web前端從Oracle數(shù)據(jù)庫加載動(dòng)態(tài)菜單所用到的數(shù)據(jù)表
這篇文章主要介紹了web前端從Oracle數(shù)據(jù)庫加載動(dòng)態(tài)菜單所用到的數(shù)據(jù)表,本文通過實(shí)例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2018-04-04![[Oracle] CPU/PSU補(bǔ)丁安裝詳細(xì)教程](http://img.jbzj.com/images/xgimg/bcimg4.png)
[Oracle] CPU/PSU補(bǔ)丁安裝詳細(xì)教程
Oracle CPU的全稱是Critical Patch Update, Oracle對(duì)于其產(chǎn)品每個(gè)季度發(fā)行一次安全補(bǔ)丁包，通常是為了修復(fù)產(chǎn)品中的安全隱患,以下是對(duì)CPU/PSU補(bǔ)丁安裝的具體操作步驟進(jìn)行了詳細(xì)的分析介紹，需要的朋友可以參考2013-07-07
linux自動(dòng)運(yùn)行rman增量備份腳本
這篇文章主要介紹了linux自動(dòng)運(yùn)行rman增量備份腳本，實(shí)現(xiàn)周日和周三凌晨1:00執(zhí)行0級(jí)全庫備份，周一、二、四、五、六凌晨1:30執(zhí)行增量備份,需要的朋友可以參考下2014-03-03
關(guān)于SQL執(zhí)行計(jì)劃錯(cuò)誤導(dǎo)致臨時(shí)表空間不足的問題
故障現(xiàn)象：臨時(shí)表空間不足的問題已經(jīng)報(bào)錯(cuò)過3次，客戶也煩了，前兩次都是同事添加5G的數(shù)據(jù)文件，目前已經(jīng)達(dá)到40G，占用臨時(shí)表空間主要是distinct 和group by 以及Union all 表數(shù)據(jù)量在200W左右，也不至于把40G的臨時(shí)表空間撐爆。2013-04-04
Oracle創(chuàng)建新undo表空間最佳實(shí)踐(包含段檢查)
這篇文章主要介紹了Oracle創(chuàng)建新undo表空間最佳實(shí)踐(包含段檢查),非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2019-04-04
Oracle查詢今天、昨天、本周、上周、本月、上月數(shù)據(jù)方式
這篇文章主要介紹了Oracle查詢今天、昨天、本周、上周、本月、上月數(shù)據(jù)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-07-07
oracle查看會(huì)話鎖定的所有對(duì)象代碼分享
oracle查看會(huì)話鎖定的所有對(duì)象2014-01-01