ORACLE LOGMNR 日誌挖掘

[oracle@yang ~]$ ls $ORACLE_HOME/rdbms/admin/dbmslm*

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd.sql

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmslm.sql

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbmslms.sql

[oracle@yang ~]$

SQL> conn scott/tiger

Connected.

SQL> create table t1 (id number);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> update emp set sal=sal+1 where deptno=10;

3 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> conn / as sysdba

Connected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIME

------------- ------------

         1          1         23   52428800          2 YES INACTIVE

       827587 16-MAY-14

         2          1         24   52428800          2 NO  CURRENT

       827708 16-MAY-14

         3          1         22   52428800          2 YES INACTIVE

       827534 16-MAY-14

SQL>

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+DATA/orcl/onlinelog/group_3.266.846638871

+DATA/orcl/onlinelog/group_3.267.846638885

+DATA/orcl/onlinelog/group_2.264.846638837

+DATA/orcl/onlinelog/group_2.265.846638853

+DATA/orcl/onlinelog/group_1.262.846638805

+DATA/orcl/onlinelog/group_1.263.846638823

6 rows selected.

SQL>

SQL> execute dbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_1.263.846638823',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_1.262.846638805',dbms_logmnr.addfile);

BEGIN dbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_1.262.846638805',dbms_logmnr.addfile); END;

*

ERROR at line 1:

ORA-01289: cannot add duplicate logfile

+DATA/orcl/onlinelog/group_1.262.846638805

ORA-06512: at "SYS.DBMS_LOGMNR", line 68

ORA-06512: at line 1

將能新增的新增

SQL>

SQL> execute dbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_2.265.846638853',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL>

SQL> execute dbms_logmnr.add_logfile('+DATA/orcl/onlinelog/group_3.267.846638885',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL>

進行日誌挖掘找到事務提交的

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL>

查詢表名為T1 所做的所有insert操作

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='T1' and operation='INSERT';

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

insert into "SCOTT"."T1"("ID") values ('1');       

delete from "SCOTT"."T1" where "ID" = '1' and ROWID = 'AAAM4sAAEAAAAGuAAA';  ---想要取消上面操作的語句

insert into "SCOTT"."T1"("ID") values ('2');

delete from "SCOTT"."T1" where "ID" = '2' and ROWID = 'AAAM4sAAEAAAAGuAAB';

SQL>

查詢EMP表

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='EMP';

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9

), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2),

 "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(I

NITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER

S" LOGGING NOCOMPRESS;

ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP_NO" PRIMARY KEY ("EMPNO") USING INDEX

PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GR

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ;

ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "

DEPT" ("DEPTNO") ENABLE NOVALIDATE;

Unsupported

Unsupported

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '2551' where "SAL" = '2550' and ROWID = 'AAAM4i

AAEAAAAAeAAG';

update "SCOTT"."EMP" set "SAL" = '2550' where "SAL" = '2551' and ROWID = 'AAAM4i

AAEAAAAAeAAG';

Unsupported

Unsupported

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '5601' where "SAL" = '5600' and ROWID = 'AAAM4i

AAEAAAAAeAAI';

update "SCOTT"."EMP" set "SAL" = '5600' where "SAL" = '5601' and ROWID = 'AAAM4i

AAEAAAAAeAAI';

Unsupported

Unsupported

update "SCOTT"."EMP" set "SAL" = '1401' where "SAL" = '1400' and ROWID = 'AAAM4i

SQL_REDO

--------------------------------------------------------------------------------

SQL_UNDO

--------------------------------------------------------------------------------

AAEAAAAAeAAN';

update "SCOTT"."EMP" set "SAL" = '1400' where "SAL" = '1401' and ROWID = 'AAAM4i

AAEAAAAAeAAN';

9 rows selected.

SQL>

通過v$logmnr_contents 可以顯示操作時間XID等

