作者 | JiekeXu

来源 | JiekeXu之路（ID: JiekeXu_IT）

转载请联系授权 | (微信ID：xxq1426321293)

大家好，我是 JiekeXu,很高兴和大家又见面了，今天分享下 Oracle DBA 工作中都需要做的数据库巡检有哪些？本文首发于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我吧！

最近有很多小伙伴们都在陆陆续续的上班了，结束了远程办公时刻，不能浑水摸鱼了，那么我也不例外，下周开始现场轮班了，首先要做的就是检查数据库的信息，填写一张关于数据库信息的巡检表，下面我们就一起来捋一捋，Oracle DBA 工作中都需要做的数据库巡检有哪些？

一、查看有几个实例：

ps -ef|grep smon
oracle    9069 29581  0 11:02 pts/0    00:00:00 grep smonoracle   27814     1  0 Mar30 ?        00:01:41 ora_smon_orcl2root     28394     1  1  2019 ?        5-01:48:28 /app/product/11.2.0/grid/bin/osysmond.bingrid     28870     1  0  2019 ?        00:13:38 asm_smon_+ASM2oracle   32266     1  0  2019 ?        00:15:40 ora_smon_PROD3

二、查看数据库状态

su - grid cs-testr2:/home/grid$crsctl status res -t --------------------------------------------------------------------------------NAME           TARGET  STATE        SERVER                   STATE_DETAILS       --------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.ARCH.dg               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.DATA.dg               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.LISTENER.lsnr               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.OCR.dg               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.asm               ONLINE  ONLINE       cs-testr1           Started                            ONLINE  ONLINE       cs-testr2           Started             ora.gsd               OFFLINE OFFLINE      cs-testr1                                              OFFLINE OFFLINE      cs-testr2                               ora.net1.network               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.ons               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               ora.registry.acfs               ONLINE  ONLINE       cs-testr1                                              ONLINE  ONLINE       cs-testr2                               --------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr      1        ONLINE  ONLINE       cs-testr1                               ora.cs-testr1.vip      1        ONLINE  ONLINE       cs-testr1                               ora.cs-testr2.vip      1        ONLINE  ONLINE       cs-testr2                               ora.cvu      1        ONLINE  ONLINE       cs-testr1                               ora.oc4j      1        ONLINE  ONLINE       cs-testr1                               ora.orcl.db      1        ONLINE  ONLINE       cs-testr1           Open                      2        ONLINE  ONLINE       cs-testr2           Open                ora.scan1.vip      1        ONLINE  ONLINE       cs-testr1

#检查监听状态cs-testr2:/home/grid$ lsnrctl status 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-APR-2020 18:13:27
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date                23-JUL-2019 10:26:56Uptime                    262 days 7 hr. 46 min. 31 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /app/product/11.2.0/grid/network/admin/listener.oraListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM2", status READY, has 1 handler(s) for this service...Service "ORCLXDB.us.exampl.com" has 1 instance(s).  Instance "PROD3", status READY, has 1 handler(s) for this service...Service "PROD3.us.exampl.com" has 1 instance(s).  Instance "PROD3", status READY, has 1 handler(s) for this service...Service "orcl" has 1 instance(s).  Instance "orcl2", status READY, has 1 handler(s) for this service...Service "orclXDB" has 1 instance(s).  Instance "orcl2", status READY, has 1 handler(s) for this service...The command completed successfully

三、检查用户连接

cs-testr2:/home/grid$ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' | wc -l  45

四、检查后台日志

#数据库alerttail -500f  $ORACLE_BASE/diag/rdbms/orcl/orcl2/trace/alert_orcl2.log |more#ASM 日志tail -500f  $ORACLE_BASE/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log#集群日志tail -200f  $ORACLE_HOME/log/cs-testr2/alertcs-testr2.log#监听日志tail -200f $ORACLE_HOME/network/log/listener.log

五、检查 sudo 配置;

sudo -lMatching Defaults entries for oracle on this host:    runaspw

User oracle may run the following commands on this host:    (root) NOPASSWD: /bin/kill, /usr/bin/printenv, /app/oraInventory/orainstRoot.sh, /app/product/11.2.0/db/root.sh, /app/product/11.2.0/db/OPatch/opatch

-------------------------------------------------------------------

以上完成了基本巡检，下面需要填写一些固定信息

-------------------------------------------------------------------

六、完善表格中的所有信息列（更新+补充）:

-------------以 SUSE 系统为例：------------

#查看CPU信息（型号）

cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c4  AMD Opteron(TM) Processor 6234

#检查操作系统版本

