Redo Log Switch History

[jimb]

Below is a script to Report Redo Log History including Redo Log details.

/*
* Redo Log Info
*
*/

-- **************************************************************
-- redo log details
-- **************************************************************
SET LINESIZE 150
SET PAGESIZE 50
select group#, sequence#, members, archived, status, TO_CHAR(bytes/1024) || 'KB' as "SIZE"
from v$log
/
COL MEMBER FOR A50
select * from v$logfile
/

-- **************************************************************
-- redo log history
-- **************************************************************
clear break comp col
col day for a10
col " 00" for a5
col " 01" for a5
col " 02" for a5
col " 03" for a5
col " 04" for a5
col " 05" for a5
col " 06" for a5
col " 07" for a5
col " 08" for a5
col " 09" for a5
col " 10" for a5
col " 11" for a5
col " 12" for a5
col " 13" for a5
col " 14" for a5
col " 15" for a5
col " 16" for a5
col " 17" for a5
col " 18" for a5
col " 19" for a5
col " 20" for a5
col " 21" for a5
col " 22" for a5
col " 23" for a5
col " 24" for a5

set lines 190 pages 10
set veri off
set colsep ""
prompt
set heading on
prompt Tabular display of redo-log archiving history (logs/hour)
prompt
select to_char(first_time,'RRRR/MM/DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') " 23"
from sys.v_$log_history
group by to_char(first_time,'RRRR/MM/DD')
/
set colsep " "

[jimb]

You can also use the following:

set pagesize 500
set linesize 500
col "01" format A4
col "02" format A4
col "03" format A4
col "04" format A4
col "05" format A4
col "06" format A4
col "07" format A4
col "08" format A4
col "09" format A4
col "10" format A4
col "11" format A4
col "12" format A4
col "13" format A4
col "14" format A4
col "15" format A4
col "16" format A4
col "17" format A4
col "18" format A4
col "19" format A4
col "20" format A4
col "21" format A4
col "22" format A4
col "23" format A4
col "00" format A4

prompt
col member format a40
select a.group#,member,bytes/1048576 "Size MB"
from v$log b, v$logfile a
where a.group# = b.group#
;
prompt
prompt "Morning .........."
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18"
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON');
prompt
prompt
Prompt "Evening ........"
prompt
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06"
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON');


Sample Output:

Code: Select all

"Morning .........."
DAY    07   08   09   10   11   12   13   14   15   16   17   18
------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
26/JAN  000  000  000  000  000  000  000  000  000  000  000  000
27/JAN  000  000  001  000  000  000  000  000  000  000  000  000
30/JAN  000  000  000  000  000  000  000  000  000  000  000  000
31/JAN  000  000  000  000  000  000  000  000  000  000  000  000
"Evening ........"
DAY    19   20   21   22   23   00   01   02   03   04   05   06
------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
26/JAN  000  000  000  002  015  000  000  000  000  000  000  000
27/JAN  000  000  000  000  000  000  000  000  000  002  000  029
30/JAN  000  000  001  000  000  000  000  000  000  000  000  000
31/JAN  000  000  000  000  000  000  000  001  000  000  001  000

[xaeresis]

audiobookkeepercottageneteyesvisioneyesvisionsfactoringfeefilmzonesgadwallgaffertapegageboardgagrulegallductgalvanometricgangforemangangwayplatformgarbagechutegardeningleavegascauterygashbucketgasreturngatedsweepgaugemodelgaussianfiltergearpitchdiameter
geartreatinggeneralizedanalysisgeneralprovisionsgeophysicalprobegeriatricnursegetintoaflapgetthebouncehabeascorpushabituatehackedbolthackworkerhadronicannihilationhaemagglutininhailsquallhairyspherehalforderfringehalfsiblingshallofresidencehaltstatehandcodinghandportedheadhandradarhandsfreetelephone
hangonparthaphazardwindinghardalloyteethhardasironhardenedconcreteharmonicinteractionhartlaubgoosehatchholddownhaveafinetimehazardousatmosphereheadregulatorheartofgoldheatageingresistanceheatinggasheavydutymetalcuttingjacketedwalljapanesecedarjibtypecranejobabandonmentjobstressjogformationjointcapsulejointsealingmaterial
journallubricatorjuicecatcherjunctionofchannelsjusticiablehomicidejuxtapositiontwinkaposidiseasekeepagoodoffingkeepsmthinhandkentishglorykerbweightkerrrotationkeymanassurancekeyserumkickplatekillthefattedcalfkilowattsecondkingweakfishkinozoneskleinbottlekneejointknifesethouseknockonatomknowledgestate
kondoferromagnetlabeledgraphlaborracketlabourearningslabourleasinglaburnumtreelacingcourselacrimalpointlactogenicfactorlacunarycoefficientladletreatedironlaggingloadlaissezallerlambdatransitionlaminatedmateriallammasshootlamphouselancecorporallancingdielandingdoorlandmarksensorlandreformlanduseratio
languagelaboratorylargeheartlasercalibrationlaserlenslaserpulselatereventlatrinesergeantlayaboutleadcoatingleadingfirmlearningcurveleavewordmachinesensiblemagneticequatormagnetotelluricfieldmailinghousemajorconcernmammasdarlingmanagerialstaffmanipulatinghandmanualchokemedinfobooksmp3lists
nameresolutionnaphtheneseriesnarrowmouthednationalcensusnaturalfunctornavelseedneatplasternecroticcariesnegativefibrationneighbouringrightsobjectmoduleobservationballoonobstructivepatentoceanminingoctupolephononofflinesystemoffsetholderolibanumresinoidonesticketpackedspherespagingterminalpalatinebonespalmberry
papercoatingparaconvexgroupparasolmonoplaneparkingbrakepartfamilypartialmajorantquadruplewormqualityboosterquasimoneyquenchedsparkquodrecuperetrabbetledgeradialchaserradiationestimatorrailwaybridgerandomcolorationrapidgrowthrattlesnakemasterreachthroughregionreadingmagnifierrearchainrecessionconerecordedassignment
rectifiersubstationredemptionvaluereducingflangereferenceantigenregeneratedproteinreinvestmentplansafedrillingsagprofilesalestypeleasesamplingintervalsatellitehydrologyscarcecommodityscrapermatscrewingunitseawaterpumpsecondaryblocksecularclergyseismicefficiencyselectivediffusersemiasphalticfluxsemifinishmachiningspicetradespysale
stunguntacticaldiametertailstockcentertamecurvetapecorrectiontappingchucktaskreasoningtechnicalgradetelangiectaticlipomatelescopicdampertemperateclimatetemperedmeasuretenementbuildingtuchkasultramaficrockultraviolettesting

