高效進行Oracle日常巡檢：數據庫性能與安全檢查
相關文章《高效進行 Oracle 日常巡檢之數據庫基本情況檢查》
前言
對于線上的業務，oracle的數據庫運行的穩定性和安全性是用戶關心的一個至關重要的問題，除了通過監控平臺對數據庫進行監控以外，還需要定期對數據庫進行“體檢”，是保障數據庫穩定運行的必不可的輔助手段。本文簡要介紹在系列一的基礎上需要巡檢的內容，主要包括資源使用、性能、安全性等。
一、檢查oracle相關資源的使用情況
主要檢查Oracle相關資源的使用情況，包含：
- 檢查Oracle初始化文件中相關的參數值
- 檢查數據庫連接情況
- 檢查系統磁盤空間
- 檢查Oracle各個表空間使用情況
- 檢查一些擴展異常的對象
- 檢查system表空間內的內容
- 檢查對象的下一擴展與表空間的最大擴展值
總共七個部分。
1. 檢查oracle初始化文件中相關參數
若LIMITVALU-MAXUTILIZATION<=5，則表明與RESOURCENAME相關的Oracle初始化參數需要調整。可以通過修改Oracle初始化參數文件$ORACLEBASE/admin/ORCL/pfile/initORCL.ora來修改。
2. 檢查數據庫連接情況
查看當前會話連接數，是否屬于正常范圍。
其中：
- SID 會話(session)的ID號;
- SERIAL# 會話的序列號，和SID一起用來唯一標識一個會話;
- USERNAME 建立該會話的用戶名;
- PROGRAM 這個會話是用什么工具連接到數據庫的;
- STATUS 當前這個會話的狀態，ACTIVE表示會話正在執行某些任務，INACTIVE表示當前會話沒有執行任何操作;
3. 檢查系統磁盤空間
如果文件系統的剩余空間過小或增長較快，需對其進行確認并刪除不用的文件以釋放空間。
4. 檢查表空間的使用情況
如果空閑率%Free小于10%以上(包含10%)，則注意要增加數據文件來擴展表空間而不要是用數據文件的自動擴展功能。
5. 檢查一些擴展異常的對象
如果有記錄返回，則這些對象的擴展已經快達到它定義時的最大擴展值。對于這些對象要修改它的存儲結構參數。
6. 檢查system表空間內的內容
如果記錄返回，則表明system表空間內存在一些非system和sys用戶的對象。應該進一步檢查這些對象是否與我們應用相關。如果相關請把這些對象移到非System表空間，同時應該檢查這些對象屬主的缺省表空間值。
7. 檢查對象的下一擴展與表空間的最大擴展值
如果有記錄返回，則表明這些對象的下一個擴展大于該對象所屬表空間的最大擴展值，需調整相應表空間的存儲參數。
二、檢查數據庫的性能
檢查Oracle數據庫性能情況，包含：
- 檢查數據庫的等待事件
- 檢查死鎖及處理
- 檢查cpu、I/O、內存性能
- 查看是否有僵死進程
- 檢查行鏈接/遷移
- 定期做統計分析
- 檢查緩沖區命中率
- 檢查共享池命中率
- 檢查排序區
- 檢查日志緩沖區
總共十個部分。
1. 檢查數據庫的等待事件
- set pages 80
- set lines 120
- col event for a40
- select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
如果數據庫長時間持續出現大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件時，需要對其進行分析，可能存在問題的語句。
2. Disk Read最高的SQL語句的獲取
- SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
- WHERE ROWNUM<=5 desc;
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. 等待時間最多的5個系統等待事件的獲取
- SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
5. 檢查運行很久的SQL
- COLUMN USERNAME FORMAT A12
- COLUMN OPNAME FORMAT A16
- COLUMN PROGRESS FORMAT A8
- SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
6. 檢查消耗CPU最高的進程
- SET LINE 240
- SET VERIFY OFF
- COLUMN SID FORMAT 999
- COLUMN PID FORMAT 999
- COLUMN S_# FORMAT 999
- COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
- COLUMN PROGRAM FORMAT A29
- COLUMN SQL FORMAT A60
- COLUMN OSNAME FORMAT A9 HEADING "OS USER"
- SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%';
7. 檢查碎片程序高的表
- SQL> 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);
8. 檢查表空間的 I/O 比例
- SQL>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;
9. 檢查文件系統的 I/O 比例
- SQL>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#;
10. 檢查死鎖及處理
查詢目前鎖對象信息：
- col sid for 999999
- col username for a10
- col schemaname for a10
- col osuser for a16
- col machine for a16
- col terminal for a20
- col owner for a10
- col object_name for a30
- col object_type for a10
- 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;
oracle級kill掉該session：
- alter system kill session '&sid,&serial#';
操作系統級kill掉session：
- #>kill -9 pid
11.查看是否有僵死進程
- select spid from v$process where addr not in (select paddr from v$session);
有些僵尸進程有阻塞其他業務的正常運行，定期殺掉僵尸進程。
12. 檢查緩沖區命令中率
13. 檢查共享池命令中率
如低于95%，則需要調整應用程序使用綁定變量，或者調整數據庫參數shared pool的大小。
14.檢查排序區
如果disk/(memoty+row)的比例過高，則需要調整sortareasize(workareasizepolicy=false)或pgaaggregatetarget(workareasizepolicy=true)。
15. 檢查日志緩中區
如果redo buffer allocation retries/redo entries 超過1% ，則需要增大log_buffer。
三、檢查數據庫cpu、I/O、內存性能
1. CPU使用情況
- top
2. 內存使用情況
- free -m
3. 系統io情況
四、檢查Oracle數據庫的安全性
主要檢查Oracle數據庫的安全性，包含：檢查系統安全信息，定期修改密碼，總共兩個部分。
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)視為系統正常，出現錯誤提示，應作出系統告警通知。
2. 檢查用戶修改密碼
數據庫系統上往往存在很多的用戶，如：第三方數據庫監控系統，初始安裝數據庫時的演示用戶，管理員用戶等等，這些用戶的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統甚至進行修改數據。需要修改密碼的用戶包括： 數據庫管理員用戶SYS，SYSTEM;其他用戶。
修改密碼方法：
- Sql>alter user USER_NAME identified by PASSWORD;
