Oracle database monitoring scripts

$ contab -l
 0 * * * * sh /oradb/report/monitor.sh

2. vi /oradb/report/monitor.sh

. /oradb/u01/oracle/PROD/11.2.0/db_1/PROD_mustak.env
cd /oradb/report
/oradb/u01/oracle/PROD/11.2.0/db_1/sqlplus -s sys/manager as sysdba <<EOFSQL>/oradb/report/HealthCheck.log
!date
@report.sql
df -h >> /oradb/report/size.log
exit
EOFSQL
mailx -s "DATABASE REPORT FROM PROD" dbamustak@gmail.com  < /oradb/report/HealthCheck.log

vi /oradb/report/report.sql

set pagesize 1100
set markup html on spool on
spool Dailycheck.html
set feedback off
set pages 50
set lines 1000
set pages 70
set heading on
PROMPT================================================================
prompt
PROMPT                            

PROMPT                    Daily_Monitoring Checkup Report
prompt =================================================================================================================================================
PROMPT  REPORT DATE
select to_char(sysdate,'DD-MON-YYYY:HH:MI:SS') "Report Run Date"  from dual;
--
prompt DATABASE NAME
PROMPT ==============
select instance_name,host_name,version,status from gv$instance ,v$database a;
--

PROMPT DATABASE INFO
PROMPT =============
select     NAME,
    CREATED,
    LOG_MODE,
    CHECKPOINT_CHANGE#,
    ARCHIVE_CHANGE#
from     v$database;

select status from v$instance;

prompt TABLESPACE INFO
prompt ===================
column tablespace_name for a30
column INITIAL_SIZE    for 999999999.99
column tbfree    for 99999.99
column Largest   for 99999.99
column ratio     for  9999.99
column FREE_SPACE  for 99999.99
SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace - fs.freespace) used,
fs.freespace free,
100 * (fs.freespace / df.totalspace) pct_free
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name(+);
PROMPT   SESSION INFORMATION
PROMPT ========================
select status,count(status) Count from v$session group by status;

PRoMPT HIT RATIOS
PROMPT =============
PROMPT SORT STATISTICS  THIS SHOULD BE MORE THAN 95 %
SELECT (1-d.VALUE/m.value)*100 "SORT RATIO " FROM V$SYSsTAT d,v$sysstat m
WHERE d.name ='sorts (disk)' and m.name='sorts (memory)';

PROMPT DICTIONARY HIT RATIO
PROMPT Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora
PROMPT ======================
select (1-(sum(getmisses)/sum(gets)))*100 "OVER ALL DICT HIT RATIO" from v$rowcache;

PROMPT  THE OVER ALL HITRATIO OF THE LIBRARY CACHE
select sum(gethitratio)/count(*) *100 " LIBRARY CACHE HIT RATIO " from v$librarycache;

PROMPT BUFFER HIT RATIO
PROMPT =================
SELECT (1-PHY.VALUE/(cur.value+con.value))*100 " BUFFER CACHE HIT RATIO " from v$sysstat phy,v$sysstat con,v$sysstat cur
where cur.name='dbck gets'
and con.name='consistents'
and phy.name='physicalds';

PROMPT  INDEX LOOK UP RATIO
PROMPT =====================
SELECT (1-l.VALUE/(l.value+s.value))*100 "INDEX LOOK UP RATIO " FROM V$SYSSTAT l ,v$sysstat s
WHERE s.name ='table scans (short tables)' and l.name= 'table scans (long tables)';

--
PROMPT DATABASE SIZE
PROMPT ================
PROMPT TOTAL SIZE OF A DATABASE
select sum(bytes)/1024/1024/1024 "Physical Database Size" ,' GB '
        from dba_data_files ;

PROMPT ACTUAL SIZE OF DATABASE
select sum(bytes)/1024/1024/1024 "Actual Database Size", ' GB '
        from dba_segments ;

PROMPT   INVALID OBJECTS
PROMPT =====================
Select count(*) "INVALID OBJECTS",OWNER,object_type  from all_objects where status='INVALID' group by owner,object_type order by 2;


----
PROMPT  REDO LOGS AND ARCHIVE STATUS
PROMPT ==================================
COLUMN member_name HEADING 'Member_Name';
COL MEMBER FOR A40;
SELECT vlf.member "member_name",
       vl.group# "Group",
       vl.status "Status",
       vl.archived "Archived",
       vl.bytes / 1024 "Size (K)",
       vl.sequence# "Sequence"
  FROM v$logfile vlf,
       v$log vl
 WHERE vlf.group# = vl.group#
ORDER BY 1, vl.group#, vlf.member;

