如何恢复DROP的存储过程

如何恢复DROP的存储过程

--此方法，适用开启归档模式的Oracle 数据库

当你DROP掉存储过程时，首先

SQL>drop procedure select_data;

SQL> alter system switch logfile;--切换日志

　　系统已更改。

查看flash_recovery_area文件下下最新的归档日志

SQL> conn sys as sysdba;--使用sys用户连接

SQL> show parameter utl

 

NAME                                 TYPE        VALUE

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

create_stored_outlines               string      

utl_file_dir                         string      

设置utl_file_dir路径

--单实例环境

SQL> alter system set utl_file_dir='d:demo' scope=spfile;

--RAC环境

SQL> alter system set utl_file_dir='d:demo' scope=spfile sid='*';

重启数据库

SQL> Shutdown immediate

SQL> Startup ;

建立字典文件(环境为windows)

--dict.ora为文件名d:\demo,为路径名

SQL>executedbms_logmnr_d.build('dict.ora','d:\logmnr',dbms_logmnr_d.store_in_flat_file);

PL/SQL 过程已成功完成。

建立日志分析列表

SQL>execute dbms_logmnr.add_logfile(logfilename=>'E:\app\ZT\flash_recovery_area\lyz\ARCHIVELOG\2012_11_21\O1_MF_1_29_8BRJ6S2J_.ARC',options=>dbms_logmnr.new);

PL/SQL 过程已成功完成。

--继续添加日志

SQL>execute dbms_logmnr.add_logfile(logfilename=>'E:\app\ZT\flash_recovery_area\lyz\ARCHIVELOG\2012_11_21\O1_MF_1_30_8BRJSY77_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 过程已成功完成。

启动LogMiner

SQL>execute dbms_logmnr.start_logmnr　　(dictfilename=>'D:\logmnr\dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL 过程已成功完成。

查询分析日志结果

PS:不知道为何显示的用户为UNKONW，有知道的告诉我下

查看到存储过程DROP的时间点，试用闪回功能恢复，脚本如下

闪回功能恢复

set pagesize 0

column text format a4000

spool D:\7.txt

SELECT text

       FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP('2012-11-21 10:40:02', 'YYYY-MM-DD HH24:MI:SS')

      WHERE OWNER = 'SCOTT'

        AND NAME = 'SELECT_DATA'

       ORDER BY LINE

     ;

spool off;

此时在D盘下的7.txt文件里就是被DROP的存储过程select_data

在Eygle大神指点的方向下，我发现还有一种方法可以实现恢复，也感谢

BearFishShow 的方法

创建恢复函数,此方法来自http://bbs.csdn.net/topics/370143351，感谢BearFishShow

CREATE OR REPLACE FUNCTION RECOVE_PROCE(del_Time  in varchar2,

                                        proc_Name in varchar2)

  RETURN VARCHAR2 IS

/**

*

*  function Name :存储过程或function 删除后恢复方法 

*

*  del_time 对象被删除时间

*

*  proc_Name :被删除对象名称 

*

*  return :返回重建对象的语句

*

*/

  OBJ_NUM  NUMBER;

  str_Proc varchar2(2000);

  str_End  varchar2(2000) := '';

  str_Sql  varchar2(2000);

BEGIN

  SELECT obj#

    INTO OBJ_NUM

    FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')

   WHERE NAME = upper(proc_Name);

 

  for i in (SELECT rowid rid, source

              FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')

             where obj# = OBJ_NUM

             order by line) loop

    select source

      into str_Proc

      from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')

     where obj# = OBJ_NUM

       AND ROWID = I.RID

     order by line;

    str_End := str_End || str_Proc;

  end loop;

 

  str_Sql := 'CREATE OR REPLACE ' || str_End;

  return str_Sql;

exception

  when others then

    dbms_output.put_line(sqlcode || sqlerrm);

    return null;

END RECOVE_PROCE;

/

set pagesize 0

column text format a4000

spool D:\7.txt

SELECT RECOVE_PROCE(‘2012-11-21 10:40:02’,’SELECT_DATA’)       FROM from DUAL;

spool off;

此时在D盘下的7.txt文件里就是被DROP的存储过程select_data(创建语句)