高效进行Oracle日常巡检：数据库性能与安全检查

相关文章《 高效进行 Oracle 日常巡检之数据库基本情况检查 》

前言

​对于线上的业务，oracle的数据库运行的稳定性和安全性是用户关心的一个至关重要的问题，除了通过监控平台对数据库进行监控以外，还需要定期对数据库进行“体检”，是保障数据库稳定运行的必不可的辅助手段。本文简要介绍在系列一的基础上需要巡检的内容，主要包括资源使用、性能、安全性等。

一、检查oracle相关资源的使用情况

主要检查Oracle相关资源的使用情况，包含：

• 检查Oracle初始化文件中相关的参数值
• 检查数据库连接情况
• 检查系统磁盘空间
• 检查Oracle各个表空间使用情况
• 检查一些扩展异常的对象
• 检查system表空间内的内容
• 检查对象的下一扩展与表空间的最大扩展值

总共七个部分。

1. 检查oracle初始化文件中相关参数

若LIMITVALU-MAXUTILIZATION<=5，则表明与RESOURCENAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLEBASE/admin/ORCL/pfile/initORCL.ora来修改。

2. 检查数据库连接情况

查看当前会话连接数，是否属于正常范围。

其中：

• SID 会话(session)的ID号;
• SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话;
• USERNAME 建立该会话的用户名;
• PROGRAM 这个会话是用什么 工具 连接到数据库的;
• STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作;

3. 检查系统磁盘空间

如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。

4. 检查表空间的使用情况

如果空闲率%Free小于10%以上(包含10%)，则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。

5. 检查一些扩展异常的对象

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。

6. 检查system表空间内的内容

如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。

7. 检查对象的下一扩展与表空间的最大扩展值

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。

二、检查数据库的性能

检查Oracle数据库性能情况，包含：

• 检查数据库的等待事件
• 检查死锁及处理
• 检查cpu、I/O、内存性能
• 查看是否有僵死进程
• 检查行链接/迁移
• 定期做统计分析
• 检查缓冲区命中率
• 检查共享池命中率
• 检查 排序 区
• 检查日志缓冲区

总共十个部分。

1. 检查数据库的等待事件

set pages 80 
set lines 120 
col event for a40 
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%'; 

如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。

2. Disk Read最高的 SQL 语句的获取

SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) 
WHERE ROWNUM<=5 desc; 

3. 查找前十条性能差的SQL

SELECT * FROM (SELECT PARSING_USER_ID 
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, 
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)  
WHERE ROWNUM<10 ; 

4. 等待时间最多的5个系统等待事件的获取

SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5; 

5.  检查运行很久的SQL

COLUMN USERNAME FORMAT A12  
COLUMN OPNAME FORMAT A16  
COLUMN PROGRESS FORMAT A8  
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE; 

6.  检查消耗CPU最高的进程

SET LINE 240 
SET VERIFY OFF 
COLUMN SID FORMAT 999 
COLUMN PID FORMAT 999  
COLUMN S_# FORMAT 999 
COLUMN USERNAME FORMAT A9 HEADING "ORA USER" 
COLUMN PROGRAM FORMAT A29 
COLUMN SQL FORMAT A60 
COLUMN OSNAME FORMAT A9 HEADING "OS USER" 
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%'; 

7. 检查碎片程序高的表

SQL> SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name); 

8.  检查表空间的 I/O 比例

SQL>SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; 

9.  检查文件系统的 I/O 比例

SQL>SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#; 

10. 检查死锁及处理

查询目前锁对象信息：

col sid for 999999 
col username for a10 
col schemaname for a10 
col osuser for a16 
col machine for a16 
col terminal for a20 
col owner for a10 
col object_name for a30 
col object_type for a10 
select sid,serial#,username,SCHEMANAME,osuser,MACHINE, 
terminal,PROGRAM,owner,object_name,object_type,o.object_id  
from dba_objects o,v$locked_object l,v$session s  
where o.object_id=l.object_id and s.sid=l.session_id; 

oracle级kill掉该session：

alter system kill session '&sid,&serial#'; 

操作系统级kill掉session：

#>kill -9 pid 

11.查看是否有僵死进程

select spid from v$process where addr not in (select paddr from v$session); 

有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。

12.  检查缓冲区命令中率

13. 检查共享池命令中率

如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。

14.检查排序区

如果disk/(memoty+row)的比例过高，则需要调整sortareasize(workareasizepolicy=false)或pgaaggregatetarget(workareasizepolicy=true)。

15. 检查日志缓中区

如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。

三、检查数据库cpu、I/O、内存性能

1. CPU使用情况

1. top 

2. 内存使用情况

free -m 

3. 系统io情况

四、检查Oracle数据库的安全性

主要检查Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码，总共两个部分。

1. 检查系统安全日志信息

系统安全日志文件的目录在/var/log 下，主要检查登录成功或失败的用户日志信息。

检查登录成功的日志：

[root@rac2 ~]# grep -i accepted /var/log/secure 
Jan 8 08:44:43 rac2 sshd[29559]: Accepted password for root from ::ffff:10.10.10.6 port 1119 ssh2…… 

检查登录失败的日志：

[root@rac2 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure 
Jan 9 10:30:44 rac2 sshd[3071]: Invalid user ydbuser from ::ffff:192.168.3.5 
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2 
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2 
Jan 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2 

在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused)视为系统正常，出现错误提示，应作出系统告警通知。

2.  检查用户修改密码

数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括： 数据库管理员用户SYS，SYSTEM;其他用户。

修改密码方法：

Sql>alter user USER_NAME identified by PASSWORD;