$ 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