一、添加database追加日志
SQL> alter database add supplemental log data;
 
Database altered

SQL> alter system switch logfile;
 
System altered

二、查询日志情况
1.select a.group#,a.thread#,a.sequence#,a.bytes,a.members,b.member,b.type,a.archived,a.status
  from v$log a,v$logfile b where a.group#=b.group#;
2.select * from v$log_history order by sequence# desc;
3.select * from v$achived_log;   --查询归档
SQL> select * from v$archived_log;
 
     RECID      STAMP NAME                                                                                DEST_ID    THREAD#  SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME       BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED   DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
         1  779452462 E:\ORACLE11G\APP\ADMIN\ARCHIVELOG\ARC0000000086_0778602906.0001                           1          1         86                 1 2012/3/22 14:3    778602906       1403709 2012/4/1 8:      1408201 2012/4/1 10      13193        512 FGRD    FGRD      NO           YES      NO        NO      A      2012/4/1 10:34: NO               NO             NO                     0                1  2647344282 NO                    NO         NO                   NO
 
三、增删数据测试
SQL> create table t1(id number ,xm varchar2(10));
 
Table created

SQL> insert into t1 values (1,'金');
 
1 row inserted
 
SQL> insert into t1 values (2,'木');
 
1 row inserted
 
SQL> insert into t1 values (3,'水');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> delete from t1 where id=3;
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> update  t1 set id=10 where id=2;
 
1 row updated
 
SQL> select * from t1;
 
        ID XM
---------- ----------
         1 金
        10 木
 
SQL> alter system switch logfile;
 
System altered

SQL> alter system switch logfile;
 
System altered  
----最好多切换几次日志。

SQL> select * from v$archived_log;
 
     RECID      STAMP NAME                                                                                DEST_ID    THREAD#  SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME RESETLOGS_ID FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME       BLOCKS BLOCK_SIZE CREATOR REGISTRAR STANDBY_DEST ARCHIVED APPLIED   DELETED STATUS COMPLETION_TIME DICTIONARY_BEGIN DICTIONARY_END END_OF_REDO BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_RECOVERY_DEST_FILE COMPRESSED FAL END_OF_REDO_TYPE BACKED_BY_VSS
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------------- -------------- ------------ ------------- ----------- ------------ ----------- ---------- ---------- ------- --------- ------------ -------- --------- ------- ------ --------------- ---------------- -------------- ----------- ------------ ---------------- ----------- --------------------- ---------- --- ---------------- -------------
         1  779452462 E:\ORACLE11G\APP\ADMIN\ARCHIVELOG\ARC0000000086_0778602906.0001                           1          1         86                 1 2012/3/22 14:3    778602906       1403709 2012/4/1 8:      1408201 2012/4/1 10      13193        512 FGRD    FGRD      NO           YES      NO        NO      A      2012/4/1 10:34: NO               NO             NO                     0                1  2647344282 NO                    NO         NO                   NO
         2  779453894 E:\ORACLE11G\APP\ADMIN\ARCHIVELOG\ARC0000000087_0778602906.0001                           1          1         87                 1 2012/3/22 14:3    778602906       1408201 2012/4/1 10      1409075 2012/4/1 10        612        512 ARCH    ARCH      NO           YES      NO        NO      A      2012/4/1 10:58: NO               NO             NO                     0                1  2647344282 NO                    NO         NO                   NO
 
四、添加日志分析
SQL> exec dbms_logmnr.add_logfile(logfilename=>'E:\ORACLE11G\APP\ADMIN\ARCHIVELOG\ARC0000000087_0778602906.0001',options=>dbms_logmnr.new);
 
PL/SQL procedure successfully completed

五、执行logmnr分析
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
 
PL/SQL procedure successfully completed

六、查询分析结果
SQL> alter session set NLS_DATE_FORMAT='YYYYMMDD HH24:MI;SS';
 
Session altered

----此处执行了DDL语句，update  t1 set id=10 where id=2;已经自动提交了。
 
SQL> select scn,timestamp,operation,username,table_name,machine_name,sql_redo,sql_undo from v$logmnr_contents
  2  where table_name='T1';
 
       SCN TIMESTAMP   OPERATION                        USERNAME                       TABLE_NAME                       MACHINE_NAME                                                                     SQL_REDO                                                                         SQL_UNDO
---------- ----------- -------------------------------- ------------------------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
   1408918 2012/4/1 10 DDL                              ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                                                                                                              
                                                                                                                                                                                                         create table t1(id number ,xm varchar2(10))                                     
                                                                                                                                                                                                         ;                                                                               
 
   1408973 2012/4/1 10 INSERT                           ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                              insert into "ADMIN"."T1"("ID","XM") values ('1','金');                           delete from "ADMIN"."T1" where "ID" = '1' and "XM" = '金' and ROWID = 'AAASCwAAFA
   1408976 2012/4/1 10 INSERT                           ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                              insert into "ADMIN"."T1"("ID","XM") values ('2','木');                           delete from "ADMIN"."T1" where "ID" = '2' and "XM" = '木' and ROWID = 'AAASCwAAFA
   1408979 2012/4/1 10 INSERT                           ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                              insert into "ADMIN"."T1"("ID","XM") values ('3','水');                           delete from "ADMIN"."T1" where "ID" = '3' and "XM" = '水' and ROWID = 'AAASCwAAFA
   1409029 2012/4/1 10 DELETE                           ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                              delete from "ADMIN"."T1" where "ID" = '3' and "XM" = '水' and ROWID = 'AAASCwAAFA insert into "ADMIN"."T1"("ID","XM") values ('3','水');
   1409048 2012/4/1 10 UPDATE                           ADMIN                          T1                               WORKGROUP\WINDOWS-7                                                              update "ADMIN"."T1" set "ID" = '10' where "ID" = '2' and ROWID = 'AAASCwAAFAAAAC update "ADMIN"."T1" set "ID" = '2' where "ID" = '10' and ROWID = 'AAASCwAAFAAAAC
 
6 rows selected

 七、结束日志分析

SQL> exec dbms_logmnr.end_logmnr;
 
PL/SQL procedure successfully completed