Health Checks

<- TroubleShooting

Daily

• Are the OS and system hardware functioning OK (SAN/RAID, CPU, Network, RAM)?
• Is the database up? Was it down for any reason in the last 24 hours?
• Can client apps connect to the database?
• When was the last RMAN incremental backup run and what were the results?
• Are there any database errors that you need to know about?
  • Alert Log
    SQL To determine Alert Log location:
    SELECT value FROM v$diag_info WHERE name='Diag Trace';
    alias alog='tail -f /u01/app/oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log'
    Common Path:
    %ORACLE_BASE%\diag\rdbms\{DBNAME}\{DBNAME}\trace\alert_{DBNAME}.log
  • Listener Log
    To determine location:
    OS> lsnrctl status
    Common Path:
    %ORACLE_BASE%\diag\tnslsnr\{DBNAME}\listener\trace\listener.log
• Is there ample disk space for the datafiles, archive logs, export files and RMAN files?
• Did the daily scheduled jobs run OK?
• Review any security provided monitoring reports for inappropriate changes (Tripwire etc.).

Weekly

• When was the last RMAN full backup run and what were the results?
• When was the last RMAN database validation run and what were the results?
• When was the last full database export and what were the results?
• How often are redo log switches occurring?
• Are the number of connections increasing? If so by how much?
• Are all the blocks in your datafiles OK (not just the ones with data)?
• Is any partition maintenance required (extending ranges etc.)?
• Does any of your documentation need to be updated?
• Did the weekly scheduled jobs run OK?

Monthly

• Are all your database patches up-to-date?
• Are your indexes OK or do they need to be rebuilt?
• Which tablespaces are growing, and by how much?
• Is the size of any schema increasing? If so by how much?
• Is your SGA and PGA usage increasing? If so by how much?
• What SQL is being run most often and can it be made more efficient?
• Is there a significant increase in DML (INSERTS, UPDATES and DELETES)?
• Are the segments in your tablespaces OK or do they need to be optimized?
• Can the maintenance of any the databases be simplified?
• Did the monthly scheduled jobs run OK?

Appendix: Using SQL to Check Alert Log (11g and later)

SELECT inst_id, originating_timestamp, message_text
FROM TABLE(gv$(cursor(SELECT inst_id, originating_timestamp, message_text 
                      FROM v$diag_alert_ext
                      WHERE originating_timestamp > (sysdate - 1)
                      AND   message_text LIKE '%ORA-%')))
ORDER BY inst_id, originating_timestamp;

<- TroubleShooting