Oracle数据库监控

Posted 2023-10-12 Updated 2024-09- 9

By 已删除用户 Mr.chen

24~31 min read

前言

最近由于突然出现数据库突然挂掉的情况(磁盘空间满了,导致一直无法释放),所以结合其他部门人员一起讨论对数据库各方面状态的监控,得出了一套方案,在这里记录一下

介绍

呵呵哒,其实没啥方案,就是和其他部门一起把数据返回监控一下:

1. A部门是负责信息展示和监控告警的,当数据给定值超出之后,A部门就会监控到进行告警发送信息

2. B部门也就是我这,把相关数据库的一些信息查询后生成指定格式文件,放到指定的服务器中,A部门就取后解析进行监控

问题:

开始有两种取值方案,

第一种: 我们给A部门账号密码,他们自己进行查询取值,那这样也就没我啥事了!

(监控数据具有实时性,更加方便直接取直接展示,中间没有太多变故,能够即使的发现问题.但B部门却把账号密码保护了,有很大的安全隐患)

第二种: 我们自己把查好的值定时生成指定文件,然后放到指定服务器上, 然后A部门定时来取!

(不会透露较为重要的信息, 安全性相对较高, 但数据获取步骤变多,也增大了延时性和不确定性)

方案:

考虑到安全因素,最终采用第二种方案,由B部门定时生成指定格式文件,放到指定服务器目录,A部门定时进行拉取. 这里放文件的服务器当时也在商量是放在A部门还是B部门的, 最后是决定放在B部门中,也就是我们的服务器上,于是就给A部门创建了一个单独的账号仅供文件提取,至于定时生成的时间呢,就由我们自己决定了,因为是监控的自己的数据库,我们是主导,A部门是配合,A部门定时来获取的频率倒是很高,基本是每两秒的样子就提取走了

正文

下面是获取相关参数的sql或者命令,有些是不准确的,但当时确实讨论不出结果,只能就先这么顶着用了

数据库CPU利用率(数据库服务占用的CPU使用情况)

top -b -n 1 -c|grep 'oracle'|grep -v 'grep'|awk -v a=`cat /proc/cpuinfo| grep "processor"| wc -l` '{print $9/a,$10}' OFS=","

> 说明：

- top -b -n 1 -c

- 1）.top -b: ‘批量模式’，用来将输出重定向到指定文件，一般配合-n 指定输出几次统计信息

- 2）.top -c: 显示产生进程的完成命令

- grep 'java'|grep -v 'grep' 筛选出java 进程,这里就使用oracle

- awk -v a=`cat /proc/cpuinfo| grep "processor"| wc -l` '{print $9/a,$10}' OFS=","

- cat /proc/cpuinfo| grep "processor"| wc -l 为查询cpu个数

- awk -v a=`cat /proc/cpuinfo| grep "processor"| wc -l` 意思是将查询的cpu个数赋值给a

- '{print $9/a,$10}' 意思打印出第9列（%cpu）和第10列（%MEN）

- OFS="," 意思是输出的数值以，分割

--查看数据库名

select name,dbid from v$database
select name from  v$database

--实例名

 select instance_name from v$instance

--查看数据库端口(linux)

 lsnrctl status

查看cup个数: nproc /

查看每个cpu详情: cat /proc/cpuinfo

查看动态运行情况: /top

数据库内存利用率(数据库服务占用的内存使用情况)

free -g

说明:这是查看服务器系统的内存使用率,因为数据库是专门的一台服务器,所以就用这个命令

数据库磁盘信息

select free_mb from v$asm_diskgroup;

说明: 这块我不是很清楚,我看oracle[官网]的介绍是这个,然后也[查了一下]

是oracle系统视图ASM中的一个信息:

Automatic Storage Management (ASM) 将文件系统与卷管理器纵向集成在一起，这是一项专门为Oracle DB 文件建立的技术。使用ASM 可管理单个对称多处理(SMP) 计算机，或通过管理集群的多个节点来支持Oracle Real Application Clusters (RAC)

大概也就是各种磁盘组等等一些复杂集合,不过展示了总磁盘空间,也可以作为容量信息的一个参考

