作者 | JiekeXu
来源 | JiekeXu之路（ID: JiekeXu_IT）
转载请联系授权 | (微信ID：xxq1426321293)
大家好，我是 JiekuXu,很高兴和大家又见面了，今天分享下 Oracle DBA 工作中都需要做的数据库巡检有哪些？本文首发于微信公众号【JiekeXu之路】，欢迎点击上方蓝字关注我吧！
最近有很多小伙伴们都在陆陆续续的上班了，结束了远程办公时刻，不能浑水摸鱼了，那么我也不例外，下周开始现场轮班了，首先要做的就是检查数据库的信息，填写一张关于数据库信息的巡检表，下面我们就一起来捋一捋，Oracle DBA 工作中都需要做的数据库巡检有哪些？
一、查看有几个实例：
ps -ef|grep smon
oracle 9069 29581 0 11:02 pts/0 00:00:00 grep smon
oracle 27814 1 0 Mar30 ? 00:01:41 ora_smon_orcl2
root 28394 1 1 2019 ? 5-01:48:28 /app/product/11.2.0/grid/bin/osysmond.bin
grid 28870 1 0 2019 ? 00:13:38 asm_smon_+ASM2
oracle 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 LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-JUL-2019 10:26:56
Uptime 262 days 7 hr. 46 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/product/11.2.0/grid/network/admin/listener.ora
Listening 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
四、检查后台日志
#数据库alert
tail -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 -l
Matching 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 -c
4 AMD Opteron(TM) Processor 6234
#检查操作系统版本
#查看物理 CPU 个数：
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
2
# 查看逻辑 CPU 的个数
cat /proc/cpuinfo| grep "processor"| wc -l
4
#查看内存：
free -g
total used free shared buffers cached
Mem: 7 7 0 0 0 5
-/+ buffers/cache: 1 5
Swap: 7 0 7
--------------------AIX 系统----------------
#主机型号：
uname -um
00F8F7964C00 IBM,0206F796T
#主机序列号：
uname -uM
IBM,9179-MHD IBM,0206F796T
#查看CPU信息（型号）
prtconf|more （查看Processor Type 行）
System Model: IBM,9179-MHD
Machine Serial Number: 06F796T
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 64
Processor Clock Speed: 3724 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 3 KHYXDB1
Memory Size: 311296 MB
Good Memory Size: 311296 MB
Platform Firmware level: AM760_068
Firmware Version: IBM,AM760_068
Console Login: enable
Auto Restart: true
Full Core: false
--当然也可以过滤下：
prtconf|grep 'Processor Type'
Processor Type: PowerPC_POWER7
#查看物理CPU个数：
prtconf|grep Processors
Number Of Processors: 64
#逻辑CPU：
pmcycles -m | wc -l
256
#查看内存：
prtconf|grep Memory
Memory Size: 311296 MB
Good 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 status
LSNRCTL 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 LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-JUL-2019 10:26:56
Uptime 262 days 3 hr. 56 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /app/product/11.2.0/grid/network/admin/listener.ora
Listening 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 - oracle
sqlplus / as sysdba
#查看实例名，数据库名：
show parameter name
SYS@orcl2> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl2
lock_name_space string
log_file_name_convert string
processor_group_name string
service_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 1
db_writer_processes integer 1
gcs_server_processes integer 2
global_txn_processes integer 1
job_queue_processes integer 15
log_archive_max_processes integer 4
processes integer 2000
#查看在线日志组的大小：
set linesize 250
COLUMN 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 groupno
SELECT 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 YES
2 1 +DATA/orcl/onlinelog/group_2.262.1014371373 ONLINE CURRENT 512 NO
3 2 +DATA/orcl/onlinelog/group_3.265.1014371591 ONLINE CURRENT 512 NO
4 2 +DATA/orcl/onlinelog/group_4.266.1014371593 ONLINE INACTIVE 512 YES
【需要重点记录归档是否开启，是否有备份】
SYS@orcl2> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCH
Oldest online log sequence 66
Next log sequence to archive 67
Current 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/bdf
SYS@orcl2> ! df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 189G 60G 119G 34% /
udev 3.9G 132K 3.9G 1% /dev
tmpfs 3.9G 492M 3.4G 13% /dev/shm
/dev/sda1 1011M 62M 899M 7% /boot
#检查备份情况
【ADG 备库需要部署删除归档的脚本】：
①查询是否有备份：
col START_TIME for a30
col END_TIME for a30
col status for a10
select 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-14
XXYX 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 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
ORDER BY 4;
TABLESPACE_NAME Total g Free g USED%
------------------------------ ---------- ---------- ----------
UNDOTBS2 2 2 .38
USERS 0 0 2.63
UNDOTBS1 0 0 18.42
SYSAUX 3 1 56.53
SYSTEM 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
点亮在看，你最好看！
919
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
