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 105511640 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 108364841 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"）的语句就能恢复。本文永久更新链接地址