Oracle 10g/11g 数据库巡检脚本汇总

数据库巡检过程

本文来源是付培利主编的《深度解析Oracle 实战与提高》的第二章节数据库巡检

1. 文件系统利用率df -h

[root@qqcloud ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.9G     0  1.9G   0% /dev
tmpfs           1.9G  408M  1.5G  22% /dev/shm
tmpfs           1.9G  1.5M  1.9G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/vda1        79G   31G   46G  40% /
tmpfs           379M   44K  379M   1% /run/user/0
/dev/sr0         17M   17M     0 100% /run/media/root/config-2

2. 操作系统的性能 top

[root@qqcloud ~]# top
top - 09:00:37 up 34 days, 13:52,  2 users,  load average: 0.10, 0.07, 0.10
Tasks: 274 total,   1 running, 273 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  3.1 sy,  0.0 ni, 96.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  3880180 total,   127344 free,  2387964 used,  1364872 buff/cache
KiB Swap:  4194300 total,  3648732 free,   545568 used.   795360 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 1228 root      20   0   50088    636    592 S   6.7  0.0 420:06.25 rshim
12431 root      20   0  162256   2336   1544 R   6.7  0.1   0:00.01 top
    1 root      20   0  199632   3788   2140 S   0.0  0.1   9:02.27 systemd
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.85 kthreadd

3. 虚拟内存的使用情况 vmstat 2 10

[root@qqcloud ~]# 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
 1  0 546596 163468  14768 1313644    0    0    22    41    5    7  1  1 99  0  0
 0  0 546596 163912  14792 1313684    0    0     4   120 3137 5818  3  1 95  0  0
 0  0 546596 163912  14792 1313684    0    0     0    18 2853 5463  0  1 99  0  0
 0  0 546596 163664  14804 1313684    0    0     2    58 2887 5500  1  1 99  0  0
 0  0 546596 163656  14804 1313704    0    0     0     0 3045 5814  2  2 96  0  0
 0  0 546596 157872  14804 1313732    0    0     4    36 2985 5595  1  1 98  0  0
 0  0 546596 157920  14812 1313732    0    0     0    56 2865 5480  1  1 99  0  0
 0  0 546596 157672  14812 1313868    0    0     0   276 2804 5388  0  1 98  1  0
 0  0 546596 156928  14820 1313876    0    0     2    32 2941 5595  1  1 98  0  0
 0  0 546596 156656  14820 1313884    0    0     0    16 2889 5499  1  1 98  0  0

4. 系统资源的使用情况 sar 2 10

[root@qqcloud ~]# sar 2 10
Linux 3.10.0-1160.11.1.el7.x86_64 (qqcloud)     09/17/2021      _x86_64_        (2 CPU)

09:07:19 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:07:21 AM     all     11.62      0.00      2.02      0.25      0.00     86.11
09:07:23 AM     all      2.52      0.00      1.76      0.00      0.00     95.72
09:07:25 AM     all      0.25      0.00      0.76      0.25      0.00     98.74
09:07:27 AM     all      0.25      0.00      0.25      0.00      0.00     99.49
09:07:29 AM     all      0.75      0.00      1.01      0.00      0.00     98.24
09:07:31 AM     all      0.50      0.00      0.75      0.25      0.00     98.49
09:07:33 AM     all      1.01      0.00      1.01      0.00      0.00     97.98
09:07:35 AM     all      0.25      0.00      0.51      0.00      0.00     99.24
09:07:37 AM     all      0.50      0.00      0.50      0.25      0.00     98.75
09:07:39 AM     all      0.51      0.00      0.51      0.00      0.00     98.99
Average:        all      1.81      0.00      0.91      0.10      0.00     97.18

5. 数据库的基本信息

可以使用以下脚本巡检：

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 lower(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(GB): ' || round(sum(bytes)/1024/1024/1024) || 'g' as sum_g from dba_data_files;

6. 数据库设置

show sga;

select 'db_cache_size(MB): ' || value/1024/1024 from v$parameter where lower(name) = 'db_cache_size';

select 'log_buffer(KB): ' || value/1024 from v$parameter where lower(name) = 'log_buffer';

select 'shared_pool(MB): ' || value/1024/1024 from v$parameter where lower(name) = 'shared_pool_size';

select 'java_pool_size(MB): ' || value/1024/1024 from v$parameter where lower(name) = 'java_pool_size';

select 'large_pool_size(MB): ' || value/1024/