SQL> desc v$LOGMNR_CONTENTS

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 SCN                                                NUMBER

 CSCN                                               NUMBER

 TIMESTAMP                                          DATE

 COMMIT_TIMESTAMP                                   DATE

 THREAD#                                            NUMBER

 LOG_ID                                             NUMBER

 XIDUSN                                             NUMBER

 XIDSLT                                             NUMBER

 XIDSQN                                             NUMBER

 PXIDUSN                                            NUMBER

 PXIDSLT                                            NUMBER

 PXIDSQN                                            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_OBJD#                                         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)

 SESSION#                                           NUMBER

 SERIAL#                                            NUMBER

 USERNAME                                           VARCHAR2(30)

 SESSION_INFO                                       VARCHAR2(4000)

 TX_NAME                                            VARCHAR2(256)

 ROLLBACK                                           NUMBER

 OPERATION                                          VARCHAR2(32)

 OPERATION_CODE                                     NUMBER

 SQL_REDO                                           VARCHAR2(4000)

 SQL_UNDO                                           VARCHAR2(4000)

 RS_ID                                              VARCHAR2(32)

 SEQUENCE#                                          NUMBER

 SSN                                                NUMBER

 CSF                                                NUMBER

 INFO                                               VARCHAR2(32)

 STATUS                                             NUMBER

 REDO_VALUE                                         NUMBER

 UNDO_VALUE                                         NUMBER

 SQL_COLUMN_TYPE                                    VARCHAR2(30)

 SQL_COLUMN_NAME                                    VARCHAR2(30)

 REDO_LENGTH                                        NUMBER

 REDO_OFFSET                                        NUMBER

 UNDO_LENGTH                                        NUMBER

 UNDO_OFFSET                                        NUMBER

 DATA_OBJV#                                         NUMBER

 SAFE_RESUME_SCN                                    NUMBER

 XID                                                RAW(8)

 PXID                                               RAW(8)

 AUDIT_SESSIONID                                    NUMBER

SQL>

將檢視的內容儲存到表中 並結束日誌挖掘

SQL> create table tlog as select * from v$logmnr_contents;

table created。

QL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

SQL> select sql_redo,sql_undo from  v$logmnr_contents where table_name ='EMP';  --在此查詢v$logmnr_contents無效了

select sql_redo,sql_undo from  v$logmnr_contents where table_name ='EMP'

                               *

ERROR at line 1:

ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from

v$logmnr_contents

SQL>

SQL> conn scott/tiger

Connected.

SQL> update emp set sal=sal+500 where deptno=10;

3 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> conn / as sysdba

Connected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIME

------------- ------------

         1          1         23   52428800          2 YES INACTIVE

       827587 16-MAY-14

         2          1         24   52428800          2 NO  CURRENT

       827708 16-MAY-14

         3          1         22   52428800          2 YES INACTIVE

       827534 16-MAY-14

SQL> alter system switch logfile;

System altered.

SQL>

SQL> conn scott/tiger

Connected.

SQL> update emp set sal=sal+100 where deptno=20;

5 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> conn / as sysdba

SQL>  alter system switch logfile;

System altered.

SQL>

SQL> conn scott/tiger

Connected.

SQL> update emp set sal=sal+100 where deptno=30;

6 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> conn / as sysdba

SQL>  alter system switch logfile;

System altered.

SQL>

SQL> select name from v$archived_log;

NAME

--------------------------------------------------------------------------------

+DATA/orcl/1_3_846638803.dbf

+DATA/orcl/1_4_846638803.dbf

+DATA/orcl/1_5_846638803.dbf

+DATA/orcl/1_6_846638803.dbf

+DATA/orcl/1_7_846638803.dbf

+DATA/orcl/1_8_846638803.dbf

+DATA/orcl/1_9_846638803.dbf

+DATA/orcl/1_10_846638803.dbf

+DATA/orcl/1_11_846638803.dbf

+DATA/orcl/1_12_846638803.dbf

+DATA/orcl/1_13_846638803.dbf

NAME

--------------------------------------------------------------------------------

+DATA/orcl/1_14_846638803.dbf

+DATA/orcl/1_15_846638803.dbf

+DATA/orcl/1_16_846638803.dbf

+DATA/orcl/1_17_846638803.dbf

+DATA/orcl/1_18_846638803.dbf

+DATA/orcl/1_19_846638803.dbf

+DATA/orcl/1_20_846638803.dbf

+DATA/orcl/1_21_846638803.dbf

+DATA/orcl/1_22_846638803.dbf

+DATA/orcl/1_23_846638803.dbf

+DATA/orcl/1_24_846638803.dbf

NAME

--------------------------------------------------------------------------------

+DATA/orcl/1_25_846638803.dbf

+DATA/orcl/1_26_846638803.dbf   ---直接從最後找就可以了

24 rows selected.

SQL>

確定unl_file_dir引數已設定路徑

SQL> conn / as sysdba

Connected.

SQL> show parameter utl_file

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string

SQL> alter system set utl_file_dir='/home/oracle/' scope=spfile;

System altered.

SQL>

SQL> shutdown immediate;

SQL>startup

SQL> show parameter utl_file

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                         string      /home/oracle/