--
PROMPT  Session I/O By User
PROMPT ==================================

select    nvl(ses.USERNAME,'ORACLE PROC') username,
    OSUSER os_user,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
    BLOCK_GETS,
    CONSISTENT_GETS,
    BLOCK_CHANGES,
    CONSISTENT_CHANGES
from    v$session ses, 
    v$sess_io sio
where     ses.SID = sio.SID
order     by PHYSICAL_READS, ses.USERNAME;

---
prompt SEGMENTS HAVING LESS THAN 45 FREE EXTENTS
PROMPT ========================================
SELECT owner, segment_name, segment_type, extents, max_extents, next_extent,
       initial_extent
  FROM dba_segments
 WHERE max_extents - extents < 45;

--
PROMPT LOCK INFORMATION
PROMPT ===================
select    OS_USER_NAME os_user,
    PROCESS os_pid,
    ORACLE_USERNAME oracle_user,
    l.SID oracle_id,
    decode(TYPE,
        'MR', 'Media Recovery',
        'RT', 'Redo Thread',
        'UN', 'User Name',
        'TX', 'Transaction',
        'TM', 'DML',
        'UL', 'PL/SQL User Lock',
        'DX', 'Distributed Xaction',
        'CF', 'Control File',
        'IS', 'Instance State',
        'FS', 'File Set',
        'IR', 'Instance Recovery',
        'ST', 'Disk Space Transaction',
        'TS', 'Temp Segment',
        'IV', 'Library Cache Invalidation',
        'LS', 'Log Start or Switch',
        'RW', 'Row Wait',
        'SQ', 'Sequence Number',
        'TE', 'Extend Table',
        'TT', 'Temp Table', type) lock_type,
    decode(LMODE,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive', lmode) lock_held,
    decode(REQUEST,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive', request) lock_requested,
    decode(BLOCK,
        0, 'Not Blocking',
        1, 'Blocking',
        2, 'Global', block) status,
    OWNER,
    OBJECT_NAME
from    v$locked_object lo,
    dba_objects do,
    v$lock l
where     lo.OBJECT_ID = do.OBJECT_ID
AND     l.SID = lo.SESSION_ID;

-----

PROMPT HIGH RESOURCE CONSUMING SQL
PROMPT ===========================
select sql_text,
       username,
       disk_reads_per_exec,
       buffer_gets,
       disk_reads,
       parse_calls,
       sorts,
       executions,
       rows_processed,
       hit_ratio,
       first_load_time,
       sharable_mem,
       persistent_mem,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
(select sql_text ,
        b.username ,
 round((a.disk_reads/decode(a.executions,0,1,
 a.executions)),2)
       disk_reads_per_exec,
       a.disk_reads ,
       a.buffer_gets ,
       a.parse_calls ,
       a.sorts ,
       a.executions ,
       a.rows_processed ,
       100 - round(100 *
       a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
       a.first_load_time ,
       sharable_mem ,
       persistent_mem ,
       runtime_mem,
       cpu_time,
       elapsed_time,
       address,
       hash_value
from
   sys.v_$sqlarea a,
   sys.all_users b
where
   a.parsing_user_id=b.user_id and
   b.username not in ('sys','system')
order by 3 desc)
where rownum < 21;


PROMPT Scheduled Job Status
PROMPT ====================

set pagesize 1000
SELECT log_id, job_name, status,
EXTRACT(HOUR FROM RUN_DURATION)||':'||EXTRACT(MINUTE FROM RUN_DURATION)||':'||EXTRACT(SECOND FROM RUN_DURATION) RUN_DURATION,
to_char(log_date,'DD-MON-YYYY HH24:MI') log_date FROM dba_scheduler_job_run_details
WHERE owner='STAGING' AND TO_CHAR(LOG_DATE,'DD-MON-YYY') = TO_CHAR(SYSDATE, 'DD-MON-YYY');

PROMPT RMAN BACKUP Status
PROMPT ====================


col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

PROMPT TEMP TABLESPACE Status
PROMPT ====================
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIM
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

rompt ******************************************************************************************************
prompt number of tables analyzed in the last 5 days .. we have problem
prompt tHe below count indicates that tables which had their stats gathered
prompt ******************************************************************************************************
select count(*) from dba_tables where last_analyzed > sysdate-5;
prompt ******************************************************************************************************

prompt db users who are expired or locked in last 7 days
prompt *************************************************************************************************************
select username , ACCOUNT_STATUS from dba_users where ACCOUNT_STATUS NOT LIKE 'OPEN' and EXPIRY_DATE > sysdate -7;
spool off
exit