Oracle查询归档日志的产生情况（每天增量大小）
👉 本文共约167个字，系统预计阅读时间或需1分钟。
归档日志大小（通用）
无论归档日志放在文件系统还是ASM磁盘上，都可以通过如下方式查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | -- 按照天数计算 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; |
归档日志存放在在文件系统
在文件系统上，进入归档目录后，可以直接用如下命令查询：
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 | du -sh ./* |
归档日志在ASM磁盘示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 | [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. |