SQL>

開始建立分析日誌目錄

SQL> exec dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file)

PL/SQL procedure successfully completed.

SQL>

新增歸檔日誌

SQL> exec dbms_logmnr.add_logfile('+DATA/orcl/1_26_846638803.dbf',dbms_logmnr.new);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.add_logfile('+DATA/orcl/1_25_846638803.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('+DATA/orcl/1_24_846638803.dbf',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

開啟日誌挖掘

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');

PL/SQL procedure successfully completed.

SQL>

查詢

SQL> select sql_redo from v$logmnr_contents where table_name='EMP';

SQL_REDO

--------------------------------------------------------------------------------

CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9

), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2),

 "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(I

NITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USER

S" LOGGING NOCOMPRESS;

ALTER TABLE "EMP" ADD  CONSTRAINT "PK_EMP_NO" PRIMARY KEY ("EMPNO") USING INDEX

PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GR

OUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ;

ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "

SQL_REDO

--------------------------------------------------------------------------------

DEPT" ("DEPTNO") ENABLE NOVALIDATE;

Unsupported

update "SCOTT"."EMP" set "SAL" = '2551' where "SAL" = '2550' and ROWID = 'AAAM4i

AAEAAAAAeAAG';

Unsupported

update "SCOTT"."EMP" set "SAL" = '5601' where "SAL" = '5600' and ROWID = 'AAAM4i

AAEAAAAAeAAI';

Unsupported

SQL_REDO

--------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '1401' where "SAL" = '1400' and ROWID = 'AAAM4i

AAEAAAAAeAAN';

Unsupported

update "SCOTT"."EMP" set "SAL" = '3051' where "SAL" = '2551' and ROWID = 'AAAM4i

AAEAAAAAeAAG';

Unsupported

update "SCOTT"."EMP" set "SAL" = '6101' where "SAL" = '5601' and ROWID = 'AAAM4i

AAEAAAAAeAAI';

SQL_REDO

--------------------------------------------------------------------------------

Unsupported

update "SCOTT"."EMP" set "SAL" = '1901' where "SAL" = '1401' and ROWID = 'AAAM4i

AAEAAAAAeAAN';

Unsupported

update "SCOTT"."EMP" set "SAL" = '1001' where "SAL" = '901' and ROWID = 'AAAM4iA

AEAAAAAeAAA';

Unsupported

update "SCOTT"."EMP" set "SAL" = '4176' where "SAL" = '4076' and ROWID = 'AAAM4i

AAEAAAAAeAAD';

SQL_REDO

--------------------------------------------------------------------------------

Unsupported

update "SCOTT"."EMP" set "SAL" = '3200' where "SAL" = '3100' and ROWID = 'AAAM4i

AAEAAAAAeAAH';

Unsupported

update "SCOTT"."EMP" set "SAL" = '1300' where "SAL" = '1200' and ROWID = 'AAAM4i

AAEAAAAAeAAK';

Unsupported

update "SCOTT"."EMP" set "SAL" = '3200' where "SAL" = '3100' and ROWID = 'AAAM4i

SQL_REDO

--------------------------------------------------------------------------------

AAEAAAAAeAAM';

Unsupported

update "SCOTT"."EMP" set "SAL" = '1800' where "SAL" = '1700' and ROWID = 'AAAM4i

AAEAAAAAeAAB';

Unsupported

update "SCOTT"."EMP" set "SAL" = '1450' where "SAL" = '1350' and ROWID = 'AAAM4i

AAEAAAAAeAAC';

Unsupported

SQL_REDO

--------------------------------------------------------------------------------

update "SCOTT"."EMP" set "SAL" = '1450' where "SAL" = '1350' and ROWID = 'AAAM4i

AAEAAAAAeAAE';

Unsupported

update "SCOTT"."EMP" set "SAL" = '3050' where "SAL" = '2950' and ROWID = 'AAAM4i

AAEAAAAAeAAF';

Unsupported

update "SCOTT"."EMP" set "SAL" = '1701' where "SAL" = '1601' and ROWID = 'AAAM4i

AAEAAAAAeAAJ';

SQL_REDO

--------------------------------------------------------------------------------

Unsupported

update "SCOTT"."EMP" set "SAL" = '1151' where "SAL" = '1051' and ROWID = 'AAAM4i

AAEAAAAAeAAL';

37 rows selected.

SQL>

結束

SQL> exec dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

SQL>

取消utl_file_dir引數

SQL> alter system reset utl_file_dir scope=spfile sid='*';

System altered.

SQL>