数据库表空间使用情况

 SELECT

	total.tablespace_name,

	round((( total.MB - free.MB ) / maxsize.mb ) * 100, 2 ) || '%' AS Used_Pct 

FROM

	( SELECT tablespace_name, sum( bytes ) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name ) free,

	( SELECT tablespace_name, sum( bytes ) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name ) total,

	( SELECT tablespace_name, sum( decode( maxbytes, 0, bytes, maxbytes )) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name ) maxsize 

WHERE

	free.tablespace_name = total.tablespace_name 

	AND maxsize.tablespace_name = total.tablespace_name UNION

SELECT

	D.TABLESPACE_NAME,

	ROUND( NVL ( USED_SPACE, 0 )/ maxsize * 100, 2 )|| '%' used_pct 

FROM

	(

	SELECT

		TABLESPACE_NAME,

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

		ROUND( SUM( decode( maxbytes, 0, bytes, maxbytes ))/( 1024 * 1024 ), 2 ) maxsize,

		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_pct DESC;

说明: 这里的表空间就是数据库存放东西的地方,它直接体现了数据的容量大小,关于介绍以及满后如何扩张可参考[文章1]

数据库连接使用情况

说明:一般数据库系统表中,gv代表全局连接(比如双机) v代表当前连接

#当前连接数

select  count(*) from v$process ;

#全局会话连接数

select inst_id,count(*) from gv$session group by inst_id;

#查询数据库允许最大连接数

select  value from v$parameter where name ='processes' ;

说明:如果是数据库集群,就用全局查,会展示每台的连接信息,如果使用了当前会话查询,那就返回总连接信息

锁表-sql语句长时间未提交

SELECT  s.inst_id

       ,S.SID

       ,S.SQL_ID

       ,S.SERIAL#

       ,S.USERNAME

       ,S.OSUSER 

       ,S.PROGRAM 

       ,S.EVENT

       ,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME

       ,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') START_DATE

       ,S.LAST_CALL_ET 

       ,S.BLOCKING_SESSION   

       ,S.STATUS

       ,( 

              SELECT Q.SQL_TEXT 

              FROM    V$SQL Q 

              WHERE  Q.LAST_ACTIVE_TIME=T.START_DATE 

              AND    ROWNUM<=1) AS SQL_TEXT   

FROM   gV$SESSION S, 

       gV$TRANSACTION T  

WHERE  S.SADDR = T.SES_ADDR 

and  S.STATUS='INACTIVE' --ACTIVE 指活跃状态的语句,正在执行,一般指客户端操作;INACTIVE 指不是正在执行中的状态,一般指程序中连接没有释放

and T.START_DATE < sysdate-1/24;

#如何觉得相应的sql不对,可以在根据sql_ID去查相应sql

SELECT * FROM v$sql where sql_id ='sql_id';

#查到后可以杀死进程(生成环境操作有风险)

--杀死进程

-- alter system kill session 'SID,SERIAL#'--参数替换上面查询的结果

上面的sql解释:

这个查询语句用于获取会话和事务的相关信息。以下是每个字段的含义：

s.inst_id：实例ID

S.SID：会话ID

S.SQL_ID：SQL语句ID

S.SERIAL#：会话序列号

S.USERNAME：用户名

S.OSUSER：操作系统用户名

S.PROGRAM：程序名称

S.EVENT：当前事件

TO_CHAR(S.LOGON_TIME,‘YYYY-MM-DD HH24:MI:SS’)：会话登录时间

TO_CHAR(T.START_DATE,‘YYYY-MM-DD HH24:MI:SS’)：事务开始时间

S.LAST_CALL_ET：最后一次调用的时间（以秒为单位）

S.BLOCKING_SESSION：阻塞会话ID

S.STATUS：会话状态

(SELECT Q.SQL_TEXT FROM V$SQL Q WHERE Q.LAST_ACTIVE_TIME=T.START_DATE AND ROWNUM<=1) AS SQL_TEXT：最后活动时间等于事务开始时间的SQL语句文本(相关阻塞语句)

> 这个需要说明一下,因为线上也遇到过几次告警了,有一点经验
> 这里查询的事务语句有个条件,就是 S.STATUS='INACTIVE', 这是语句的会话状态, 正在执行的显示"ACTIV" , 而"INACTIVE"则表示当前已经停止执行, 有这么几种情况会导致

- 等待资源：当一个查询或操作需要等待某个资源（如锁或磁盘I/O）时，语句的状态会变为INACTIVE。这表示该语句当前正在等待资源，并且暂时停止执行
- 执行完成：如果一个查询或操作已经执行完成，并且不再需要任何资源，那么它的状态会变为INACTIVE。这表示该语句已经成功执行，并且不再占用任何系统资源
- 会话被终止：如果一个会话被终止或关闭，并且该会话中的语句仍然处于执行状态，那么这些语句的状态会变为INACTIVE。这表示会话已经结束，但语句仍然保留在内存中

> 也就是说这种状态下, 就有可能是正常的结束语句(这里又包含了另一种情况,后面说), 也有可能是异常的中断语句, 一直在等待资源. 这个就是我开始没有注意的地方, 导致是正常的也告警了
> 我遇到的情况是, 当前锁表语句已经停止, 但很离谱的事, 查出来的相关语句却是 select语句, 常识都知道, select是不会导致事务的, 但查出来又却是是这个. 于是我就问问AI, 终于是明白了, 它给到的解释是:

在Oracle数据库中，语句状态为INACTIVE表示该语句已经执行完毕，并且已经释放了所有资源，但仍然保留在数据库的缓存中。这意味着该语句当前不在执行，也不占用任何资源。
通常情况下，当一个用户会话执行一个语句时，Oracle会将该语句加载到内存中的共享池中（Shared Pool )，并将其状态设置为ACTIVE。一旦语句执行完毕，Oracle会将其状态更改为INACTIVE，并将其保留在共享池中，以便在需要时再次使用 , 这是Oracle数据库的一项优化特性, 就类似于缓存
语句状态为INACTIVE并不表示错误或问题，它只是表示该语句当前不在执行。这对于数据库性能和资源管理非常重要，因为它允许Oracle在需要时重复使用已编译和优化的语句，而无需重新解析和优化它们

