- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
发表于 2012-5-21 21:26:43
方案 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 。 |
|