2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
如何找回被create or replace覆盖的PL/SQL对象
有同学在T.Askmaclean.com上提问关于10gR2下原存储过程procedure，因为开发人员误操作create or replace 使用同样的过程名导致原存储过程被覆盖，希望通过Oracle技术手段在不停机的前提下找回原存储过程procedure。
这里Maclean 提供2种10gR2以后可以在线挽救被覆盖PL/SQL对象的方案：
方案1： 利用Flashback Query 闪回特性，该方案并不要求数据库已启用flashback database，唯一和最关键的要求是create or replace时递归SQL删除source$数据字典基础表相关的undo data，不要因为时间过久而被重用：
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> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com SQL> create or replace procedure maclean_proc as 2 begin 3 execute immediate 'select 1 from dual'; 4 end; 5 / Procedure created. 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 create or replace procedure maclean_proc as begin -- I am new procedure execute immediate 'select 2 from dual'; 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 where name='MACLEAN_PROC'; 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;如果无法找出准备的scn作为flashback query闪回原点，那么可以尝试使用as of timestamp多次指定不同的时间点，一般只要PL/SQL对象被覆盖的时间不要太久且实例对undo的并发事务需求较低时，都可以通过以上方法找回被replace/drop 覆盖或删除的PL/SQL对象。 方案2 利用logminer找出replace/drop PL/SQL对象的递归SQL主要是DELETE语句，利用logminer的UNDO SQL来找回PL/SQL对象的定义。 该方案的前提是启用了归档且相关的archivelog未被删除，数据库最好是启用了最小追加日志 minimal supplemental logging，否则可能出现挖掘出的Unsupported SQLREDO的情况： create or replace替换 一个 procedure存储过程的递归SQL包括以下这些， 主要是删除原procedure在数据字典中的记录并插入新的记录， source$字典基表是找回存储过程的重点：
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> create or replace procedure maclean_proc as 2 begin 3 execute immediate 'select 1 from dual'; 4 end; 5 / Procedure created. SQL> SQL> oradebug setmypid; Statement processed. SQL> SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. SQL> SQL> create or replace procedure maclean_proc as 2 begin 3 execute immediate 'select 2 from dual'; 4 end; 5 / Procedure created. SQL> oradebug tracefile_name /s01/admin/G10R25/udump/g10r25_ora_4305.trc [oracle@vrh8 ~]$ egrep "update|insert|delete|merge" /s01/admin/G10R25/udump/g10r25_ora_4305.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 dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7) 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)而drop procedure也会类似的去source$删除该PL/SQL对应的记录:
SQL> oradebug setmypid; Statement processed. SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. SQL> drop procedure maclean_proc; Procedure dropped. SQL> oradebug tracefile_name /s01/admin/G10R25/udump/g10r25_ora_4331.trc delete from context$ where obj#=:1 delete from dir$ where obj#=:1 delete from type_misc$ where obj#=:1 delete from library$ where obj#=:1 delete from procedure$ where obj#=:1 delete from javaobj$ where obj#=:1 delete from operator$ where obj#=:1 delete from opbinding$ where obj#=:1 delete from opancillary$ where obj#=:1 delete from oparg$ where obj# = :1 delete from com$ where obj#=:1 delete from source$ where obj#=:1 delete from idl_ub1$ where obj#=:1 and part=:2 delete from idl_char$ where obj#=:1 and part=:2 delete from idl_ub2$ where obj#=:1 and part=:2 delete from idl_sb4$ where obj#=:1 and part=:2 delete from ncomp_dll$ where obj#=:1 returning dllname into :2 delete from idl_ub1$ where obj#=:1 and part=:2 delete from idl_char$ where obj#=:1 and part=:2 delete from idl_ub2$ where obj#=:1 and part=:2 delete from idl_sb4$ where obj#=:1 and part=:2 delete from ncomp_dll$ where obj#=:1 returning dllname into :2 delete from idl_ub1$ where obj#=:1 and part=:2 delete from idl_char$ where obj#=:1 and part=:2 delete from idl_ub2$ where obj#=:1 and part=:2 delete from idl_sb4$ where obj#=:1 and part=:2 delete from ncomp_dll$ where obj#=:1 returning dllname into :2 delete from error$ where obj#=:1 delete from settings$ where obj# = :1 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 delete from dependency$ where d_obj#=:1 delete from access$ where d_obj#=:1 delete from objauth$ where obj#=:1 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)开始正觉挖掘日志寻找source$相关的redo:
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
--------------------------------------------------------------------------------
242
/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_242_7vnm13k6_.arc
SQL> exec dbms_logmnr.add_logfile ('/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_242_7vnm13k6_.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.
SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_name = 'SOURCE$' and operation='DELETE';
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '1' and "SOURCE" = 'procedure maclean_proc as
' and ROWID = 'AAAABIAABAAALpyAAN';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','1','procedure maclean_proc as
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '2' and "SOURCE" = 'begin
' and ROWID = 'AAAABIAABAAALpyAAO';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','2','begin
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '3' and "SOURCE" = 'execute immediate ''select 1 from dual'';
' and ROWID = 'AAAABIAABAAALpyAAP';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','3','execute immediate ''select 1 from dual'';
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '4' and "SOURCE" = 'end;' and ROWID = 'AAAABIAABAAALpyAAQ';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','4','end;');
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 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 from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '3' and "SOURCE" = 'execute immediate ''select 2 from dual'';
' and ROWID = 'AAAABIAABAAALpyAAL';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','3','execute immediate ''select 2 from dual'';
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '4' and "SOURCE" = 'end;' and ROWID = 'AAAABIAABAAALpyAAM';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','4','end;');
可以利用 logminer获得的UNDO SQL替换其中的表名source$为临时表，将DELETE的数据插入到这张临时表中，之后查询SOURCE字段就可以获得被覆盖前的PL/SQL对象的DDL定义。
posted on 2013-03-19 00:51 Oracle和MySQL 阅读(463) 评论(0) 编辑 收藏 举报
