Oracle Archive generation report
set term off term on
DEFINE size_label=Gb
DEFINE size_divider="1024/1024/1024"
DEFINE round_precision=2
SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000
COLUMN separator HEADING "!|!|!" FORMAT A1
COLUMN "Date" HEADING "Date" FORMAT A9
COLUMN "Total" HEADING "Day|Total|(&size_label)" FORMAT 9999
COLUMN "Day" HEADING "Day" FORMAT A3
COLUMN h0 HEADING "h0|(&size_label)" FORMAT 999
COLUMN h1 HEADING "h1|(&size_label)" FORMAT 999
COLUMN h2 HEADING "h2|(&size_label)" FORMAT 999
COLUMN h3 HEADING "h3|(&size_label)" FORMAT 999
COLUMN h4 HEADING "h4|(&size_label)" FORMAT 999
COLUMN h5 HEADING "h5|(&size_label)" FORMAT 999
COLUMN h6 HEADING "h6|(&size_label)" FORMAT 999
COLUMN h7 HEADING "h7|(&size_label)" FORMAT 999
COLUMN h8 HEADING "h8|(&size_label)" FORMAT 999
COLUMN h9 HEADING "h9|(&size_label)" FORMAT 999
COLUMN h10 HEADING "h10|(&size_label)" FORMAT 999
COLUMN h11 HEADING "h11|(&size_label)" FORMAT 999
COLUMN h12 HEADING "h12|(&size_label)" FORMAT 999
COLUMN h13 HEADING "h13|(&size_label)" FORMAT 999
COLUMN h14 HEADING "h14|(&size_label)" FORMAT 999
COLUMN h15 HEADING "h15|(&size_label)" FORMAT 999
COLUMN h16 HEADING "h16|(&size_label)" FORMAT 999
COLUMN h17 HEADING "h17|(&size_label)" FORMAT 999
COLUMN h18 HEADING "h18|(&size_label)" FORMAT 999
COLUMN h19 HEADING "h19|(&size_label)" FORMAT 999
COLUMN h20 HEADING "h20|(&size_label)" FORMAT 999
COLUMN h21 HEADING "h21|(&size_label)" FORMAT 999
COLUMN h22 HEADING "h22|(&size_label)" FORMAT 999
COLUMN h23 HEADING "h23|(&size_label)" FORMAT 999
PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT * A R C H I V E L O G S W I T C H S U M M A R Y (By Size)
PROMPT * (Hourly and Daily figures in &&size_label)
PROMPT *******************************************************************************************************************************************
PROMPT
SELECT to_char(trunc(COMPLETION_TIME),'DD-Mon-YY') "Date",
to_char(COMPLETION_TIME, 'Dy') "Day",
'|' separator,
ROUND(SUM(((BLOCKS * BLOCK_SIZE)/&size_divider))) "Total",
'|' separator,
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'00',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h0",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'01',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h1",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'02',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h2",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'03',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h3",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'04',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h4",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'05',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h5",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'06',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h6",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'07',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h7",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'08',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h8",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'09',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h9",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'10',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h10",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'11',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h11",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'12',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h12",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'13',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h13",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'14',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h14",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'15',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h15",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'16',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h16",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'17',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h17",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'18',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h18",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'19',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h19",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'20',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h20",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'21',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h21",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'22',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h22",
ROUND(SUM(decode(to_char(COMPLETION_TIME, 'hh24'),'23',(BLOCKS * BLOCK_SIZE)/&size_divider,0))) "h23"
from v$archived_log
where 1=1
and standby_dest = 'NO'
-- and CREATOR IN ('ARCH' , 'FGRD','LGWR','RFS')
group by trunc(COMPLETION_TIME), to_char(COMPLETION_TIME, 'Dy')
order by trunc(COMPLETION_TIME)
/
! Day !
! Total ! h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
Date Day ! (GB) ! (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB) (GB)
--------- --- - ----- - ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
17-Oct-14 Fri | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
18-Oct-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
19-Oct-14 Sun | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
25-Oct-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
02-Nov-14 Sun | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
08-Nov-14 Sat | 0 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
set term off term on
SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000
COLUMN separator HEADING "!|!|!" FORMAT A1
COLUMN "Date" HEADING "Date" FORMAT A9
COLUMN "Total" HEADING "Total|(#)" FORMAT 9999
COLUMN "Day" HEADING "Day" FORMAT A3
COLUMN h0 HEADING "h0|(#)" FORMAT 999
COLUMN h1 HEADING "h1|(#)" FORMAT 999
COLUMN h2 HEADING "h2|(#)" FORMAT 999
COLUMN h3 HEADING "h3|(#)" FORMAT 999
COLUMN h4 HEADING "h4|(#)" FORMAT 999
COLUMN h5 HEADING "h5|(#)" FORMAT 999
COLUMN h6 HEADING "h6|(#)" FORMAT 999
COLUMN h7 HEADING "h7|(#)" FORMAT 999
COLUMN h8 HEADING "h8|(#)" FORMAT 999
COLUMN h9 HEADING "h9|(#)" FORMAT 999
COLUMN h10 HEADING "h10|(#)" FORMAT 999
COLUMN h11 HEADING "h11|(#)" FORMAT 999
COLUMN h12 HEADING "h12|(#)" FORMAT 999
COLUMN h13 HEADING "h13|(#)" FORMAT 999
COLUMN h14 HEADING "h14|(#)" FORMAT 999
COLUMN h15 HEADING "h15|(#)" FORMAT 999
COLUMN h16 HEADING "h16|(#)" FORMAT 999
COLUMN h17 HEADING "h17|(#)" FORMAT 999
COLUMN h18 HEADING "h18|(#)" FORMAT 999
COLUMN h19 HEADING "h19|(#)" FORMAT 999
COLUMN h20 HEADING "h20|(#)" FORMAT 999
COLUMN h21 HEADING "h21|(#)" FORMAT 999
COLUMN h22 HEADING "h22|(#)" FORMAT 999
COLUMN h23 HEADING "h23|(#)" FORMAT 999
PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT * A R C H I V E L O G S W I T C H S U M M A R Y (By Frequency)
PROMPT * (Hourly and Daily figures in number of archivelogs)
PROMPT *******************************************************************************************************************************************
PROMPT
PROMPT - <-------------------------------------------------------- Hourly Total ----------------------------------------------->
SELECT to_char(trunc(first_time),'DD-Mon-YY') "Date",
to_char(first_time, 'Dy') "Day",
'|' separator,
count(1) Total,
'|' separator,
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
from v$log_history
where 1=1
-- and standby_dest = 'NO'
group by trunc(first_time), to_char(first_time, 'Dy')
order by trunc(first_time)
/
! !
! Total ! h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
Date Day ! (#) ! (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#) (#)
--------- --- - ----- - ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
25-Sep-14 Thu | 4 | 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 0
26-Sep-14 Fri | 6 | 0 0 0 0 0 0 0 0 2 1 0 2 1 0 0 0 0 0 0 0 0 0 0 0
27-Sep-14 Sat | 4 | 0 0 0 0 0 0 0 0 0 3 0 1 0 0 0 0 0 0 0 0 0 0 0 0
05-Oct-14 Sun | 2 | 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0
17-Oct-14 Fri | 1 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0
18-Oct-14 Sat | 10 | 0 0 0 0 0 0 0 0 0 0 0 1 5 4 0 0 0 0 0 0 0 0 0 0
19-Oct-14 Sun | 8 | 0 0 0 0 0 0 0 0 2 3 2 1 0 0 0 0 0 0 0 0 0 0 0 0
25-Oct-14 Sat | 1 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0
02-Nov-14 Sun | 4 | 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0====
set linesize 200 pagesize 1000
column day format a3
column total format 9999
column h00 format 9999
column h01 format 9999
column h02 format 9999
column h03 format 9999
column h04 format 9999
column h04 format 9999
column h05 format 9999
column h06 format 9999
column h07 format 9999
column h08 format 9999
column h09 format 9999
column h10 format 9999
column h11 format 9999
column h12 format 9999
column h13 format 9999
column h14 format 9999
column h15 format 9999
column h16 format 9999
column h17 format 9999
column h18 format 9999
column h19 format 9999
column h20 format 9999
column h21 format 9999
column h22 format 9999
column h23 format 9999
column h24 format 9999
break on report
compute max of "total" on report
compute max of "h00" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report
REM Script to Report the Redo Log Switch History
alter session set nls_date_format='DD MON YYYY';
select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total",
sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00",
sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01",
sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02",
sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03",
sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04",
sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05",
sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06",
sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07",
sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08",
sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09",
sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10",
sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11",
sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12",
sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13",
sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14",
sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15",
sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16",
sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17",
sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18",
sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19",
sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20",
sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21",
sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22",
sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23"
from
v$archived_log
where first_time > trunc(sysdate-30)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;
THREAD# date Day total h00 h01 h02 h03 h04 h05 h06 h07 h08 h09 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23
---------- ----------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 17 FEB 2024 Sat 857 672 7 9 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 9 7 9 8 8 8
2 17 FEB 2024 Sat 356 170 8 8 8 8 8 8 9 8 7 9 8 7 8 8 9 8 8 9 8 8 7 9 8
1 18 FEB 2024 Sun 194 9 7 9 8 8 8 8 8 8 8 8 10 7 8 9 7 8 8 8 8 8 8 8 8
2 18 FEB 2024 Sun 195 10 7 9 8 8 8 8 8 8 8 8 10 7 9 8 7 8 8 8 8 8 8 8 8
1 19 FEB 2024 Mon 193 8 8 9 7 8 8 8 8 8 8 9 8 7 9 7 9 9 7 8 8 8 8 8 8
2 19 FEB 2024 Mon 192 8 8 8 8 8 8 8 8 8 8 9 8 7 9 7 8 9 8 7 8 8 8 8 8
1 20 FEB 2024 Tue 2145 8 8 8 8 8 9 8 463 207 134 137 155 8 495 36 49 7 8 349 8 8 8 8 8
2 20 FEB 2024 Tue 804 8 8 8 8 8 9 8 133 69 47 44 49 8 160 54 22 8 7 105 8 8 8 9 8
1 21 FEB 2024 Wed 2803 8 8 8 8 8 9 8 673 479 161 76 8 8 8 8 8 76 30 31 8 10 544 611 7
2 21 FEB 2024 Wed 918 8 8 8 8 8 9 8 171 121 49 25 8 8 8 8 8 28 12 35 8 8 172 185 7
select THREAD#, trunc(completion_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
, trunc(sum(blocks*block_size)/1024/1024) as MB
, sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time)
order by 2,1
;
THREAD# DATE NUM GB MB KB ---------- ----------- ---------- ---------- ---------- ---------- 1 08 MAR 2024 606 20 20782 21281002.5 2 08 MAR 2024 346 3 3396 3478454 1 09 MAR 2024 791 29 30327 31054968 2 09 MAR 2024 337 0 167 171143.5 1 10 MAR 2024 195 0 160 164815.5
==========
set pagesize 9999 linesize 120
column mb_arch format 9G999G999D99
column real_mb_in_period format 9G999G999D99column max_mb_in_period format 9G999G999D99
column min_mb_in_period format 9G999G999D99
column counted format 99G999D99
column counted_in_period format 99G999D99
define days_on_disk=15 -----<<<
select dag, mb_arch,
sum(mb_arch) over
( order by dag
range &days_on_disk preceding
) as real_mb_in_period,
counted,
sum(counted) over
( order by dag
range &days_on_disk preceding
) counted_in_period,
max(mb_arch) over
( order by dag
range &days_on_disk preceding
) * &days_on_disk as max_mb_in_period,
min(mb_arch) over
( order by dag
range &days_on_disk preceding
) * &days_on_disk as min_mb_in_period
from ( select trunc(completion_time) dag, sum(blocks * block_size)/1024/1024 mb_arch, count(*) counted from v$archived_log
where months_between(trunc(sysdate), trunc(completion_time)) <= 1
and completion_time < trunc(sysdate)
group by trunc(completion_time)
);===
set linesize 300 pagesize 300 with log_history as ( select to_char(trunc(first_time, 'HH24'),'YYYY-MM-DD HH24:MI') "Date" , count(1) "Total LFS per hour" FROM v$log_history where trunc(first_time) > = trunc(sysdate -14) GROUP by to_char(first_time, 'Dy'), to_char(trunc(first_time,'HH24'),'YYYY-MM-DD HH24:MI') order by to_date(to_char(trunc(first_time, 'HH24'),'YYYY-MM-DD HH24:MI'), 'YYYY-MM-DD HH24:MI') desc ), log_history_normalized as ( select snap_id, 'v$log_history' metric_source, 'Total LFS per hour' metric_name, "Total LFS per hour" delta_value from log_history lh, dba_hist_snapshot snap where lh."Date" = to_char(trunc(snap.begin_interval_time,'HH24'),'YYYY-MM-DD HH24:MI') ) select snap_id, metric_source, metric_name, delta_value from log_history_normalized where 1=1 order by snap_id ; SNAP_ID METRIC_SOURCE METRIC_NAME DELTA_VALUE ---------- ------------- ------------------ ----------- 41039 v$log_history Total LFS per hour 1 41039 v$log_history Total LFS per hour 1 41041 v$log_history Total LFS per hour 1 41041 v$log_history Total LFS per hour 1
=========set linesize 300 pagesize 300 col name for a90 col first_time for a16 select thread#,sequence#, name ,creator -- , to_char(first_time,'DD-MON HH24:MI') first_time , to_char(completion_time,'DD-MON HH24:MI') arc_completion,decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status from v$archived_log where 1=1 and first_time > sysdate -4 --and first_time > sysdate-1 -- and CREATOR!='LGWR' and SEQUENCE#>11468 -- and THREAD# =2 -- and name like '%+RECO01%' order by 5
====
from web
define 1=1
col num_days new_value num_days noprint
set feed on term on echo off tab off
prompt
prompt report how much space N days of archive logs consume
prompt for each day and the preceding (N-1) days
prompt
prompt the first (N-1) days of the report are inaccurate
prompt
prompt Calculate archive log sums for how many days? :
set term off feed off
select '&1' num_days from dual;
set term on feed on
col log_date format a20 head 'LOG DATE'
col bytes format 99,999,999,999,999
col bytes_today format 99,999,999,999,999
col bytes_Nday format 99,999,999,999,999
set pagesize 100 linesize 200 trimspool on
-- for repeated use a temp table of archive logs
-- is *much* faster
--define src_table='archlogs'
define src_table='v$archived_log'
with rawlogs as (
select distinct a.first_time, a.sequence#, a.thread#, a.block_size, a.blocks
from &src_table a
order by a.first_time, a.sequence#,a.thread#
),
logdaysums as (
select
trunc(r.first_time) log_date
, sum(block_size * blocks) bytes
from rawlogs r
group by trunc(r.first_time)
),
ndaysums as (
select
l.log_date
, &num_days days
, l.bytes bytes_today
, sum(l.bytes)
over (order by l.log_date rows (&num_days - 1) preceding) bytes_Nday
from logdaysums l
group by log_date, &num_days, bytes
order by l.log_date
)
select to_char(n.log_date,'yyyy-mm-dd') log_date, n.days, n.bytes_today, n.bytes_Nday
from ndaysums n
union all
select '== AVG N DAYS =====', null, null,null from dual
union all
select 'AVERAGES' log_date, n.days, avg(n.bytes_today) bytes_today, avg(n.bytes_Nday) bytes_Nday
from ndaysums n
group by 'AVERAGES', n.days
union all
select '== MEDIAN N DAYS ===', null, null,null from dual
union all
select 'MEDIAN' log_date, n.days, median(n.bytes_today) bytes_today, median(n.bytes_Nday) bytes_Nday
from ndaysums n
group by 'MEDIAN', n.days
union all
select '== MIN N DAYS =====', null, null,null from dual
union all
select to_char(n2.log_date,'yyyy-mm-dd') log_date, n2.days, null, n2.bytes_Nday
from ndaysums n2
where (n2.bytes_Nday) in (
select min(n3.bytes_Nday) bytes_Nday
from ndaysums n3
)
union all
select '== MAX N DAYS =====', null, null,null from dual
union all
select to_char(n2.log_date,'yyyy-mm-dd') log_date, n2.days, null, n2.bytes_Nday
from ndaysums n2
where (n2.bytes_Nday) in (
select max(n3.bytes_Nday) bytes_Nday
from ndaysums n3
)
/
set linesize 300 pagesize 300 col HOUR for a20 select to_char(first_time,'DD-MM-YYYY HH24:MI') as hour,count(*) as num_switches, round((sum(blocks*block_size)/1024/1024/1024)) "REDO PER DAY (GB)" from gv$archived_log where 1=1 and first_time >sysdate -7 --and first_time between to_date('05/07/2023 00:00:00','DD/MM/YYYY HH24:MI:SS') and to_date('05/07/2023 02:00:00','DD/MM/YYYY HH24:MI:SS') group by to_char(first_time,'DD-MM-YYYY HH24:MI') order by 1;
validate archivelog sequence xxxx;
3 comments:
set pagesize 300
select to_char(first_time,'DD-MM-YYYY HH24') as hour,count(*) as num_switches, round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from gv$archived_log
where 1=1
and first_time >sysdate -2
group by to_char(first_time,'DD-MM-YYYY HH24')
order by 1;
set pagesize 9999
col day format a15
SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM(SELECT TRUNC(First_Time) DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#
FROM gv$log_history GROUP BY TRUNC(First_Time)
ORDER BY 1 DESC
) A,
(SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM gv$log ) B
;
Post a Comment