Fy_Recover_Data ———— 用于数据恢复的PLSQL包

日期： 2012-08-14 03:56:53

分享到

Fy_Recover_Data是利用Oracle表扫描机制、数据嫁接机制恢复TRUNCATE或者损坏数据的工具包。由纯PLSQL编写。
关于其实现原理和使用示例请参考：移花接木————利用Oracle表扫描机制恢复被Truncate的数据。
包中过程、函数描述如下：

  -- 更新记录：

-- 15/08/2012, Fuyuncat: ---

-- 1. Fixed Bug in Clean_Up_Ts (Not change TS status correctly) ---

-- 2. Added Exception Handle when Restore Data ---

-- 3. Added Parameter in recover_table, ---

-- to balance Fault Tolerance and Performance ---

-- ---

-- 16/08/2012, Fuyuncat: ---

-- 1. Enhanced corrupted block processing, get rows as possilbe ---

-- ---

-- 17/08/2012, Fuyuncat: ---

-- 1. Omit the LOB columns raised ORA-22922 exception ---

-- ---

-- 20/08/2012, Fuyuncat: ---

-- 1. Omit the LOB columns via db link ---

-- ---

-- 22/08/2012, Fuyuncat: ---

-- 1. Updated logging and tracing interface ---

-- ---

-- 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 ---

-- ---

-- 05/03/2014, Fuyuncat: ---

-- 1. Fixed bugs ---

-- 2. Use existing dirctory if applicable ---

-- 3. Recover data from offline files ---

SQL代码

create or replace package FY_Recover_Data is

  ---------------------------------------------------------------------------

  -- WWW.HelloDBA.COM                                                     ---

  -- Created By: Fuyuncat                                                 ---

  -- Created Date: 08/08/2012                                             ---

  -- Email: Fuyuncat@gmail.com                                            ---

  -- Copyright (c), 2014, WWW.HelloDBA.COM All rights reserved.           ---

  -- Latest Version: http://www.HelloDBA.com/Download/FY_Recover_Data.zip ---

  -- ---

---------------------------------------------------------------------------



  type r_cursor is REF CURSOR;

  type o_fileprop is record (

   file# number,

   status$ number);

  type t_fileprops is table of o_fileprop;



  /************************************************************************

  ** recover truncated table

  **

  ** tgtowner: Owner of Target Table to be recovered;

  ** tgttable: Name of Target Table to be recovered;

  ** datapath: Absolute path of Data Files;

  ** fbks: block number to be filled in recovery table;

  ** offline_files: Offline data files that data should be recovered from;

  **    foramt: full_path_file1;full_path_file2...;

  ************************************************************************/

  procedure recover_truncated_table( tow varchar2,

                                     ttb varchar2,

                                     fbks number default 1,

                                     tmppath varchar2 default null,

                                     offline_files varchar2 default null);



  /************************************************************************

  ** dump a block in raw, for testing

  **

  ** hdfile: Data file name;

  ** srcdir: data file directory

  ** blknb: block number to be dumped;

  ** blksz: block size;

  ************************************************************************/

  procedure dump_seg_block_raw( hdfile varchar2,

                                srcdir varchar2,

                                blknb number,

                                blksz number default 8192);

  /************************************************************************

  ** Set Initial parameters

  **

  ** tracing: trace the process for debug;

  ** logging: show logging information;

  ** repobjid: replace the data object id wiht the recover table data object id;

  ************************************************************************/

  procedure init_set( tracing boolean default true,

                      logging boolean default true,

                      repobjid boolean default true);

end FY_Recover_Data;

点击此处：下载源代码。

--- Fuyuncat ---

上一篇：OraTracer升级

下一篇：《Oracle 高性能SQL引擎剖析:SQL优化与调优机制详解》随书脚本

本类中的所有文章