oracle 数据库日常巡检：数据库基本状况、oracle资源使用、数据备份结果、性能、cpu等、数据安全、归档日志、会话、SGA/PGA使用情况

巡检目录1. 检查数据库基本状况2. 检查Oracle相关资源的使用情况3. 检查Oracle数据库备份结果4. 检查Oracle数据库性能5. 检查数据库cpu、I/O、内存性能6. 检查数据库安全性7. 其他检查1. 检查数据库基本状况包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。1.1. 检查Ora...

Reyn_vip

652人浏览 · 2020-03-04 14:11:19

Reyn_vip · 2020-03-04 14:11:19 发布

巡检目录

1. 检查数据库基本状况
2. 检查Oracle相关资源的使用情况
3. 检查Oracle数据库备份结果
4. 检查Oracle数据库性能
5. 检查数据库cpu、I/O、内存性能
6. 检查数据库安全性
7. 其他检查

1. 检查数据库基本状况

包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。

1.1. 检查Oracle实例状态

select instance_name,host_name,startup_time,status,database_status from v$instance;

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

1.2. 检查Oracle在线日志状态

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

输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。注：“STATUS”显示为空表示正常。

1.3. 检查Oracle表空间的状态

select tablespace_name,status from dba_tablespaces;

输出结果中STATUS应该都为ONLINE。

1.4. 检查Oracle所有数据文件状态

select name,status from v$datafile;

输出结果中“STATUS”应该都为“ONLINE”。或者：

select file_name,status from dba_data_files;

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

2. 检查Oracle相关资源的使用情况

2.1. 检查数据库连接情况

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

select count(*) from v$session;

2.2. 检查系统磁盘空间

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

[oracle@AS14 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_ssc-LogVol00

902G 12G 845G 2% /

tmpfs 3.9G 8.0K 3.9G 1% /dev/shm

/dev/sda1 194M 33M 152M 18% /boot

2.3. 检查表空间使用情况

select tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space from dba_data_files order by tablespace_name;

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

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;

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

2.5. 检查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';

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

2.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;

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

3. 检查Oracle数据库备份结果
3.1. 检查数据库备份日志信息

假设：备份的临时目录为/backup/hotbakup，我们需要检查2019年12月30日的备份结果，则用下面的命令来检查：

cat /backup/hotbackup/hotbackup-19-12-30.log|grep –i error

备份脚本的日志文件为hotbackup-月份-日期-年份.log，在备份的临时目录下面。如果文件中存在“ERROR:”，则表明备份没有成功，存在问题需要检查。

4. 检查Oracle数据库性能

在本节主要检查Oracle数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。

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

select event, count(*)
from v$session_wait
group by event
order by count(*) desc;

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

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

SELECT SQL_TEXT

FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)

WHERE ROWNUM <= 5;

4.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.4. 等待时间最多的5个系统等待事件的获取

SELECT *

FROM (SELECT *

FROM V$SYSTEM_EVENT

WHERE EVENT NOT LIKE 'SQL%'

ORDER BY TOTAL_WAITS DESC)

WHERE ROWNUM <= 5;

4.5. 检查碎片程度高的表

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

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

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;

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

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

4.8.检查死锁及处理

查询目前锁对象信息：

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;

select sid,serial#,username,SCHEMANME,osuser,MACHINE,terminal,program,owner,object_name,object_type,o.object_id from dba_objects o,v$locked_object l,v$session s where

oracle级kill掉该session：

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

操作系统级kill掉session：

#>kill -9 pid

5. 检查数据库CPU,I/O,内存性能

记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。

5.1 CPU使用情况：

[root@sale8 ~]# top

查看系统剩余的cpu，当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。

5.2 内存使用情况：

[root@sale8 ~]# free -m

查看系统剩余内存，当剩余内存低于总内存的10%时视为异常。

5.3 系统I/O情况：

[root@sale8 ~]# iostat -k 1 3

查看cpu IO等待情况。

5.4 系统负载情况：

[root@sale8 ~]#uptime

最后3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常。

5.5.查看是否有僵尸进程

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

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

5.6 检查缓冲区命中率

SELECT a.VALUE + b.VALUE logical_reads,

c.VALUE phys_reads,

round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio

FROM v$sysstat a, v$sysstat b, v$sysstat c

WHERE a.NAME = 'db block gets'

AND b.NAME = 'consistent gets'

AND c.NAME = 'physical reads';

如果命中率低于90%则需加大数据库参数db_cache_size。

5.7 检查共享池命中率

select sum(pinhits) / sum(pins) * 100 from v$librarycache;

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

5.8 检查排序区

select name,value from v$sysstat where name like '%sort%';

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

5.9 检查日志缓冲区

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

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

6. 检查数据库安全性

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

6.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 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2

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

6.2. 检查用户修改密码

在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括：
数据库管理员用户SYS，SYSTEM；其他用户。
登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常。

修改密码方法：

alter user USER_NAME identified by PASSWORD;

7. 其他检查

在本节主要检查当前crontab任务是否正常，检查Oracle Job是否有失败等共六个部分。

7.1 检查当前crontab任务是否正常

[oracle@AS14 ~]$ crontab -l

7.2 Oracle Job是否有失败

select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';

如有问题建议重建job

7.3. 监控数据量的增长情况

select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent

from (select tablespace_name, sum(bytes) total

from dba_free_space

group by tablespace_name) A,

(select tablespace_name, sum(bytes) total

from dba_data_files

group by tablespace_name) B

where A.tablespace_name = B.tablespace_name;

根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施：

--删除历史数据
移动规定数据库中至少保留6个月的历史数据，所以以前的历史数据可以考虑备份然后进行清除以便释放其所占的资源空间。

---扩表空间

alter tablespace <tablespace_name> add datafile ‘<file>’ size <size> autoextend off;

注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作，都会造成Oracle数据库控制文件的变化，DBA应及进行控制文件的备份，备份方法是：

执行SQL语句：

alter database backup controlfile to '/home/backup/control.bak';

或：

alter database backup controlfile to trace;

这样，会在USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的SQL命令。

7.4. 检查无效的trigger

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

如有失效触发器则启用，如：

alter Trigger TRIGGER_NAME Enable;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

数据库归档日志状态情况

SQL> archive log list;

　查询归档日志存储大小

SQL>  show parameter db_recovery_file_dest_size;
SQL>  select sum(a.BLOCK_SIZE*a.BLOCKS)/1024/1024 from v$archived_log a where a.DELETED='NO';

. 查看锁住的表

SELECT b.owner,b.object_name,a.session_id,a.locked_mode
FROM v$locked_object a ,dba_objects b
WHERE b.object_id = a.object_id;

查看被锁住的会话

SELECT b.username,b.sid,b.serial#,logon_time
        FROM v$locked_object a,v$session b
        WHERE a.session_id = b.sid order by b.logon_time;

如果要断开某个会话，执行

Alter system kill session ‘sid,serial#’

+++++++++++++++++++++++++++++++++++++++++++=

SGA、PGA使用情况

SQL> select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from du  2    3  al)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);  4    5    6    7  

NAM	 TOTAL	     USED	FREE	PCTUSED
--- ---------- ---------- ---------- ----------
PGA	 25088	  1596.19   23491.81	   6.36
SGA 71614.8477	 65943.05     5671.8	  92.08