其他
你知道 DBA 工作中都要做的巡检有哪些吗？
作者 | 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
点亮在看，你最好看！