Oracle Database Archivelog Generation

Daily Archivelog Generation (By Thread -RAC)

--------------------------------------------

select trunc(COMPLETION_TIME,'DD') Day, thread#, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log where STANDBY_DEST='NO' 

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Daily Archivelog Generation 

--------------------------------------------

select trunc(COMPLETION_TIME,'DD') Day, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log where STANDBY_DEST='NO'

group by trunc(COMPLETION_TIME,'DD') order by 1;

Hourly Archive Log Generation  (By Thread -RAC):

-------------------------------

set pages 1000

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from v$archived_log where STANDBY_DEST='NO'

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Hourly Archive Log Generation  

-------------------------------

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives from v$archived_log where STANDBY_DEST='NO'

group by trunc(COMPLETION_TIME,'HH') order by 1 ;​