Oracle delete誤運算元據恢復(BBED)

在Oracle中，表資料被錯誤執行了delete，並 已 提交，如何找回資料呢？

常規的修復方法可以想到閃回、Rman、impdp 、 DG ......

如果這些方法均失效了，也可以考慮使用 BBED 工具找回資料。

Oracle在執行delete操作時，並沒有把資料塊內的資料馬上清除掉，而是將塊標記為已刪除，表示可以重用。

那麼在資料塊內容沒有被覆蓋之前，理論上資料時可以找回的。

理論上，在資料塊沒有被覆蓋之前，將資料塊上的標誌位的已刪除標記去掉，就可以找回資料了。

修復過程如下：

修復資料需要知道如下資訊：

執行誤操作表對應的：

1 資料檔案號( dba_objects )

select object_id, object_name, owner from dba_objects where object_name = 'T1' and owner = 'CJC';

2 塊號(bbed)

假設塊號範圍1到200，查詢sid=object_id的資料塊號

BBED> p /d dba 13, 1..200 ktbbh.ktbbhsid.ktbbhod1

3 offset(bbed)

BBED> set dba 13,131

BBED> x /rnc *kdbr[1]

4 每行識別符號(dump)

alter system dump datafile 13 block 131;

透過對比某一資料塊刪除資料前、後兩次轉儲資訊，可以知道 塊中行 存在資料時標誌位是fb: --H-FL--， 行資料 被刪除後標誌位變成fb: --HDFL-- ，換行成16進位制，標誌位改成0x2c表示存在資料。

5 修復資料

下面是修復131號塊中第二行資料，將標誌位改成0x2c ；

BBED> assign /x dba 13, 131 offset 8083 = 0x2c

BBED> sum apply dba 13,131

SQL> alter system flush buffer_cache;

實驗如下：

一：資料庫版本

---資料庫19C，相當於12.2.0.3版本

SQL> select banner_full from v$version;

BANNER_FULL

---------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 CJCPDB READ WRITE NO

二：建立測試資料

SQL> conn cjc/cjc@cjcpdb

SQL> create table t1 as select * from dba_objects where object_id <=1000;

Table created.

三：檢視測試資料基本資訊

---1 測試表t1的OBJECT_ID = 73216

SQL> set line 150

SQL> col object_name for a20

SQL> select object_id,object_name from user_objects where object_name='T1';

OBJECT_ID OBJECT_NAME

---------- --------------------

73216 T1

---2 測試表t1所在資料檔案號=13

SQL> col file_name for a65

SQL> select file_name,file_id from dba_data_files where tablespace_name = 'CJCTBS';

FILE_NAME FILE_ID

----------------------------------------------------------------- ----------

/u01/app/oracle/oradata/CJCDB/cjcpdb/cjctbs01.dbf 13

---3 測試表t1資料儲存在13號資料檔案，131-148號資料塊裡(資料分佈在17個資料塊裡)

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t1 order by 2;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

------------------------------------ ------------------------------------

13 131

13 132

13 133

13 134

13 135

13 136

13 137

13 138

13 139

13 140

13 141

13 142

13 143

13 145

13 146

13 147

13 148

17 rows selected.

四：檢視誤刪除前資料塊資訊

--- 轉儲 T1表的第一個塊（131塊），檢視刪除之前的內容

SQL> alter system dump datafile 13 block 131;

System altered.

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

VALUE

-------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_18805.trc

[oracle@cjcos ~]$ vim /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_18805.trc

Trace file /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_18805.trc

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417

ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1

System name: Linux

Node name: cjcos

Release: 4.1.12-112.16.4.el7uek.x86_64

Version: #2 SMP Mon Mar 12 23:57:12 PDT 2018

Machine: x86_64

Instance name: cjcdb

Redo thread mounted by this instance: 1

Oracle process number: 51

Unix process pid: 18805, image: oracle@cjcos

