oracle查询某时间段内归档日志生成量 -m6米乐安卓版下载

今天碰到一个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