LogMiner用于日志的分析

来源：互联网发布：java内部类例子ppt 编辑：程序博客网时间：2024/05/21 22:34

LogMiner可以用于日志的分析，跟踪数据库变化，回退数据库变化，对部分误操作数据进行恢复，误操作人员账号定位查看，结合工具完成基于日志事物级别增量数据传输。

1、LogMiner的安装；

首先确认一下咱们数据库上是否有LogMiner安装包，使用DBA用户登录数据库查看是否有dbms_logmnr、dbms_logmnr_d包，如果没有就需要咱们执行下面的脚本安装（必须以DBA用户执行安装）。

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Package created.

Synonym created.

执行完毕就完成了logminer的安装，第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

打开数据库的扩充日志：supplemental logging（扩充日志）在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用redo log进行一些其他应用时是不够的，例如在 redo log中使用rowid唯一标识一行而不是通过Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些dml时就可能会有问题，因为不同的数据库其rowid代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入redo log，这就是supplemental logging。

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

fafa

查看是否开启扩充日志：

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MI

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

YES

这里开启了

开启方法 alter database add supplemental log data;

创建日志分析用的用户并授权：

SQL> CREATE USER logminer IDENTIFIED BY logminer ;

User created.

SQL> GRANT CONNECT, RESOURCE,DBA TO logminer;

Grant succeeded.

3、LogMiner的配置；

使用oracle用户建立一个logminer使用的目录

[oracle@n3 oradata]$ mkdir logminer

创建logminer字典文件路径：

SQL> CREATE DIRECTORY utlfile AS '/opt/app/oracle/oradata/logminer';

Directory created.

SQL> alter system set utl_file_dir='/opt/app/oracle/oradata/logminer' scope=spfile;

System altered.

SQL> show parameter utl_file_dir

NAME TYPE

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

VALUE

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

utl_file_dir string

注意这里无法修改内存设置，需要重启数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4.2758E+10 bytes

Fixed Size 2262656 bytes

Variable Size 9529461120 bytes

Database Buffers 3.3152E+10 bytes

Redo Buffers 74420224 bytes

Database mounted.

Database opened.

SQL> show parameter utl_file_dir

NAME TYPE

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

VALUE

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

utl_file_dir string

/opt/app/oracle/oradata/logmin

可以看到logminer字典路径已经设置成功

4、开始分析日志文件

一、分析在线日志文件

准备测试数据

SQL> conn logminer/logminer

Connected.

SQL> CREATE TABLE test(id varchar2(100));

Table created.

SQL> INSERT INTO test (id) values ('000001');

INSERT INTO test (id) values ('000011');

1 row created.

SQL>

1 row created.

SQL> commit;

Commit complete.

创建数据字典文件

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/opt/app/oracle/oradata/logminer');

PL/SQL procedure successfully completed.

查看数据库当前联机日志文件

SQL> SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;

GROUP# SEQUENCE# STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1 27 INACTIVE

5595782 26-JUN-16

2 28 INACTIVE

5945244 02-JUL-16

3 29 CURRENT

6273005 09-JUL-16

发现只有redo03是当前状态。

将需要解析的在线日志文件加入

SQL> exec dbms_logmnr.add_logfile('/opt/app/oracle/oradata/fafa/redo03.log', dbms_logmnr.new);

PL/SQL procedure successfully completed.

启动logminer进行日志分析

SQL> exec dbms_logmnr.start_logmnr( dictfilename=>'/opt/app/oracle/oradata/logminer/dictionary.ora');

PL/SQL procedure successfully completed.

查看日志分析结果

SQL> col sql_redo for a50

SQL> col sql_undo for a50

SQL> col seg_owner for a10

SQL> set linesize 5000 pagesize 5000

SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='TEST' AND seg_owner='LOGMINER';

SQL_REDO SQL_UNDO SEG_OWNER

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

CREATE TABLE test(id varchar2(100)); LOGMINER

insert into "LOGMINER"."TEST"("ID") values ('00000 delete from "LOGMINER"."TEST" where "ID" = '000001 LOGMINER

1'); ' and ROWID = 'AAAX6ZAAEAAADNmAAA';

insert into "LOGMINER"."TEST"("ID") values ('00001 delete from "LOGMINER"."TEST" where "ID" = '000011 LOGMINER

1'); ' and ROWID = 'AAAX6ZAAEAAADNmAAB';

可以看到update转换成了redo的insert和undo的delete操作了

二、分析归档日志

测试数据准备

SQL> CREATE TABLE test2(id NUMBER(4) CONSTRAINT PK_test PRIMARY KEY,

NAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)); 2 3 4 5 6 7 8

Table created.

SQL> INSERT INTO test2 VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO test2 VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO test2 VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO test2 VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL> commit;

Commit complete.

切换日志，将当前日志归档后进行分析

SQL> alter system switch logfile;

System altered.

查看归档日志文件

SQL> select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NAME

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

29 6273005 6438505 /opt/app/oracle/archivelog/arch_1_29_907693874.arc

28 5945244 6273005 /opt/app/oracle/archivelog/arch_1_28_907693874.arc

27 5595782 5945244 /opt/app/oracle/archivelog/arch_1_27_907693874.arc

26 5282647 5595782 /opt/app/oracle/archivelog/arch_1_26_907693874.arc

25 5014061 5282647 /opt/app/oracle/archivelog/arch_1_25_907693874.arc

24 4710938 5014061 /opt/app/oracle/archivelog/arch_1_24_907693874.arc

23 4420861 4710938 /opt/app/oracle/archivelog/arch_1_23_907693874.arc

22 4117700 4420861 /opt/app/oracle/archivelog/arch_1_22_907693874.arc

21 3872985 4117700 /opt/app/oracle/archivelog/arch_1_21_907693874.arc

20 3577102 3872985 /opt/app/oracle/archivelog/arch_1_20_907693874.arc

19 3265865 3577102 /opt/app/oracle/archivelog/arch_1_19_907693874.arc

18 2981609 3265865 /opt/app/oracle/archivelog/arch_1_18_907693874.arc

17 2947176 2981609 /opt/app/oracle/archivelog/arch_1_17_907693874.arc

16 2787918 2947176 /opt/app/oracle/archivelog/arch_1_16_907693874.arc

15 2782411 2787918 /opt/app/oracle/archivelog/1_15_907693874.dbf

14 2781867 2782411 /opt/app/oracle/archivelog/1_14_907693874.dbf

13 2519736 2781867 /opt/app/oracle/archivelog/1_13_907693874.dbf

12 2196321 2519736 /opt/app/oracle/archivelog/1_12_907693874.dbf

11 1858788 2196321 /opt/app/oracle/archivelog/1_11_907693874.dbf

10 1461679 1858788 /opt/app/oracle/archivelog/1_10_907693874.dbf

9 985247 1461679 /opt/app/oracle/archivelog/1_9_907693874.dbf

8 968512 985247 /opt/app/oracle/archivelog/1_8_907693874.dbf

22 rows selected.

注意这里的.dbf和.arc可以随便改只是一个后缀名

创建数据字典文件

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'fafa.ora', dictionary_location =>'/opt/app/oracle/oradata/logminer');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/opt/app/oracle/archivelog/1_15_907693874.dbf', dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/opt/app/oracle/oradata/logminer/fafa.ora');

PL/SQL procedure successfully completed.

查看结果

SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='TEST2' AND seg_owner='LOGMINER';

no rows selected

这里由于选取错了archive log日志导致查询空值，这里不在熬述

可以看出logminer可以从redo和archive里面得到日志，并且可以再次基础上进行还原

0 0