[oracle@dg ~(16:50:39)]$ export ORACLE_SID=wailon
[oracle@dg ~(17:24:57)]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 28 17:25:01 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 系统是否处于归档日志模式
17:25:20 SCOTT@wailon> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

-- 需要使用LOGMINER必须启用SUPPLEMENTAL LOG（追加日志模式）
17:28:46 SCOTT@wailon> alter database add supplemental log data;

Database altered.

17:29:50 SCOTT@wailon> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

-- 建立LOGMNR_ADMIN角色，赋予相关授权
17:29:52 SCOTT@wailon> create role logmnr_admin;

Role created.

17:33:43 SCOTT@wailon> grant create session to logmnr_admin;

Grant succeeded.

17:35:18 SYS@wailon> grant select on v_$logmnr_contents to logmnr_admin;

Grant succeeded.

17:35:30 SYS@wailon> grant select on v_$logmnr_dictionary to logmnr_admin;

Grant succeeded.

17:35:46 SYS@wailon> grant select on v_$logmnr_parameters to logmnr_admin;

Grant succeeded.

17:36:00 SYS@wailon> grant select on v_$logmnr_logs to logmnr_admin;

Grant succeeded.

17:36:20 SYS@wailon> grant select on v_$archived_log to logmnr_admin;

Grant succeeded.

17:36:24 SYS@wailon> grant execute_catalog_role,
17:36:59   2  select any dictionary,
17:37:07   3  select any transaction,
17:37:18   4  select any table,
17:37:22   5  create tablespace,
17:37:37   6  drop tablespace to logmnr_admin;

Grant succeeded.

-- 创建用户并授权
17:37:47 SYS@wailon> create user logmnr identified by logmnr;

User created.

17:37:53 SYS@wailon> alter user logmnr quota 10m on users;

User altered.

17:38:08 SYS@wailon> grant logmnr_admin to logmnr;

Grant succeeded.

17:38:31 SYS@wailon> grant execute on dbms_flashback to logmnr;

Grant succeeded.

17:38:48 SYS@wailon> grant create any table to logmnr;

Grant succeeded.

17:39:21 SYS@wailon> grant flashback any table to logmnr;

Grant succeeded.

17:39:29 SYS@wailon> alter system switch logfile;

System altered.

-- 连接用户并创建测试表
17:39:50 SYS@wailon> conn logmnr/logmnr
Connected.
17:39:58 LOGMNR@wailon> create table tab_logmnr (id int,name varchar2(30),salary number(8,2));

Table created.

17:42:48 LOGMNR@wailon> insert into tab_logmnr values (1,'wailon',1000);

1 row created.

17:42:50 LOGMNR@wailon> insert into tab_logmnr values (2,'yojan',1200);

1 row created.

17:43:00 LOGMNR@wailon> insert into tab_logmnr values (3,'peter',1100);

1 row created.

17:43:11 LOGMNR@wailon> commit;

Commit complete.

17:43:15 LOGMNR@wailon> select * from tab_logmnr;

        ID NAME                                         SALARY
---------- ---------------------------------------- ----------
         1 wailon                                         1000
         2 yojan                                          1200
         3 peter                                          1100

17:43:21 LOGMNR@wailon> -- transaction 1
17:44:11 LOGMNR@wailon> update tab_logmnr set salary=1500 where id=1;

1 row updated.

17:44:30 LOGMNR@wailon> update tab_logmnr set salary=1500 where id=2;

1 row updated.

17:44:37 LOGMNR@wailon> commit;

Commit complete.

17:44:39 LOGMNR@wailon> -- transaction 2
17:44:46 LOGMNR@wailon> delete from tab_logmnr where id=1;

1 row deleted.

17:44:59 LOGMNR@wailon> commit;

Commit complete.

17:45:00 LOGMNR@wailon> select * from tab_logmnr;

        ID NAME                                         SALARY
---------- ---------------------------------------- ----------
         2 yojan                                          1500
         3 peter                                          1100

