Data Recovery after truncate table in oracle

Address: http://steve-111.iteye.com/blog/750326

A few days ago, I accidentally truncate a table, and the data in the table is very important. In addition, the table data is not backed up, and the data backed up is not the latest. For a moment, I do not know what to do. I have found a lot of information on the Internet, but none of them are very suitable. I am helpless because I have the appropriate information but I have not explained it in detail. After multiple searches, the following are detailed steps for my comprehensive online data to successfully restore table data for your reference. In order to encounter the same problem, it can be well restored.

1. Download The odu data recovery tool and decompress it. (For odu tools, see the attachment)
2. query data file path information: select ts #, file #, rfile #, name, BLOCK_SIZE from v $ datafile;
Add its structure to the control.txt file under odu.
The format is as follows:
# Ts # fno # rfno filename block_size
0 1 1/bbdata/hzdb/system01.dbf 8192
1 2 2/bbidx/hzdb/undotbs01.dbf 8192
3 3 3/bbidx/hzdb/indx01.dbf 8192
4 4 4/bbdata/hzdb/tools01.dbf 8192
5 5 5/bbdata/hzdb/users01.dbf 8192
6 6 6/bbdata/hzdb/REPORT. dbf 8192
7 7 7/bbdata/hzdb/RESERVE. dbf 8192
8 8 8/bbdata/hzdb/WZHTBS. dbf 8192
9 9 9/bbdata/hzdb/perfstat01.dbf 8192
3. Open oud
4. Execute the command: unload dict
5. Execute the command: scan extent (wait for a while)
6. Run the command: desc [user name]. [Table Name of the deleted Data]
Object ID: 33547
Storage (Obj # = 33547 DataObj # = 33549 TS # = 11 File # = 10 Block # = 1400 Cluster = 0)
NO. seg int Column Name Null? Type
------------------------------------------------------------------------------
1 1 1 OWNER VARCHAR2 (30)
2 2 2 OBJECT_NAME VARCHAR2 (128)
3 3 3 SUBOBJECT_NAME VARCHAR2 (30)
4 4 4 OBJECT_ID NUMBER
5 5 5 DATA_OBJECT_ID NUMBER
6 6 6 6 OBJECT_TYPE VARCHAR2 (18)
7 7 7 CREATED DATE
8 8 8 LAST_DDL_TIME DATE
9 9 9 TIMESTAMP VARCHAR2 (19)
10 10 10 STATUS VARCHAR2 (7)
11 11 11 TEMPORARY VARCHAR2 (1)
12 12 12 GENERATED VARCHAR2 (1)
13 13 13 SECONDARY VARCHAR2 (1)
From the above output, we can see that table TEST. T1 is in the tablespace of table 11, and the data segment header is in the No. 1400 block of file 10.

We use ODU to determine the original data object id of table T1. Generally, the data block of the data segment is usually in the adjacent block behind the segment header. However, we can confirm from the field header:
ODU> dump datafile 10 block 1400
Block Header:
Block type = 0 × 23 (ASSM segment header block)
Block format = 0 × 02 (oracle 8 or 9)
Block rdba = 0 × 02800578 (file # = 10, block # = 1400)
Scn = 0 × 0000. 00286f2d, seq = 4, tail = 0 × 6f2d2304
Block checksum value = 0x0 = 0, flag = 0
Data Segment Header:
Extent Control Header
-------------------------------------------------------------
Extent Header: extents: 1 blocks: 5
Last map: 0x00000000 # maps: 0 offset: 668
Highwater: 0 × 02800579 (rfile # = 10, block # = 1401)
Ext #: 0 blk #: 3 ext size: 5
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk: 0x00000000 offset: 0
--------------------------------------------------------
Low HighWater Mark:
Highwater: 0x02800579 ext #: 0 blk #: 3 ext size: 5
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0 × 02800576
Level 1 BMB for Low HWM block: 0 × 02800576
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 2048 fbsz: 0
L2 Array start offset: 0 × 00000434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x02800577
Last Level 1 BMB: 0x02800576
Last Level 1I BMB: 0x02800577
Last Level 1II BMB: 0x00000000
Map Header: next 0x00000000 # extents: 1 obj #: 33549 flag: 0x220000000
Extent Map
-------------------------------------------------------------
0 × 02800576 length: 5
Auxillary Map
-------------------------------------------------------------
Extent 0: L1 dba: 0x02800576 Data dba: 0x02800579
-------------------------------------------------------------
Second Level Bitmap block DBAs
-------------------------------------------------------------
DBA 1: 0 × 02800577
From the output "Extent 0: L1 dba: 0 × 02800576 Data dba: 0 × 02800579", we can see that the RDBA of the first Data block in the segment is 0 × 1st, that is, the first block of file 10.
We dumped the first part of file No. 10th to get the original data object id of table T1:
ODU> dump datafile 10 block 1401 header
Block Header:
Block type = 0 × 06 (table/index/cluster segment data block)
Block format = 0 × 02 (oracle 8 or 9)
Block rdba = 0 × 02800579 (file # = 10, block # = 1401)
Scn = 0 × 0000. 00285f2b, seq = 2, tail = 0 × 5f2b0602
Block checksum value = 0x0 = 0, flag = 0
Data Block Header Dump:
Object id on Block? Y
Seg/obj: 0 x 830b = 33547 csc: 0x00. 285f21 itc: 3 flg: E typ: 1 (data)
Brn: 0 bdba: 0x2800576 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0 × 01 0xffff. 000.00000000 0 × 00000000. 0000.00 C --- 0 scn 0 × 0000. 00285f21
0x02 0x0000. 000.00000000 0x00000000. 0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000. 000.00000000 0x00000000. 0000.00 ---- 0 fsc 0x0000.00000000
Data Block Dump:
======================
Flag = 0x0 --------
Ntab = 1
Nrow = 16
Frre =-1
Fsbo = 0x32
Ffeo = 0 ×145
Avsp = 0 ×113
Tosp = 0 ×113
As you can see, the original data object id of table T1 is 33547.
7. Use ODU to unload data:
ODU & gt; unload table test. t1 object 33547
8. Use sqlldr to import the recovered data: Open the cmd command and execute E: \ ODU \ data> sqlldr username/password @ Database id control = TEST_T1.ctl

 

Attachment: odu