“被误删除的package能恢复吗”logmnr案例：恢复被drop的包

建表test、建package:mypack

SQL> conn chennan/xxxx
已连接。
SQL>
SQL> create table test(id number primary key,name varchar2(20));

表已创建。

SQL> ed
已写入文件 afiedt.buf

  1  begin
  2  for x in 1 .. 10 loop
  3  insert into test values(x,dbms_random.string('u',20));
  4  end loop;
  5  commit;
  6* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> select * from test;

        ID NAME
---------- --------------------
         1 FGIUJVOACAFAWLLLYJZP
         2 GWANRKJKDHPPUEKCBGBO
         3 GHLAMYSQDVGZIDGWQSFN
         4 DEPWFPCXRMAERLPTUSEU
         5 FQKMLWLLXPPEKRLLXOXE
         6 LSVUGVSDWLRWIPZOBBHC
         7 ERCCDLQOZNYGKAPMLDPH
         8 IDHFFOTMSTOCFAHPEMIF
         9 JRJHLGITTOWJEPHJGVOW
        10 FEQFNFVOMXSUUZOTJQZY

已选择10行。

SQL> select * from v$log;  -- 检查当前日志是哪组

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           
---------- ---------- ---------- ---------- ---------- --- ---------------- 
         1          1       9481    2097152          1 NO  INACTIVE
         2          1       9482    2097152          1 NO  CURRENT
         3          1          0    2097152          1 YES UNUSED

已选择3行。

SQL> create or replace package mypack
  2  is
  3  procedure myproc;
  4  end;
  5  /

程序包已创建。

SQL> create or replace package body mypack
  2  is
  3  procedure myproc
  4  is
  5  begin
  6  delete from test;
  7  for x in 1 .. 5 loop
  8  insert into test values(x,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
  9  end loop;
 10  commit;
 11  end;
 12  end;
 13  /

程序包主体已创建。


SQL> select name,type from  user_source group by name,type;

NAME                           TYPE
------------------------------ ------------
P                              PROCEDURE
NOTE                           PACKAGE
NOTE                           PACKAGE BODY
MYPACK                     PACKAGE
MYPACK                     PACKAGE BODY
LOGON_TRG                      TRIGGER
PRINT_TEMP                     PROCEDURE

已选择7行。

删除package：

SQL> drop package mypack;

程序包已丢弃。

SQL> select name,type from  user_source group by name,type;

NAME                           TYPE
------------------------------ ------------
P                              PROCEDURE
NOTE                           PACKAGE
NOTE                           PACKAGE BODY
LOGON_TRG                      TRIGGER
PRINT_TEMP                     PROCEDURE

已选择5行。

SQL> select * from v$log;  -- 再次检查当前日志是哪组

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           
---------- ---------- ---------- ---------- ---------- --- ---------------- 
         1          1       9481    2097152          1 NO  INACTIVE
         2          1       9482    2097152          1 NO  CURRENT
         3          1          0    2097152          1 YES UNUSED

已选择3行。


SQL> alter system switch logfile;

系统已更改。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
         1          1       9481    2097152          1 NO  INACTIVE
         2          1       9482    2097152          1 NO  ACTIVE
         3          1       9483    2097152          1 NO  CURRENT

已选择3行。


SQL> alter system checkpoint;

系统已更改。

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
         1          1       9481    2097152          1 NO  INACTIVE
         2          1       9482    2097152          1 NO  INACTIVE
         3          1       9483    2097152          1 NO  CURRENT

已选择3行。

SQL>

此时拷贝日志组2的日志文件，更名为1REDO02.LOG
进行日志挖掘：

SQL> conn / as sysdba
已连接。
SQL>
SQL> exec dbms_logmnr.add_logfile('E:/oracle/oradata/cwgl/1REDO02.LOG',dbms_logmnr.NEW);

PL/SQL 过程已成功完成。

SQL> exec SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL 过程已成功完成。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss.sssss';

会话已更改。

SQL> select timestamp,sql_redo,sql_undo from v$logmnr_contents  order by timestamp ;

TIMESTAMP                 SQL_REDO                                 SQL_UNDO
------------------------- ---------------------------------------- --------------------------------
2008-07-23 10:39:36.38376 set transaction read write;
2008-07-23 10:39:36.38376 Unsupported                              Unsupported
2008-07-23 10:39:36.38376 Unsupported                              Unsupported
2008-07-23 10:39:36.38376 commit;
2008-07-23 10:39:36.38376 set transaction read write;
2008-07-23 10:39:36.38376 Unsupported                              Unsupported
......
2008-07-23 10:46:19.38779 drop package mypack;
2008-07-23 10:46:19.38779 delete from "SYS"."PROCEDURE$" where "OB insert into "SYS"."PROCEDURE$"("OBJ#","A
                          J#" = '46198' and "AUDIT$" = '---------- UDIT$","STORAGESIZE","OPTIONS") values (
                          ----------------------' and "STORAGESIZE '46198','-------------------------------
                          " IS NULL and "OPTIONS" = '1' and ROWID  -',NULL,'1');
                          = 'AAAABEAABAAAJ9DAB4';

2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '1' and "SOURCE" ","SOURCE") values ('46198','1','package
                           = 'package body mypack                   body mypack
                          ' and ROWID = 'AAAABHAABAAAJeHAAc';      ');

2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '2' and "SOURCE" ","SOURCE") values ('46198','2','is
                           = 'is                                   ');
                          ' and ROWID = 'AAAABHAABAAAJeHAAd';

2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '3' and "SOURCE" ","SOURCE") values ('46198','3','procedu
                           = 'procedure myproc                     re myproc
                          ' and ROWID = 'AAAABHAABAAAJeHAAe';      ');

2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '4' and "SOURCE" ","SOURCE") values ('46198','4','is
                           = 'is                                   ');
                          ' and ROWID = 'AAAABHAABAAAJeHAAf';

2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '5' and "SOURCE" ","SOURCE") values ('46198','5','begin
                           = 'begin                                ');
                          ' and ROWID = 'AAAABHAABAAAJeHAAg';

2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
                           = '46198' and "LINE" = '6' and "SOURCE" ","SOURCE") values ('46198','6','delete
                           = 'delete from test;                    from test;
                          ' and ROWID = 'AAAABHAABAAAJeHAAh';      ');

2008-07