#查看物理 CPU 个数：

cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l2

# 查看逻辑 CPU 的个数

cat /proc/cpuinfo| grep "processor"| wc -l4

#查看内存：

free -g             total       used       free     shared    buffers     cachedMem:             7          7          0          0          0          5-/+ buffers/cache:          1          5Swap:            7          0          7

--------------------AIX 系统----------------

#主机型号：uname -um00F8F7964C00 IBM,0206F796T
#主机序列号：uname -uMIBM,9179-MHD IBM,0206F796T

#查看CPU信息（型号）

prtconf|more （查看Processor Type 行）System Model: IBM,9179-MHDMachine Serial Number: 06F796TProcessor Type: PowerPC_POWER7Processor Implementation Mode: POWER 7Processor Version: PV_7_CompatNumber Of Processors: 64Processor Clock Speed: 3724 MHzCPU Type: 64-bitKernel Type: 64-bitLPAR Info: 3 KHYXDB1Memory Size: 311296 MBGood Memory Size: 311296 MBPlatform Firmware level: AM760_068Firmware Version: IBM,AM760_068Console Login: enableAuto Restart: trueFull Core: false

--当然也可以过滤下：prtconf|grep 'Processor Type'Processor Type: PowerPC_POWER7

#查看物理CPU个数：

prtconf|grep ProcessorsNumber Of Processors: 64

#逻辑CPU：

pmcycles -m | wc -l256

#查看内存：

prtconf|grep MemoryMemory Size: 311296 MBGood Memory Size: 311296 MB+ mem0                                                                             Memory

--------------HPUX:命令输出信息省略-------------

#查看主机型号 machinfo(Model)#主机序列号：machinfo（查看Platform info:下的Machine serial number）#查看CPU信息（型号）machinfo
#查看物理CPU个数（HP只有物理C）：machinfo（查看CPU info下的24 logical processors，24即为cpu个数）#内存：machinfo

#查看主机名：hostname
#查看 ip 地址映射：cat /etc/hosts#查看端口号：lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-APR-2020 14:23:20

Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date                23-JUL-2019 10:26:56Uptime                    262 days 3 hr. 56 min. 23 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /app/product/11.2.0/grid/network/admin/listener.oraListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1XX.XX.XX)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM2", status READY, has 1 handler(s) for this service...Service "ORCLXDB.us.exampl.com" has 1 instance(s).  Instance "PROD3", status READY, has 1 handler(s) for this service...Service "PROD3.us.exampl.com" has 1 instance(s).  Instance "PROD3", status READY, has 1 handler(s) for this service...Service "orcl" has 1 instance(s).  Instance "orcl2", status READY, has 1 handler(s) for this service...Service "orclXDB" has 1 instance(s).  Instance "orcl2", status READY, has 1 handler(s) for this service...

-----------------数据库实例级别检查------------------

su - oraclesqlplus / as sysdba

#查看实例名，数据库名：

show parameter nameSYS@orcl2> show parameter name
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cell_offloadgroup_name               stringdb_file_name_convert                 stringdb_name                              string      orcldb_unique_name                       string      orclglobal_names                         boolean     FALSEinstance_name                        string      orcl2lock_name_space                      stringlog_file_name_convert                stringprocessor_group_name                 stringservice_names                        string      orcl

#查看字符集：

select userenv('language') from dual;
SYS@orcl2> select userenv('language') from dual;
USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

#查看连接数：

show parameter processes
SYS@orcl2> show parameter processes
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------aq_tm_processes                      integer     1db_writer_processes                  integer     1gcs_server_processes                 integer     2global_txn_processes                 integer     1job_queue_processes                  integer     15log_archive_max_processes            integer     4processes                            integer     2000

#查看在线日志组的大小：

