一、本文说明：
本文转载于飚哥发表的一篇文章：，此处只做转载+模拟。
二、转载内容：
2.1.1、版本和用户：
SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> show user;USER is "JACK"
2.1.2、新建测试表：
SQL> create table t_logminer(id int) tablespace jack;Table created.SQL> insert into t_logminer select rownum from dual connect by rownum < 11;10 rows created.SQL> select * from t_logminer; ID---------- 1 2 3 4 5 6 7 8 9 1010 rows selected.SQL> commit;Commit complete.
2.1.3、查询当前的log文件：
SQL> select member from v$logfile where group# in (select group# from v$log where status = 'CURRENT');MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/yft/redo03.log
2.1.4、查询当前的SCN：
SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2781038
2.1.5、假设此时用户一条误操作并成功commit：
SQL> delete t_logminer where id = 1;1 row deleted.SQL> commit;Commit complete.
2.1.6、误操作之后的SCN：
SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2781164
2.1.7、用户意识到误操作，请求DBA协助恢复。
准备使用logminer尝试找到误操作的数据，并恢复。添加日志文件：
SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/yft/redo03.log',options => dbms_logmnr.new);PL/SQL procedure successfully completed.
2.1.8、开始logminer：
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog,startscn => 2781038,endscn => 2781164);PL/SQL procedure successfully completed.
2.1.9、logminer提取的日志信息会格式化后存放在视图v$logmnr中，此视图只对当前会话有效：
SQL> desc v$logmnr_contents; Name Null? Type ----------------------------------------- -------- ---------------------------- SCN NUMBER START_SCN NUMBER COMMIT_SCN NUMBER TIMESTAMP DATE START_TIMESTAMP DATE COMMIT_TIMESTAMP DATE XIDUSN NUMBER XIDSLT NUMBER XIDSQN NUMBER XID RAW(8) PXIDUSN NUMBER PXIDSLT NUMBER PXIDSQN NUMBER PXID RAW(8) TX_NAME VARCHAR2(256) OPERATION VARCHAR2(32) OPERATION_CODE NUMBER ROLLBACK NUMBER SEG_OWNER VARCHAR2(32) SEG_NAME VARCHAR2(256) TABLE_NAME VARCHAR2(32) SEG_TYPE NUMBER SEG_TYPE_NAME VARCHAR2(32) TABLE_SPACE VARCHAR2(32) ROW_ID VARCHAR2(18) USERNAME VARCHAR2(30) OS_USERNAME VARCHAR2(4000) MACHINE_NAME VARCHAR2(4000) AUDIT_SESSIONID NUMBER SESSION# NUMBER SERIAL# NUMBER SESSION_INFO VARCHAR2(4000) THREAD# NUMBER SEQUENCE# NUMBER RBASQN NUMBER RBABLK NUMBER RBABYTE NUMBER UBAFIL NUMBER UBABLK NUMBER UBAREC NUMBER UBASQN NUMBER ABS_FILE# NUMBER REL_FILE# NUMBER DATA_BLK# NUMBER DATA_OBJ# NUMBER DATA_OBJV# NUMBER DATA_OBJD# NUMBER SQL_REDO VARCHAR2(4000) SQL_UNDO VARCHAR2(4000) RS_ID VARCHAR2(32) SSN NUMBER CSF NUMBER INFO VARCHAR2(32) STATUS NUMBER REDO_VALUE NUMBER UNDO_VALUE NUMBER SAFE_RESUME_SCN NUMBER CSCN NUMBER OBJECT_ID RAW(16) EDITION_NAME VARCHAR2(30) CLIENT_ID VARCHAR2(64)
2.1.10、查询视图：
1 SQL> select count(*) from v$logmnr_contents; 2 3 COUNT(*) 4 ---------- 5 3366 6 SQL> select operation from v$logmnr_contents; 7 OPERATION 8 -------------------------------- 9 INSERT10 COMMIT11 START12 UPDATE13 COMMIT14 START15 UNSUPPORTED16 COMMIT17 UNSUPPORTED18 UNSUPPORTED19 UNSUPPORTED20 21 3366 rows selected.22 23 SQL> select sql_undo from v$logmnr_contents;24 SQL_UNDO25 --------------------------------------------------------------------------------26 delete from "SYS"."WRH$_SQL_SUMMARY" where "SNAP_ID" = '11' and "DBID" = '29636127 1937' and "INSTANCE_NUMBER" = '1' and "TOTAL_SQL" = '618' and "TOTAL_SQL_MEM" =28 '12578332' and "SINGLE_USE_SQL" = '340' and "SINGLE_USE_SQL_MEM" = '7121376' and29 ROWID = 'AAABhDAACAAABCcAAC';30 31 32 33 update "SYS"."WRM$_SNAPSHOT" set "FLUSH_ELAPSED" = NULL, "STATUS" = '1' where "F34 LUSH_ELAPSED" = TO_DSINTERVAL('+00000 00:00:06.8') and "STATUS" = '0' and ROWID35 = 'AAABlvAACAAABeUAAC';36 37 38 SQL_UNDO39 --------------------------------------------------------------------------------40 41 42 Unsupported43 44 SQL> select sql_redo from v$logmnr_contents;45 SQL_REDO46 --------------------------------------------------------------------------------47 '0','0','1','1','0','0','1','1','0','0','0','0','0','0','10997','10997','16194',48 '16194','0','0','0','0','0','0','0','0','0','0','0','0','0','0',NULL,NULL,'0','049 ','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0');50 51 insert into "SYS"."WRH$_SQL_SUMMARY"("SNAP_ID","DBID","INSTANCE_NUMBER","TOTAL_S52 QL","TOTAL_SQL_MEM","SINGLE_USE_SQL","SINGLE_USE_SQL_MEM") values ('11','296361153 937','1','618','12578332','340','7121376');54 55 commit;56 set transaction read write;57 update "SYS"."WRM$_SNAPSHOT" set "FLUSH_ELAPSED" = TO_DSINTERVAL('+00000 00:00:058 59 SQL_REDO60 --------------------------------------------------------------------------------61 6.8'), "STATUS" = '0' where "FLUSH_ELAPSED" IS NULL and "STATUS" = '1' and ROWID62 = 'AAABlvAACAAABeUAAC';63 64 commit;65 set transaction read write;66 Unsupported67 commit;68 69 70 71 72 3366 rows selected. 在上面的查询中发现啥也没有！
2.1.11、检查logging：SQL> select force_logging from v$database;FOR---YES
2.1.12、经过在网上搜索了好久，发现必须要启动supplemental log data!
SQL> shutdown immediate;ORA-01031: insufficient privilegesSQL> show user;USER is "JACK"SQL> conn /as sysdbaConnected.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 246902784 bytesFixed Size 1335780 bytesVariable Size 125832732 bytesDatabase Buffers 117440512 bytesRedo Buffers 2293760 bytesDatabase mounted.SQL> alter database add supplemental log data;Database altered.SQL> alter database open;Database altered.
2.2、上面的步骤重新走一遍：
SQL> conn jack/jackConnected.SQL> select * from t_logminer; ID---------- 2 3 4 5 6 7 8 9 109 rows selected.SQL> select member from v$logfile where group# in (select group# from v$log where status = 'CURRENT');MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/yft/redo03.logSQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2782607SQL> delete t_logminer where id=3;1 row deleted.SQL> commit;Commit complete.SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------ 2782616SQL> exec dbms_logmnr.add_logfile(logfilename => '/u01/app/oracle/oradata/yft/redo03.log',options => dbms_logmnr.new);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog,startscn => 2782607,endscn => 2782616);PL/SQL procedure successfully completed.SQL> set linesize 150;SQL> col sql_redo for a50;SQL> col sql_undo for a50;SQL> select operation,sql_redo,sql_undo from v$logmnr_contents where table_name = 'T_LOGMINER';OPERATION SQL_REDO SQL_UNDO-------------------------------- -------------------------------------------------- --------------------------------------------------DELETE delete from "JACK"."T_LOGMINER" where "ID" = '3' a insert into "JACK"."T_LOGMINER"("ID") values ('3') nd ROWID = 'AAAR7aAAFAAAAU3AAC'; ; 找到了执行的误操作，及相应的恢复数据的undo操作，执行即可恢复。