Recovery dropped Procedure

 

Recovery dropped Procedure

 

Oracle version: 9.2.0.6.0

Theory: using Oracle redo & undo mechanism

 

1、 check the redo log 

select * from v$log;

2、 find the timestamp from logminer

reference the sop ‘如何利用TOAD来使用LOGMiner分析archive log.docx’

 

Select SCN, timestamp, session# session_num, sql_redo,sql_undo

From V$LOGMNR_CONTENTS

where timestamp > sysdate-0.2 and sql_redo like '%drop%'

Result:

SCN TIMESTAMP     SESSION_NUM         SQL_REDO        SQL_UNDO

 

32615077171 2009-6-17 14:02:28 52     drop table "MISAP"."MPW_WAT_DETAIL" cascade constraints force;

32615077198 2009-6-17 14:02:28 52     drop table "MISAP"."MPW_WAT_SUMM" cascade constraints force; 

32615077227 2009-6-17 14:02:28 52     drop table "MISAP"."TOAD_PLAN_TABLE" cascade constraints force;

32615077242 2009-6-17 14:02:29 52     drop procedure "MISAP"."SP_MPW_WAT_DETAIL";     

32615077260 2009-6-17 14:02:29 52     drop procedure "MISAP"."SP_MPW_WAT_SUMM";      

32615077288 2009-6-17 14:02:31 52     drop user misap cascade ;      

 

3、 find the object id using sys accout

/* Formatted on 2009/06/17 15:35 (Formatter Plus v4.8.8) */

SELECT obj#, NAME

 FROM SYS.obj$ AS OF TIMESTAMP TO_TIMESTAMP ('2009-06-17 14:02:29',

                                              'yyyy-mm-dd hh24:mi:ss'

                                             )

 WHERE NAME in ('SP_MPW_WAT_SUMM',’ SP_MPW_WAT_DETAIL’);

Result:

12467 SP_MPW_WAT_DETAIL

12455 SP_MPW_WAT_SUMM

 

4、 find the procedure scripts

SELECT source FROM SYS.source$ AS OF TIMESTAMP TO_TIMESTAMP ('2009-06-17 14:02:29',                                              'yyyy-mm-dd hh24:mi:ss') WHERE obj# = 12467;

                                                                           

SELECT source FROM SYS.source$ AS OF TIMESTAMP TO_TIMESTAMP ('2009-06-17 14:02:29',                                              'yyyy-mm-dd hh24:mi:ss') WHERE obj# = 12455;

Remark：If the undo/redo is rewrite, it can’t find the dropped procedure.