set linesize 250COLUMN groupno                  FORMAT a6                 HEADING 'Group'   COLUMN thread                     FORMAT a6                 HEADING 'Thread'   COLUMN member                  FORMAT a50                HEADING 'Member'         COLUMN redo_file_type         FORMAT a10                HEADING 'Redo Type'      COLUMN group_status           FORMAT a12                HEADING 'Group Status'COLUMN member_status       FORMAT a15                HEADING 'Member Status'     COLUMN bytes                       FORMAT 999,999            HEADING 'Size(M)'        COLUMN archived                  FORMAT a10                HEADING 'Archived'BREAK ON groupnoSELECT to_char(f.group#)  groupno, to_char(l.thread#) thread, f.member member, f.type  redo_file_type, l.status group_status, f.status member_status, l.bytes/1024/1024 bytes, l.archived archived FROM v$logfile f, v$log l WHERE f.group# = l.group# ORDER BY f.group#, f.member;
Group  Thread Member                                             Redo Type  Group Status Member Status    Size(M) Archived------ ------ -------------------------------------------------- ---------- ------------ --------------- -------- ----------1      1      +DATA/orcl/onlinelog/group_1.261.1014371369        ONLINE     INACTIVE                          512 YES2      1      +DATA/orcl/onlinelog/group_2.262.1014371373        ONLINE     CURRENT                           512 NO3      2      +DATA/orcl/onlinelog/group_3.265.1014371591        ONLINE     CURRENT                           512 NO4      2      +DATA/orcl/onlinelog/group_4.266.1014371593        ONLINE     INACTIVE                          512 YES

【需要重点记录归档是否开启，是否有备份】

SYS@orcl2> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            +ARCHOldest online log sequence     66Next log sequence to archive   67Current log sequence           67

#归档目录大小根据情况查：

#若ASM：select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;GROUP_NUMBER NAME                             TOTAL_GB    FREE_GB TYPE------------ ------------------------------ ---------- ---------- ------           1 ARCH                                   50 4.63964844 EXTERN           2 DATA                                  100         92 EXTERN           3 OCR                                     3 2.09570313 NORMAL           #若文件系统：df -h/df -g/bdfSYS@orcl2> ! df -h Filesystem      Size  Used Avail Use% Mounted on/dev/sda3       189G   60G  119G  34% /udev            3.9G  132K  3.9G   1% /devtmpfs           3.9G  492M  3.4G  13% /dev/shm/dev/sda1      1011M   62M  899M   7% /boot

#检查备份情况

【ADG 备库需要部署删除归档的脚本】：

①查询是否有备份：col START_TIME for a30col END_TIME for a30col status for a10select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,ELAPSED_SECONDS / 3600 from v$rman_backup_job_details where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi') order by SESSION_KEY;SESSION_KEY INPUT_TYPE    STATUS     START_TIME                     END_TIME                       ELAPSED_SECONDS/3600----------- ------------- ---------- ------------------------------ ------------------------------ --------------------      63833 ARCHIVELOG    COMPLETED  2020-03-29 19:13               2020-03-29 19:31                         .289444444      63841 DB INCR       COMPLETED  2020-03-30 00:20               2020-03-30 03:01                         2.67833333      63843 ARCHIVELOG    COMPLETED  2020-03-30 00:55               2020-03-30 01:25                         .506388889      63851 ARCHIVELOG    COMPLETED  2020-03-30 07:30               2020-03-30 08:06                         .599444444      63859 ARCHIVELOG    COMPLETED  2020-03-30 13:55               2020-03-30 14:08                         .220555556      63867 ARCHIVELOG    COMPLETED  2020-03-30 19:12               2020-03-30 19:26                         .238888889      63875 DB INCR       COMPLETED  2020-03-31 00:01               2020-03-31 02:42                         2.68805556      63877 ARCHIVELOG    COMPLETED  2020-03-31 00:28               2020-03-31 01:06                         .636666667      63885 ARCHIVELOG    COMPLETED  2020-03-31 06:48               2020-03-31 07:26                               .635      63893 ARCHIVELOG    COMPLETED  2020-03-31 12:13               2020-03-31 12:31                         .302222222      63901 ARCHIVELOG    COMPLETED  2020-03-31 19:30               2020-03-31 19:44                         .234166667      63909 DB INCR       COMPLETED  2020-04-01 00:26               2020-04-01 02:38                         2.19027778      63911 ARCHIVELOG    COMPLETED  2020-04-01 00:31               2020-04-01 00:39                              .1475      63919 ARCHIVELOG    COMPLETED  2020-04-01 06:29               2020-04-01 07:08                         .648611111      63927 ARCHIVELOG    COMPLETED  2020-04-01 12:36               2020-04-01 12:48                              .2025
SQL> col END_TIME for a20 SQL> select input_bytes_display,start_time,end_time,status,input_type,ELAPSED_SECONDS from V$RMAN_BACKUP_JOB_DETAILS  where input_type ='DB INCR' order by start_time;
INPUT_BYTES_DISPLAY  START_TIME           END_TIME             STATUS     INPUT_TYPE    ELAPSED_SECONDS-------------------- -------------------- -------------------- ---------- ------------- ---------------    1.61T            04-FEB-20            05-FEB-20            COMPLETED  DB INCR                  5572    2.10T            05-FEB-20            06-FEB-20            COMPLETED  DB INCR                  6619    2.30T            06-FEB-20            07-FEB-20            COMPLETED  DB INCR                  9305    2.18T            08-FEB-20            08-FEB-20            COMPLETED  DB INCR                  7596    2.39T            09-FEB-20            09-FEB-20            COMPLETED  DB INCR                 11599    2.26T            09-FEB-20            10-FEB-20            COMPLETED  DB INCR                  7862   28.11T            10-FEB-20            11-FEB-20            COMPLETED  DB INCR                 89220    1.63T            12-FEB-20            12-FEB-20            COMPLETED  DB INCR                  7650    2.53T            12-FEB-20            13-FEB-20            COMPLETED  DB INCR                  8945    2.54T            14-FEB-20            14-FEB-20            COMPLETED  DB INCR                  7906

#若数据库有备库，查看备库是否正常，若异常及时恢复；

①查看有没有备库：
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2                   string      SERVICE=xxyx3  VALID_FOR=(ON                                                 LINE_LOGFILES,PRIMARY_ROLE) DB                                                 _UNIQUE_NAME=xxyx3
②查看备库同步情况，备库查询：set linesize 150;set pagesize 20;column name format a13;column value format a20;column unit format a30;column TIME_COMPUTED format a30;select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

#查看数据库版本：

set line 150   col ACTION_TIME for a30    col ACTION for a8    col NAMESPACE for a8    col VERSION for a10    col BUNDLE_SERIES for a5    col COMMENTS for a20    select * from dba_registry_history;ACTION_TIME                    ACTION   NAMESPAC VERSION            ID BUNDL COMMENTS------------------------------ -------- -------- ---------- ---------- ----- --------------------17-JUL-14 05.30.00.525281 PM   APPLY    SERVER   11.2.0.3            9 PSU   PSU 11.2.0.3.9

#查看数据库补丁版本：

PS：这项忘记了，截个图吧。

su -grid$ORACLE_HOME/OPatch/opatch lsinventory

#安装日期：

select NAME,CREATED from gv$database;SQL> select NAME,CREATED from gv$database;
NAME          CREATED------------- ------------------XXYX        17-JUL-14XXYX        17-JUL-14

#表空间使用情况（超过 80% 需扩容，磁盘使用超过 85% 需要加盘）

SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"FROM (SELECT tablespace_name,SUM(bytes) free FROMDBA_FREE_SPACEGROUP BY tablespace_name ) a,(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILESGROUP BY tablespace_name) bWHERE a.tablespace_name=b.tablespace_nameORDER BY 4;TABLESPACE_NAME                   Total g     Free g      USED%------------------------------ ---------- ---------- ----------UNDOTBS2                                2          2        .38USERS                                   0          0       2.63UNDOTBS1                                0          0      18.42SYSAUX                                  3          1      56.53SYSTEM                                  1          0      99.45

#磁盘使用情况：

select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;GROUP_NUMBER NAME                             TOTAL_GB    FREE_GB TYPE------------ ------------------------------ ---------- ---------- ------           1 ARCH                                   50 4.63964844 EXTERN           2 DATA                                  100         92 EXTERN           3 OCR                                     3 2.09570313 NORMAL

#数据库表空间大小：

SELECT round(SUM(bytes / (1024*1024*1024)), 0) "ts_size g" FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name;

SYS@orcl2> SELECT round(SUM(bytes / (1024*1024*1024)), 0) "ts_size g"   2  FROM dba_tablespaces t, dba_data_files d   3  WHERE t.tablespace_name = d.tablespace_name;
 ts_size g----------         6

#数据库表空间已使用大小（GB）(数据量)：

select sum(bytes)/1024/1024 mb from dba_segments;SYS@orcl2> select sum(bytes)/1024/1024 mb from dba_segments;        MB----------  2712.375

好了，到这里就算完事了，可以休息了。写作不易，此文如果对你有帮助，请支持点“在看”与转发，你的支持便是我最大的动力，让我们一起努力做更好的自己！

全国计算机等级考试二级 Python 软件安装指南

Oracle 11GR2 RAC 最新补丁 190416 安装指导

你该知道的 Oracle 认证那些事儿（送 OCP 题库）

三万字打造 91 道 MySQL 面试题【建议收藏】

Oracle 软件包及补丁包免费下载及简单说明

Oracle 12C 最新补丁下载与安装操作指北

Oracle OCP考试经验总结与心得体会

Oracle 12CR2 安装配置与基础学习

Windows 环境下安装 Oracle 19C

点亮在看，你最好看！

你知道 DBA 工作中都要做的巡检有哪些吗？