Oracle Logminer 日志挖掘

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 生产环境中考虑到数据库的性能问题,很少会打开数据的审计功能,应用层也不会记录SQL的执行信息;但是生产上经常会遇到某张表的某几条被修改掉,但是应用又查不到是哪个接口修改的记录,这时候Logminer 就派上用场了。
生产环境中考虑到数据库的性能问题,很少会打开数据的审计功能,应用层也不会记录SQL的执行信息;但是生产上经常会遇到某张表的某几条被修改掉,但是应用又查不到是哪个接口修改的记录,这时候Logminer 就派上用场了。
Logminer 8i之后的一款免费日志分析工具:通过分析在线日志文件或者归档日志文件,返回数据库DDL/DML操作语句、执行时间、用户等等可以追查的信息,快速定位问题。
使用Logminer 工具,数据库需要开启强制日志和归档模式
数据库配置
# 开启强制日志模式

# 查看当前数据库日志模式
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

# 如果返回结果为NO,则
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;

# 确认是否已经修改,输出为YES
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

# 切换系统日志
SQL> ALTER SYSTEM SWITCH LOGFILE;

# 数据库处于归档模式
SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     107
Next log sequence to archive   109
Current log sequence           109
# 默认归档日志路径
SQL> show parameter db_recover

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size         big integer 4977M
# 若数据库处于非归档模式,则需要
# 干净的关闭掉数据库
SQL> shutdown immediate
# 打开至mount状态
SQL> startup mount
# 设置为归档模式
SQL> alter database archivelog;
# 开启数据库
SQL> alter database open;
Logminer 配置
# 首先安装 logminer 使用到的包,将创建用于分析的过程和视图
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

# 参数配置,用于创建字典文件
SQL> show parameter utl

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string     /u01/app/oracle/logminer
# 如果VALUE 为 NULL,那么需要在线修改并重启实例生效
SQL> alter system set utl_file_dir='/u01/app/oracle/logminer' scope=spfile;

#  创建字典文件
SQL> CREATE DIRECTORY utlfile AS '/u01/app/oracle/logminer';

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

SQL> !ls  /u01/app/oracle/logminer
dictionary.ora
对测试数据表进行操作
#  创建临时表
SQL>  create table u_logminer.dba_objects as select * from all_objects;
# 更新操作
SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS    OWNER                   OBJECT_NAME
------- ------------------------------ --------------------
INVALID U_LOGMINER               ALL_OBJECTS
INVALID U_LOGMINER               ALL_OBJECTS
INVALID U_LOGMINER               ALL_OBJECTS
INVALID U_LOGMINER               ALL_OBJECTS
 # update 更新
SQL> update U_LOGMINER.dba_objects set STATUS = 'VALID' where owner = 'U_LOGMINER';

4 rows updated.

SQL> commit;

Commit complete.

SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS    OWNER                   OBJECT_NAME
------- ------------------------------ --------------------
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
# insert 插入
SQL> insert into U_LOGMINER.dba_objects select * from dba_objects where owner = 'U_LOGMINER';

4 rows created.

SQL> commit;

Commit complete.

SQL> select status,owner,object_name from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

STATUS    OWNER                   OBJECT_NAME
------- ------------------------------ --------------------
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS
VALID    U_LOGMINER               ALL_OBJECTS

8 rows selected.
# delete 删除
SQL> delete from U_LOGMINER.dba_objects where owner = 'U_LOGMINER';

8 rows deleted.

SQL> commit;

Commit complete.
使用Logminer 分析归档日志
# 添加日志文件进行分析,第一个文件 dbms_logmnr.NEW,后面的文件dbms_logmnr.ADDFILE
SQL> BEGIN
  2  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_106_dshlmn6r_.arc',options=>dbms_logmnr.NEW);
  3  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_107_dshlmoo2_.arc',options=>dbms_logmnr.ADDFILE);
  4  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2017_08_19/o1_mf_1_108_dshm5n1x_.arc',options=>dbms_logmnr.ADDFILE);
  5  end;
  6  /

PL/SQL procedure successfully completed.
# 开始分析,当前是完整分析日志文件的内容,可以操作时间、SCN等过滤需要分析的内容
SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename =>'/u01/app/oracle/logminer/dictionary.ora');
# 或者
SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartTime =>to_date('2017-8-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),EndTime =>to_date('2017-8-19 17:00:00','YYYY-MM-DD HH24:MI:SS '));
# 或者
SQL> 
SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName =>'/u01/app/oracle/logminer/dictionary.ora', StartScn>=20, EndScn<= 50);

PL/SQL procedure successfully completed.
# 结果放在视图中,注意当会话结束,临时表会被删除
SQL> SELECT count(*) FROM v$logmnr_contents; 

  COUNT(*)
----------
     42118
# 将临时表数据备份至物理表中
SQL> create table u_logMIner.logminer_tmp as select * from  v$logmnr_contents; 

