DB Monitoring scripts

select trunc(completion_time) rundate
,count(*)  logswitch
,round((sum(blocks*block_size)/1024/1024/1024)) "REDO PER DAY (GB)"
,min(completion_time) "From Time"
,max(completion_time) "Up to Time"
from gv$archived_log
where completion_time between TO_DATE ('2017-05-20 01:00:00', 'YYYY-mm-dd HH24:MI:SS') 
                          AND TO_DATE('2017-05-20  20:40:00', 'YYYY-mm-dd HH24:MI:SS')
                                                    group by trunc(completion_time)                                                                                                
order by 1 desc;

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

If you are using data guard exclude 2nd destination logs.

select trunc(completion_time) rundate
,count(*)  logswitch
,round((sum(blocks*block_size)/1024/1024/1024)) "REDO SIZE SO FAR (GB)"
,min(completion_time) "From Time"
,max(completion_time) "Up to Time"
from gv$archived_log
WHERE NAME!='standb'
AND completion_time between TO_DATE ('2017-05-22 01:00:00', 'YYYY-mm-dd HH24:MI:SS') 
                          AND TO_DATE('2017-05-22  10:42:00', 'YYYY-mm-dd HH24:MI:SS')
                                                    group by trunc(completion_time)                                                                                                

order by 1 desc;

##############ACTIVE SESSIONS ON CPU#################



select
      count(*),
      CASE WHEN state != 'WAITING' THEN 'WORKING'
           ELSE 'WAITING'
      END AS state,
      CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
           ELSE event
      END AS sw_event
   FROM
      gv$session
   WHERE
       type = 'USER'
   AND status = 'ACTIVE'
   GROUP BY
      CASE WHEN state != 'WAITING' THEN 'WORKING'
           ELSE 'WAITING'
      END,
      CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
           ELSE event
      END
   ORDER BY
      1 DESC, 2 DESC
 
 
 
 
####################TOP WAIT EVENTS##############

select
      count(*),
      CASE WHEN state != 'WAITING' THEN 'WORKING'
           ELSE 'WAITING'
      END AS state,
      CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
           ELSE event
      END AS sw_event
   FROM
      v$session_wait
   GROUP BY
      CASE WHEN state != 'WAITING' THEN 'WORKING'
           ELSE 'WAITING'
      END,
      CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
           ELSE event
      END
   ORDER BY
      1 DESC, 2 DESC
   /
#################RMAN OVERALL PROGRESS##################

select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024/1024 input_GBs
     , output_bytes/1024/1024/1024 output_GBs
     , (output_bytes/input_bytes*100) compression
     , (mbytes_processed/dbsize_mbytes*100) "complete%"
     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
  from v$rman_status rs
     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
   and output_device_type is not null
   
   
 #####################SESSION CPU USAGE################


 SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/


################## ARCHIVED LOG COMPARISON #################

PRIMARY

select max(sequence#) from gv$archived_log


STANDBY


select max(sequence#) from gv$archived_log where applied='YES';


################### NUMBER OF ARCHIVED LOGS AND SIZE ################


select RECID, NAME, to_char(COMPLETION_TIME,'YYYY-MM-DD HH24:MI:SS'), ARCHIVAL_THREAD# , blocks * block_size/1024/1024 "Size MBs" from V$ARCHIVED_LOG WHERE 
IS_RECOVERY_DEST_FILE='YES' 
--AND STANDBY_DEST='YES'
 order by COMPLETION_TIME desc;


 ##############MAJOR WAIT EVENTS################


 SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";



################### LIBRARY CACHE WAITS ##############

Select sid, event, p1raw, seconds_in_wait, wait_time
From v$session_wait
Where event = 'library cache pin'
And state = 'WAITING';


##############Users sessions wait time and state: #################

SELECT NVL (s.username, '(oracle)') AS username, s.SID, s.serial#, sw.event,
sw.wait_time, sw.seconds_in_wait, sw.state
FROM v$session_wait sw, v$session s
WHERE s.SID = sw.SID
ORDER BY sw.seconds_in_wait DESC;


################Users and Sessions CPU consumption #########################

Set lines 1000
select ss.username, se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;

######################Users and Sessions CPU and I/O consumption ###############

-- shows Day wise,User wise,Process id of server wise- CPU and I/O consumption 
set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day 
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr 
and round((ss.value/100),0) > 10 
order by 8;


################Long running Jobs####################

select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message 
from v$session_longops 
where time_remaining = 0 
order by time_remaining desc;


####################INVALID JOBS#####################

select owner||' '||object_name||' '||created||' '||status from dba_objects where status='INVALID';



###################ANALYZING JOBS###################

select sid, job,instance from dba_jobs_running;

We can find out the failed jobs and Broken jobs details with the help of the Below query:

select job||' '||schema_user||' '||Broken||' '||failures||' '||what||' '||last_date||' '||last_sec from dba_jobs;

#####################Temp usage / Rollback segment/PGA usage###################

Set lines 1000
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

#####################TABLESPACE USAGE##############

set lines 1000
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

################PGA###############


select st.sid "SID", sn.name "TYPE",
ceil(st.value / 1024 / 1024/1024) "GB" 
from v$sesstat st, v$statname sn 
where st.statistic# = sn.statistic# 
and sid in 
(select sid from v$session where username like UPPER('&user'))
and upper(sn.name) like '%PGA%' 
order by st.sid, st.value desc;

##################LOG SWITCHES###############

set lines 120; 
set pages 999;
select to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history
group by to_char(first_time,'DD-MON-RR')
order by 1
/

##############Archive logs by dates##############

set lines 1000
select to_char(first_time,'DD-MON-RR') "Date",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') " 00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') " 01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') " 02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') " 03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') " 04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') " 05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') " 06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') " 07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') " 08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') " 09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') " 10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') " 11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') " 12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') " 13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') " 14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') " 15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') " 16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') " 17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') " 18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') " 19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') " 20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') " 21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') " 22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') " 23"
from v$log_history 
group by to_char(first_time,'DD-MON-RR')
order by 1
/

#################Archive log generation details Day-wise###########

select to_char(COMPLETION_TIME,'DD-MON-YYYY'),count(*) 
from v$archived_log group by to_char(COMPLETION_TIME,'DD-MON-YYYY') 
order by to_char(COMPLETION_TIME,'DD-MON-YYYY');

#################Archive log count of the day###########

select count(*)
from v$archived_log
where trunc(completion_time)=trunc(sysdate); 

###############I/O Generation:############

set linesize 140
col spid for a6
col program for a35 trunc
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day 
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr 
and round((ss.value/100),0) > 10 
order by 8;


###########WHAT KIND OF SQL SESSION IS USING##########
set lines 9999
set pages 9999

select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid order by piece;


##############DATA GUARD SYNC STATUS################


SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;


################ARCHIVELOG GAP #############

 SELECT * FROM V$ARCHIVE_GAP;


#############COMPARE PRIMARY AND STANDBY ARCHIVELOGS#########

Primary

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

Standby

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED ='YES'