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
select * from v$logfile order by group#; select * from v$log order by SEQUENCE#; select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;
select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;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
COL lgs_dest_id FOR 9999 HEAD "ID" COL lgs_dest_name FOR a20 HEAD "Name" COL lgs_target FOR a10 HEAD "Target" COL lgs_status FOR a10 HEAD "Status" COL lgs_destination FOR a30 HEAD "Destination" SELECT a.dest_id lgs_dest_id, a.dest_name lgs_dest_name, a.target lgs_target, a.status lgs_status, a.destination lgs_destination FROM v$archive_dest a, v$archived_log b WHERE a.dest_id=b.dest_id AND a.status='VALID' GROUP by a.dest_id,a.dest_name,a.status,a.destination,a.target; set feedback on set feedback off COL lgs_date FOR a10 HEAD "Day" COL lgs_blocks FOR 99999999 HEAD "GB/day" COL lgs_logs FOR 99999 HEAD "Log/day" COL lgs_switch FOR 99999 HEAD "Switch/day" COL lgs_00 FOR a4 HEAD "00" JUSTIFY RIGHT COL lgs_01 FOR a4 HEAD "01" JUSTIFY RIGHT COL lgs_02 FOR a4 HEAD "02" JUSTIFY RIGHT COL lgs_03 FOR a4 HEAD "03" JUSTIFY RIGHT COL lgs_04 FOR a4 HEAD "04" JUSTIFY RIGHT COL lgs_05 FOR a4 HEAD "05" JUSTIFY RIGHT COL lgs_06 FOR a4 HEAD "06" JUSTIFY RIGHT COL lgs_07 FOR a4 HEAD "07" JUSTIFY RIGHT COL lgs_08 FOR a4 HEAD "08" JUSTIFY RIGHT COL lgs_09 FOR a4 HEAD "09" JUSTIFY RIGHT COL lgs_10 FOR a4 HEAD "10" JUSTIFY RIGHT COL lgs_11 FOR a4 HEAD "11" JUSTIFY RIGHT COL lgs_12 FOR a4 HEAD "12" JUSTIFY RIGHT COL lgs_13 FOR a4 HEAD "13" JUSTIFY RIGHT COL lgs_14 FOR a4 HEAD "14" JUSTIFY RIGHT COL lgs_15 FOR a4 HEAD "15" JUSTIFY RIGHT COL lgs_16 FOR a4 HEAD "16" JUSTIFY RIGHT COL lgs_17 FOR a4 HEAD "17" JUSTIFY RIGHT COL lgs_18 FOR a4 HEAD "18" JUSTIFY RIGHT COL lgs_19 FOR a4 HEAD "19" JUSTIFY RIGHT COL lgs_20 FOR a4 HEAD "20" JUSTIFY RIGHT COL lgs_21 FOR a4 HEAD "21" JUSTIFY RIGHT COL lgs_22 FOR a4 HEAD "22" JUSTIFY RIGHT COL lgs_23 FOR a4 HEAD "23" JUSTIFY RIGHT prompt prompt ===================================================================================================================================================== prompt Log switch's history map prompt ===================================================================================================================================================== WITH archived_logs AS ( SELECT min(dest_id) DEST_ID,al.sequence#,al.BLOCKS,al.BLOCK_SIZE FROM v$archived_log al GROUP BY al.sequence#,al.blocks,al.block_size) SELECT SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),1,10) lgs_date, round(sum(al.BLOCKS*al.BLOCK_SIZE)/1024/1024/1024,0) lgs_blocks, COUNT(lh.first_time) lgs_switch, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'00',1,0)),'9999'))),3) lgs_00, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'01',1,0)),'9999'))),3) lgs_01, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'02',1,0)),'9999'))),3) lgs_02, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'03',1,0)),'9999'))),3) lgs_03, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'04',1,0)),'9999'))),3) lgs_04, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'05',1,0)),'9999'))),3) lgs_05, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'06',1,0)),'9999'))),3) lgs_06, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'07',1,0)),'9999'))),3) lgs_07, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'08',1,0)),'9999'))),3) lgs_08, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'09',1,0)),'9999'))),3) lgs_09, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'10',1,0)),'9999'))),3) lgs_10, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'11',1,0)),'9999'))),3) lgs_11, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'12',1,0)),'9999'))),3) lgs_12, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'13',1,0)),'9999'))),3) lgs_13, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'14',1,0)),'9999'))),3) lgs_14, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'15',1,0)),'9999'))),3) lgs_15, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'16',1,0)),'9999'))),3) lgs_16, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'17',1,0)),'9999'))),3) lgs_17, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'18',1,0)),'9999'))),3) lgs_18, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'19',1,0)),'9999'))),3) lgs_19, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'20',1,0)),'9999'))),3) lgs_20, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'21',1,0)),'9999'))),3) lgs_21, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'22',1,0)),'9999'))),3) lgs_22, lpad(rtrim(ltrim(TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),12,2),'23',1,0)),'9999'))),3) lgs_23 FROM V$log_history lh, archived_logs al WHERE lh.first_time > sysdate - 30 AND lh.sequence#=al.sequence# GROUP BY SUBSTR(TO_CHAR(lh.first_time, 'dd.mm.yyyy hh24mi'),1,10) ORDER BY to_date(lgs_date, 'dd.mm.yyyy hh24mi'); Day GB/day Switch/day 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 ---------- --------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 05.08.2024 350 50 0 0 0 0 0 0 0 0 0 0 0 1 4 4 4 4 4 4 5 4 4 4 4 4 06.08.2024 1087 108 9 4 7 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 07.08.2024 898 104 8 7 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 4 4 4 4 4 08.08.2024 921 105 8 5 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 09.08.2024 976 104 8 4 4 6 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 4 4 4 4 4 10.08.2024 493 97 4 4 4 4 4 4 4 4 4 4 5 4 4 4 4 4 4 4 4 4 4 4 4 4 11.08.2024 491 97 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 5 4 4
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;******************************* -- redo per hour set pagesize 100 col stat_name format a30 col MB format 999,999,999.99 select btime, stat_name, round((end_value-beg_value)/(1024*1024),2) MB from ( select e.stat_name, to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI') btime, e.value end_value, Lag (e.value) OVER( PARTITION BY e.stat_name ORDER BY s.snap_id) beg_value from DBA_HIST_SYSSTAT e, DBA_HIST_SNAPSHOT s where s.snap_id=e.snap_id and s.begin_interval_time > sysdate -2 and e.stat_name = 'redo size' order by e.stat_name, begin_interval_time ) where end_value-beg_value > 0 order by btime;
--query shows time between log switches select b.recid, to_char(b.first_time,'dd-mon-yy hh24:mi:ss') start_time, a.recid, to_char(a.first_time,'dd-mon-yy hh24:mi:ss') end_time, round(((a.first_time-b.first_time)*25)*60,2) minutes from v$log_history a, v$log_history b where a.recid = b.recid+1 --and a.first_time between to_date('2024-05-29:08:00:00','yyyy-mm-dd:hh24:mi:ss') and to_date('2024-05-29:17:00:00','yyyy-mm-dd:hh24:mi:ss') and a.first_time > sysdate -1 order by a.first_time asc
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