Table created.
日志文件分析完成,接下来就看看对表到底做了什么操作
# 根据事务开始时间进行排序,查询数据表的变更记录
SQL > SELECT START_TIMESTAMP,COMMIT_TIMESTAMP,sql_redo,sql_undo,machine_name,os_username,username,table_name FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

# sql_redo 更改数据的SQL
# sql_undo 回滚数据的SQL
# machine_name,os_username,username 三位一体定位执行更新的机器、数据库用户名

# 如下:
SQL> SELECT sql_redo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

SQL_REDO
--------------------------------------------------------------------------------
update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL
ID' and ROWID = 'AAAD8EAAIAAAAJAAAf';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'INVALID' where "STATUS" = 'VAL
ID' and ROWID = 'AAAD8EAAIAAAAKHAAv';
…………

# 如下:
SQL> SELECT sql_undo FROM u_logMIner.logminer_tmp WHERE username='U_LOGMINER' AND table_name='DBA_OBJECTS' order by START_TIMESTAMP;

SQL_UNDO
--------------------------------------------------------------------------------
update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAJAAAf';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAKHAAv';

update "U_LOGMINER"."DBA_OBJECTS" set "STATUS" = 'VALID' where "STATUS" = 'INVAL
ID' and ROWID = 'AAAD8EAAIAAAAPoAA0';
………………
SQL拿出来了,就可以定位问题,但是能不能立刻回滚到生产环境,还需要和业务部门进行沟通。
建议在创建数据库时,就打开强制日志、归档模式,配置 utl_file_dir 参数,避免数据库重启对线上的影像
当然也可以直接下其他环境下的数据库做日志分析工作,有两个点需要注意:数据字典和日志文件,其他和在本机处理没什么两样。
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
29天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的控制文件与归档日志文件
本文介绍了Oracle数据库中的控制文件和归档日志文件。控制文件记录了数据库的物理结构信息,如数据库名、数据文件和联机日志文件的位置等。为了保护数据库,通常会进行控制文件的多路复用。归档日志文件是联机重做日志文件的副本,用于记录数据库的变更历史。文章还提供了相关SQL语句,帮助查看和设置数据库的日志模式。
【赵渝强老师】Oracle的控制文件与归档日志文件
|
29天前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的参数文件与告警日志文件
本文介绍了Oracle数据库的参数文件和告警日志文件。参数文件分为初始化参数文件(PFile)和服务器端参数文件(SPFile),在数据库启动时读取并分配资源。告警日志文件记录了数据库的重要活动、错误和警告信息,帮助诊断问题。文中还提供了相关视频讲解和示例代码。
|
29天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中,联机重做日志文件记录了数据库的变化,用于实例恢复。每个数据库有多组联机重做日志,每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
|
4月前
|
SQL 监控 Oracle
Oracle数据误删不用怕,跟我来学日志挖掘
Oracle数据误删不用怕,跟我来学日志挖掘
61 0
|
监控 Oracle 关系型数据库
|
2月前
|
存储 Oracle 关系型数据库
Oracle数据库的应用场景有哪些?
【10月更文挑战第15天】Oracle数据库的应用场景有哪些?
185 64
|
4天前
|
存储 Oracle 关系型数据库
数据库数据恢复—ORACLE常见故障的数据恢复方案
Oracle数据库常见故障表现: 1、ORACLE数据库无法启动或无法正常工作。 2、ORACLE ASM存储破坏。 3、ORACLE数据文件丢失。 4、ORACLE数据文件部分损坏。 5、ORACLE DUMP文件损坏。
34 11
|
17天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错,报错信息: “system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。管理员联系我们数据恢复中心寻求帮助,并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份,无法通过备份去恢复数据库。
|
23天前
|
存储 Oracle 关系型数据库
oracle数据恢复—Oracle数据库文件大小变为0kb的数据恢复案例
存储掉盘超过上限,lun无法识别。管理员重组存储的位图信息并导出lun,发现linux操作系统上部署的oracle数据库中有上百个数据文件的大小变为0kb。数据库的大小缩水了80%以上。 取出&并分析oracle数据库的控制文件。重组存储位图信息,重新导出控制文件中记录的数据文件,发现这些文件的大小依然为0kb。
|
9天前
|
存储 Oracle 关系型数据库
服务器数据恢复—华为S5300存储Oracle数据库恢复案例
服务器存储数据恢复环境: 华为S5300存储中有12块FC硬盘,其中11块硬盘作为数据盘组建了一组RAID5阵列,剩下的1块硬盘作为热备盘使用。基于RAID的LUN分配给linux操作系统使用,存放的数据主要是Oracle数据库。 服务器存储故障: RAID5阵列中1块硬盘出现故障离线,热备盘自动激活开始同步数据,在同步数据的过程中又一块硬盘离线,RAID5阵列瘫痪,上层LUN无法使用。

推荐镜像

更多