oracle 数据库日常巡检：数据库基本状况、oracle资源使用、数据备份结果、性能、cpu等、数据安全、归档日志、会话、SGA/PGA使用情况
巡檢目錄
- 1. 檢查數據庫基本狀況
- 2. 檢查Oracle相關資源的使用情況
- 3. 檢查Oracle數據庫備份結果
- 4. 檢查Oracle數據庫性能
- 5. 檢查數據庫cpu、I/O、內存性能
- 6. 檢查數據庫安全性
- 7. 其他檢查
?
1. 檢查數據庫基本狀況
包含：檢查Oracle實例狀態，檢查Oracle服務進程，檢查Oracle監聽進程，共三個部分。
1.1. 檢查Oracle實例狀態
select?instance_name,host_name,startup_time,status,database_status from?v$instance;?
其中“STATUS”表示Oracle當前的實例狀態，必須為“OPEN”；“DATABASE_STATUS”表示Oracle當前數據庫的狀態，必須為“ACTIVE”。
1.2. 檢查Oracle在線日志狀態
select?group#,status,type,member from?v$logfile;?
輸出結果應該有3條以上（包含3條）記錄，“STATUS”應該為非“INVALID”，非“DELETED”。注：“STATUS”顯示為空表示正常。
1.3. 檢查Oracle表空間的狀態
select?tablespace_name,status from?dba_tablespaces;?
輸出結果中STATUS應該都為ONLINE。
1.4. 檢查Oracle所有數據文件狀態
select?name,status from?v$datafile;
輸出結果中“STATUS”應該都為“ONLINE”。或者：
select?file_name,status from?dba_data_files;?
輸出結果中“STATUS”應該都為“AVAILABLE”。
?
2. 檢查Oracle相關資源的使用情況
2.1. 檢查數據庫連接情況
查看當前會話連接數，是否屬于正常范圍。
select?count(*) from?v$session;
2.2. 檢查系統磁盤空間
如果文件系統的剩余空間過小或增長較快，需對其進行確認并刪除不用的文件以釋放空間。
[oracle@AS14 ~]$ df -h
Filesystem ???????????Size ?Used Avail Use% Mounted on
/dev/mapper/vg_ssc-LogVol00
??????????????????????902G ??12G ?845G ??2% /
tmpfs ????????????????3.9G ?8.0K ?3.9G ??1% /dev/shm
/dev/sda1 ????????????194M ??33M ?152M ?18% /boot
2.3. 檢查表空間使用情況
select tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_space from dba_data_files order by tablespace_name;
2.4. 檢查一些擴展異常的對象
select?Segment_Name,
Segment_Type,
TableSpace_Name,
(Extents /?Max_extents) *?100?Percent
From?sys.DBA_Segments
Where?Max_Extents !=?0
and?(Extents /?Max_extents) *?100?>=?95
order?By?Percent;
如果有記錄返回，則這些對象的擴展已經快達到它定義時的最大擴展值。對于這些對象要修改它的存儲結構參數。
2.5. 檢查system表空間內的內容
select?distinct?(owner)
from?dba_tables
where?tablespace_name =?'SYSTEM'
and?owner !=?'SYS'
and?owner !=?'SYSTEM'
union
select?distinct?(owner)
from?dba_indexes
where?tablespace_name =?'SYSTEM'
and?owner !=?'SYS'
and?owner !=?'SYSTEM';
如果記錄返回，則表明system表空間內存在一些非system和sys用戶的對象。應該進一步檢查這些對象是否與我們應用相關。如果相關請把這些對象移到非system表空間，同時應該檢查這些對象屬主的缺省表空間值。
2.6. 檢查對象的下一擴展與表空間的最大擴展值
select?a.table_name, a.next_extent, a.tablespace_name
from?all_tables a,
(select?tablespace_name, max(bytes) as?big_chunk
from?dba_free_space
group?by?tablespace_name) f
where?f.tablespace_name =?a.tablespace_name
and?a.next_extent >?f.big_chunk
union
select?a.index_name, a.next_extent, a.tablespace_name
from?all_indexes a,
(select?tablespace_name, max(bytes) as?big_chunk
from?dba_free_space
group?by?tablespace_name) f
where?f.tablespace_name =?a.tablespace_name
and?a.next_extent >?f.big_chunk;
如果有記錄返回，則表明這些對象的下一個擴展大于該對象所屬表空間的最大擴展值，需調整相應表空間的存儲參數。
?
3. 檢查Oracle數據庫備份結果
3.1. 檢查數據庫備份日志信息
假設：備份的臨時目錄為/backup/hotbakup，我們需要檢查2019年12月30日的備份結果，則用下面的命令來檢查：
cat /backup/hotbackup/hotbackup-19-12-30.log|grep –i error
備份腳本的日志文件為hotbackup-月份-日期-年份.log，在備份的臨時目錄下面。如果文件中存在“ERROR:”，則表明備份沒有成功，存在問題需要檢查。
?
?
4. 檢查Oracle數據庫性能
在本節主要檢查Oracle數據庫性能情況，包含：檢查數據庫的等待事件，檢查死鎖及處理，檢查cpu、I/O、內存性能，查看是否有僵死進程，檢查行鏈接/遷移，定期做統計分析，檢查緩沖區命中率，檢查共享池命中率，檢查排序區，檢查日志緩沖區，總共十個部分。
4.1. 檢查數據庫的等待事件
select event, count(*)
from v$session_wait
group by event
order by count(*) desc;
如果數據庫長時間持續出現大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件時，需要對其進行分析，可能存在問題的語句。
4.2. Disk Read最高的SQL語句的獲取
SELECT?SQL_TEXT
FROM?(SELECT?*?FROM?V$SQLAREA ORDER?BY?DISK_READS)
WHERE?ROWNUM <=?5;
4.3. 查找前十條性能差的sql
SELECT?*
FROM?(SELECT?PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM?V$SQLAREA
ORDER?BY?DISK_READS DESC)
WHERE?ROWNUM <?10;
4.4. 等待時間最多的5個系統等待事件的獲取
SELECT?*
??FROM?(SELECT?*
??????????FROM?V$SYSTEM_EVENT
?????????WHERE?EVENT NOT?LIKE?'SQL%'
?????????ORDER?BY?TOTAL_WAITS DESC)
?WHERE?ROWNUM <=?5;
4.5. 檢查碎片程度高的表
SELECT?segment_name table_name, COUNT(*) extents
??FROM?dba_segments
?WHERE?owner NOT?IN?('SYS', 'SYSTEM')
?GROUP?BY?segment_name
HAVING?COUNT(*) =?(SELECT?MAX(COUNT(*))
?????????????????????FROM?dba_segments
????????????????????GROUP?BY?segment_name);
4.6. 檢查表空間的I/O比例
SELECT?DF.TABLESPACE_NAME NAME,
???????DF.FILE_NAME???????"FILE",
???????F.PHYRDS ??????????PYR,
???????F.PHYBLKRD ????????PBR,
???????F.PHYWRTS ?????????PYW,
???????F.PHYBLKWRT ???????PBW
??FROM?V$FILESTAT F, DBA_DATA_FILES DF
?WHERE?F.FILE# =?DF.FILE_ID
?ORDER?BY?DF.TABLESPACE_NAME;?
4.7. 檢查文件系統的I/O比例
SELECT?SUBSTR(A.FILE#, 1, 2) "#",
???????SUBSTR(A.NAME, 1, 30) "NAME",
???????A.STATUS,
???????A.BYTES,
???????B.PHYRDS,
???????B.PHYWRTS
??FROM?V$DATAFILE A, V$FILESTAT B
?WHERE?A.FILE# =?B.FILE#;?
4.8.檢查死鎖及處理
查詢目前鎖對象信息：
select?sid,
???????serial#,
???????username,
???????SCHEMANAME,
???????osuser,
???????MACHINE, ????
???????terminal,
???????PROGRAM,
???????owner,
???????object_name,
???????object_type,
???????o.object_id
??from?dba_objects o, v$locked_object l, v$session s
?where?o.object_id?=?l.object_id
???and?s.sid =?l.session_id;?
select sid,serial#,username,SCHEMANME,osuser,MACHINE,terminal,program,owner,object_name,object_type,o.object_id from dba_objects o,v$locked_object l,v$session s whereoracle級kill掉該session：
alter?system kill?session '&sid,&serial#';
操作系統級kill掉session：
#>kill -9 pid
?
?
5. 檢查數據庫CPU,I/O,內存性能
記錄數據庫的cpu使用、IO、內存等使用情況，使用vmstat,iostat,sar,top等命令進行信息收集并檢查這些信息，判斷資源使用情況。
5.1 CPU使用情況：
[root@sale8 ~]# top
查看系統剩余的cpu，當其平均值下降至10%以下的時視為CPU使用率異常，需記錄下該數值，并將狀態記為異常。
5.2 內存使用情況：
[root@sale8 ~]# free -m
查看系統剩余內存，當剩余內存低于總內存的10%時視為異常。
5.3 系統I/O情況：
[root@sale8 ~]# iostat -k 1 3
查看cpu IO等待情況。
5.4 系統負載情況：
[root@sale8 ~]#uptime
最后3個數值如果有高于2.5的時候就表明系統在超負荷運轉了，并將此值記錄到巡檢表，視為異常。
5.5.查看是否有僵尸進程
select?spid from?v$process where?addr not?in?(select?paddr from?v$session);?
有些僵尸進程有阻塞其他業務的正常運行，定期殺掉僵尸進程。
5.6?檢查緩沖區命中率
SELECT?a.VALUE +?b.VALUE logical_reads,
???????c.VALUE phys_reads,
???????round(100?*?(1?-?c.value /?(a.value +?b.value)), 4) hit_ratio
??FROM?v$sysstat a, v$sysstat b, v$sysstat c
?WHERE?a.NAME =?'db block gets'
???AND?b.NAME =?'consistent gets'
???AND?c.NAME =?'physical reads';
如果命中率低于90%則需加大數據庫參數db_cache_size。
5.7?檢查共享池命中率
select?sum(pinhits) /?sum(pins) *?100?from?v$librarycache;?
如低于95%，則需要調整應用程序使用綁定變量，或者調整數據庫參數shared pool的大小。
5.8?檢查排序區
select?name,value from?v$sysstat where?name like?'%sort%';?
如果disk/(memoty+row)的比例過高，則需要調整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。
5.9?檢查日志緩沖區
select?name,value from?v$sysstat where?name in?('redo entries','redo buffer allocation retries');
如果redo buffer allocation retries/redo entries超過1%，則需要增大log_buffer。
?
?
6. 檢查數據庫安全性
本節主要檢查Oracle數據庫的安全性，包含：檢查系統安全信息，定期修改密碼，總共兩個部分。
6.1. 檢查系統安全日志信息
系統安全日志文件的目錄在/var/log下，主要檢查登錄成功或失敗的用戶日志信息。
檢查登錄成功的日志：
[root@rac2 ~]# grep -i accepted /var/log/secure
Jan 8 08:44:43 rac2 sshd[29559]: Accepted password for root from ::ffff:10.10.10.6 port 1119 ssh2……
檢查登錄失敗的日志：
[root@rac2 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure
Jan 9 10:30:44 rac2 sshd[3071]: Invalid user ydbuser from ::ffff:192.168.3.5
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2
Jan 9 10:30:56 rac2 sshd[3071]: Failed password for invalid user ydbuser from ::ffff:192.168.3.5 port 36005 ssh2
Jan 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2
在出現的日志信息中沒有錯誤(Invalid、refused)提示，如果沒有(Invalid、refused)視為系統正常，出現錯誤提示，應作出系統告警通知。
6.2. 檢查用戶修改密碼
在數據庫系統上往往存在很多的用戶，如：第三方數據庫監控系統，初始安裝數據庫時的演示用戶，管理員用戶等等，這些用戶的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統甚至進行修改數據。需要修改密碼的用戶包括：
數據庫管理員用戶SYS，SYSTEM；其他用戶。
登陸系統后，提示符下輸入cat /etc/passwd，在列出來的用戶中查看是否存在已經不再使用的或是陌生的帳號。若存在，則記錄為異常。
修改密碼方法：
alter?user?USER_NAME?identified by?PASSWORD;?
?
?
7. 其他檢查
在本節主要檢查當前crontab任務是否正常，檢查Oracle Job是否有失敗等共六個部分。
7.1 檢查當前crontab任務是否正常
[oracle@AS14 ~]$ crontab -l
7.2 Oracle Job是否有失敗
select?job,what,last_date,next_date,failures,broken from?dba_jobs Where?schema_user='CAIKE';
如有問題建議重建job
7.3. 監控數據量的增長情況
select?A.tablespace_name, (1?-?(A.total) /?B.total) *?100?used_percent
??from?(select?tablespace_name, sum(bytes) total
??????????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;
根據本周每天的檢查情況找到空間擴展很快的數據庫對象,并采取相應的措施：
--刪除歷史數據
移動規定數據庫中至少保留6個月的歷史數據，所以以前的歷史數據可以考慮備份然后進行清除以便釋放其所占的資源空間。
---擴表空間
alter?tablespace <tablespace_name>?add?datafile ‘<file>’ size <size>?autoextend off;
?
注意：在數據庫結構發生變化時，如增加了表空間，增加了數據文件或重做日志文件這些操作，都會造成Oracle數據庫控制文件的變化，DBA應及進行控制文件的備份，備份方法是：
執行SQL語句：
alter?database?backup?controlfile to?'/home/backup/control.bak';
或：
alter?database?backup?controlfile to?trace;
這樣，會在USER_DUMP_DEST(初始化參數文件中指定)目錄下生成創建控制文件的SQL命令。
7.4. 檢查無效的trigger
SELECT?owner, trigger_name, table_name, status FROM?dba_triggers WHERE?status =?'DISABLED';
如有失效觸發器則啟用，如：
alter?Trigger?TRIGGER_NAME Enable;?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
?
? 數據庫歸檔日志狀態情況
SQL> archive log list;
?
查詢歸檔日志存儲大小
SQL> show parameter db_recovery_file_dest_size; SQL> select sum(a.BLOCK_SIZE*a.BLOCKS)/1024/1024 from v$archived_log a where a.DELETED='NO';.??? 查看鎖住的表
?SELECT b.owner,b.object_name,a.session_id,a.locked_mode
??????? FROM v$locked_object a ,dba_objects b
??????? WHERE b.object_id = a.object_id;
查看被鎖住的會話
SELECT b.username,b.sid,b.serial#,logon_timeFROM v$locked_object a,v$session bWHERE a.session_id = b.sid order by b.logon_time;如果要斷開某個會話，執行
? Alter system kill session ‘sid,serial#’
?
+++++++++++++++++++++++++++++++++++++++++++=
SGA、PGA使用情況SQL> select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from (select 'SGA' name,(select sum(value/1024/1024) from v$sga) total, (select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from du 2 3 al) union select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from ( select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total, (select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual); 4 5 6 7 NAM TOTAL USED FREE PCTUSED --- ---------- ---------- ---------- ---------- PGA 25088 1596.19 23491.81 6.36 SGA 71614.8477 65943.05 5671.8 92.08?
?
以上，為oracle數據庫巡檢全部內容。
總結
以上是生活随笔為你收集整理的oracle 数据库日常巡检：数据库基本状况、oracle资源使用、数据备份结果、性能、cpu等、数据安全、归档日志、会话、SGA/PGA使用情况的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： VCenter (虚拟架构环境的集中管理
- 下一篇： 一 如何检查oracle的归档日志及空