check

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 9. Check for alerts in OEM 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_ --- OR --- UNIX/Linux command to locate the alert log file ----------------------------------------------- $ find / -name 'alert_*.log' 2> /dev/null vi 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 --- OR --- 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 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; WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 --------------- --------------- --------- --------- -------------- -------- -------- 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; BLOCKER SID 'ISBLOCKING' BLOCKEE SID ------- ---------- ---------- ------- -------- 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; 9. Check for alerts in OEM :- ============================ Login to Oracle Enterprise Manager with valid username and password click on "Alerts" tab then select the below tabs one by one to see the alerts generated Targets Down/Critical/Warning/Errors/