数据库巡检

1.数据库巡检

    1.1 数据运行状态

    1.1.1 数据库运行状态

select count(*) from v$sessions where state = 'ACTIVE'

union all

select count(*) from v$sessions

union all

select count(*) from v$trxwait

union all

select count(*) from v$lock l ,SYSOBJECTS o where  l.TABLE_ID=O.ID and L.BLOCKED=1 ;

   1.1.2 数据库运行日志

[dmdba@dmdb ~]$ ps -ef | grep dms

dmdba      99939       1  2 13:36 pts/0    00:00:00 /home/dmdba/dmdbms/bin/dmserver path=/home/dmdba/dmdbms/data/DAMENG/dm.ini -noconsole

dmdba     100032   99869  0 13:36 pts/0    00:00:00 grep dms

[dmdba@dmdb ~]$ cd /home/dmdba/dmdbms/log

cat 日志文件 | grep -i error

cat 日志文件 | grep -i warn

    1.1.3 监视期运行

./dmmonitor /opt/dmdbms/data/DAMENG/dmmonitor.ini      -- 部署是可以建议客户设置别名 alise 

    

    1.1.4 DMHS延时

cd /opt/app/dmhs/bin

./dmhs_console

connect

cpt

state

    1.1.5 v$sql_history

select top 10  a.SESS_ID,a.TOP_SQL_TEXT,a.TIME_USED/1000/1000.0  from  SYS."V$SQL_HISTORY" a order by 3 ;

    1.2 os

    1.2.1 一周cpu idle变化

    1.2.2 一周内存变化

    1.2.3 swap使用情况

free -m

    1.2.4 I/O速度

iostat -dmx 

    1.3 存储

    1.3.1 文件系统

df -hl

    1.3.2 表空间使用

--tablespace status

select t.TABLESPACE_NAME ,DBA_DATA_FILES.FILE_ID,DBA_DATA_FILES.FILE_NAME , concat( cast(DBA_DATA_FILES.BYTES/power(2,20) as INTEGER),' MB') as size,

DBA_DATA_FILES.AUTOEXTENSIBLE ,DBA_DATA_FILES.STATUS ,DBA_DATA_FILES.ONLINE_STATUS

from SYS.DBA_DATA_FILES ,DBA_TABLESPACES t

where t.TABLESPACE_NAME  = DBA_DATA_FILES.TABLESPACE_NAME (+) ;

--tablespace usage

select c.ID ,c.NAME,c."TYPE$" ,c.MAX_SIZE,c."STATUS$" ,c.USED_SIZE  ,d.used_per||'%' as used_per  from V$TABLESPACE c join(

SELECT a.id,100-(sum(b.free_size)*100/sum(b.total_size)) used_per FROM V$TABLESPACE a,V$DATAFILE b

where a.id=b.GROUP_ID

group by a.id) d

on c.id=d.id

order by c.id;

https://eco.dameng.com