oracle bbed Restore Delete Data Instance
- 2021-09-12 02:34:43
- OfStack
Recover deleted data
1. Create a simulation environment
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
Query file# , block , to be used for later recovery
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.
2. bbed Recovery Deleted Data
[oracle@localhost ~]$ bbed parfile=/tmp/parfile.cnf
Password:
BBED: Release 2.0.0.0.0 In fact, in fact, the 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>
By trying, infer that 3c Adj. 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>
Modify 3c For 2c
BBED> sum apply
Check value for File 2, Block 530:
current = 0xb1b9, required = 0xb1b9
3. Check the results
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
Description:
1) If data is not deleted: row flag has a value of 32+8+4=44 or 0x2c
2) If data is deleted: row flag has a value of 32+16+8+4=60 or 0x3c
Retrieve deleted data
Create mock table data
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 Data block
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 File content
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
Delete table data
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 File content
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
By comparing these two times, dump File discovery
1. Data content is deleted , Is not really deleted , But added to it 1 Identification bits (fd:---D----)
2.fb:--H-FL--(head of row piece+first data piece+last data piece )
It has 8 The value of each option corresponds to the value of each option bitmask I.e. 32+8+4=44 or 0x2c
3. If 1 A row Be delete It's over , Then row flag It will be updated ,bitmask In deleted Is set to 16.
At this time row flag Is: 32+16+8+4 = 60 or 0x3c.
4. If we want to retrieve the deleted data, , Just put 3c Replace with 2c You can
Close the database
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 Modify data
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
Start database validation
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