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

Fy_Recover_Data Update (2014-02-20) --- Support ASM now!

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2016-02-19 22:07:41

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

Fy_Recover_Data can recover the truncated table created on ASM now. 

This update includes,

  -- 19/02/2014, Fuyuncat:                                                ---

  --   1. Temp Restore and Recover tablespace & files                     ---

  --      will be created on temp folder                                  ---

  --   2. Handle tablespace has files located at diff folders             ---

  --   3. Handle tables on ASM                                            ---

A new interface:

SQL代码
  1. /************************************************************************  
  2. ** recover truncated table  
  3. **  
  4. ** tgtowner: Owner of Target Table to be recovered;  
  5. ** tgttable: Name of Target Table to be recovered;  
  6. ** datapath: Absolute path of Data Files;  
  7. ** fbks: block number to be filled in recovery table;  
  8. ************************************************************************/  
  9. procedure recover_truncated_table( tow varchar2 default 'DEMO',   
  10.                                    ttb varchar2 default 'T_CHAIN',  
  11.                                    fbks number default 1,   
  12.                                    tmppath varchar2 default '/tmp/');  

Removed those unnecessary interfaces.

Example:

SQL代码
  1. HelloDBA.COM> create tablespace asm_data datafile '+DATA1' autoextend on maxsize 1g;  
  2.   
  3. Tablespace created.  
  4.   
  5. HelloDBA.COM> create table demo.t_asmtable tablespace asm_data as select * from dba_objects where 1=2;  
  6.   
  7. Table created.  
  8.   
  9. HelloDBA.COM> alter tablespace asm_data add datafile '/u01/app/oracle/oradata/dbat1/asm_data_fs.dat' size 100M;  
  10.   
  11. Tablespace altered.  
  12.   
  13. HelloDBA.COM> insert into demo.t_asmtable select * from dba_objects;  
  14.   
  15. 91155 rows created.  
  16.   
  17. HelloDBA.COM> select count(*) from demo.t_asmtable;  
  18.   
  19. HelloDBA.COM>  
  20.   COUNT(*)  
  21. ----------  
  22.      91155  
  23.   
  24. HelloDBA.COM> truncate table demo.t_asmtable;  
  25.   
  26. Table truncated.  
  27.   
  28. HelloDBA.COM> exec fy_recover_data.recover_truncated_table('DEMO','T_ASMTABLE',1,'/tmp');  
  29. 10:46:55: Directory Name: FY_DATA_DIR2  
  30. 10:46:56: Recover Table: DEMO.T_ASMTABLE$  
  31. 10:46:56: Restore Table: DEMO.T_ASMTABLE$$  
  32. 10:47:08: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT  
  33. 10:47:08: begin to recover table DEMO.T_ASMTABLE  
  34. 10:47:09: Directory Name: TMP_HF_DIR  
  35. 10:47:10: Recovering data in datafile /u01/app/oracle/oradata/dbat1/asm_data_fs.dat  
  36. 10:47:10: Directory Name: TMP_DATA_FILE_DIR4  
  37. 10:47:19: 630 truncated data blocks found.  
  38. 10:47:19: 43830 records recovered in backup table DEMO.T_ASMTABLE$$  
  39. 10:47:19: Recovering data in datafile +DATA1/dbat1/datafile/asm_data.360.839931591  
  40. 10:47:19: Directory Name: TMP_DATA_FILE_DIR4  
  41. 10:47:30: 686 truncated data blocks found.  
  42. 10:47:30: 47325 records recovered in backup table DEMO.T_ASMTABLE$$  
  43. 10:47:30: Total: 1316 truncated data blocks found.  
  44. 10:47:30: Total: 91155 records recovered in backup table DEMO.T_ASMTABLE$$  
  45. 10:47:30: Recovery completed.  
  46. 10:47:30: Data has been recovered to DEMO.T_ASMTABLE$$  
  47.   
  48. PL/SQL procedure successfully completed.  
  49.   
  50. HelloDBA.COM> select count(*) from DEMO.T_ASMTABLE$$;  
  51.   
  52.   COUNT(*)  
  53. ----------  
  54.      91155  

Click Here to download source code of the package.

 

--- Fuyuncat ---

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat