nums

 WITH nums as (
Select 'C'||Rownum rn
From dual Connect By Rownum < 12 )
select decode (rn, 'C1', 'LogFile Size - Count : '|| (
select case when (
select count( distinct bytes)
from v$log ) = 1 then (
select round(bytes/(1024*1024),2) ||' MB' || ' - ' || to_char(count(*))||' Logs , Members '|| max(members)||' Each'
from v$log a
where thread#=userenv('instance') group by bytes ) else (
select '** ' || sum(count(distinct bytes )) || ' Mixed files** - ' || sum(count(*)) || ' MinSize ' ||round(min(bytes/1024/1024/1024),2) ||'G - '|| round(max(bytes/1024/1024/1024),2) ||' G'
from v$log
where thread#=userenv('instance') group by bytes ) end logs
from dual ), 'C2', 'Logfile Sync Resp : '|| (
select round(10*m.time_waited/nullif(m.wait_count,0),2) || ' ms'
from v$eventmetric m, v$event_name n
where m.event_id=n.event_id and n.name = 'log file sync' and m.wait_count > 0 ), 'C3', 'Logfile Prll Write Resp : '|| (
select round(10*m.time_waited/nullif(m.wait_count,0),2) || ' ms'
from v$eventmetric m, v$event_name n
where m.event_id=n.event_id and n.name = 'log file parallel write' and m.wait_count > 0 ), 'C4', 'No.of Switches in Last hour : '|| (
SELECT COUNT(*) "#ofSwitches"
FROM V$LOG_HISTORY a
where first_time > SYSDATE - (60 / (24 * 60)) and thread#=userenv('instance') ), 'C5', 'Active/TotalLogs : ' || (
select Activelogs||' / '|| totallogs
from
(select count(*) ActiveLogs
from v$Log
where status in ( 'ACTIVE','CURRENT') and thread#=userenv('INSTANCE')), (
select count(*) TotalLogs
from v$log
where thread#=userenv('INSTANCE')) b ), 'C6', 'Standby Status : '|| (
select case when stdby_cnt > 0 THEN 'YES : ' ||stdby_cnt || ' ' || (
select listagg(process ||'-'||count(process) ,' ') within group ( order by process)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by process ) || ' ' || (
select listagg(transmit_mode ||'-'||count(transmit_mode)||' ',' ') within group ( order by transmit_mode)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by transmit_mode ) || ' ' || (
select listagg(decode(affirm,'NO','NOAFFRIM','YES','AFFIRM' ) ||'-'||count(affirm) ,' ') within group ( order by affirm)
from v$archive_dest
where status ='VALID' and target='STANDBY' group by affirm ) ELSE 'NO' END standby_status
from (
select count(*) stdby_cnt
from v$archive_dest
where status ='VALID' and target ='STANDBY' ) ), 'C7', 'Log Buffer : '|| (
select round(value/(1048576),0) || ' MB'
from v$parameter
where name ='log_buffer' ), 'C8', 'Redo log Space Requests : '|| (
SELECT decode(value,0,'NO', value || ' - YES *** Warning *** ')
FROM v$sysstat
WHERE name = 'redo log space requests' ), 'C9', 'Redo Latch Allocation Ratio : '|| (
select decode(rratio,0,'OK', rratio ||' *** Warning *** chk 457966.1 _log_parallelism_max')
from (
SELECT round(immediate_misses/(immediate_gets + immediate_misses) * 100,0) rratio
FROM v$latch l, v$latchname ln
WHERE ln.name = 'redo allocation' AND ln.latch# = l.latch#) ), 'C10', 'Rman Backup Sessions : '|| (
select decode(count(*),0,'NONE','YES - Backup Sessions '|| count(*))
from v$session
where ( CLIENT_INFO LIKE 'rman%' or PROGRAM like 'rman%' or client_info like '%RMAN%') and status ='ACTIVE' ),
--'C10', 'Total Archive Dest - Standby   : '||
--(
----select Arcdest ||' -  ' || StandbyDest
--from ( select count(*) Arcdest from v$archive_dest where status !='INACTIVE' and  target ='PRIMARY' ) a ,
--(select count(*) StandbyDest from v$archive_dest  where status !='INACTIVE' and  target ='STANDBY')
--),
'C11', 'Not Archived Logs : '|| (
select case when redolog > 1 then '*** Warning ** '||redolog else ' '|| redolog end if
from (
select count(*) redolog
from v$log
where archived ='NO' and thread# = userenv('instance') and not exists (
select 1
from v$database
where log_mode ='NOARCHIVELOG') ) )
---'C8', 'Hotbackup- Db Files            : '||
--(
--SELECT /*+ rule */  decode(count(*),0,'NO', count(*) || ' - YES *** Warning *** ')  FROM v$backup
--where status ='ACTIVE'
--),
)"Instance - Redo Information"
from nums