作者 | JiekeXu
來源 | JiekeXu之路（ID: JiekeXu_IT）
轉載請聯系授權 | (微信ID：xxq1426321293)
大家好，我是 JiekuXu,很高興和大家又見面了，今天分享下 Oracle DBA 工作中都需要做的數據庫巡檢有哪些？本文首發于微信公眾號【JiekeXu之路】，歡迎點擊上方藍字關注我吧！
最近有很多小伙伴們都在陸陸續續的上班了，結束了遠程辦公時刻，不能渾水摸魚了，那么我也不例外，下周開始現場輪班了，首先要做的就是檢查數據庫的信息，填寫一張關于數據庫信息的巡檢表，下面我們就一起來捋一捋，Oracle DBA 工作中都需要做的數據庫巡檢有哪些？
一、查看有幾個實例：
ps -ef|grep smonoracle 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 - gridcs-testr2:/home/grid$crsctl status res -t--------------------------------------------------------------------------------NAME TARGET STATE SERVER STATE_DETAILS--------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.ARCH.dgONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.DATA.dgONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.LISTENER.lsnrONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.OCR.dgONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.asmONLINE ONLINE cs-testr1 StartedONLINE ONLINE cs-testr2 Startedora.gsdOFFLINE OFFLINE cs-testr1OFFLINE OFFLINE cs-testr2ora.net1.networkONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.onsONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2ora.registry.acfsONLINE ONLINE cs-testr1ONLINE ONLINE cs-testr2--------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr1 ONLINE ONLINE cs-testr1ora.cs-testr1.vip1 ONLINE ONLINE cs-testr1ora.cs-testr2.vip1 ONLINE ONLINE cs-testr2ora.cvu1 ONLINE ONLINE cs-testr1ora.oc4j1 ONLINE ONLINE cs-testr1ora.orcl.db1 ONLINE ONLINE cs-testr1 Open2 ONLINE ONLINE cs-testr2 Openora.scan1.vip1 ONLINE ONLINE cs-testr1
#檢查監聽狀態cs-testr2:/home/grid$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-APR-2020 18:13:27Copyright (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 -l45
四、檢查后臺日志
#數據庫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:runaspwUser 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 -gtotal 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:20Copyright (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 nameNAME 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 processesSYS@orcl2> show parameter processesNAME 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 EXTERN2 DATA 100 92 EXTERN3 OCR 3 2.09570313 NORMAL#若文件系統：df -h/df -g/bdfSYS@orcl2> ! df -hFilesystem 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_detailswhere 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 .28944444463841 DB INCR COMPLETED 2020-03-30 00:20 2020-03-30 03:01 2.6783333363843 ARCHIVELOG COMPLETED 2020-03-30 00:55 2020-03-30 01:25 .50638888963851 ARCHIVELOG COMPLETED 2020-03-30 07:30 2020-03-30 08:06 .59944444463859 ARCHIVELOG COMPLETED 2020-03-30 13:55 2020-03-30 14:08 .22055555663867 ARCHIVELOG COMPLETED 2020-03-30 19:12 2020-03-30 19:26 .23888888963875 DB INCR COMPLETED 2020-03-31 00:01 2020-03-31 02:42 2.6880555663877 ARCHIVELOG COMPLETED 2020-03-31 00:28 2020-03-31 01:06 .63666666763885 ARCHIVELOG COMPLETED 2020-03-31 06:48 2020-03-31 07:26 .63563893 ARCHIVELOG COMPLETED 2020-03-31 12:13 2020-03-31 12:31 .30222222263901 ARCHIVELOG COMPLETED 2020-03-31 19:30 2020-03-31 19:44 .23416666763909 DB INCR COMPLETED 2020-04-01 00:26 2020-04-01 02:38 2.1902777863911 ARCHIVELOG COMPLETED 2020-04-01 00:31 2020-04-01 00:39 .147563919 ARCHIVELOG COMPLETED 2020-04-01 06:29 2020-04-01 07:08 .64861111163927 ARCHIVELOG COMPLETED 2020-04-01 12:36 2020-04-01 12:48 .2025SQL> col END_TIME for a20SQL> 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 55722.10T 05-FEB-20 06-FEB-20 COMPLETED DB INCR 66192.30T 06-FEB-20 07-FEB-20 COMPLETED DB INCR 93052.18T 08-FEB-20 08-FEB-20 COMPLETED DB INCR 75962.39T 09-FEB-20 09-FEB-20 COMPLETED DB INCR 115992.26T 09-FEB-20 10-FEB-20 COMPLETED DB INCR 786228.11T 10-FEB-20 11-FEB-20 COMPLETED DB INCR 892201.63T 12-FEB-20 12-FEB-20 COMPLETED DB INCR 76502.53T 12-FEB-20 13-FEB-20 COMPLETED DB INCR 89452.54T 14-FEB-20 14-FEB-20 COMPLETED DB INCR 7906
①查看有沒有備庫：SQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string SERVICE=xxyx3 VALID_FOR=(ONLINE_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 150col ACTION_TIME for a30col ACTION for a8col NAMESPACE for a8col VERSION for a10col BUNDLE_SERIES for a5col COMMENTS for a20select * 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 EXTERN2 DATA 100 92 EXTERN3 OCR 3 2.09570313 NORMAL
#數據庫表空間大小：
SELECT round(SUM(bytes / (1024*1024*1024)), 0) "ts_size g"FROM dba_tablespaces t, dba_data_files dWHERE 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 d3 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
點亮在看，你最好看！
文章轉載自JiekeXu之路，如果涉嫌侵權，請發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
