Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

49

积分

0

好友

0

主题
发表于 2012-5-21 16:28:16 | 查看: 10228| 回复: 9
标题已被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 出来?


原帖 标题和提问内容文字不通  错别字连篇,请下次酝酿好再发帖。
发表于 2012-5-21 16:29:30
CREATE OR REPLACE PROCEDURE A()
过称开头是这样写的

回复 显示全部楼层 道具 举报

发表于 2012-5-21 16:35:13
答案是有的, 稍后会总结并公布。

回复 显示全部楼层 道具 举报

发表于 2012-5-21 17:36:08
flashback query dba_source,try

回复 显示全部楼层 道具 举报

发表于 2012-5-21 19:13:14
多谢刘大 和大家的 关注,说明一点,被replace的过程很重要,而且是在生产库上的,不允许有停机停库 操作什么的,再次感谢大家

回复 显示全部楼层 道具 举报

发表于 2012-5-21 20:42:49
问题已经解决,用如下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 语句的一些使用技巧

回复 显示全部楼层 道具 举报

发表于 2012-5-21 20:50:54
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;

回复 显示全部楼层 道具 举报

发表于 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 。

回复 显示全部楼层 道具 举报

发表于 2012-5-21 22:17:48
学习。。。。。。。。。

回复 显示全部楼层 道具 举报

发表于 2012-5-21 22:31:30
日志有时候真不好找!

回复 显示全部楼层 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站

GMT+8, 2020-1-18 05:54 , Processed in 0.069675 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569