LogMiner应用1：表的访问统计

本文使用DBMS_LOGMNR_D.STORE_IN_FLAT_FILE方式

一、案例环境和思路：
1、环境：Oracle 19C PDB，开启归档、闪回、附加日志，使用了LogMiner用户，FLAT FILE目录为"utlfile"

mkdir -p /home/oracle/logmnr
sqlplus / as sysdba
alter session set container=pdb;
GRANT READ,WRITE ON DIRECTORY "utlfile" TO c##roma_logminer;

## Python脚本 ，安装Python3，pip3 install cx-Oracle
#/usr/bin/env python3
# -*- coding:utf-8 -*-
#Author:ld

import cx_Oracle
dsn_tnsstr = cx_Oracle.makedsn('192.168.1.232','1521', 'pdb')
dsn_tns = dsn_tnsstr.replace('SID', 'SERVICE_NAME')

def Oracle_Lock():
 conn = cx_Oracle.connect('hr', 'hrpw', dsn_tns)
 sql = 'update employees set salary=salary+1 where EMPLOYEE_ID = 119'
 for i in range(0,100):
  cursor = conn.cursor()
  result = cursor.execute(sql)
  cursor.close()
  conn.commit()
 conn.close()

if __name__ == '__main__':
 Oracle_Lock()

2、思路与过程：Python写个脚本更新PDB中employees表100次，通过LogMiner的IN_FLAT_FILE方式进行挖掘，目的是统计在该SCN段表的访问次数（DML次数）。

二、测试
1、执行Python脚本，完成100次更新

sqlplus c##roma_logminer/password
c##roma_logminer@ORCL>select current_scn from v$database;
CURRENT_SCN
-----------
    2792822
# Oracle用户执行
[oracle@Oracle19C ~]$ python3 test.py
c##roma_logminer@ORCL>select current_scn from v$database;
CURRENT_SCN
-----------
    2793430

2、挖掘

# 设置字典的方式为：STORE_IN_FLAT_FILE
alter session set container=pdb;    # 这里因为是使用的pdb，所以需要在该pdb下创建字典，如果是非CDB环境则此处省略
EXECUTE dbms_logmnr_d.build(dictionary_location=>'utlfile', - 
    dictionary_filename=>'dictionary.ora', -
    options => dbms_logmnr_d.store_in_flat_file);
# 查询最近的redolog，并添加该日志
alter session set container=cdb$root;   # 需要回到CDB环境执行日志添加
SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;
    GROUP#  SEQUENCE# STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ------------------------------------------------ ------------- -------------------
         1         13 INACTIVE                                               2635649 2021/01/03 17:34:25
         2         14 INACTIVE                                               2707852 2021/01/03 18:00:23
         3         15 CURRENT                                                2780775 2021/03/27 12:19:40
EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/oradata/ORCL/redo03.log',options=>dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   STARTSCN => 2792822, -
   ENDSCN => 2793430, -
   DICTFILENAME => '/home/oracle/logmnr/dictionary.ora');   # 根据SCN的范围启动LogMiner
# 查询update的DML语句
select operation,username,timestamp,sql_redo 
    from v$logmnr_contents 
    where username='HR' and operation='UPDATE';
# 查询在这个SCN时间段内，各表被执行DML的次数
col SEG_OWNER for a30
col SEG_NAME for a30
SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM
   V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY
   SEG_OWNER, SEG_NAME ORDER BY Hits DESC;
SEG_OWNER                      SEG_NAME                             HITS
------------------------------ ------------------------------ ----------
HR                             EMPLOYEES                             100    # update100次
# 停止LogMiner
EXECUTE DBMS_LOGMNR.END_LOGMNR;