Database Health Check

OS Level:-
1. Check physical memory: free -m
2. Virtual memory: vmstat 5
3. top 10 process : top
4. ps -aux
5. Free volume available df-h, du -csh
6. Filesystem space: Under normal threshold.
7. Check the filesystem OS side
whether the sufficient space is available at all mount points.

Database level: 


1) Check extents / Proactive Space addition:
Check each of the Data, Index and temporary tablespaces for extend and blocks
allocation details.
SET LINES 500
SELECT SEGMENT_NAME,TABLESPACE_NAME, EXTENTS,BLOCKS
FROM DBA_SEGMENTS;

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME=’STAR01D’;

2) Check alert log for ORA- and warning messages:
Checking the alert log file on regulary basis, we have to do. Look for any of the oracle related errors.This will give you the error details and time of occurrence.

Look for the Database level or Tablespace level changes
Monitor the alert log file and search the file for each Day activities happening In the Database either it is bouncing of Database or Increase in the size of the tablespaces ,Increase in the size of the Database parameters. In the 11g database we can look for TNS errors in the alert log file.
1. Wait events
2. Long runing jobs from v$session_longops
3. Invalid objects( if found than compile them)
4. Temp usage / Rollback segment/PGA usage:
5. Redo generation/Archive logs generation details:

Make sure there should not be frequent log switch happening in a Database.