一般巡检的操作

检查内存

grep "Physical:" /var/adm/syslog/syslog.log

或

# /usr/sbin/dmesg | grep "Physical:"

系统硬件配置检查

# /opt/ignite/bin/print_manifest

Show parameter sga

Show parameter pga

检查锁

----查询TX锁----

SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, 

       l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time,a.sql_text

    FROM v$locked_object l, all_objects o, v$session s,v$sqlarea a

   WHERE l.object_id = o.object_id

     AND l.session_id = s.sid

     and a.address=s.prev_sql_addr

ORDER BY sid, s.serial# ;

----查询TM锁----

select /*+ rule */ 

 lpad('--', decode(b.block, 1, 0, 4)) || s.username user_name,

 b.type,

 o.owner || '.' || o.object_name object_name,

 s.sid,

 s.serial#,

 decode(b.request, 0, 'BLOCKED', 'Waiting') status,

 t.SQL_TEXT

  from dba_objects             o,

       v$session               s,

       v$lock                  v,

       v$lock                  b,

       v$sqltext_with_newlines t

 where v.id1 = o.object_id

   and v.sid = s.sid

   and v.sid = b.sid

   and (b.block = 1 or b.request > 0)

   and v.type = 'TM'

   and t.ADDRESS = s.PREV_SQL_ADDR

order by status,s.sid;

----进程数

select count(*) from v$process;

select count(*) from v$session;

select count(*) from v$session where status='ACTIVE';

数据库总体性能检查

----缓冲区命中率

SELECT (1 - (SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /

(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) +

SUM(DECODE(NAME, 'consistent gets', VALUE, 0))))) * 100

"缓冲区命中率"

FROM V$SYSSTAT;

----数据字典命中率

SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "数据字典命中率"

FROM V$ROWCACHE;

----库缓存命中率

SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "库缓存命中率"

FROM V$LIBRARYCACHE;

----内存排序百分比

select a.value "磁盘排序",

b.value "内存排序",

round((100 * b.value) /decode((a.value + b.value), 0, 1, (a.value + b.value)),2) "内存排序百分比"

from v$sysstat a, v$sysstat b

where a.name = 'sorts (disk)'

and b.name = 'sorts (memory)';

----检查当前数据库的等待

select se.sid,s.serial#, s.username, se.event, se.wait_time, se.seconds_in_wait

  from v$session s, v$session_wait se

 where s.USERNAME is not null

   and se.SID = s.SID

   and s.STATUS = 'ACTIVE'

   and se.EVENT not like '%SQL*Net%'

      and   se.wait_time=0

 order by se.wait_time desc;

----表空间使用率 

 SELECT d.tablespace_name

             ,round((1 - nvl(free_space, 0) / space) * 100, 0) "used_rate(%)"

          FROM (SELECT tablespace_name

                      ,round(SUM(bytes) / (1024 * 1024), 0) space

                      ,SUM(blocks) blocks

                  FROM dba_data_files

                 GROUP BY tablespace_name) d

              ,(SELECT tablespace_name

                      ,round(SUM(bytes) / (1024 * 1024), 0) free_space

                  FROM dba_free_space

                 GROUP BY tablespace_name) f

         WHERE d.tablespace_name = f.tablespace_name(+)

        UNION ALL --if have tempfile

        SELECT D.TABLESPACE_NAME

               ,ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)"

          FROM (SELECT TABLESPACE_NAME

                      ,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE

                      ,SUM(BLOCKS) BLOCKS

                  FROM DBA_TEMP_FILES

                 GROUP BY TABLESPACE_NAME) D

              ,(SELECT TABLESPACE_NAME

                      ,ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE

                      ,ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE

                  FROM V$TEMP_SPACE_HEADER

                 GROUP BY TABLESPACE_NAME) F

         WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

ORDER BY "used_rate(%)" DESC;

------crs状态，转到oracle用户执行，遇到unkown一般不需要调整，一般已经在正常工作了。

------cmmb有时会出现crs_stat -t刷不出来的情况，这时候需要留意是不是运行有问题了。

crs_stat -t

------crs状态

crsctl check crs