LogMiner分析日志文件

分手后的思念是犯贱 2022-06-10 06:56 255阅读 0赞

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表明没有打开

执行下面语句打开

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>