编译存储过程procedure被覆盖如何挽救【标题已被maclean改良】

yangang_q

标题已被maclean改良   好的标题是一切的开始










版本：oracle 10.2.0.4
rac 环境

比如说 我以 A过称为模板，在此基础上编写B过程，过称写好后，在编译前没有修改过程名，还是用A 就进行了编译，且正常通过。后来发现 改问题，想要恢复A过称，怎么处理。

生产库，不允许停机的

麻烦刘大 看看






============================================================================================


comment by maclean

这个问题 应当这样描述 ， 10.2.0.4 + RAC 环境 旧的存储过程stored procedure 被 create or replace 同名的新存储过程覆盖，  问： 是否有办法把旧的 存储过程procedure 还原restore 出来？


原帖 标题和提问内容文字不通  错别字连篇，请下次酝酿好再发帖。

yangang_q

CREATE OR REPLACE PROCEDURE A()
过称开头是这样写的

Maclean Liu(刘相兵

答案是有的， 稍后会总结并公布。

anbob

flashback query dba_source,try

yangang_q

多谢刘大 和大家的 关注，说明一点，被replace的过程很重要，而且是在生产库上的，不允许有停机停库 操作什么的，再次感谢大家

yangang_q

问题已经解决，用如下Flashback  Query语句可以查看指定时间的过程的语句。

SELECT TEXT  FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2012-05-21 15:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE OWNER = 'DW'   AND TYPE = 'PROCEDURE'   AND NAME = 'P_DW_V_USER_CHARGE_EXT_DAILY' ORDER BY LINE;

但是 使用该语句的权限还不太清楚。是否必须使用sys 用户操作才行，还望刘大赐教，希望刘大能总结一下oracle flash back 语句的一些使用技巧

Maclean Liu(刘相兵

4楼的同学其实已经给出很好的答案了，  

方案1 利用 flashback query 闪回查询找回原procedure

如果无法知道精确的scn ， 可以使用 as of timestamp  估算旧procedure存在的时间戳


ODM TEST:

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production




SQL> create or replace procedure maclean_proc as
  2  begin
  3  execute immediate 'select 1 from dual';
  4  end;
  5  /

Procedure created.



SQL>
SQL> select * from dba_source where name='MACLEAN_PROC';

OWNER      NAME                           TYPE               LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS        MACLEAN_PROC                   PROCEDURE             1 procedure maclean_proc as
SYS        MACLEAN_PROC                   PROCEDURE             2 begin
SYS        MACLEAN_PROC                   PROCEDURE             3 execute immediate 'select 1 from dual';
SYS        MACLEAN_PROC                   PROCEDURE             4 end;




SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2660057

        
  1  create or replace procedure maclean_proc as
  2  begin
  3  -- I am new procedure
  4  execute immediate 'select 2 from dual';
  5* end;

Procedure created.


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2660113



SQL> select * from dba_source where name='MACLEAN_PROC';

OWNER      NAME                           TYPE               LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS        MACLEAN_PROC                   PROCEDURE             1 procedure maclean_proc as
SYS        MACLEAN_PROC                   PROCEDURE             2 begin
SYS        MACLEAN_PROC                   PROCEDURE             3 -- I am new procedure
SYS        MACLEAN_PROC                   PROCEDURE             4 execute immediate 'select 2 from dual';
SYS        MACLEAN_PROC                   PROCEDURE             5 end;




SQL> create table old_source as select * from dba_source as of scn 2660057;

Table created.



SQL> select * from old_source where name='MACLEAN_PROC';

OWNER      NAME                           TYPE               LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS        MACLEAN_PROC                   PROCEDURE             1 procedure maclean_proc as
SYS        MACLEAN_PROC                   PROCEDURE             2 begin
SYS        MACLEAN_PROC                   PROCEDURE             3 execute immediate 'select 1 from dual';
SYS        MACLEAN_PROC                   PROCEDURE             4 end;

Maclean Liu(刘相兵

方案 2  使用 logminer挖掘日志， 前提是 归档模式 或者 非归档模式下 replace procedure的redo仍在未被覆盖的online logfile中

SQL> alter database add supplemental log data;

Database altered.


SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> create or replace procedure MACLEAN_PROC
  2  as
  3  begin
  4  null;
  5  end;
  6  /

Procedure created.



replace 一个procedure 的过程包括 以下的递归DML:

[oracle@vrh8 ~]$ egrep  "update|insert|delete|merge"  /s01/admin/G10R25/udump/g10r25_ora_4127.trc
delete from procedureinfo$ where obj#=:1
delete from argument$ where obj#=:1
delete from procedurec$ where obj#=:1
delete from procedureplsql$ where obj#=:1
delete from procedurejava$ where obj#=:1
delete from vtable$ where obj#=:1
insert into procedureinfo$(obj#,procedure#,overload#,procedurename,properties,itypeobj#) values (:1,:2,:3,:4,:5,:6)
insert into argument$( obj#,procedure$,procedure#,overload#,position#,sequence#,level#,argument,type#,default#,in_out,length,precision#,scale,radix,charsetid,charsetform,properties,type_owner,type_name,type_subname,type_linkname,pls_type) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)
insert into procedureplsql$(obj#,procedure#,entrypoint#) values (:1,:2,:3)
update procedure$ set audit$=:2,options=:3 where obj#=:1
delete from source$ where obj#=:1
insert into source$(obj#,line,source) values (:1,:2,:3)
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
update idl_sb4$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub1$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_char$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub2$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_sb4$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
update idl_sb4$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub1$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from error$ where obj#=:1
delete from settings$ where obj# = :1
insert into settings$(obj#, param, value) values (:1, :2, :3)
delete from warning_settings$ where obj# = :1
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
delete from dependency$ where d_obj#=:1
delete from access$ where d_obj#=:1
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)



其中与源代码有关系的是

delete from source$ where obj#=:1
insert into source$(obj#,line,source) values (:1,:2,:3)


只需要 logminer 挖掘出 与source$相关的DELETE 语句即可：

SQL> alter system switch logfile;

System altered.


SQL> select sequence#,name from v$archived_log where sequence#=(select max(sequence#) from v$archived_log);

SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
       241
/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_241_7vnh9qk0_.arc


SQL> exec dbms_logmnr.add_logfile ('/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_241_7vnh9qk0_.arc',options => dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.



  1* select operation, sql_redo,sql_undo from v$logmnr_contents where seg_name = 'SOURCE$'
SQL> /

OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------
DELETE
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '1' and "SOURCE" = 'procedure maclean_proc as
' and ROWID = 'AAAABIAABAAALpyAAJ';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','1','procedure maclean_proc as
');

DELETE
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '2' and "SOURCE" = 'begin
' and ROWID = 'AAAABIAABAAALpyAAK';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','2','begin
');

DELETE
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '3' and "SOURCE" = '-- I am new procedure
' and ROWID = 'AAAABIAABAAALpyAAL';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','3','-- I am new procedure
');

DELETE
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '4' and "SOURCE" = 'execute immediate ''select 2 from dual'';
' and ROWID = 'AAAABIAABAAALpyAAM';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','4','execute immediate ''select 2 from dual'';
');

DELETE
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '5' and "SOURCE" = 'end;' and ROWID = 'AAAABIAABAAALpyAAS';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','5','end;');




使用挖掘到的 undo sql即可 还原这些procedure 。

psufnxk2000

学习。。。。。。。。。

am196

日志有时候真不好找！