......

Block header dump: 0x03400083

Object id on Block? Y

seg/obj: 0x11e00 csc: 0x0000000000276a70 itc: 3 flg: E typ: 1 - DATA

brn: 0 bdba: 0x3400080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000000000276a70

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x03400083

---nrow=68 表示datafile 13 block 131塊中包含68行資料

data_block_dump,data header at 0x8bcca07c

===============

tsiz: 0x1f80

hsiz: 0x9a

pbl: 0x8bcca07c

76543210

flag=--------

ntab=1

nrow=68

frre=-1

fsbo=0x9a

fseo=0x3ca

avsp=0x330

tosp=0x330

0xe:pti[0] nrow=68 offs=0

0x12:pri[0] offs=0x1f17

......

---fb: --H-FL-- 表示資料刪除前的標誌位，32+8+4 = 44 轉換成16進製為2c

block_row_dump:

tab 0, row 0, @0x1f17

tl: 105 fb: --H-FL-- lb: 0x0 cc: 22

col 0: [ 3] 53 59 53

col 1: [ 6] 43 5f 4f 42 4a 23

col 2: *NULL*

col 3: [ 2] c1 03

col 4: [ 2] c1 03

col 5: [ 7] 43 4c 55 53 54 45 52

col 6: [ 7] 78 77 04 11 01 39 0f

col 7: [ 7] 78 77 04 11 01 39 0f

col 8: [19] 32 30 31 39 2d 30 34 2d 31 37 3a 30 30 3a 35 36 3a 31 34

col 9: [ 5] 56 41 4c 49 44

col 10: [ 1] 4e

col 11: [ 1] 4e

col 12: [ 1] 4e

col 13: [ 2] c1 06

col 14: *NULL*

col 15: [13] 4d 45 54 41 44 41 54 41 20 4c 49 4e 4b

col 16: *NULL*

col 17: [ 1] 59

col 18: [ 1] 4e

col 19: *NULL*

col 20: [ 1] 4e

col 21: [ 1] 4e

tab 0, row 1, @0x1eb9

tl: 94 fb: --H-FL-- lb: 0x0 cc: 22

透過bbed檢視刪除之前 131 塊 的資訊

---b bed ( Oracle Block Browerand EDitor Tool )

---BBED工具的安裝方法見我的另一篇部落格結尾部分

---http://blog.itpub.net/29785807/viewspace-2128326/

[oracle@cjcos ~]$ cat bbed.par

blocksize=8192

listfile=/home/oracle/filelist.txt

mode=edit

[oracle@cjcos ~]$ bbed parfile=bbed.par

Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sun Feb 9 15:44:21 2020

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

BBED> set dba 13,131

DBA 0x03400083 (54526083 13,131)

BBED> p

kcbh.type_kcbh

--------------

ub1 type_kcbh @0 0x06

---kdbr[68] 表示 131塊包含 68行資料

BBED> map

File: /u01/app/oracle/oradata/CJCDB/cjcpdb/cjctbs01.dbf (13)

Block: 131 Dba:0x03400083

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 96 bytes @20

struct kdbh, 14 bytes @124

struct kdbt[1], 4 bytes @138

sb2 kdbr[68] @142

ub1 freespace[816] @278

ub1 rowdata[7094] @1094

ub4 tailchk @8188

BBED> p kdbt

struct kdbt[0], 4 bytes @138

sb2 kdbtoffs @138 0

sb2 kdbtnrow @140 68

---0x2c 表示標誌位是--H-FL--(32+8+4 = 44 轉換成16進製為2c)

BBED> p *kdbr[1]

rowdata[6895]

-------------

ub1 rowdata[6895] @7989 0x2c

檢視第二行資料

BBED> x /rnc *kdbr[1]

rowdata[6895] @7989

-------------

flag@7989: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@7990: 0x00

cols@7991: 22

col 0[3] @7992: #########################################