我还在好奇呢, 为什么会这样. 而且事实也证明, 当时的那个表并没有受到任何影响, 不存在事务锁表的情况. 所以这里就需要注意了, 非DDL的语句, 可能就是正常的, DDL ,那铁定有问题

锁表-sql语句执行时间异常

说明:由于锁导致的单次执行时间超过十五分钟的sql

select * from (

select s.sid,s.serial#,s.blocking_session,b.owner,b.object_name,s.event,decode(s.wait_time,0,SECONDS_IN_WAIT,null) as sec

from gv$locked_object l, dba_objects b,gv$session s

where b.object_id=l.object_id

and s.sid=l.session_id

and s.event='enq: TX - row lock contention')

where sec>900;

存储磁盘空间使用率(磁盘阵列空间使用情况)

blkid 查询所有磁盘的磁盘UUID信息和盘符名称

multpath -ll 多路径软件命令，用于查询磁盘阵列映射的磁盘空间信息（包含磁盘UUID和磁盘空间大小）

df -h 查询所有磁盘分区的使用情况

说明:这里有很多命令无法使用,所以就直接用了df -h

查询指定文件夹下超过100M的文件 find /var/lib/mysql -type f -size +100M

inode节点利用率(inode节点使用情况)

inode利用率其实就是文件系统的inode，也就是有多少个文件夹，当利用率满后，将阻止你创建新的文件或目录，即便磁盘空间还很充足

查询命令：

df -i |awk 'NR >1 {print $5,$6}' OFS=","

当利用率满后，解决方案：

查找当前目录下占用inode最多的前10个目录：

find . -type d -print0 | xargs -0 du --inodes | sort -n -r | head -10

find /path/to/directory -type f | wc -l

查看特定目录下inode的使用情况，例如：

du --inodes /path/to/directory

df -i /path/to/mountpoint

中间件

数据库

License: