[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2012-08-14 01:59:34
Several days ago, I helped a friend recovered a truncated table in QA environment (without backup and archive) using FySafe. And I was wonder if I can find a way to recover such data with a more efficient and simple approach. Finally, I got an idea.
First, Let's analyze the procedure of TRUNCATE. It will NOT erase data in the data blocks, but just reset the meta data in data dictionary and meta data blocks, e.g. segment header, extent map. Almost all of data blocks are re-claimed by system. (Bear in mind, if you want to recover those data, you need backup the data file promptly after truncate.)
Then, Let's analyze the procedure of Table Scan (You can find the detail at here: Secret of oracle logic IO: Full Table Scan: Part 1). During the procedure, Oracle will read the meta data blocks, to get such information as HWM. And then scan blocks under HWM and read formatted blocks. Therefore, theoretically, if we can recover all of meta data, we could recover all truncated data. However, it's a mission impossible.
But we may try to find a solution from another point of view. We may create a set of meta data in a dummy table who has same structure as the truncated table, then replace the data block content with the truncated data block content. Such trick will cheet Oracle to read the truncated data blocks.
Re-call the procedure of Table Scan, I beleive such approach will work. What we need do is to create a dummy table with same structure, and format the data blocks in it. Then find out all data blocks belong to the truncated table, transplant content to the dummy table. Finally, Oracle will help us to scan and read the data. Descript it as below diagram.XML/HTML代码
- +-------------------------+
- | Copy Of Dummy Data File |
- | (With Formmated Blocks)|
- +-------------------------+
- ||
- \/
- (Blcok Header, Block Tail)
- ||
- \/
- +-------------------+ +----------------+ Table Scan +---------------+
- | Source Data File | => (Data Block Content) => | Dummy Table | ============> | Restore Table |
- |(Without Meta Data)| |(With Meta Data)| +---------------+
- +-------------------+ +----------------+
Following this way, I created a PLSQL package, Fy_Recover_Data, and recovered the truncated data utilizing this package.
SQL代码
- HELLODBA.COM>insert into demo.truntab select * from demo.t_objects;
- 47585 rows created.
- HELLODBA.COM>commit;
- Commit complete.
- HELLODBA.COM>select count(*) from demo.truntab;
- COUNT(*)
- ----------
- 47585
- HELLODBA.COM>truncate table demo.truntab;
- Table truncated.
- HELLODBA.COM>declare
- 2 tgtowner varchar2(30);
- 3 tgttable varchar2(30);
- 4 datapath varchar2(4000);
- 5 datadir varchar2(30);
- 6 rects varchar2(30);
- 7 recfile varchar2(30);
- 8 rstts varchar2(30);
- 9 rstfile varchar2(30);
- 10 blksz number;
- 11 rectab varchar2(30);
- 12 rsttab varchar2(30);
- 13 copyfile varchar2(30);
- 14 begin
- 15 tgtowner := 'DEMO';
- 16 tgttable := 'TRUNTAB';
- 17 datapath := 'D:\oracle\product\10.2.0\oradata\EDGAR\DATAFILE\';
- 18 datadir := 'FY_DATA_DIR';
- 19 Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
- 20 Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
- 21 Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
- 22 end;
- 23 /
- Directory Name: FY_DATA_DIR
- Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
- Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
- Recover Table: DEMO.TRUNTAB$
- Restore Table: DEMO.TRUNTAB$$
- Data Blocks formatted.
- Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
- 373 records recovered
- 328 records recovered
- 334 records recovered
- .. ...
- 285 records recovered
- 275 records recovered
- 235 records recovered
- 47585 records recovered in backup table DEMO.TRUNTAB$$
- PL/SQL procedure successfully completed.
- HELLODBA.COM>insert into demo.truntab select * from DEMO.TRUNTAB$$;
- 47585 rows created.
- HELLODBA.COM>commit;
- Commit complete.
All data have been recovered. Let me test a compressed table.
SQL代码
- HELLODBA.COM>set serveroutput on format wrapped
- HELLODBA.COM>insert into demo.truntab select * from demo.t_objects;
- 47585 rows created.
- HELLODBA.COM>commit;
- Commit complete.
- HELLODBA.COM>alter table demo.truntab move compress;
- Table altered.
- HELLODBA.COM>select count(*) from demo.truntab;
- COUNT(*)
- ----------
- 95170
- HELLODBA.COM>truncate table demo.truntab;
- Table truncated.
- HELLODBA.COM>declare
- 2 tgtowner varchar2(30);
- 3 tgttable varchar2(30);
- 4 datapath varchar2(4000);
- 5 datadir varchar2(30);
- 6 rects varchar2(30);
- 7 recfile varchar2(30);
- 8 rstts varchar2(30);
- 9 rstfile varchar2(30);
- 10 blksz number;
- 11 rectab varchar2(30);
- 12 rsttab varchar2(30);
- 13 copyfile varchar2(30);
- 14 begin
- 15 tgtowner := 'DEMO';
- 16 tgttable := 'TRUNTAB';
- 17 --datapath := 'D:\oracle\product\10.2.0\oradata\EDGAR\DATAFILE\';
- 18 datadir := 'FY_DATA_DIR';
- 19 --prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);
- 20 rects := 'FY_REC_DATA';
- 21 rstts := 'FY_RST_DATA';
- 22 recfile := 'FY_REC_DATA.DAT';
- 23 Fy_Recover_data.clean_up_ts(rects, rstts);
- 24 --select block_size into blksz from dba_tablespaces ts, dba_tables t where ts.tablespace_name = t.tablespace_name and t.owner = tgtowner and t.table_name = tgttable;
- 25 blksz := 8192;
- 26 Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);
- 27 Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);
- 28 end;
- 29 /
- Recover Table: DEMO.TRUNTAB$
- Restore Table: DEMO.TRUNTAB$$
- Data Blocks formatted.
- Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
- 965 records recovered
- 958 records recovered
- 1144 records recovered
- ... ...
- 655 records recovered
- 662 records recovered
- 97 records recovered
- 95170 records recovered in backup table DEMO.TRUNTAB$$
- PL/SQL procedure successfully completed.
It also works.
With this package, we can recover the truncated data in most scenarios. In additional, it can also do something like FySafe. For example, to recover table of a damaged database, we can recover the dictionary tables from SYSTEM tablespace (The structure and data block id of dictionary tables are same in different DB with same Oracle version) first, and create dummy table with such information, then recover the user table. However, comparing to such dul tools as FySafe, function of Fy_Recover_Data is limited. For instance, if there is no meta data can be found, FySafe can guess the table structure from the data content while Fy_Recover_Data can do nothing. Compare them as below.XML/HTML代码
- Fy_Recover_Data FySafe
- Single Table Recover Perf. High High
- Multiple Table Recover Perf. Low High
- Compressed Table Support Support
- Index Orgnized Table Support Support
- Partition Table Support Support
- Row Chain Not Support Support
- Standard SQL Type Support Support
- BLOB/CLOB Store in Row Only Support
- Recover Deleted Rows Not Support Support
- Offline Recovering Support Support
- Recovering without Meta Data Not Support Support
- OS Platforms All All
- Oracle DB Versions 9i Up 8i Up
- Local Managed Tablespace Support Support
- Dictionary Managed Tablespace Not Test Support
- Other requirements No Java
The latest source code can be download at here: Fy_Recover_Data