logminer来恢复在表DDL之前被删除的数据

做这个测试是因为前同事(开发人员)在客户现场做维护误删除了一张表的记录，但在删除表之后修改了表的结构(修改了字段的精度），发现误删除记录后，想通对表执行闪回查询来恢复被删除的记录发现不能闪回了，因为表结构发现了修改(ORA-01466: unable to read data - table definition has changed)。而且这个数据库没有备份，只有归档。简单的方法就是通过logminer来挖掘归档日志来进行恢复。

一.创建测试表t1，并插入两条记录

SQL> create table t1(t_id number,t_name varchar2(50));  Table created.  SQL>  alter table t1 add t_salary number(8);  Table altered.  SQL> desc t1  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  T_ID                                               NUMBER  T_NAME                                             VARCHAR2(50)  T_SALARY                                           NUMBER(8)  SQL> insert into t1 values(1,'jy',10000);  1 row created.  SQL> insert into t1 values(2,'wj',8000);  1 row created.  SQL> commit;  Commit complete.  SQL> select * from t1;        T_ID T_NAME                                               T_SALARY ---------- -------------------------------------------------- ----------          1 jy                                                      10000          2 wj    

二.删除表中记录

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';  Session altered.  SQL> select sysdate from dual;  SYSDATE -------------------- 2016-05-13 11:07:02 

删除表t1中t_id=2的记录，通过logminer来恢复的记录就是它

SQL> delete from t1 where t_id=2;  1 row deleted.  SQL> commit;  Commit complete. 

在表结构没有发生改变之前，尝试使用闪回查询执行成功

SQL> select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss');        T_ID T_NAME                                               T_SALARY ---------- -------------------------------------------------- ----------          1 jy                                                      10000          2 wj                                                       8000 

修改表结构，这里只是简单的修改了字段类型的长度

SQL> alter table t1 modify t_salary number(10);  Table altered. 

在表结构发生改变之后，尝试使用闪回查询执行报错

SQL> select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss'); select *  from t1     as of timestamp to_timestamp('2016-05-13 11:07:02','yyyy-mm-dd hh24:mi:ss')                * ERROR at line 1: ORA-01466: unable to read data - table definition has changed 

三.使用logminer来挖掘归档重做日志
查看当前的归档日志文件

SQL> set long 900 SQL> set linesize 900 SQL> set pagesize 900 SQL> col name for a100 SQL> select name,sequence#,first_change# from v$archived_log ;  NAME                                                                                                  SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc                      3        559310 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc                      4        590316 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc                      5        622788 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc                      6        645078 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc                      7        656708 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc                      8        670463 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc                      9        688685 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc                    10        705191 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc                    11        715113 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc                    12        731487 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc                    13        737140 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc                    14        749330 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc                    15        757183 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc                    16        766296 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc                    17        773422 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc                    18        779449 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc                    19        790719 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc                    20        804844 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc                    21        814648 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc                    22        826389 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc                    23        834321 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc                    24        846100 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc                    25        863715 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc                    26        874870 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc                    27        885043 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc                    28        898767 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc                    29        912954 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc                    30        923000 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc                    31        929385 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc                    32        936055 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc                    33        946964 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc                    34        952518 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc                    35        955561 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc                    36        966403 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc                    37        977840 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc                    38        996480 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc                    39       1018201 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc                    40       1019655 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc                    41       1036392 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc                    42       1055116  40 rows selected. 

强制日志切换将当前使用的联机重做日志文件时行归档

SQL> alter system switch logfile;  System altered.  SQL> select name,sequence#,first_change# from v$archived_log ;  NAME                                                                                                  SEQUENCE# FIRST_CHANGE# ---------------------------------------------------------------------------------------------------- ---------- ------------- /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_3_bkl8b4to_.arc                      3        559310 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_4_bkl92rxm_.arc                      4        590316 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_5_bkl94mv3_.arc                      5        622788 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_6_bkl9612c_.arc                      6        645078 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_7_bkl97n1p_.arc                      7        656708 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_8_bkl99bb9_.arc                      8        670463 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_9_bkl9c4v2_.arc                      9        688685 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_10_bkl9dvf0_.arc                    10        705191 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_11_bkl9gjpd_.arc                    11        715113 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_12_bkl9hpqq_.arc                    12        731487 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_13_bkl9m1gk_.arc                    13        737140 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_14_bkl9mlg9_.arc                    14        749330 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_15_bkl9n5ky_.arc                    15        757183 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_16_bkl9o2p1_.arc                    16        766296 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_17_bkl9oyz0_.arc                    17        773422 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_18_bkl9qcpf_.arc                    18        779449 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_19_bkl9rhv4_.arc                    19        790719 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_20_bkl9ssv4_.arc                    20        804844 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_21_bkl9w2xt_.arc                    21        814648 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_22_bkl9xjx8_.arc                    22        826389 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_23_bkl9ym63_.arc                    23        834321 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_24_bklb0gh1_.arc                    24        846100 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_25_bklb17f6_.arc                    25        863715 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_26_bklb23gw_.arc                    26        874870 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_27_bklb3pmz_.arc                    27        885043 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_28_bklb5fl3_.arc                    28        898767 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_29_bklb6xr6_.arc                    29        912954 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_30_bklb89wn_.arc                    30        923000 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_31_bklb97v9_.arc                    31        929385 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_32_bklbb8yk_.arc                    32        936055 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_33_bklbccbm_.arc                    33        946964 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_34_bklbd33q_.arc                    34        952518 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_35_bklbf7v8_.arc                    35        955561 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_36_bklbg9mf_.arc                    36        966403 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_37_bklcon42_.arc                    37        977840 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_38_bkldl4pl_.arc                    38        996480 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_39_bklfrzhj_.arc                    39       1018201 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2015_03_30/o1_mf_1_40_bklor7cf_.arc                    40       1019655 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_04_13/o1_mf_1_41_cjvtwjm2_.arc                    41       1036392 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc                    42       1055116 /u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc                    43       1083648  41 rows selected. 

向logminer增加需要分析的归档重做日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_42_cm9fovjk_.arc',options=>dbms_logmnr.NEW);  PL/SQL procedure successfully completed. 

继续向logminer增加需要分析的归档重做日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/JYTEST/archivelog/2016_05_13/o1_mf_1_43_cmbkro1j_.arc',options=>dbms_logmnr.addfile);  PL/SQL procedure successfully completed. 

执行分析

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);  PL/SQL procedure successfully completed. 

