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