第二章：数据库巡检
点击数：1485发布日期：2020-04-19 14:38:25 来源：oracle数据库秘籍
第二章：数据库巡检
数据库巡检和常见问题。
数据库巡检是一个相对比较轻松的工作，是大家比较喜欢的工作，不像故障处理和割接，压力大还要加班。如果时间短，问问用户有什么性能或其它问题，我们重点先看看表空间利用率和文件系统利用率等几个要点。然后再研究一些深入的问题，给出建议，写进巡检报告里。写巡检报告有的比较长，几十页，客户又有十几套库，写文档的量比较大。我们有最简化版的巡检报告，可以省去写巡检报告长篇大论的问题，轻轻松松二三页，只写要紧的，让巡检工作成为一种享受。
要巡检的内容，下面就所用的脚本做一个介绍。以下都以linux作为样例。
巡检中我们需要关注的问题，主要如下：
1、主机配置，有多少内存；多少个CPU每个CPU多少核；存储多大，分别给谁用，RAID情况，存储的速度。
2、操作系统的核心参数和信号量等设置。
3、网络配置，网络速度，listener是否正常。
4、Instance参数配置，SGA大小，PGA大小，是否手工管理SGA。
5、数据库配置；块大小；日志文件大小，组数；ASM、裸设备、文件系统。
6、数据库安全配置；用户的密码；是否授予DBA角色；应用用户的权限。
7、数据库性能。SQL语句的响应时间，数据库整体响应时间，问题SQL的执行计划。
8、数据库的版本；小补丁情况，警告日志有无报错；报错的处理方案。
9、数据库的容灾和备份情况，是否使用了DataGuard或其它容灾方式；RMAN备份的可用性；是否做恢复演练。
数据库巡检过程：
1.1、文件系统利用率df -h。aix命令用df -g。hp-ux命令用bdf。
[root@yingshu ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
53G 50G 3G 94% /
/dev/sda1 99M 22M 73M 23% /boot
tmpfs 1.8G 236M 1.6G 14% /dev/shm
none 1.8G 104K 1.8G 1% /var/lib/xenstored
建议：文件系统/挂载点下利用率过高，建议查看被哪些文件所占用，及时清理或扩展文件系统空间。
1.2、操作系统的性能top。CPU、内存使用率，I/O等待情况，I/O速度。
linux命令sar 2 10。vmstat 2 10。aix命令topas也要掌握。
[root@yingshu ~]# top
top - 11:41:41 up 5:45, 3 users, load average: 0.05, 0.04, 0.00
Tasks: 193 total, 1 running, 191 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.0%us, 1.0%sy, 0.0%ni, 94.7%id, 3.2%wa, 0.0%hi, 0.3%si, 0.8%st
Mem: 3685376k total, 3178520k used, 506856k free, 43112k buffers
Swap: 6094840k total, 232k used, 6094608k free, 2516776k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7804 root 15 0 0 0 0 S 1.7 0.0 0:04.65 pdflush
1 root 15 0 10368 636 544 S 0.0 0.0 0:01.53 init
1.3、虚拟内存的使用情况
[root@yingshu ~]# vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 4 126104 56952 6528 2906768 0 1 215 705 173 318 2 4 86 8 0
0 8 126096 55904 6532 2906980 16 0 102 44820 157 272 2 31 4 61 1
3 7 126096 57648 6536 2907028 0 0 4 24594 183 266 0 30 0 68 1
1 3 126096 58964 6536 2907052 0 0 0 32006 158 250 4 48 5 42 1
1 4 126096 61568 6536 2907068 0 0 0 23028 166 251 2 43 8 45 1
1 0 126092 63444 6552 2907056 0 0 0 506 168 298 0 49 16 35 1
建议：b表示被阻塞的进程，如果大于CPU核数的两倍，则说明系统的性能较差。
1.4、系统资源的使用情况。也可以配合，-d查看硬盘使用报告。
[root@yingshu ~]# sar 2 10
Linux 2.6.18-308.el5xen (yingshu) 09/24/2014
12:09:14 PM CPU %user %nice %system %iowait %steal %idle
12:09:17 PM all 1.56 0.00 72.57 21.60 1.17 3.11
12:09:19 PM all 5.97 0.00 45.02 38.81 1.24 8.96
12:09:21 PM all 1.24 0.00 60.40 34.90 0.99 2.48
12:09:23 PM all 0.24 0.00 31.54 58.92 1.96 7.33
12:09:25 PM all 6.63 0.00 43.98 41.03 1.47 6.88
12:09:27 PM all 1.01 0.00 31.39 57.47 1.52 8.61
12:09:29 PM all 3.47 0.00 41.19 49.63 1.49 4.22
12:09:31 PM all 2.21 0.00 36.03 50.00 2.21 9.56
建议：本系统I/O等待较高，建议关注消耗I/O的进程和磁盘的读写速度
2.2.1数据库的基本信息
可以使用以下脚本巡检。
select name as "db name",instance_name,open_mode,log_mode,status,host_name from v$database,v$instance;
set head off
select 'the count of cpu: ' || value from v$parameter where name = 'CPU_COUNT';
select name from v$datafile where rownum <2;
select 'db component version: ' || product || ' ' || version from product_component_version
where upper(substr(product,1,3)) in ('ORA','TNS');
select 'sum of datafile size(g): ' || trunc(sum(bytes)/1024/1024/1024) || ' g' as sum_g from dba_data_files;
第一句查询：数据库名称，实例名称，开启状态，是否归档，启动状态，主机名称。
第二句查询：CPU核数。
第三句查询：数据文件在什么地方，是裸设备，文件系统或ASM。
第四句查询：数据库版本。
第五句查询：数据文件一共多大。一般可以认为是数据库的总大小，当然还要加上redo
和控制文件所占的空间。
2.2.2 数据库设置
show sga;
select 'db_cache_size(m):' || value/1024/1024 from v$parameter where name = 'db_cache_size';
select 'log_buffer(k):' || value/1024 from v$parameter where name = 'log_buffer';
select 'shared_pool(m):' || value/1024/1024 from v$parameter where name = 'shared_pool_size';
select 'java_pool_size(m):' || value/1024/1024 from v$parameter where name = 'java_pool_size';
select 'large_pool_size(m):' || value/1024/1024 from v$parameter where name = 'large_pool_size';
select 'db_block_size(k):' || value/1024 from v$parameter where name = 'db_block_size';
select 'pga(m):' || value/1024/1024 from v$parameter where name = 'pga_aggregate_target';
select 'the count of tablespaces: ' || count(*) from v$tablespace;
select 'the count of datafiles: ' || count(*) from v$datafile;
select 'the count of controlfiles: ' || count(*) from v$controlfile;
以上语句分别查询的是：1 sga大小，2 数据高速缓冲区大小，3 日志缓冲区大小，4 共享池大小，5 java池大小，6 大池大小，7 数据块大小，8 程序全局区大小，9 表空间总个数，10 数据文件总个数，11 控制文件总个数。
对于10g,11g Oracle可以自动管理内存大小，10g只需要设置一个sga_target和
pga_target Oracle便可以自己管理内存的大小。11g新增了两个内存参数是memory_max_target和memory_target。sga和pga在一起可以动态管理了。
2.2.3 日志文件详情
select 'the size of redolog (m): ' || bytes/1024/1024 from v$log;
select 'redo group: ' ,vl.* from v$log vl;
select 'redo member: ' from dual;
column member format a50
select * from v$logfile;
select 'yesterday redo average switch frequency(min) : ' || round(period/y.cnt,2) from
(select sum((a.first_time - b.first_time) * 24 * 60) as period
from v$log_history a, v$log_history b
where a.recid= b.recid + 1
and a.first_time > trunc(sysdate-1)+10/24
and a.first_time < trunc(sysdate-1)+16/24) x,
(select count(*) as cnt from v$log_history
where first_time > trunc(sysdate-1)+10/24
and first_time < trunc(sysdate-1)+16/24) y;
select rpad('arch info',100,'-') from dual;
select 'log_mode: ' || log_mode from v$database;
show parameter log_archive_dest
1查询日志大小与组数，2日志组的详情，3日志member详情，4昨天日志平均切换时间。5 归档信息 6归档日志的路径。
2.2.4 许可限制
select sessions_max,sessions_warning,sessions_current,sessions_highwater,users_max from v$license;
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 2 9 0
这个视图包括许可限制的信息。
2.2.4 安装配置选件
set pagesize 500
column parameter format a40
column value format a30
set head on
select * from v$option;
PARAMETER VALUE
---------------------------------------- ------------------------------
Partitioning TRUE
Real Application Clusters FALSE
Parallel backup and recovery TRUE
Online Index Build TRUE
Automatic Storage Management FALSE
Oracle Data Guard TRUE
Oracle Label Security FALSE
Advanced Compression TRUE
2.2.4 默认的参数和非默认的参数
column name format a50
column value format a50
select rpad('default parameters',50,'-') from dual;
set head on
select num,name,value from v$parameter where isdefault ='TRUE';
set head off
select rpad('no default parameters',50,'-') from dual;
set head on
select num,name,value from v$parameter where isdefault ='FALSE';
NUM NAME VALUE
---------- --------------------------------------------------
39 processes 1500
781 memory_target 1073741824
830 db_block_size 8192
982 compatible 11.2.0.4.0
1501 undo_tablespace UNDOTBS1
1791 remote_login_passwordfile EXCLUSIVE
1804 db_domain
1997 audit_trail DB
2020 db_name yingshud
2021 db_unique_name yingshudb
2022 open_cursors 3000
2874 diagnostic_dest /u01/app/oracle
以上是非默认参数。建议查看设置的大小是否满足生产需求。
2.2.4 内存参数设置。
show sga;
select 'db_cache_size(m):'|| value/1024/1024 from v$parameter where name = 'db_cache_size';
select 'log_buffer(k):'|| value/1024 from v$parameter where name = 'log_buffer';
select 'shared_pool(m):'|| value/1024/1024 from v$parameter where name = 'shared_pool_size';
select 'java_pool_size(m):' || value/1024/1024 from v$parameter where name = 'java_pool_size';
select 'large_pool_size(m):' || value/1024/1024 from v$parameter where name = 'large_pool_size';
1查询sga的大小，2db_cache_size的大小，3log_buffer的大小，4共享池的大小，5java池的大小，6大池的大小。如果设置了sga_target或memory_target，则2-6的设置不起作用。
2.2.5 查询sga内部详细分配信息
select pool,name,bytes/1024/1024 as "size(m)" from v$sgastat where name like '%free%';
包括共享池，大池，java池，流池。如果空闲空间较多，则不需要增大sga.
2.2.6数据库失效对象的查询。
select rpad('invalid objects',100,'-') from dual;
column owner format a15
column object_name format a25
column object_type format a15
column status format a10
select owner,object_name,object_type,status from dba_objects where status='INVALID';
INVALID OBJECTS---------------------------------------------------
SYSTEM ORA$_SYS_REP_AUTH PROCEDURE INVALID
SYSTEM DBMS_REPCAT_AUTH PACKAGE INVALID
SYSTEM DEF$_PROPAGATOR_TRIG TRIGGER INVALID
SYS DBMS_CUBE_EXP PACKAGE BODY INVALID
SYS AWM_CREATEXDSFOLDER FUNCTION INVALID
YINGS PRO_TJ_KPD_XXFPDY PROCEDURE INVALID
YINGS PRO_TJ_KPFWQ_XXFPDY PROCEDURE INVALID
YINGS PRO_TJ_NSR_XXFPDY_2013111 PROCEDURE INVALID
建议：关注失效对象是否被使用，如果不被使用，建议删除。对于应用使用的失效对象，建议重新编译。在数据库升级或迁移前后，对比失效对象的个数，判断带来的影响。
2.2.7是否有offline的数据文件
select rpad('datafile is offline ? ',100,'-') from dual;
set head on
select file_name from dba_data_files where status='OFFLINE'
如果存在offline的数据文件，归档模式下，归档日志还在，直接做介质恢复处理，然后将数据文件online就可以了。如果归档日志已删除，则只能恢复整个数据库才能将此数据文件online。
2.2.8 是否存在失效的约束和触发器
select rpad('invalid constraints ',100,'-') from dual;
select owner, constraint_name, table_name, constraint_type, status from dba_constraints where status = 'DISABLED';
select rpad('invalid triggers ',100,'-') from dual;
set head on
select owner, trigger_name, table_name, status from dba_triggers where status = 'DISABLED';
如果想让无效的触发器生效，则使用语句：
alter tigger trigger_name enable;
如果想让约束失效或生效，则使用：
alter table table_name disable constrant constraint_name;
alter table table_name enable constrant constraint_name;
2.2.9 数据文件的状态
select rpad('datafile info',100,'-') from dual;
column file_name format a50
column tablespace_name format a30
set head on
select file_id,file_name,tablespace_name,bytes/1024/1024 as "size(m)",status,autoextensible from dba_data_files;
文件id，数据文件名称和路径，表空间名，数据文件大小，状态，是否自动扩展。
建议：数据文件最好不要自动扩展，自动扩展会导致文件大小不一，某个文件异常过大，显的杂乱没有规划，自动扩展减少了维护量，但没人维护的数据库比较危险。
2.2.10临时文件的状态
select rpad('tmpefile info',100,'-') from dual;
column file_name format a50
column tablespace_name format a30
select file_id,file_name,tablespace_name,bytes/1024/1024 as "size(m)",status,autoextensible from dba_temp_files;
临时文件id，临时文件名称和路径，表空间名，临时文件大小，状态，是否自动扩展。
2.2.11统计哪些数据文件读写最频繁
select rpad('the count of read/write',100,'-') from dual;
col name for a40
select df.name,phyrds,phywrts
from v$filestat fs,v$datafile df
where fs.file#=df.file#
order by phyrds,phywrts;
NAME PHYRDS PHYWRTS
---------------------------------------- ---------- ----------
/oracle/oradata/yingshudb/users01.dbf 1 0
/oracle/oradata/yingshudb/fuzong01.dbf 1 0
/oracle/oradata/yingshudb/undotbs01.dbf 28 60
/oracle/oradata/yingshudb/sysaux01.dbf 618 219
/oracle/oradata/yingshudb/system01.dbf 3146 33
数据文件名称和路径，物理读总数，物理写总数。通过这个语句可以查出哪些数据文件读写最频繁，如果有I/O瓶颈，分散这些频繁读写的对象也是一个解决办法。
2.2.12查询控制文件的名称
select * from v$controlfile;
STATUS NAME IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS
------------ ---------------------------------------- --------- ---------- --------------
/oracle/oradata/yingshudb/control01.ctl NO 16384 594
/oracle/oradata/yingshudb/control02.ctl NO 16384 594
2.2.12查询日志文件
set head on
set linesize 132
col status for a20
col member for a38
select vlf.member,vl.group#,vl.thread#,vl.bytes/1024/1024 as "size(m)",vl.status,vlf.type
from v$log vl,v$logfile vlf
where vl.group# = vlf.group#;
MEMBER GROUP# THREAD# size(M) STATUS TYPE
------------------------------------ ---------- ---------- --------- -------- ------------
/oracle/oradata/yingshudb/redo01.log 1 1 500 INACTIVE ONLINE
/oracle/oradata/yingshudb/redo02.log 2 1 500 CURRENT ONLINE
/oracle/oradata/yingshudb/redo03.log 3 1 500 INACTIVE ONLINE
/oracle/oradata/yingshudb/redo04.log 4 1 500 INACTIVE ONLINE
6列分别为：日志文件的位置和名称，一共有多少组，属于哪一个实例，日志文件大小，日志文件状态，是否在线。
2.2.13表空间信息
select rpad('tablespace info',100,'-') from dual;
set head on
select tablespace_name tbs_name,block_size,extent_management ext_mag,allocation_type,segment_space_management seg_space from dba_tablespaces;
TBS_NAME BLOCK_SIZE EXT_MAG ALLOCATION_TYPE SEG_SPACE
SYSTEM 8192 LOCAL SYSTEM MANUAL
SYSAUX 8192 LOCAL SYSTEM AUTO
UNDOTBS1 8192 LOCAL SYSTEM MANUAL
TEMP 8192 LOCAL UNIFORM MANUAL
USERS 8192 LOCAL SYSTEM AUTO
FUZONG 8192 LOCAL SYSTEM AUTO
6 rows selected.
5列分别为：表空间名称，块大小，表空间区管理方式，区分配方式，段空间管理方式。
2.2.14 表空间剩余百分比
select rpad('tablespace use',100,'-') from dual;
set head on
column pct_free format a10
select tablespace_name, sum_m as "sum(m) " , sum_free as "sum_free(m) ",to_char(100*sum_free/sum_m, '99.99') || '%' as pct_free
from ( select tablespace_name,sum(bytes)/1024/1024 as sum_m from dba_data_files group by tablespace_name),
( select tablespace_name as ts_name, sum(bytes/1024/1024) as sum_free from dba_free_space group by tablespace_name )
where tablespace_name = ts_name (+)
union
select tablespace_name , sum_m as "sum(m) " , sum_free as "sum_free(m) " , to_char(100*sum_free/sum_m, '99.99') || '%' as pct_free
from (select tablespace_name, sum(bytes)/1024/1024 as sum_m from dba_temp_files group by tablespace_name),
(select tablespace_name as ts_name, sum(bytes_free /1024/1024) as sum_free from v$temp_space_header group by tablespace_name)
where tablespace_name = ts_name (+)
order by pct_free;
TABLESPACE_NAME sum(M) sum_free(M) PCT_FREE
------------------ ---------- ------------ ----------
SYSTEM 700 427.625 61.09%
TEMP 20 15 75.00%
USERS 5 4 80.00%
SYSAUX 600 486.75 81.13%
FUZONG 10240 9247 90.30%
UNDOTBS1 5660 5647.625 99.78%
6 rows selected.
4列分别为：表空间名称，表空间总大小，剩余空间的大小，剩余百分之多少。
2.2.15 表空间利用率
set pages 100
set linesize 200
col ts_name form a20 head 'tablespace'
col pieces form 99990 head 'pcs'
col ts_size form 99999,990 head 'sizeMB'
col largestpc form 999,990 head 'lrgMB'
col totalfree form 99999,990 head 'freeMB'
col pct_free form 990 head '%free'
col whatsused form 999999,990 head 'used'
col pct_used form 990 head '%used'
col problem head 'prob??'
select q2.other_tname ts_name, pieces, ts_size ts_size,
nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
nvl(round((total_free/ts_size)*100,2),0) pct_free,
ts_size-total_free whatsused,
nvl(100-round((total_free/ts_size)*100,2),100) pct_used,
decode(nvl(100-round((total_free/ts_size)*100,0),100),
85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
98,'+++++',99,'+++++',100,'+++++','') problem
from (select dfs.tablespace_name,count(*) pieces,
round(max(dfs.bytes)/1024/1024,2) largest_chunk,
round(sum(dfs.bytes)/1024/1024,2) total_free
from dba_free_space dfs group by tablespace_name) q1,
(select tablespace_name other_tname,
round(sum(ddf2.bytes)/1024/1024,2) ts_size
from dba_data_files ddf2 group by tablespace_name) q2
where q2.other_tname = q1.tablespace_name(+)
order by nvl(100-round((total_free/ts_size)*100,0),100) desc
/
Tablespace Pcs SizeMb LrgMB FreeMb %Free Used %Used Prob??
-------------------- ------ ---------- -------- ---------- ----- ----------- ----- ------
JISHIYU 6 0 0 0 100 +++++
SYSTEM 2 700 427 428 61 272 39
USERS 1 5 4 4 80 1 20
SYSAUX 2 600 485 485 81 115 19
FUZONG 3 10,240 3,968 9,247 90 993 10
UNDOTBS1 7 5,660 3,149 5,623 99 37 1
6 rows selected.
9列分别为：表空间名称，空闲空间分布在多少个片上，表空间总大小，最大的空闲区，空闲剩余多少M，剩余百分比，使用了多少M，使用率的百分比，是否利用率过高。
当超
上一篇：oracle数据库巡检,深度巡检
图文推荐
序言
2020-04-19 查看：2271
第十五章：Oracle 12c介绍
2020-04-19 查看：2208
第十四章：常用Oracle工具
2020-04-19 查看：1934
第十三章：Oracle Golde...
2020-04-19 查看：2199
第十二章：DataGuard
2020-04-19 查看：1796