col 1[6] @7996: I_OBJ#

col 2[0] @8003: *NULL*

col 3[2] @8004: ￮

col 4[2] @8007: ￮

col 5[5] @8010: INDEX

col 6[7] @8016: xw...9.

col 7[7] @8024: xw...9.

col 8[19] @8032: 2019-04-17:00:56:14

col 9[5] @8052: VALID

col 10[1] @8058: N

col 11[1] @8060: N

col 12[1] @8062: N

col 13[2] @8064: ￮

col 14[0] @8067: *NULL*

col 15[4] @8068: NONE

col 16[0] @8073: *NULL*

col 17[1] @8074: Y

col 18[1] @8076: N

col 19[0] @8078: *NULL*

col 20[1] @8079: N

col 21[1] @8081: N

透過SQL檢視第二行資料：

SELECT * FROM (select rownum no, t1.* from t1) A WHERE NO = 2;

五：刪除T1表資料，模擬誤刪除操作

SQL> show user

USER is "CJC"

SQL> show user con_name

USER is "CJC"

CON_NAME

------------------------------

CJCPDB

SQL> delete t1;

998 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

六轉儲 T1表的第一個塊（131塊），檢視刪除後的內容

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 13 block 131;

System altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_21476.trc

[oracle@cjcos ~]$ vim /u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace/cjcdb_ora_21476.trc

---fb: --HDFL-- 該標誌位從原來的--H-FL--變成了--HDFL--，其中D就代表刪除的意思

......

Block header dump: 0x03400083

Object id on Block? Y

seg/obj: 0x11e00 csc: 0x0000000000276a70 itc: 3 flg: E typ: 1 - DATA

brn: 0 bdba: 0x3400080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000000000276a70

0x02 0x0001.01d.000002e6 0x02400631.011c.01 --U- 68 fsc 0x1b2e.002773fc

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x03400083

---nrow=68 表示有68行資料

data_block_dump,data header at 0x7f491a40a07c

===============

tsiz: 0x1f80

hsiz: 0x9a

pbl: 0x7f491a40a07c

76543210

flag=--------

ntab=1

nrow=68

frre=-1

fsbo=0x9a

fseo=0x3ca

avsp=0x330

tosp=0x1ee6

......

---fb: --HDFL-- 表示每行資料標誌位已經改成已刪除標誌

block_row_dump:

tab 0, row 0, @0x1f17

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 1, @0x1eb9

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 2, @0x1e46

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 3, @0x1dd3

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 4, @0x1d6b

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 5, @0x1d0e

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 6, @0x1c9d

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 7, @0x1c37

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 8, @0x1bcd

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 9, @0x1b6e

tl: 2 fb: --HDFL-- lb: 0x2

透過bbed檢視刪除之後的 資訊

BBED> set dba 13,131

DBA 0x03400083 (54526083 13,131)

BBED> map

File: /u01/app/oracle/oradata/CJCDB/cjcpdb/cjctbs01.dbf (13)

Block: 131 Dba:0x03400083

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 96 bytes @20

struct kdbh, 14 bytes @124

struct kdbt[1], 4 bytes @138

sb2 kdbr[ 68 ] @142

ub1 freespace[816] @278

ub1 rowdata[7094] @1094

ub4 tailchk @8188

--- 檢視 第二行沒有資料

BBED> x /rnc *kdbr[1]

rowdata[6895] @7989

-------------

flag@7989: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)

lock@7990: 0x02

cols@7991: 0

七表資料 已經被刪除，需要找到表T1對應的資料塊有哪些

SQL> conn cjc/cjc@cjcpdb

SQL> col object_name for a15

SQL> col owner for a10

SQL> set line 100

SQL> select object_id, object_name, owner from dba_objects where object_name = 'T1' and owner = 'CJC';

OBJECT_ID OBJECT_NAME OWNER

---------- --------------- ----------

73216 T1 CJC

