日志挖掘LogMiner

1.开启补全日志：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

2.找出需要挖掘的归档日志文件路径
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE FIRST_TIME between to_date('2024-04-03 01:13:00','yyyy-mm-dd hh24:mi:ss') and to_date('2024-04-03 01:15:00','yyyy-mm-dd hh24:mi:ss') order by 1;

NAME
/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154146_m0rh6bhh_.arc

/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154147_m0rh72pw_.arc

/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154148_m0rh8192_.arc

/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154149_m0rh9n68_.arc

3.Logminer中添加归档日志文件
	3.1.Logminer中新建归档日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154145_m0rh5h7f_.arc', OPTIONS => DBMS_LOGMNR.NEW);
	3.2.Logminer中附加归档日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154146_m0rh6bhh_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154147_m0rh72pw_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/tqls_system/app/oracle/flash_recovery_area/DATAEXC/archivelog/2024_04_03/o1_mf_1_154148_m0rh8192_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

4.指定Logminer字典
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

5.查看添加的日志列表
select filename from V$LOGMNR_LOGS;

6.开始日志挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

7.分析数据
SQL> select table_name,count(table_name) from v$logmnr_contents group by table_name;

TABLE_NAME COUNT(TABLE_NAME)
LIPS  3311

8.关闭日志挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR;