logminer来恢复在表DDL之前被删除的数据
hellowillness 2016-05-17
做这个测试是因为前同事(开发人员)在客户现场做维护误删除了一张表的记录，但在删除表之后修改了表的结构(修改了字段的精度），发现误删除记录后，想通对表执行闪回查询来恢复被删除的记录发现不能闪回了，因为表结构发现了修改(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')的语句就能恢复。
