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
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
************* !!! 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
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脚本恢复删除的表数据)
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
扫一扫
390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
