Oracle数据库日常巡检指令

数据库的日常巡检可以说是保障系统稳定运行的基础，虽然不同的运维团队、不同的数据库，都会有适合自己的巡检体系，但是其中很多内容还是可以互相借鉴的。技术社群的这篇文章《Oracle数据库日常巡检指令》给我们提供了Oracle数据库日常巡检指令，虽然只针对Oracle，但是一些巡检的方向还是值得借鉴和学习的。

Oracle数据库的日常巡检内容包括：

（1）Oracle数据库基本状况检查；

（2）Oracle相关资源的使用情况检查；

（3）Oracle数据库性能检查；

（4）数据库服务器cpu、mem和I/O性能检查；

（5）数据库服务器安全性及其他事项检查。

需要指出的是这些巡检指令不仅在巡检过程中用得上，某些故障的诊断场景中同样可以利用，还是比较通用的。

1、数据库基本状况检查

（1）数据库实例状况检查

说明：其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。

（2）数据库表空间状态检查

1c1896b54872c6eaed6b40bdabb23f48.png

说明：输出结果中STATUS应该都为“ONLINE”。

（3）数据库数据文件检查

select tablespace_name,status from dba_tablespaces;

cec41e734b00a94dfd18ebc96116a42d.png

说明：输出结果中“STATUS”应该都为“AVAILABLE”。

（4）数据库在线日志检查

select group#,status,type,member from v$logfile;

981a7630c811e18505784f9f21b9ada6.png

说明：输出结果应该有3条或3条以上记录，“STATUS”应该为非“INVALID”，非“DELETED”。“STATUS”的值为空表示正常。

（5）数据库回滚段检查

select segment_name,status from dba_rollback_segs;

a6aadde188b914d695c567479e5d3650.png

说明：输出结果中所有回滚段的“STATUS”应该为“ONLINE”。

2、数据库相关资源使用情况检查

（1）检查Oracle初始化文件中相关参数值

select resource_name,max_utilization,initial_allocation, limit_value from v$resource_limit;

3f12a4d18b9b6e5b24ab11c86fd1664f.png

说明：若字段值【LIMIT_VALU】-【MAX_UTILIZATION】<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/orcl/pfile/initORCL.ora来修改。

（2）检查数据库连接情况

select sid,serial#,username,program,machine,status from v$session;

d7a8dbf294e2fe5fe05340df7f6868cf.png

说明：STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作；如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。

（3）检查表空间使用情况

select f.tablespace_name,a.total,f.free,round((f.free / a.total) 100) “% Free” from (select tablespace_name, sum(bytes / (1024 1024)) total from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes / (1024 * 1024))) free from dba_free_space group by tablespace_name) f WHERE a.tablespace_name =f.tablespace_name(+) order by “% Free”;

66b65159755f74b8ca64d3beab85f3c7.png

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

（4）检查system表空间内的内容

select distinct (owner) from dba_tables where tablespace_name = ‘SYSTEM’ and owner != ‘SYS’ and owner != ‘SYSTEM’ union select distinct (owner) from dba_indexes where tablespace_name = ‘SYSTEM’ and owner != ‘SYS’ and owner != ‘SYSTEM’;

c7db2768b57c7ca65333db0e36d343f2.png

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

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

select Segment_Name,Segment_Type,TableSpace_Name, (Extents / Max_extents) 100 Percent From sys.DBA_Segments Where Max_Extents != 0 and (Extents / Max_extents) 100 >= 95 order By Percent;

7cb3a6c4e2383cf0f48ced3cd5070241.png

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

（6）检查对象的下一扩展值与表空间的最大扩展值

select a.table_name, a.next_extent, a.tablespace_name from all_tables a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk union select a.index_name, a.next_extent, a.tablespace_name from all_indexes a,(select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;

4a34845dd13e61ecffeb0914a3cda080.png

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

3、检查Oracle数据库性能

（SQLPLUS的界面设置参数set pages 80 set lines 120 col event for a40）

（1）检查数据库的等待事件

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%’;

62d7893efa7205a0317fa1c174f0d13b.png

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

（2）等待时间最多的5个系统等待事件

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

717c27656ad0ed959714447f993215bc.png

（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;

5a3c8341927389df132e39745847e6f4.png

（4）Disk Read最高的SQL语句的获取

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

722ceaf006245d919839172c890b5e18.png

（5）检查死锁及处理

select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_id from dba_objects o, v$locke{d}_{o}bjectl,v$session s where o.object_id = l.object_id and s.sid = l.session_id;

272626bfc8231165ec3dd54bee8c64f2.png

4、数据库服务器CPU、MEM、I/O性能

（1）CPU查询指令 top

（2）内存查询指令 free -m

（3）I/O查询指令 iostat -k 1 3

（4）系统负载情况查询指令 uptime

（5）僵尸进程查询指令

select spid from v$processwhereaddrnotin(selectpaddrfromv$session);

bb60682ba5bd113ed236d3f54790989b.png

（6）日志缓冲区查询指令

select name,value from v$sysstat where name in (‘redo entries’,’redo buffer allocation retries’);

ab129ea341efea2fe47968e75e631eb7.png

说明：如果【redo buffer allocation retries】/【redo entries】的值超过1%，则需要增大log_buffer。

5、数据库服务器安全检查

（1）系统安全日志检查

检查登录成功日志的查询指令为 # grep -i accepted /var/log/secure

检查登录失败日志的查询指令为 # grep -i inval /var/log/secure &&grep -i failed /var/log/secure

（2）crontab任务检查

Oracle Job是否有失败任务的查询

select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user=’system’;

37f4b693fd3d25ca2493b8b342e39108.png

（3）检查失效的索引

select index_name, table_name, tablespace_name, status From dba_indexes Where owner = ‘system’ And status <> ‘VALID’;

4d0de370d5ae55e023945fe92082bcfa.png

说明：分区表上的索引status为空是正常的，如有失效索引则对该索引做rebuild。其指令为alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;