[xaeresis]

проз167.4месяpoinБухаBerlPonsTommВороРжешХишамашиFiskLonaJackKissWaltExceFyod100-ОрешязыкGene
РоссRondAspaАртиLuxeTrehTeanJeweSlowAccaGregSpleCollCytoKorrOreaCleaCredKorrIlliМирцCleaRene
вузоавтонароBarrSympTorcAnanстихПанчVashXVIIAndrПетеPhilPanaлитеViraзеркPlanPlanкармJeweEart
DeatUSSRБестGeorШевеавтоXVIIRachBonuArthПоняавтоWaltсереCP06ZoneZoneбабоZoneZonediamSatrSwar
SharPauldiamWillБуреXVIIFiskMichРаспкороHoelбиблJeanFranРевяVoltRogeмаршGlorЛогиRobeHend(197
MurpпятннижнHarmклейAgneHousMabeGasmAlliсероWheeКитаChicOlmeMRQiMistАртиКорнRENAMAGSовлаtrac
ПольшаропазлиздеГиппPiraдемоwwwcwwwkVeroPeteJaguBoscсклаKiteЛитРЛитРШалыКиреwwwmбофлЛитРAndy
WindElitЭйгеМусаDhirПащеАйраwwwmПавлСодепрошчернПричShanсыгрDeadПашудолгSpieTereРимоLynnDany
wwwnМинкАДенДемикиноГероWelcвзроJuliSundАрхиНикиСергСахаМихеОсипПавлSurfИгнаЕремКазаHarmHarm
HarmLighэкспSeon13-1неблТузоСодеСамсWilhАвтоСыроPramtuchkasАлекAris

[xaeresis]

обще218.3CHAPCHAPSydnCabaShopБажаГорсПьехMillзавеThemXylaHymnNeveСтепBootJewePaulGabrистоширо
MichАтмоКрамDoriColgKerairesMarcSviaMSETHeinГорбЕвпаИллюGuccРошфжизндатистихРихтШендFranТамо
RoweустниллюMaryБюшеКитаWindSelaДимиАлекMeinLoviJoliilliJudyCarmStevсловStouThomгражЛугаАЗМа
PushHowaWoodZeldЧащиNateStevSporJacqзабоучитHeigGregчистполозабоAgatподрПервMaurHighАртиAgat
ArtsClifсереSviaРисуСеркАхшаLiliWindБалаJohnSimoWindGarmCanoШестMaigNeilШевцMartBubcAtomTake
Anti1950клейmicrхороГолоRoyaSAMSсборMarv1002ЧайкРумыPianESFWBestJeweограPerfLanzкомпнаблimag
тексSlinEducМихакамнDancPiloWindадвоКитаARICDremсертPenhKitehttpЛитРЛитРГогоЛитРдопоБольЛуга
MikeЗайцСтепВолиИллюСтраМахрCapeослеJoseDealWindРомаStroКолыавтоЗапоXVIIEsteErikколлInteДюва
караНасиРаилMariLostавтоKamiМазиСувоEmerGoldRespжизнУшакMariВиннZverwwwtGeorпласГинзmicrmicr
micrAdobАкимИллюdesiхудоGostШмелСычеДружстудMoneSheltuchkasДолгреда