今天碰到一個ASM歸檔磁盤空間異常的情況，需要查詢過去幾小時、幾天內的歸檔日志生成量，涉及到v$archived_log視圖，借此機會學習總結一下。

一、v$archived_log與gv$archived_log

單實例情況下毋庸置疑查詢v$archived_log

那么rac環境下varchived_log與gvarchived_log是個什么關系呢

我對比了兩個視圖，gv$archived_log僅多了一列INST_ID

執行查詢看到gv$archived_log中，同一個歸檔日志記錄了兩行，區別僅是inst_id不同

所以集群環境也查詢v$archived_log即可

二、歸檔數量及大小查詢

下面列出四種查詢的情況

1.查詢當天每小時的歸檔日志生成量

alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';

select logtime,  
       count(*),  
       round(sum(blocks * block_size) / 1024 / 1024) mbsize  
  from (select trunc(first_time, 'hh') as logtime, a.BLOCKS, a.BLOCK_SIZE  
          from v$archived_log a  
         where a.DEST_ID = 1  
           and a.FIRST_TIME > trunc(sysdate))  
 group by logtime  
 order by logtime desc;  

說明：
1、first_time 是歸檔日志的開頭時間
2、DEST_ID指向本地歸檔，1代表log_archive_dest_1
3、trunc()函數中的hh代表小時，下面sql取dd代表天

上圖可見每小時產生19G左右的歸檔，count(*)列代表每小時兩節點一共產生了多少個歸檔日志。

2.查最近一周每天的歸檔日志生成量

select logtime,  
       count(*),  
       round(sum(blocks * block_size) / 1024 / 1024) mbsize  
  from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE  
          from v$archived_log a  
         where a.DEST_ID = 1  
           and a.FIRST_TIME > trunc(sysdate - 7))  
 group by logtime  
 order by logtime desc; 

3.查詢當天每小時的各個實例的歸檔日志生成量

select THREAD#,  
       logtime,  
       count(*),  
       round(sum(blocks * block_size) / 1024 / 1024) mbsize  
  from (select a.THREAD#,  
               trunc(first_time, 'hh') as logtime,  
               a.BLOCKS,  
               a.BLOCK_SIZE  
          from v$archived_log a  
         where a.DEST_ID = 1  
           and a.FIRST_TIME > trunc(sysdate))  
 group by THREAD#, logtime  
 order by THREAD#, logtime desc;  

4. 查詢最近一周每天的各個實例的歸檔日志生成量

select THREAD#,  
       logtime,  
       count(*),  
       round(sum(blocks * block_size) / 1024 / 1024) mbsize  
  from (select THREAD#,  
               trunc(first_time, 'dd') as logtime,  
               a.BLOCKS,  
               a.BLOCK_SIZE  
          from v$archived_log a  
         where a.DEST_ID = 1  
           and a.FIRST_TIME > trunc(sysdate - 7))  
 group by THREAD#, logtime  
 order by THREAD#, logtime desc;  

三、v$archived_log視圖的部分常用列說明

詳細說明見官方文檔：
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-ARCHIVED_LOG.html#GUID-51618DF3-0C20-40E1-B94F-212FDF7CD729