ORACLE LOGMINER配置v2

# ORACLE LOGMINER配置

一、Logminer配置步骤：（as sys）

1. 开启归档日志（如已开启可忽略）

a.shutdown immediate;

b.startup mount;

c.alter database archivelog;

d.alter database open;

2.依次执行以下脚本：（as sys）

a.输入 SQL> @$ORACLE\_HOME/rdbms/admin/dbmslm.sql

b.输入 SQL> @$ORACLE\_HOME/rdbms/admin/dbmslmd.sql

c.输入 SQL> @$ORACLE\_HOME/rdbms/admin/dbmslms.sql #12C执行提示无此脚本可以不执行

3.Logminer功能开启字段日志补全功能（supplemental logging,表级/列级），能够补全数据库日志信息，保证传递信息的全面性。（该操作会增加ORACLE数据库的日志量）

a.输入 **ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;**

b.输入 **ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(ALL,PRIMARY KEY,UNIQUE) COLUMNS;**

c.输入 SELECT supplemental\_log\_data\_min min, supplemental\_log\_data\_pk pk, supplemental\_log\_data\_ui ui, supplemental\_log\_data\_fk fk, supplemental\_log\_data\_all allc FROM v$database;

d.当步骤c的查询返回以下内容时，表示已经配置成功

MIN PK UI FK ALL

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

YES YES YES NO YES

二、Logminer测试使用

1.查询归档日志：select name from V$ARCHIVED\_LOG;

2.用步骤1中查到的归档日志，进行logminer日志挖掘

a.archive log file:/tqls\_system/app/flash\_recovery\_area/CCSPRD/archivelog/2024\_05\_22/o1\_mf\_1\_52828\_m4tkqcd7\_.arc

b.增加分析日志文件，输入 execute sys.dbms\_logmnr.add\_logfile(logfilename=>'/tqls\_system/app/flash\_recovery\_area/CCSPRD/archivelog/2024\_05\_22/o1\_mf\_1\_52828\_m4tkqcd7\_.arc',options=>dbms\_logmnr.new);

c.开始logmnr日志挖掘，输入 execute sys.dbms\_logmnr.start\_logmnr(Options => DBMS\_LOGMNR.DICT\_FROM\_ONLINE\_CATALOG);

d.查看日志挖掘获得的日志信息，输入 select count(*) from v$logmnr\_contents;

![](/media/202405//1716948785.9679239.png)

e.当看到数字时，即可证明logmnr已经成功部署并且可以正常使用了

三、角色授权

1.创建表空间：CREATE TABLESPACE logminer\_tbs DATAFILE '/tqls\_system/app/oracle/oradata/ccsprd/logminer\_tbs01.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

2.创建角色：CREATE USER logminer IDENTIFIED BY logminer2024# DEFAULT TABLESPACE LOGMINER\_TBS QUOTA UNLIMITED ON LOGMINER\_TBS;

3.授权

grant resource to logminer;

GRANT CREATE SESSION TO logminer;

GRANT SELECT ON V\_$DATABASE to logminer;

GRANT FLASHBACK ANY TABLE TO logminer;

GRANT SELECT ANY TABLE TO logminer;

GRANT SELECT\_CATALOG\_ROLE TO logminer;

GRANT EXECUTE\_CATALOG\_ROLE TO logminer;

GRANT SELECT ANY TRANSACTION TO logminer;

GRANT LOGMINING TO logminer;

GRANT CREATE TABLE TO logminer;

GRANT LOCK ANY TABLE TO logminer;

GRANT ALTER ANY TABLE TO logminer;

GRANT CREATE SEQUENCE TO logminer;

GRANT EXECUTE ON DBMS\_LOGMNR TO logminer;

GRANT EXECUTE ON DBMS\_LOGMNR\_D TO logminer;

grant execute on dbms\_flashback to logminer;

GRANT SELECT ON V\_$LOG TO logminer;

GRANT SELECT ON V\_$LOG\_HISTORY TO logminer;

GRANT SELECT ON V\_$LOGMNR\_LOGS TO logminer;

GRANT SELECT ON V\_$LOGMNR\_CONTENTS TO logminer;

GRANT SELECT ON V\_$LOGMNR\_PARAMETERS TO logminer;

GRANT SELECT ON V\_$LOGFILE TO logminer;

GRANT SELECT ON V\_$ARCHIVED\_LOG TO logminer;

GRANT SELECT ON V\_$ARCHIVE\_DEST\_STATUS TO logminer;

四、参数调整（一般情况下不用调整）

设置Logminer日志切换时间，默认0s

alter system set archive\_lag\_target = 60 scope =spfile;#重启生效

alter system set archive\_lag\_target = 60 scope =both;#不重启直接生效