17:45:14 LOGMNR@wailon> -- 需要恢复id=1的记录，将id=2的SALARY值恢复为原始值

-- 查看当前日志
17:46:10 LOGMNR@wailon> select a.group#,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;

    GROUP# STATUS           MEMBER
---------- ---------------- --------------------------------------------------
         1 CURRENT          /u01/app/oracle/oradata/wailon/redo01.log
         3 INACTIVE         /u01/app/oracle/oradata/wailon/redo03.log
         2 INACTIVE         /u01/app/oracle/oradata/wailon/redo02.log

-- 开始LOGMNR操作，添加日志文件
17:47:18 LOGMNR@wailon> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/wailon/redo01.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

-- 开始LOGMNR
17:48:05 LOGMNR@wailon> exec dbms_logmnr.start_logmnr;

PL/SQL procedure successfully completed.

-- 查看LOGMNR结果
17:49:37 LOGMNR@wailon> select sql_redo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';

SQL_REDO
------------------------------------------------------------------------------------------------------------------------
create table tab_logmnr (id int,name varchar2(30),salary number(8,2));

17:49:46 LOGMNR@wailon> select sql_undo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';

SQL_UNDO
------------------------------------------------------------------------------------------------------------------------

17:50:09 LOGMNR@wailon> -- 未找到任何结果，换一下options

17:50:31 LOGMNR@wailon> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

-- 重做SQL，恢复被删除的记录
17:51:22 LOGMNR@wailon> select sql_redo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR';

SQL_REDO
------------------------------------------------------------------------------------------------------------------------
create table tab_logmnr (id int,name varchar2(30),salary number(8,2));
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('1','wailon','1000');
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('2','yojan','1200');
insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('3','peter','1100');
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000' and ROWID = 'AAASwaAAEAAAADFAAA';
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1200' and ROWID = 'AAASwaAAEAAAADFAAB';
delete from "LOGMNR"."TAB_LOGMNR" where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1500' and ROWID = 'AAASwaAAEAAAADFAAA';

7 rows selected.

17:52:08 LOGMNR@wailon> insert into "LOGMNR"."TAB_LOGMNR"("ID","NAME","SALARY") values ('1','wailon','1000');

1 row created.

17:52:27 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000' and ROWID = 'AAASwaAAEAAAADFAAA';

0 rows updated.

17:52:27 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1500' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1000';

1 rows updated.

17:52:39 LOGMNR@wailon> select * from tab_logmnr;

        ID NAME                                         SALARY
---------- ---------------------------------------- ----------
         2 yojan                                          1500
         3 peter                                          1100
         1 wailon                                         1000

-- 撤销SQL，恢复被修改的记录
17:51:28 LOGMNR@wailon> select sql_undo from v$logmnr_contents where seg_owner='LOGMNR' and seg_name='TAB_LOGMNR' and sql_undo like lower(%update%);

SQL_UNDO
------------------------------------------------------------------------------------------------------------------------
update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1000' where "ID" = '1' and "NAME" = 'wailon' and "SALARY" = '1500' and ROWI
D = 'AAASwaAAEAAAADFAAA';

update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1200' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1500' and ROWID
 = 'AAASwaAAEAAAADFAAB';

2 rows selected.

17:53:48 LOGMNR@wailon> update "LOGMNR"."TAB_LOGMNR" set "SALARY" = '1200' where "ID" = '2' and "NAME" = 'yojan' and "SALARY" = '1500';

1 row updated.

17:54:34 LOGMNR@wailon> select * from tab_logmnr;

        ID NAME                                         SALARY
---------- ---------------------------------------- ----------
         2 yojan                                          1200
         3 peter                                          1100
         1 wailon                                         1500

17:54:45 LOGMNR@wailon> commit;

Commit complete.

 
以上只是演示LogMiner的简单使用，实际环境中如果有这种恢复需要，10g以上可以使用flashback技术更加方便。