---找表T1所在的塊資訊

---由於資料已經被刪除，這種方式已經用不了了。

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t1 order by 2;

no rows selected

已知表T1的object_id是73216，資料檔案號是13，求T1表對應的塊號?

預估塊號範圍在1到200(主要是為了找到object_id=73216的塊號)；

BBED> p ktbbh

struct ktbbh, 96 bytes @20

ub1 ktbbhtyp @20 0x01 (KDDBTDATA)

union ktbbhsid , 4 bytes @24

ub4 ktbbhsg1 @24 0x00011e00

ub4 ktbbhod1 @24 0x00011e00

struct ktbbhcsc, 8 bytes @28

ub4 kscnbas @28 0x00276a70

ub2 kscnwrp @32 0x8000

ub2 kscnwrp2 @34 0x0000

......

--- - 表T1的sid =73216,查詢資料檔案號13，假設塊號範圍1到200，檢視塊號1到200哪些sid=73216，即是T1對應的資料塊。

BBED> p /d dba 13,1 ktbbh.ktbbhsid.ktbbhod1

BBED-00207: invalid offset specifier (ktbbh.ktbbhsid.ktbbhod1)

......

BBED> p /d dba 13,131 ktbbh.ktbbhsid.ktbbhod1

ub4 ktbbhod1 @24 73216

BBED> p /d dba 13,132 ktbbh.ktbbhsid.ktbbhod1

ub4 ktbbhod1 @24 73216

......

手動執行速度較慢，可以透過如下指令碼批次生成T1表對應的資料塊

---指令碼如下 ：

[oracle@cjcos ~]$ cat sandata.sh

#!/bin/sh

. ~/.bash_profile

for ((i=1; i<=200; i ++))

do

objectid=`echo p /d dba 13,$i ktbbh.ktbbhsid.ktbbhod1 | bbed parfile=bbed.par password=blockedit | grep 'ktbbhod1' | awk '{print $5}'`

if [ "$objectid" = "73216" ];then

echo $i;

fi;

done

---執行指令碼，獲取 T1表對應的 資料塊號

[oracle@cjcos ~]$ chmod a+x sandata.sh

[oracle@cjcos ~]$ ./sandata.sh > sh.out

[oracle@cjcos ~]$ cat sh.out

131

132

133

134

135

136

137

138

139

140

141

142

143

145

146

147

148

八 ：修復資料

修復資料需要知道幾個資訊：

1 資料檔案號

2 塊號

3 offset

前面已經知道這三個資訊，檔案號3，塊號131到148，檢視單個資料塊的offset，例如檢視第二行的offset:

BBED> set dba 13,131

BBED> x /rnc *kdbr[1]

rowdata[6895] @7989

即第二行資料offset是7989

知道這些資訊後就可以修復第二行資料了，修復方法如下：

BBED> assign /x dba 13, 131 offset 7989 = 0x2c

BBED> sum apply dba 13,131

這樣已修復了第一個資料塊的第二行資料，t1表一共有998條資料，一條一條執行速度較慢，可以透過下面指令碼批次生成修復指令碼：

--- 指令碼如下：

---將上一步查出的17個塊的 行資料0x3c修改成0x2c（--HDFL--修改成--H-FL--）

[oracle@cjcos ~]$ vim repir.sh

#!/bin/sh

. ~/.bash_profile

cat sh.out | while read i

do

len=`echo p /d dba 13,$i offset 0 kdbt[0] | bbed parfile=bbed.par password=blockedit | grep 'kdbtnrow' | awk '{print $4}'`

if [ -n "$len" ];then

for ((j=0; j<$len; j++))

do

offset=`echo p dba 13, $i *kdbr[$j]| bbed parfile=bbed.par password=blockedit | grep '^ub1*.*rowdata' | awk '{print $3}' |sed 's/@//g'`

echo "assign /x dba 13, $i offset $offset = 0x2c"

