HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Recover Truncated Data utilizing PLSQL

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2012-08-14 01:59:34

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

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代码
  1.                                                 +-------------------------+  
  2.                                                 | Copy Of Dummy Data File |  
  3.                                                 |  (With Formmated Blocks)|  
  4.                                                 +-------------------------+  
  5.                                                             ||  
  6.                                                             \/  
  7.                                                 (Blcok Header, Block Tail)  
  8.                                                             ||  
  9.                                                             \/  
  10. +-------------------+                                +----------------+     Table Scan    +---------------+  
  11. | Source Data File  | => (Data Block Content) =>     |  Dummy Table   |    ============>  | Restore Table |  
  12. |(Without Meta Data)|                                |(With Meta Data)|                   +---------------+  
  13. +-------------------+                                +----------------+  

Following this way, I created a PLSQL package, Fy_Recover_Data, and recovered the truncated data utilizing this package.

SQL代码
  1. HELLODBA.COM>insert into demo.truntab select * from demo.t_objects;  
  2.   
  3. 47585 rows created.  
  4.   
  5. HELLODBA.COM>commit;  
  6.   
  7. Commit complete.  
  8.   
  9. HELLODBA.COM>select count(*) from demo.truntab;  
  10.   
  11.   COUNT(*)  
  12. ----------  
  13.      47585  
  14.   
  15. HELLODBA.COM>truncate table demo.truntab;  
  16.   
  17. Table truncated.  
  18.   
  19. HELLODBA.COM>declare  
  20.   2    tgtowner varchar2(30);  
  21.   3    tgttable varchar2(30);  
  22.   4    datapath varchar2(4000);  
  23.   5    datadir varchar2(30);  
  24.   6    rects varchar2(30);  
  25.   7    recfile varchar2(30);  
  26.   8    rstts varchar2(30);  
  27.   9    rstfile varchar2(30);  
  28.  10    blksz number;  
  29.  11    rectab varchar2(30);  
  30.  12    rsttab varchar2(30);  
  31.  13    copyfile varchar2(30);  
  32.  14  begin  
  33.  15    tgtowner := 'DEMO';  
  34.  16    tgttable := 'TRUNTAB';  
  35.  17    datapath := 'D:\oracle\product\10.2.0\oradata\EDGAR\DATAFILE\'; 
  36.  18    datadir := 'FY_DATA_DIR';  
  37.  19    Fy_Recover_data.prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz);  
  38.  20    Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);  
  39.  21    Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);  
  40.  22  end;  
  41.  23  /  
  42. Directory Name: FY_DATA_DIR  
  43. Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT  
  44. Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT  
  45. Recover Table: DEMO.TRUNTAB$  
  46. Restore Table: DEMO.TRUNTAB$$  
  47. Data Blocks formatted.  
  48. Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT  
  49. 373 records recovered  
  50. 328 records recovered  
  51. 334 records recovered  
  52. .. ...  
  53. 285 records recovered  
  54. 275 records recovered  
  55. 235 records recovered  
  56. 47585 records recovered in backup table DEMO.TRUNTAB$$  
  57.   
  58. PL/SQL procedure successfully completed.  
  59.   
  60. HELLODBA.COM>insert into demo.truntab select * from DEMO.TRUNTAB$$;  
  61.   
  62. 47585 rows created.  
  63.   
  64. HELLODBA.COM>commit;  
  65.   
  66. Commit complete.  

All data have been recovered. Let me test a compressed table.

SQL代码
  1. HELLODBA.COM>set serveroutput on format wrapped  
  2. HELLODBA.COM>insert into demo.truntab select * from demo.t_objects;  
  3.   
  4. 47585 rows created.  
  5.   
  6. HELLODBA.COM>commit;  
  7.   
  8. Commit complete.  
  9.   
  10. HELLODBA.COM>alter table demo.truntab move compress;  
  11.   
  12. Table altered.  
  13.   
  14. HELLODBA.COM>select count(*) from demo.truntab;  
  15.   
  16.   COUNT(*)  
  17. ----------  
  18.      95170  
  19.   
  20. HELLODBA.COM>truncate table demo.truntab;  
  21.   
  22. Table truncated.  
  23.   
  24. HELLODBA.COM>declare  
  25.   2    tgtowner varchar2(30);  
  26.   3    tgttable varchar2(30);  
  27.   4    datapath varchar2(4000);  
  28.   5    datadir varchar2(30);  
  29.   6    rects varchar2(30);  
  30.   7    recfile varchar2(30);  
  31.   8    rstts varchar2(30);  
  32.   9    rstfile varchar2(30);  
  33.  10    blksz number;  
  34.  11    rectab varchar2(30);  
  35.  12    rsttab varchar2(30);  
  36.  13    copyfile varchar2(30);  
  37.  14  begin  
  38.  15    tgtowner := 'DEMO';  
  39.  16    tgttable := 'TRUNTAB';  
  40.  17    --datapath := 'D:\oracle\product\10.2.0\oradata\EDGAR\DATAFILE\';  
  41.  18    datadir := 'FY_DATA_DIR'; 
  42.  19    --prepare_files(tgtowner, tgttable, datapath, datadir, rects, recfile, rstts, rstfile, blksz); 
  43.  20    rects := 'FY_REC_DATA'; 
  44.  21    rstts := 'FY_RST_DATA'; 
  45.  22    recfile := 'FY_REC_DATA.DAT';  
  46.  23    Fy_Recover_data.clean_up_ts(rects, rstts);  
  47.  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;  
  48.  25    blksz := 8192;  
  49.  26    Fy_Recover_data.fill_blocks(tgtowner, tgttable, datadir, rects, recfile, rstts, 8, tgtowner, tgtowner, rectab, rsttab, copyfile);  
  50.  27    Fy_Recover_data.recover_table(tgtowner, tgttable, tgtowner, rectab, tgtowner, rsttab, datadir, datadir, recfile,datadir, copyfile, blksz);  
  51.  28  end;  
  52.  29  /  
  53. Recover Table: DEMO.TRUNTAB$  
  54. Restore Table: DEMO.TRUNTAB$$  
  55. Data Blocks formatted.  
  56. Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT  
  57. 965 records recovered  
  58. 958 records recovered  
  59. 1144 records recovered  
  60. ... ...  
  61. 655 records recovered  
  62. 662 records recovered  
  63. 97 records recovered  
  64. 95170 records recovered in backup table DEMO.TRUNTAB$$  
  65.   
  66. 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代码
  1.                                 Fy_Recover_Data     FySafe 
  2. Single Table Recover Perf.      High                High  
  3. Multiple Table Recover Perf.    Low                 High  
  4. Compressed Table                Support             Support  
  5. Index Orgnized Table            Support             Support  
  6. Partition Table                 Support             Support  
  7. Row Chain                       Not Support         Support  
  8. Standard SQL Type               Support             Support  
  9. BLOB/CLOB                       Store in Row Only   Support  
  10. Recover Deleted Rows            Not Support         Support  
  11. Offline Recovering              Support             Support  
  12. Recovering without Meta Data    Not Support         Support  
  13. OS Platforms                    All                 All  
  14. Oracle DB Versions              9i Up               8i Up  
  15. Local Managed Tablespace        Support             Support  
  16. Dictionary Managed Tablespace   Not Test            Support  
  17. Other requirements              No                  Java  

The latest source code can be download at here: Fy_Recover_Data

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat