Performing Database health checks

 Performing Database health checks, when there is an issue reported by Application users.

1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database

1. Check the Database details :

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

2. Monitor the consumption of resources :

select * from v$resource_limit where resource_name in ('processes','sessions');

The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.

3. Check the Alert Log :

$locate alert_<ORACLE_SID>
--- OR ---
UNIX/Linux command to locate the alert log file
-----------------------------------------------
$ find / -name 'alert_*.log' 2> /dev/null
vi <alert_log_location_of_the_above_output>
shift+g
?ORA-   ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.
:q! --and press enter, for exiting vi editor

11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory

Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory

4. Check Listener log :

$locate listener.log
--- OR ---
UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi <listener.log>
shift+g
?TNS-    ---> press enter key
press 'n' to check backwords and 'N' for forword search.
AND
shift+g
?error   ---> press enter key
press 'n' to check backwords and 'N' for forword search.
:q! --and press enter, for exiting vi editor
--- OR ---
$lsnrctl status
from the output you can get the listener log location (see the value for "Listener Log File" in the output).

5. Check Filesystem space Usage :

df -h (Linux / UNIX)
df -g (AIX)

6. Generate AWR Report :

Generate AWR report for current and before to compare
SQL> @?/rdbms/admin/awrrpt.sql        (For RAC,  @?/rdbms/admin/awrrpti.sql - for each instance)
If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ---->   Produces Workload Repository Compare Periods Report

7. Generate ADDM Report :

Generate ADDM report for current and before to compare.
ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql     (For RAC,  @?/rdbms/admin/addmrpti.sql - for each instance)

8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :

Select * from v$lock;
Select * from gv_$lock;  (For RAC)

A fast way to check blocking/waiting situations
-----------------------------------------------

SELECT * FROM v$lock WHERE block > 0 OR request > 0;

set pages 50000 lines 32767

select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12

SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;

Blocker Session and Waiting sessions:

column Username format A15 

column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'

column Request format 990 heading 'REQ' 

column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup

SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;



To find waiters:

set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;

                            
Blocking details:

set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20

select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2; 


set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;