记oracle日志挖掘实操&查询归档不正常增长情况(一)

问题：最近几周经常手动删除归档日志，归档日志报空间不足（预留800G空间已用完），基于此查询归档日志情况（近期业务有所上涨）。

-----------------------------此为回溯操作流程，截图非实际环境-----------------------

1、查看归档日志大小---实际查到14日单日归档高于200G，

select aa.ArchiveDate, aa.ArchiveSize
from (select trunc(first_time) as ArchiveDate,
sum(block_size * blocks) / 1024 / 1024 / 1024 as ArchiveSize
from v$archived_log
group by trunc(first_time)) aa
order by aa.ArchiveDate desc;

2、查看最近几天，每小时归档日志产生数量----显示14日早8点到10点日志切换较多

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;

3、查询此时间段的归档日志名称等情况---实际查到+LOG/tzcdb/archivelog/2022_06_15/thread_1_seq_1438.346.1107420055；+LOG/tzcdb/archivelog/2022_06_15/thread_1_seq_1439.334.1107420057；

select resetlogs_time,
FIRST_TIME,
NEXT_TIME,
BLOCKS / 1024 / 1024 mbblock,
block_size,
block_size * blocks / 1024 / 1024 sjsizemb,
status,
COMPLETION_TIME
from v$archived_log
where first_time <
to_date('2022-06-16 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
and first_time >
to_date('2022-06-12 16:05:00', 'yyyy-mm-dd hh24:mi:ss')
order by 1

4、找到对应相关的日志后，就开始进入日志挖掘截断了

Logminer 基本使用步骤
1、 Specify a LogMiner dictionary. 指定Logminer字典
2、 Specify a list of redo log files for analysis. 指定需要挖掘的redo或者archivelog日志文件
3、 Start LogMiner. 开始日志挖掘
4、 Request the redo data of interest. 查询V$LOGMNR_CONTENTS获取挖掘的结果
5、End the LogMiner session. 结束日志挖掘

4-1.开启补全日志：（一定要开启，否则后面执行会报错）

#sqlplus / as sysdba 
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

4-2.找出需要挖掘的归档日志文件路径(第3步已找到)

4-3.使用此项指定Logminer字典（不用重启数据库）---.Extracting a LogMiner Dictionary to the Redo Log Files 抽取字典到redo日志文件中

EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

4-4.Logminer中添加归档日志文件（注意添加只能先添加一个）

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+LOG/tzcdb/archivelog/2022_06_15/thread_1_seq_1439.334.1107420057', OPTIONS => DBMS_LOGMNR.NEW);

4-5.继续添加日志：（注意此处的OPTIONS=> DBMS_LOGMNR.ADDFILE）

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+LOG/tzcdb/archivelog/2022_06_15/thread_1_seq_1438.346.1107420055', OPTIONS => DBMS_LOGMNR.ADDFILE);

4-6.开始日志挖掘

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS );

如果执行不成功，可以手动执行次存储过程

begin
sys.dbms_logmnr.start_logmnr();
end;
/

4-7.查看挖掘效果，并把挖取结果保存到指定表格中：

SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR', 'OE');

 create table logtab as select * from v$logmnr_contents;

4-8.关闭日志挖掘

begin
sys.dbms_logmnr.end_logmnr();
end;
/

5、查询挖掘出的数据结果（基于数据量较大，不建议直接查询，可仅查询前10行，再收集需要的记录）

select * from logtab where rownum<11;
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('PDBZ, 'JW');

-------------------------------------------------------------------------------------------------------------------------关于挖掘出来的日志如何查看，下一次更新续上！