done

fi

echo "sum apply dba 13,$i"

done

[oracle@cjcos ~]$ chmod a+x repir.sh

[oracle@cjcos ~]$ ./repir.sh > repir.txt

[oracle@cjcos ~]$ cat repir.txt

assign /x dba 13, 131 offset 8083 = 0x2c

assign /x dba 13, 131 offset 7989 = 0x2c

assign /x dba 13, 131 offset 7874 = 0x2c

assign /x dba 13, 131 offset 7759 = 0x2c

assign /x dba 13, 131 offset 7655 = 0x2c

assign /x dba 13, 131 offset 7562 = 0x2c

assign /x dba 13, 131 offset 7449 = 0x2c

assign /x dba 13, 131 offset 7347 = 0x2c

assign /x dba 13, 131 offset 7241 = 0x2c

assign /x dba 13, 131 offset 7146 = 0x2c

assign /x dba 13, 131 offset 7031 = 0x2c

assign /x dba 13, 131 offset 6916 = 0x2c

assign /x dba 13, 131 offset 6801 = 0x2c

assign /x dba 13, 131 offset 6685 = 0x2c

assign /x dba 13, 131 offset 6571 = 0x2c

assign /x dba 13, 131 offset 6455 = 0x2c

assign /x dba 13, 131 offset 6340 = 0x2c

assign /x dba 13, 131 offset 6225 = 0x2c

assign /x dba 13, 131 offset 6109 = 0x2c

assign /x dba 13, 131 offset 5994 = 0x2c

assign /x dba 13, 131 offset 5878 = 0x2c

assign /x dba 13, 131 offset 5756 = 0x2c

assign /x dba 13, 131 offset 5655 = 0x2c

assign /x dba 13, 131 offset 5528 = 0x2c

assign /x dba 13, 131 offset 5420 = 0x2c

assign /x dba 13, 131 offset 5312 = 0x2c

assign /x dba 13, 131 offset 5197 = 0x2c

assign /x dba 13, 131 offset 5091 = 0x2c

assign /x dba 13, 131 offset 4996 = 0x2c

assign /x dba 13, 131 offset 4880 = 0x2c

assign /x dba 13, 131 offset 4764 = 0x2c

assign /x dba 13, 131 offset 4670 = 0x2c

assign /x dba 13, 131 offset 4575 = 0x2c

assign /x dba 13, 131 offset 4480 = 0x2c

assign /x dba 13, 131 offset 4386 = 0x2c

assign /x dba 13, 131 offset 4292 = 0x2c

assign /x dba 13, 131 offset 4198 = 0x2c

assign /x dba 13, 131 offset 4104 = 0x2c

assign /x dba 13, 131 offset 4010 = 0x2c

assign /x dba 13, 131 offset 3916 = 0x2c

assign /x dba 13, 131 offset 3821 = 0x2c

assign /x dba 13, 131 offset 3726 = 0x2c

assign /x dba 13, 131 offset 3631 = 0x2c

assign /x dba 13, 131 offset 3538 = 0x2c

assign /x dba 13, 131 offset 3443 = 0x2c

assign /x dba 13, 131 offset 3348 = 0x2c

assign /x dba 13, 131 offset 3254 = 0x2c

assign /x dba 13, 131 offset 3160 = 0x2c

assign /x dba 13, 131 offset 3066 = 0x2c

assign /x dba 13, 131 offset 2972 = 0x2c

assign /x dba 13, 131 offset 2878 = 0x2c

assign /x dba 13, 131 offset 2783 = 0x2c

assign /x dba 13, 131 offset 2688 = 0x2c

assign /x dba 13, 131 offset 2593 = 0x2c

assign /x dba 13, 131 offset 2498 = 0x2c

assign /x dba 13, 131 offset 2403 = 0x2c

assign /x dba 13, 131 offset 2308 = 0x2c

assign /x dba 13, 131 offset 2187 = 0x2c

assign /x dba 13, 131 offset 2067 = 0x2c

assign /x dba 13, 131 offset 1948 = 0x2c

assign /x dba 13, 131 offset 1850 = 0x2c

assign /x dba 13, 131 offset 1752 = 0x2c

assign /x dba 13, 131 offset 1639 = 0x2c

assign /x dba 13, 131 offset 1537 = 0x2c

assign /x dba 13, 131 offset 1418 = 0x2c

assign /x dba 13, 131 offset 1317 = 0x2c

assign /x dba 13, 131 offset 1196 = 0x2c

assign /x dba 13, 131 offset 1094 = 0x2c

sum apply dba 13,131

...... 省略其他15個資料塊內容......

assign /x dba 13, 148 offset 8056 = 0x2c

assign /x dba 13, 148 offset 7945 = 0x2c

assign /x dba 13, 148 offset 7813 = 0x2c

assign /x dba 13, 148 offset 7702 = 0x2c

assign /x dba 13, 148 offset 7567 = 0x2c

assign /x dba 13, 148 offset 7455 = 0x2c

sum apply dba 13,148

---把執行的結果在bbed中執行

先修復第一個資料塊dba 13, 131的資料 (68行資料) ，看下效果 ：

BBED>

assign /x dba 13, 131 offset 8083 = 0x2c

assign /x dba 13, 131 offset 7989 = 0x2c

assign /x dba 13, 131 offset 7874 = 0x2c

assign /x dba 13, 131 offset 7759 = 0x2c

assign /x dba 13, 131 offset 7655 = 0x2c

assign /x dba 13, 131 offset 7562 = 0x2c

assign /x dba 13, 131 offset 7449 = 0x2c

assign /x dba 13, 131 offset 7347 = 0x2c

assign /x dba 13, 131 offset 7241 = 0x2c

assign /x dba 13, 131 offset 7146 = 0x2c

assign /x dba 13, 131 offset 7031 = 0x2c

assign /x dba 13, 131 offset 6916 = 0x2c

assign /x dba 13, 131 offset 6801 = 0x2c

assign /x dba 13, 131 offset 6685 = 0x2c

assign /x dba 13, 131 offset 6571 = 0x2c

assign /x dba 13, 131 offset 6455 = 0x2c

assign /x dba 13, 131 offset 6340 = 0x2c

assign /x dba 13, 131 offset 6225 = 0x2c

assign /x dba 13, 131 offset 6109 = 0x2c

assign /x dba 13, 131 offset 5994 = 0x2c

assign /x dba 13, 131 offset 5878 = 0x2c

assign /x dba 13, 131 offset 5756 = 0x2c

assign /x dba 13, 131 offset 5655 = 0x2c

assign /x dba 13, 131 offset 5528 = 0x2c

assign /x dba 13, 131 offset 5420 = 0x2c

assign /x dba 13, 131 offset 5312 = 0x2c

assign /x dba 13, 131 offset 5197 = 0x2c

assign /x dba 13, 131 offset 5091 = 0x2c

assign /x dba 13, 131 offset 4996 = 0x2c

assign /x dba 13, 131 offset 4880 = 0x2c

assign /x dba 13, 131 offset 4764 = 0x2c

assign /x dba 13, 131 offset 4670 = 0x2c

assign /x dba 13, 131 offset 4575 = 0x2c

assign /x dba 13, 131 offset 4480 = 0x2c

assign /x dba 13, 131 offset 4386 = 0x2c

assign /x dba 13, 131 offset 4292 = 0x2c

assign /x dba 13, 131 offset 4198 = 0x2c

assign /x dba 13, 131 offset 4104 = 0x2c

assign /x dba 13, 131 offset 4010 = 0x2c

assign /x dba 13, 131 offset 3916 = 0x2c

assign /x dba 13, 131 offset 3821 = 0x2c

