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')的语句就能恢复。

相关推荐