将分析的内容存储在临时表logmnr_contents中

SQL> create table logmnr_contents as select * from v$logmnr_contents ;  Table created. 

终止分析操作

SQL> execute dbms_logmnr.end_logmnr;  PL/SQL procedure successfully completed. 

查询T1所产生的日志记录

SQL> select username,scn,timestamp,sql_redo,sql_undo from logmnr_contents where seg_name='T1';  USERNAME                              SCN TIMESTAMP   SQL_REDO                                                                                                              SQL_UNDO ------------------------------ ---------- ---------   -------------------------------------------------------------------------------------------------------------------   ------------------------------------------------------------------------------------------------------------------------ UNKNOWN                           1102088 13-MAY-16  create table t1(t_id number,t_name varchar2(50));  JY                                1102114 13-MAY-16  alter table t1 add t_salary number(8);  UNKNOWN                           1102129 13-MAY-16  insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('1','jy','10000');                                           delete from "JY"."T1" where "T_ID" = '1' and "T_NAME" = 'jy' and "T_SALARY" = '10000' and ROWID = 'AAANc6AAEAAAAGEAAA';  UNKNOWN                           1102822 13-MAY-16  insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');                                            delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA';  UNKNOWN                           1103738 13-MAY-16  delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA'; insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');  JY                                1103789 13-MAY-16  alter table t1 modify t_salary number(10); 

从查询结果来看删除操作对应的sql_redo为:delete from "JY"."T1" where "T_ID" = '2' and "T_NAME" = 'wj' and "T_SALARY" = '8000' and ROWID = 'AAANc6AAEAAAAGFAAA',对应的sql_undo为:insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000');为了恢复删除的t_id=2的记录，只需要执行sql_undo为insert into "JY"."T1"("T_ID","T_NAME","T_SALARY") values ('2','wj','8000')的语句就能恢复。