🚀 优质资源分享 🚀
学习路线指引(点击解锁)知识定位人群定位
进阶级本课程是python flask+微信小程序的完美结合,从项目搭建到腾讯云部署上线,打造一个全栈订餐系统。
入门级手把手带你打造一个易扩展、更安全、效率更高的量化交易系统
回到顶部## 1、ORACLE归档日志介绍
归档日志暴增是oracle比较常见的问题,遇到归档日志暴增,我们该如何排查:
- 归档日志暴增一般都是应用或者人为引起的
- 理解归档日志存储的是什么
- 如何排查归档日志暴增原因
- *如何优化归档日志暴增
归档日志(Archive Log)是非活动的重做日志(redo)备份.
通过使用归档日志,可以保留所有重做历史记录,当数据库处于ARCHIVELOG模式并进行日志切换式,后台进程ARCH会将重做日志的内容保存到归档日志中.
当数据库出现介质失败时,使用数据文件备份,归档日志和重做日志可以完全恢复数据库。
所有重做的历史记录,包括DML语句、数据改变等
一般是DML操作大量的数据,导致归档日志暴增
1.SQL语句
2.AWR
3.挖掘归档日志
回到顶部## 2、归档日志暴增排查实战
create table scott.object as select * from dba\_objects;
-- 执行10次
-- insert
insert into scott.object select * from scott.object;
select count(1) from scott.object;
-- 49384448
-- update
update SCOTT.object set owner='aa';
-- delete
delete from SCOTT.object;
truncate table SCOTT.object;
SELECT
THREAD# id,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
FROM
v$log\_history a
GROUP BY SUBSTR(TO\_CHAR(first\_time, 'MM/DD/RR HH:MI:SS'),1,5),THREAD#
ORDER BY id,SUBSTR(TO\_CHAR(first\_time, 'MM/DD/RR HH:MI:SS'),1,5)
/
代表12月19号,H20(20-21时),共切换24个归档日志,如果每一个500M,那么总共约500M*24,对比其余时间,可以说该时间产生异常的归档日志,目标排查改时间段
with aa as
(SELECT IID,
USERNAME,
to\_char(BEGIN\_TIME,'mm/dd hh24:mi') begin\_time,
SQL\_ID,
decode(COMMAND\_TYPE,3,'SELECT',2,'INSERT',6,'UPDATE',7,'DELETE',189,'MERGE INTO','OTH') "SQL\_TYPE",
executions "EXEC\_NUM",
rows\_processed "Change\_NUM"
FROM (SELECT s.INSTANCE\_NUMBER IID,
PARSING\_SCHEMA\_NAME USERNAME,COMMAND\_TYPE,
cast(BEGIN\_INTERVAL\_TIME as date) BEGIN\_TIME,
s.SQL\_ID,
executions\_DELTA executions,
rows\_processed\_DELTA rows\_processed,
(IOWAIT\_DELTA) /
1000000 io\_time,
100*ratio\_to\_report(rows\_processed\_DELTA) over(partition by s.INSTANCE\_NUMBER, BEGIN\_INTERVAL\_TIME) RATIO,
sum(rows\_processed\_DELTA) over(partition by s.INSTANCE\_NUMBER, BEGIN\_INTERVAL\_TIME) totetime,
elapsed\_time\_DELTA / 1000000 ETIME,
CPU\_TIME\_DELTA / 1000000 CPU\_TIME,
(CLWAIT\_DELTA+APWAIT\_DELTA+CCWAIT\_DELTA+PLSEXEC\_TIME\_DELTA+JAVEXEC\_TIME\_DELTA)/1000000 OTIME,
row\_number() over(partition by s.INSTANCE\_NUMBER,BEGIN\_INTERVAL\_TIME order by rows\_processed\_DELTA desc) TOP\_D
FROM dba\_hist\_sqlstat s, dba\_hist\_snapshot sn,dba\_hist\_sqltext s2
where s.snap\_id = sn.snap\_id
and s.INSTANCE\_NUMBER = sn.INSTANCE\_NUMBER
and rows\_processed\_DELTA is not null
and s.sql\_id = s2.sql\_id and COMMAND\_TYPE in (2,6,7,189)
and sn.BEGIN\_INTERVAL\_TIME > sysdate - nvl(180,1)/1440 and PARSING\_SCHEMA\_NAME<>'SYS')
WHERE TOP\_D <= nvl(20,1) ) select aa.*,s.sql\_fulltext "full\_sql" from aa left join v$sql s on aa.sql\_id="s.sql\_id" order by iid, begin\_time desc,"change\_num" desc < code></=>
查看2小时的数据该变量,可以看出Change\_NUM数据该变量和执行次数EXEC\_NUM和SQL语句,update回滚了,所以没有该变量。
此时可以判断大量插入数据导致归档日志暴增,此时并不能判断update。此语句不一定有数据,只能做参考。
创建AWR报告
创建AWR报告
@?/rdbms/admin/awrrpt.sql
`
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
* 3830097027 1 ….. ….. dbserver01
Using 3830097027 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day’s Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
Original: https://blog.csdn.net/qq_43479892/article/details/125734733
Author: qq_43479892
Title: Oracle归档日志暴增排查优化
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/744570/
转载文章受原作者版权保护。转载请注明原作者出处!