Oracle查询归档日志的产生情况（每天增量大小）
归档日志大小（通用）
无论归档日志放在文件系统还是ASM磁盘上，都可以通过如下方式查询：
-- 按照天数计算
SELECT to_char(FIRST_TIME,'YYYY-MM-DD') MD,
ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
FROM v$archived_log a
WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30
-- and a.name is not null
group by to_char(FIRST_TIME,'YYYY-MM-DD')
order by to_char(FIRST_TIME,'YYYY-MM-DD');
-- 计算总大小
SELECT ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
FROM v$archived_log a
WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 30
-- and a.name is not null
order by to_char(FIRST_TIME,'YYYY-MM-DD');
-- 每天日志切换频率
SELECT a.THREAD#, '<div align="center"><font color="#336699"><b>' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '</b></font></div>' 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 gv$log_history a
WHERE first_time>=TO_CHAR(SYSDATE - 15)
group by a.THREAD#,
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY a.THREAD#,SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
-- 闪回恢复区使用情况
col name format a30
SELECT A.NAME,
round(a.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
(a.space_used / 1024 / 1024) space_used_m,
round(a.space_used / a.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
round(a.space_reclaimable / 1024 / 1024, 2) space_reclaimable,
round(a.space_reclaimable / a.SPACE_LIMIT, 2) PERCENT_SPACE_RECLAIMABLE,
number_of_files
FROM v$recovery_file_dest A
WHERE a.SPACE_LIMIT <> 0
UNION ALL
SELECT b.FILE_TYPE,
(c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
round(b.PERCENT_SPACE_USED * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_used_m,
b.PERCENT_SPACE_USED PERCENT_SPACE_USED,
round(b.PERCENT_SPACE_RECLAIMABLE * c.SPACE_LIMIT / 1024 / 1024 / 100,
2) space_reclaimable,
(b.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
b.NUMBER_OF_FILES
FROM v$flash_recovery_area_usage b, v$recovery_file_dest c
WHERE c.SPACE_LIMIT <> 0
UNION ALL
SELECT bb.FILENAME || '---' || bb.STATUS,
(c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
(bb.BYTES / 1024 / 1024) space_used,
round(bb.BYTES * 100 / c.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
0,
0,
1
FROM v$block_change_tracking bb, v$recovery_file_dest c
WHERE c.SPACE_LIMIT <> 0;
归档日志存放在在文件系统
在文件系统上，进入归档目录后，可以直接用如下命令查询：
du -sh ./*
归档日志在ASM磁盘示例
[oracle@rac1 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 28 11:11:06 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> SELECT ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
2 FROM v$archived_log a
3 WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 115
4 and a.name is not null
5 order by to_char(FIRST_TIME,'YYYY-MM-DD');
LOGSIZE_G
----------
59
SQL>
SQL> SELECT to_char(FIRST_TIME,'YYYY-MM-DD') MD,
2 ROUND(SUM(a.BLOCKS * a.BLOCK_SIZE) / 1024 / 1024/ 1024) LOGsize_G
3 FROM v$archived_log a
4 WHERE a.STANDBY_DEST='NO' AND a.FIRST_TIME >= SYSDATE - 115
5 and a.name is not null
6 group by to_char(FIRST_TIME,'YYYY-MM-DD')
7 order by to_char(FIRST_TIME,'YYYY-MM-DD');
MD LOGSIZE_G
---------- ----------
2022-11-18 6
2022-11-19 3
2022-11-20 9
2022-11-21 7
2022-11-22 6
2022-11-23 6
2022-11-24 7
2022-11-25 6
2022-11-26 4
2022-11-27 3
2022-11-28 3
11 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT a.THREAD#, SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
2 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
3 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
4 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
5 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
6 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
7 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
8 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
9 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
10 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
11 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
12 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
13 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
14 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
15 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
16 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
17 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
18 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
19 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
20 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
21 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
22 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
23 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
24 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
25 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
26 COUNT(*) TOTAL
27 FROM gv$log_history a
28 WHERE first_time>=TO_CHAR(SYSDATE - 15)
29 group by a.THREAD#,
30 SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
31 ORDER BY a.THREAD#,SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
THREAD# DAY H00 H01 H02 H03 H04 H05 H06 H07 H08 H09 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23 TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 11/28 8 6 0 2 0 0 0 2 2 4 4 0 0 0 0 0 0 0 0 0 0 0 0 0 28
1 11/27 8 2 0 2 0 0 2 0 2 0 0 2 2 0 2 2 0 2 0 2 2 0 2 0 32
1 11/26 8 6 0 0 0 2 0 0 2 0 2 0 2 0 2 2 2 2 2 0 2 2 0 0 36
1 11/25 12 4 0 0 0 0 2 0 2 4 4 4 2 2 4 6 4 4 0 2 0 0 4 0 60
1 11/24 6 4 0 0 0 0 2 0 2 6 4 6 2 2 6 8 4 6 2 0 2 0 4 10 76
1 11/23 8 6 2 0 0 2 0 0 2 4 4 4 2 4 4 6 2 4 0 2 2 0 4 2 64
1 11/22 6 4 0 0 0 2 0 0 4 4 4 4 6 2 4 6 6 4 2 2 2 0 4 2 68
1 11/21 0 2 0 0 0 2 0 0 2 10 6 4 2 2 10 8 6 2 2 0 0 2 18 2 80
1 11/20 2 2 0 0 0 2 2 2 2 0 86 0 0 2 2 0 12 2 2 0 0 0 2 4 124
1 11/19 0 2 0 0 2 0 0 2 2 0 2 2 0 0 6 0 8 0 2 0 0 0 2 2 32
1 11/18 0 6 2 0 0 0 2 0 2 6 4 4 2 2 6 8 10 6 2 8 6 2 12 10 100
1 11/17 2 2 0 0 2 0 0 0 2 6 6 4 0 0 2 6 12 2 8 0 0 2 2 2 60
1 11/16 0 2 0 0 0 2 0 0 2 4 6 4 2 2 6 8 4 2 0 2 0 0 12 0 58
1 11/15 0 2 0 0 2 0 0 2 0 4 4 4 2 2 4 8 2 4 0 2 0 2 20 2 66
1 11/14 2 8 0 0 0 2 0 0 0 4 6 2 2 2 10 6 6 4 0 2 0 2 26 4 88
1 11/13 0 4 0 0 0 0 6 2 0 0 2 0 0 2 0 0 0 2 0 2 0 0 4 0 24
2 11/28 24 2 0 2 0 0 2 0 4 6 6 2 0 0 0 0 0 0 0 0 0 0 0 0 48
2 11/27 24 8 0 0 2 0 0 2 4 0 4 2 2 2 4 2 2 4 2 2 2 2 2 0 72
2 11/26 22 2 0 2 0 2 0 2 2 2 6 2 2 0 8 2 2 4 6 0 2 2 2 0 72
2 11/25 40 8 0 2 0 0 2 0 2 6 6 6 4 2 6 6 6 6 4 2 0 2 10 2 122
2 11/24 22 8 0 0 0 2 0 2 0 6 6 6 2 4 6 6 6 6 2 2 2 2 14 18 122
2 11/23 24 2 0 0 0 2 0 2 0 6 6 4 2 4 6 4 6 4 2 2 0 2 14 2 94
2 11/22 22 8 0 0 2 0 0 2 4 6 6 4 2 4 6 6 6 4 2 2 4 2 12 0 104
2 11/21 0 8 0 2 0 0 0 2 0 24 10 6 2 2 26 10 8 4 4 0 2 2 6 4 122
2 11/20 0 6 0 2 0 0 0 4 0 0 28 0 2 0 0 2 34 10 0 2 0 2 0 2 94
2 11/19 0 8 0 0 2 0 2 2 0 2 2 0 2 0 2 2 20 2 0 2 0 2 0 2 52
2 11/18 0 4 0 0 2 0 2 0 2 4 6 6 2 4 8 10 28 6 4 18 22 4 4 14 150
2 11/17 2 8 2 0 0 2 0 0 2 6 8 4 2 0 2 6 6 4 22 0 2 0 6 2 86
2 11/16 0 8 0 0 2 0 0 2 2 4 4 4 2 4 10 6 4 4 0 2 0 2 4 2 66
2 11/15 2 6 0 2 0 0 2 0 0 8 6 4 2 4 4 8 4 4 2 0 2 2 20 2 84
2 11/14 2 2 0 0 2 0 0 2 0 4 6 4 2 8 14 10 4 4 2 0 0 2 18 10 96
2 11/13 0 8 0 0 2 0 2 0 0 2 0 0 0 2 0 2 0 2 0 2 0 0 2 0 24
32 rows selected.
SQL> SELECT A.NAME,
2 round(a.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
3 (a.space_used / 1024 / 1024) space_used_m,
4 round(a.space_used / a.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
5 round(a.space_reclaimable / 1024 / 1024, 2) space_reclaimable,
6 round(a.space_reclaimable / a.SPACE_LIMIT, 2) PERCENT_SPACE_RECLAIMABLE,
7 number_of_files
8 FROM v$recovery_file_dest A
9 WHERE a.SPACE_LIMIT <> 0
10 UNION ALL
11 SELECT b.FILE_TYPE,
12 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
13 round(b.PERCENT_SPACE_USED * c.SPACE_LIMIT / 1024 / 1024 / 100, 2) space_used_m,
14 b.PERCENT_SPACE_USED PERCENT_SPACE_USED,
15 round(b.PERCENT_SPACE_RECLAIMABLE * c.SPACE_LIMIT / 1024 / 1024 / 100,
16 2) space_reclaimable,
17 (b.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
18 b.NUMBER_OF_FILES
19 FROM v$flash_recovery_area_usage b, v$recovery_file_dest c
20 WHERE c.SPACE_LIMIT <> 0
21 UNION ALL
22 SELECT bb.FILENAME || '---' || bb.STATUS,
23 (c.SPACE_LIMIT / 1024 / 1024) SPACE_LIMIT_m,
24 (bb.BYTES / 1024 / 1024) space_used,
25 round(bb.BYTES * 100 / c.SPACE_LIMIT, 2) PERCENT_SPACE_USED,
26 0,
27 0,
28 1
29 FROM v$block_change_tracking bb, v$recovery_file_dest c
30 WHERE c.SPACE_LIMIT <> 0;
NAME SPACE_LIMIT_M SPACE_USED_M PERCENT_SPACE_USED SPACE_RECLAIMABLE PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------------ ------------- ------------ ------------------ ----------------- ------------------------- ---------------
+DATA 819200 65389 .08 0 0 851
CONTROL FILE 819200 0 0 0 0 1
REDO LOG 819200 4177.92 .51 0 0 14
ARCHIVED LOG 819200 61112.32 7.46 0 0 836
BACKUP PIECE 819200 0 0 0 0 0
IMAGE COPY 819200 0 0 0 0 0
FLASHBACK LOG 819200 0 0 0 0 0
FOREIGN ARCHIVED LOG 819200 0 0 0 0 0
---DISABLED 819200 0 0 1
9 rows selected.
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-11-30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
