高效进行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. 检查数据库的等待事件

setpages80
setlines120
coleventfora40
selectsid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfromv$session_waitwhereeventnotlike'SQL%'andeventnotlike'rdbms%';

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

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

SQL>SELECTSQL_TEXTFROM(SELECT*FROMV$SQLAREAORDERBYDISK_READS)
WHEREROWNUM<=5desc;

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

SELECT*FROM(SELECTPARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)
WHEREROWNUM<10;

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

SELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSDESC)WHEREROWNUM<=5;

5. 检查运行很久的SQL

COLUMNUSERNAMEFORMATA12
COLUMNOPNAMEFORMATA16
COLUMNPROGRESSFORMATA8
SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=HASH_VALUE;

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

SETLINE240
SETVERIFYOFF
COLUMNSIDFORMAT999
COLUMNPIDFORMAT999
COLUMNS_#FORMAT999
COLUMNUSERNAMEFORMATA9HEADING"ORAUSER"
COLUMNPROGRAMFORMATA29
COLUMNSQLFORMATA60
COLUMNOSNAMEFORMATA9HEADING"OSUSER"
SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&1%';

7. 检查碎片程序高的表

SQL>SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);

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

SQL>SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAME"FILE",F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBYDF.TABLESPACE_NAME;

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

SQL>SELECTSUBSTR(A.FILE#,1,2)"#",SUBSTR(A.NAME,1,30)"NAME",A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSFROMV$DATAFILEA,V$FILESTATBWHEREA.FILE#=B.FILE#;

10. 检查死锁及处理

查询目前锁对象信息：

colsidfor999999
colusernamefora10
colschemanamefora10
colosuserfora16
colmachinefora16
colterminalfora20
colownerfora10
colobject_namefora30
colobject_typefora10
selectsid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
fromdba_objectso,v$locked_objectl,v$sessions
whereo.object_id=l.object_idands.sid=l.session_id;

oracle级kill掉该session：

altersystemkillsession'&sid,&serial#';

操作系统级kill掉session：

#>kill-9pid

11.查看是否有僵死进程

selectspidfromv$processwhereaddrnotin(selectpaddrfromv$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使用情况

top

2. 内存使用情况

free-m

3. 系统io情况

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

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

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

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

检查登录成功的日志：

[root@rac2~]#grep-iaccepted/var/log/secure
Jan808:44:43rac2sshd[29559]:Acceptedpasswordforrootfrom::ffff:10.10.10.6port1119ssh2……

检查登录失败的日志：

[root@rac2~]#grep-iinval/var/log/secure&&grep-ifailed/var/log/secure
Jan910:30:44rac2sshd[3071]:Invaliduserydbuserfrom::ffff:192.168.3.5
Jan910:30:56rac2sshd[3071]:Failedpasswordforinvaliduserydbuserfrom::ffff:192.168.3.5port36005ssh2
Jan910:30:56rac2sshd[3071]:Failedpasswordforinvaliduserydbuserfrom::ffff:192.168.3.5port36005ssh2
Jan1022:44:38rac2sshd[21611]:Failedpasswordforrootfrom::ffff:10.10.10.6port1723ssh2

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

2. 检查用户修改密码

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

修改密码方法：

Sql>alteruserUSER_NAMEidentifiedbyPASSWORD;