归档日志存在arch_查归档日志文件每小时生成量

在O

racle数据库中，通过v$archived_log数据字典视图查询该数据库的归档日志文件的生成情况。

如果你以为在rac下需要查的gv$archvied_log视图，这其实是一个错误的想法。

无论在单实例数据库，还是多实例的RAC数据库，都是查这个视图来获取信息。

查当天每小时的归档日志生成量

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;

查最近一周每天的归档日志生成量

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;

如果你需要知道RAC下各个节点的归档日志情况，我将上面脚本略作修改，增加thread#列。

查当天每小时的各个实例的归档日志生成量

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;

查最近一周每天的各个实例的归档日志生成量

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;

敬请留存，以备不时之需

@mikixiyou

分享到：

2012-12-18 16:13

浏览 10180

分类:数据库

评论