Routine Health Check Up of Your Oracle Database

Being the DOCTOR of DATABASES, doing a routine checkup on your databases is mandatory. Here are a few steps and associated queries to check the health of your database.

OS Level Checks

1. If the instance and associated background processes are up and running.

$ps -ef |grep <instance_name>

2. If the listeners are up and running.

$lsnrctl status

3. Check for alert log errors from bdump.

$tail -1000 <alertlog location> | grep ORA

4. Check the Trace Files for any User errors.

Take the trace files of the relevant timestamp from udump, when the database needs to be analyzed. But these are non readable files. So tkprof utility is used to analyze the file

$tkprof oraclesid_ora_XXXX.trc output.tkf

5. Filesystem Space Issues.

Check if all filesystems are adequately spaced, esp the backup,archive and data.

$df -h

5. Analysing CPU/IO load and Memory resources.

mpstat,iostat,prstat and sar commands

Database Level Checks:

======================

1.Space Monitoring – To check % used space in a tabelspace.This query gives TBS which crosses 80% utilization.

SQL>select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 80;

2. Check for BAD/ BROKEN jobs.

SQL>select JOB,BROKEN,WHAT from dba_jobs where broken =’Y’;

3. Check for Invalid objects.

SQL>select OBJECT_NAME from dba_objects where STATUS =’INVALID’;

4.Check audit records to track the actities done in a database.

To do this, auditing should be enabled in the database.

Major operations like drop,delete,truncate,create,alter needs to be checked.

SQL>select to_char(timestamp, ‘Mon-DD-YYYY’) ,action_name,OBJ_NAME,owner,count(*) from dba_audit_trail group by action_name,to_char(timestamp, ‘Mon-DD-YYYY’),OBJ_NAME,owner order by to_char(timestamp, ‘Mon-DD-YYYY’) ;

5. Frequency of redo/archive generation- This gives redo switch frequency if crosses 70.

SQL> select INST_ID,trunc(first_time),count(*) total_logs from gV$LOGHIST

where trunc(first_time) > sysdate -30

group by INST_ID, trunc(first_time)

having count(*) >70

order by 1,2;

6. Check the if backup taken is upto date.

select START_TIME,END_TIME INPUT_BYTES,STATUS,INPUT_TYPE from v$rman_backup_job_details

where STATUS<>’COMPLETED’;

Article by: Divya