如何找回被create or replace覆盖的PL/SQL对象 - Oracle和MySQL？
如何找回被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定义。 