3 Replies Latest reply on Dec 26, 2012 5:29 PM by onedbguru


      Can any body tell me the RAC Monitoring Scripts .

      Thanks in Advance
        • 1. Re: RAC RELATED SCRIPTS
          Hi Friend,

          I have some scripts...

          Finding Locked Objects :

          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;

          Display Long Operation :
          SELECT s.inst_id,
          ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
          ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
          ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
          FROM gv$session s,
          gv$session_longops sl
          WHERE s.sid = sl.sid
          AND s.inst_id = sl.inst_id
          AND s.serial# = sl.serial#;

          Monitoring Memory:
          SELECT a.inst_id, NVL(a.username,’(oracle)’) AS username, a.module, a.program,
          Trunc(b.value/1024) AS memory_kb
          FROM gv$session a, gv$sesstat b, gv$statname c
          WHERE a.sid = b.sid AND a.inst_id = b.inst_id AND b.statistic# = c.statistic#
          AND b.inst_id = c.inst_id AND c.name = ‘session pga memory’
          AND a.program IS NOT NULL
          ORDER BY b.value DESC

          Monitor Session Undo Information :

          SELECT s.inst_id, s.username, s.sid, s.serial#, t.used_ublk, t.used_urec, rs.segment_name, r.rssize, r.status
          FROM gv$transaction t, gv$session s, gv$rollstat r, dba_rollback_segs rs
          WHERE s.saddr = t.ses_addr AND s.inst_id = t.inst_id AND t.xidusn = r.usn AND t.inst_id = r.inst_id AND rs.segment_id = t.xidusn
          ORDER BY t.used_ublk DESC;

          Monitoring Session Wait:

          SELECT s.inst_id, NVL(s.username, ‘(oracle)’) AS username, s.sid, s.serial#, sw.event, sw.wait_class, sw.wait_time, sw.seconds_in_wait, sw.state
          FROM gv$session_wait sw, gv$session s
          WHERE s.sid = sw.sid
          AND s.inst_id = sw.inst_id
          ORDER BY sw.seconds_in_wait DESC;

          Monitoring Session RAC :

          SELECT NVL(s.username, ‘(oracle)’) AS username, s.inst_id, s.osuser, s.sid, s.serial#, p.spid, s.lockwait, s.status, s.module, s.machine, s.program,
          TO_CHAR(s.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
          FROM gv$session s, gv$process p
          WHERE s.paddr = p.addr AND s.inst_id = p.inst_id
          ORDER BY s.username, s.osuser;

          At Database level: You have to monitor all the cluster logs, event logs, asm logs and rdbms logs

          Cluster (ORA_CRS_HOME) and all related log files:

          CRS alert log file
          CRS logs: log/hostname/crsd
          CSS logs: log/hostname/cssd
          EVM logs: log/hostname/evmd & /log/hostname/evm/log
          SRVM logs: log/hostname/client
          OPMN logs: opmn/logs
          Resource specific logs - /log/hostname/racg


          Resource spec logs - /log/hostname/racg
          SRVM logs- log/hostname/client


          alert_SID.log : location: ORACLE_HOME/rdbms/log

          Trace files:

          bdump - background_dump_dest
          cdump - core_dump_dest
          udump - user_dump_dest
          listener_<NODE>.log : ORACLE_HOME/network/log

          Hope it helps.....

          • 2. Re: RAC RELATED SCRIPTS
            Thanks for responding my post, if you have any links please send it to me
            • 3. Re: RAC RELATED SCRIPTS
              Configure OEM Database or GRID control. This will make your job a LOT easier. Read the documentation.