LogMiner分析日志文件

LogMiner

1.        把数据库改到归档模式

Alter database archive log

2.        安装LogMiner工具要运行以下脚本

Dbmslm.sql创建dbms_logmnr程序包

SQL> @?/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL>

Dbmslmd.sql创建数据字典

SQL> @?/rdbms/admin/dbmslmd.sql

Package created.

Synonym created.

SQL>

3.        打开数据库的追加日志，如果选项不打开，日志中只有ddl语句可以被分析无法查出dml操作的数据

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG

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

NO

为NO表明没有打开

执行下面语句打开

SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG

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

YES

4.        新建测试表插入修改数据并归档日志

SQL> create table logmin(id number,name varchar2(20));

Table created.

SQL> insert into logmin values(1000,'jack');

1 row created.

SQL> insert into logmin values(1001,'wangwei');

1 row created.

SQL> commit;

Commit complete.

SQL> update logmin set  name='tom' where id=1001;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

5.        将生成的日志文件添加到logminer列表中

SQL>executedbms_logmnr.add_logfile('/opt/oracle/archive/1_85_782895629.dbf',dbms_logmnr.new);

PL/SQL procedure successfully completed.

6. 进行分析日志

SQL>execute dbms_logmnr.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

7. 查询相关的字典表

SQL> select LOW_TIME,HIGH_TIME,LOW_SCN,NEXT_SCN from v$logmnr_logs

  2  ;

LOW_TIME     HIGH_TIME       LOW_SCN   NEXT_SCN

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

28-JUN-12    28-JUN-12       2818092    2820184

SQL> select username,sql_redo,sql_undo from v$logmnr_contents where operation='INSERT' and sql_redo like '%LOGMIN%';

USERNAME

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

SQL_REDO

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

SQL_UNDO

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

SYS

insert into "SYS"."LOGMIN"("ID","NAME") values ('1000','jack');

delete from "SYS"."LOGMIN" where "ID" = '1000' and "NAME" = 'jack' and ROWID = 'AAATeAAABAAAU/hAAA';

USERNAME

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

SQL_REDO

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

SQL_UNDO

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

SYS

insert into "SYS"."LOGMIN"("ID","NAME") values ('1001','wangwei');

delete from "SYS"."LOGMIN" where "ID" = '1001' and "NAME" = 'wangwei' and ROWID

= 'AAATeAAABAAAU/hAAB';

注意：

1.使用logminer查询表v$logmnr_contents必须在同一个会话中进行，因为分析的信息存储在这个session 的PGA中，在别的session 里面是查不到的。视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析

2.还有中可能会报ORA-01306错误是因为删除了一个或者多个redolog，这时你需要重新start.logmnr() ，否则也会报这个错误。

3.要在别的session中看到分析的结果解决办法在你启动了logmnr session中create table xxxx as select * from v$logmnr_contents;

8. 结束LogMnr

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>