assign /x dba 13, 131 offset 3726 = 0x2c

assign /x dba 13, 131 offset 3631 = 0x2c

assign /x dba 13, 131 offset 3538 = 0x2c

assign /x dba 13, 131 offset 3443 = 0x2c

assign /x dba 13, 131 offset 3348 = 0x2c

assign /x dba 13, 131 offset 3254 = 0x2c

assign /x dba 13, 131 offset 3160 = 0x2c

assign /x dba 13, 131 offset 3066 = 0x2c

assign /x dba 13, 131 offset 2972 = 0x2c

assign /x dba 13, 131 offset 2878 = 0x2c

assign /x dba 13, 131 offset 2783 = 0x2c

assign /x dba 13, 131 offset 2688 = 0x2c

assign /x dba 13, 131 offset 2593 = 0x2c

assign /x dba 13, 131 offset 2498 = 0x2c

assign /x dba 13, 131 offset 2403 = 0x2c

assign /x dba 13, 131 offset 2308 = 0x2c

assign /x dba 13, 131 offset 2187 = 0x2c

assign /x dba 13, 131 offset 2067 = 0x2c

assign /x dba 13, 131 offset 1948 = 0x2c

assign /x dba 13, 131 offset 1850 = 0x2c

assign /x dba 13, 131 offset 1752 = 0x2c

assign /x dba 13, 131 offset 1639 = 0x2c

assign /x dba 13, 131 offset 1537 = 0x2c

assign /x dba 13, 131 offset 1418 = 0x2c

assign /x dba 13, 131 offset 1317 = 0x2c

assign /x dba 13, 131 offset 1196 = 0x2c

assign /x dba 13, 131 offset 1094 = 0x2c

BBED> sum apply dba 13,131

Check value for File 13, Block 131:

current = 0x4aca, required = 0x4aca

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from t1;

COUNT(*)

----------

68

可以看到第一個資料塊裡的68條資料已經恢復成功。

同理，恢復剩下16個資料塊的資料 ：

......

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from t1;

COUNT(*)

----------

998

注意：

嘗試向恢復的表裡插入資料：

報如下錯誤， 並且自動中斷了例項 ，看來使用bbed直接修改資料塊是有一定風險的，生 產環境需謹慎使用 。

SQL> insert into t1 select *from t1;

insert into t1 select *from t1

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2032], [54526098], [1225216857],

[8192], [3], [255], [1162101599], [4134602241], [], [], [], []

ORA-00600: internal error code, arguments: [2032], [54526098], [1225216857],

[8192], [3], [255], [1162101599], [4134602241], [], [], [], []

ORA-00600: internal error code, arguments: [2032], [54526098], [1225216857],

[8192], [3], [255], [1162101599], [4134602241], [], [], [], []

Process ID: 3081

Session ID: 390 Serial number: 12672

[oracle@cjcos trace]$ pwd

/u01/app/oracle/diag/rdbms/cjcdb/cjcdb/trace

[oracle@cjcos trace]$ vim alert_cjcdb.log

......

透過BBED 恢復完 T1 表後，最好馬上執行 CTAS 將 T1 資料備份到 T2 表中：

SQL> conn / as sysdba

SQL> startup

SQL> alter session set container=cjcpdb;

SQL> startup

SQL> conn cjc/cjc@cjcpdb

SQL> create table t2 as select *from t1;

備份完成後刪除T1 表，並將 T2 表名改成 T1 ；

SQL> drop table t1 purge;

SQL> rename t2 to t1;

Table renamed.

再次插入資料就正常了

SQL> insert into t1 select * from t1;

998 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)

----------

1996

--- 本文參考 qxy0503---表刪除恢復指令碼(bbed指令碼恢復刪除的表資料)

https://blog.csdn.net/m15217321304/article/details/101152226

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle delete誤運算元據恢復(BBED)

Oracle delete誤運算元據恢復(BBED)

相關文章