SQL> archive log list;   Database log mode           Archive Mode   Automatic archival           Enabled   Archive destination           /home/oracle/archive_log   Oldest online log sequence     27   Next log sequence to archive   29   Current log sequence           29      SQL> alter database add supplemental log data;      Database altered.

       SQL> conn scott/tiger       Connected.       SQL> select * from tab;              TNAME                   TABTYPE    CLUSTERID       ------------------------------ ------- ----------       DEPT                   TABLE       EMP                   TABLE       BONUS                   TABLE       SALGRADE               TABLE       TEST                   TABLE       EXT_TEST               TABLE       TEST2                   TABLE       EXT_TEST_SQLLOADER           TABLE              8 rows selected.              SQL> delete emp;                14 rows deleted.              SQL> commit;              Commit complete.

SQL> conn /as sysdbaConnected.SQL> desc dbms_logmnrPROCEDURE ADD_LOGFILE Argument Name            Type            In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME            VARCHAR2        IN OPTIONS            BINARY_INTEGER        IN     DEFAULTFUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER Argument Name            Type            In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO            NUMBER            IN     DEFAULT COLUMN_NAME            VARCHAR2        IN     DEFAULTPROCEDURE END_LOGMNRFUNCTION MINE_VALUE RETURNS VARCHAR2 Argument Name            Type            In/Out Default? ------------------------------ ----------------------- ------ -------- SQL_REDO_UNDO            NUMBER            IN     DEFAULT COLUMN_NAME            VARCHAR2        IN     DEFAULTPROCEDURE REMOVE_LOGFILE Argument Name            Type            In/Out Default? ------------------------------ ----------------------- ------ -------- LOGFILENAME            VARCHAR2        INPROCEDURE START_LOGMNR Argument Name            Type            In/Out Default? ------------------------------ ----------------------- ------ -------- STARTSCN            NUMBER            IN     DEFAULT ENDSCN             NUMBER            IN     DEFAULT STARTTIME            DATE            IN     DEFAULT ENDTIME            DATE            IN     DEFAULT DICTFILENAME            VARCHAR2        IN     DEFAULT OPTIONS            BINARY_INTEGER        IN     DEFAULT

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/ORCL/redo03.log/u01/app/oracle/oradata/ORCL/redo02.log/u01/app/oracle/oradata/ORCL/redo01.logSQL> exec dbms_logmnr.ADD_LOGFILE(LOGFILENAME=>‘/u01/app/oracle/oradata/ORCL/redo01.log‘,OPTIONS=>dbms_logmnr.new);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.ADD_LOGFILE(LOGFILENAME=>‘/u01/app/oracle/oradata/ORCL/redo02.log‘,OPTIONS=>dbms_logmnr.addfile);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.ADD_LOGFILE(LOGFILENAME=>‘/u01/app/oracle/oradata/ORCL/redo03.log‘,OPTIONS=>dbms_logmnr.addfile);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.start_logmnr(OPTIONS=>dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=‘EMP‘;USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7369‘ a                      nd "ENAME" = ‘SMITH‘ and "JOB" = ‘CLERK‘ and "MGR"                       = ‘7902‘ and "HIREDATE" = TO_DATE(‘1980-12-17 00:                      00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘800‘                       and "COMM" IS NULL and "DEPTNO" = ‘20‘ and ROWID                      = ‘AAAMfMAAEAAAAAgAAA‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7499‘ a                      nd "ENAME" = ‘ALLEN‘ and "JOB" = ‘SALESMAN‘ and "M                      GR" = ‘7698‘ and "HIREDATE" = TO_DATE(‘1981-02-20                      00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘1USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                      600‘ and "COMM" = ‘300‘ and "DEPTNO" = ‘30‘ and RO                      WID = ‘AAAMfMAAEAAAAAgAAB‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7521‘ a                      nd "ENAME" = ‘WARD‘ and "JOB" = ‘SALESMAN‘ and "MG                      R" = ‘7698‘ and "HIREDATE" = TO_DATE(‘1981-02-22 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘12                      50‘ and "COMM" = ‘500‘ and "DEPTNO" = ‘30‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAC‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7566‘ aUSERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                      nd "ENAME" = ‘JONES‘ and "JOB" = ‘MANAGER‘ and "MG                      R" = ‘7839‘ and "HIREDATE" = TO_DATE(‘1981-04-02 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘29                      75‘ and "COMM" IS NULL and "DEPTNO" = ‘20‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAD‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7654‘ a                      nd "ENAME" = ‘MARTIN‘ and "JOB" = ‘SALESMAN‘ and "                      MGR" = ‘7698‘ and "HIREDATE" = TO_DATE(‘1981-09-28                       00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘                      1250‘ and "COMM" = ‘1400‘ and "DEPTNO" = ‘30‘ andUSERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                      ROWID = ‘AAAMfMAAEAAAAAgAAE‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7698‘ a                      nd "ENAME" = ‘BLAKE‘ and "JOB" = ‘MANAGER‘ and "MG                      R" = ‘7839‘ and "HIREDATE" = TO_DATE(‘1981-05-01 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘28                      50‘ and "COMM" IS NULL and "DEPTNO" = ‘30‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAF‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7782‘ a                      nd "ENAME" = ‘CLARK‘ and "JOB" = ‘MANAGER‘ and "MGUSERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                      R" = ‘7839‘ and "HIREDATE" = TO_DATE(‘1981-06-09 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘24                      50‘ and "COMM" IS NULL and "DEPTNO" = ‘10‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAG‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7788‘ a                      nd "ENAME" = ‘SCOTT‘ and "JOB" = ‘ANALYST‘ and "MG                      R" = ‘7566‘ and "HIREDATE" = TO_DATE(‘1987-04-19 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘30                      00‘ and "COMM" IS NULL and "DEPTNO" = ‘20‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAH‘;USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7839‘ a                      nd "ENAME" = ‘KING‘ and "JOB" = ‘PRESIDENT‘ and "M                      GR" IS NULL and "HIREDATE" = TO_DATE(‘1981-11-17 0                      0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘50                      00‘ and "COMM" IS NULL and "DEPTNO" = ‘10‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAI‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7844‘ a                      nd "ENAME" = ‘TURNER‘ and "JOB" = ‘SALESMAN‘ and "                      MGR" = ‘7698‘ and "HIREDATE" = TO_DATE(‘1981-09-08USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                       00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘                      1500‘ and "COMM" = ‘0‘ and "DEPTNO" = ‘30‘ and ROW                      ID = ‘AAAMfMAAEAAAAAgAAJ‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7876‘ a                      nd "ENAME" = ‘ADAMS‘ and "JOB" = ‘CLERK‘ and "MGR"                       = ‘7788‘ and "HIREDATE" = TO_DATE(‘1987-05-23 00:                      00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘1100                      ‘ and "COMM" IS NULL and "DEPTNO" = ‘20‘ and ROWID                       = ‘AAAMfMAAEAAAAAgAAK‘;USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7900‘ a                      nd "ENAME" = ‘JAMES‘ and "JOB" = ‘CLERK‘ and "MGR"                       = ‘7698‘ and "HIREDATE" = TO_DATE(‘1981-12-03 00:                      00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘950‘                       and "COMM" IS NULL and "DEPTNO" = ‘30‘ and ROWID                      = ‘AAAMfMAAEAAAAAgAAL‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7902‘ a                      nd "ENAME" = ‘FORD‘ and "JOB" = ‘ANALYST‘ and "MGR                      " = ‘7566‘ and "HIREDATE" = TO_DATE(‘1981-12-03 00                      :00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘300USERNAME      SCN TIMESTAMP       SQL_REDO---------- ---------- ------------------- --------------------------------------------------                      0‘ and "COMM" IS NULL and "DEPTNO" = ‘20‘ and ROWI                      D = ‘AAAMfMAAEAAAAAgAAM‘;SCOTT           765514 2014-05-14 15:30:04 delete from "SCOTT"."EMP" where "EMPNO" = ‘7934‘ a                      nd "ENAME" = ‘MILLER‘ and "JOB" = ‘CLERK‘ and "MGR                      " = ‘7782‘ and "HIREDATE" = TO_DATE(‘1982-01-23 00                      :00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) and "SAL" = ‘130                      0‘ and "COMM" IS NULL and "DEPTNO" = ‘10‘ and ROWI                      D = ‘AAAMfMAAEAAAAAgAAN‘;

SQL> set pagesize 1000SQL> select sql_undo from v$logmnr_contents where seg_name=‘EMP‘;SQL_UNDO------------------------------------------------------------------------------------------------------------------------insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7369‘,‘SMITH‘,‘CLERK‘,‘7902‘,TO_DATE(‘1980-12-17 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘800‘,NULL,‘20‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7499‘,‘ALLEN‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘1981-02-20 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1600‘,‘300‘,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7521‘,‘WARD‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘1981-02-22 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1250‘,‘500‘,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7566‘,‘JONES‘,‘MANAGER‘,‘7839‘,TO_DATE(‘1981-04-02 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘2975‘,NULL,‘20‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7654‘,‘MARTIN‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘1981-09-28 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1250‘,‘1400‘,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7698‘,‘BLAKE‘,‘MANAGER‘,‘7839‘,TO_DATE(‘1981-05-01 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘2850‘,NULL,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7782‘,‘CLARK‘,‘MANAGER‘,‘7839‘,TO_DATE(‘1981-06-09 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘2450‘,NULL,‘10‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7788‘,‘SCOTT‘,‘ANALYST‘,‘7566‘,TO_DATE(‘1987-04-19 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘3000‘,NULL,‘20‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7839‘,‘KING‘,‘PRESIDENT‘,NULL,TO_DATE(‘1981-11-17 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘5000‘,NULL,‘10‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7844‘,‘TURNER‘,‘SALESMAN‘,‘7698‘,TO_DATE(‘1981-09-08 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1500‘,‘0‘,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7876‘,‘ADAMS‘,‘CLERK‘,‘7788‘,TO_DATE(‘1987-05-23 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1100‘,NULL,‘20‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7900‘,‘JAMES‘,‘CLERK‘,‘7698‘,TO_DATE(‘1981-12-03 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘950‘,NULL,‘30‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7902‘,‘FORD‘,‘ANALYST‘,‘7566‘,TO_DATE(‘1981-12-03 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘3000‘,NULL,‘20‘);insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values (‘7934‘,‘MILLER‘,‘CLERK‘,‘7782‘,TO_DATE(‘1982-01-23 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),‘1300‘,NULL,‘10‘);14 rows selected.

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



转：http://www.cnblogs.com/myrunning/p/4003509.html