相關(guān)文章《高效進行 Oracle 日常巡檢之數(shù)據(jù)庫基本情況檢查》

前言

​對于線上的業(yè)務(wù)，oracle的數(shù)據(jù)庫運行的穩(wěn)定性和安全性是用戶關(guān)心的一個至關(guān)重要的問題，除了通過監(jiān)控平臺對數(shù)據(jù)庫進行監(jiān)控以外，還需要定期對數(shù)據(jù)庫進行“體檢”，是保障數(shù)據(jù)庫穩(wěn)定運行的必不可的輔助手段。本文簡要介紹在系列一的基礎(chǔ)上需要巡檢的內(nèi)容，主要包括資源使用、性能、安全性等。

[[269462]]

一、檢查oracle相關(guān)資源的使用情況

主要檢查Oracle相關(guān)資源的使用情況，包含：

• 檢查Oracle初始化文件中相關(guān)的參數(shù)值
• 檢查數(shù)據(jù)庫連接情況
• 檢查系統(tǒng)磁盤空間
• 檢查Oracle各個表空間使用情況
• 檢查一些擴展異常的對象
• 檢查system表空間內(nèi)的內(nèi)容
• 檢查對象的下一擴展與表空間的最大擴展值

總共七個部分。

1. 檢查oracle初始化文件中相關(guān)參數(shù)

若LIMITVALU-MAXUTILIZATION<=5，則表明與RESOURCENAME相關(guān)的Oracle初始化參數(shù)需要調(diào)整。可以通過修改Oracle初始化參數(shù)文件$ORACLEBASE/admin/ORCL/pfile/initORCL.ora來修改。

2. 檢查數(shù)據(jù)庫連接情況

查看當前會話連接數(shù)，是否屬于正常范圍。

其中：

• SID 會話(session)的ID號;
• SERIAL# 會話的序列號，和SID一起用來唯一標識一個會話;
• USERNAME 建立該會話的用戶名;
• PROGRAM 這個會話是用什么工具連接到數(shù)據(jù)庫的;
• STATUS 當前這個會話的狀態(tài)，ACTIVE表示會話正在執(zhí)行某些任務(wù)，INACTIVE表示當前會話沒有執(zhí)行任何操作;

3. 檢查系統(tǒng)磁盤空間

如果文件系統(tǒng)的剩余空間過小或增長較快，需對其進行確認并刪除不用的文件以釋放空間。

4. 檢查表空間的使用情況

如果空閑率%Free小于10%以上(包含10%)，則注意要增加數(shù)據(jù)文件來擴展表空間而不要是用數(shù)據(jù)文件的自動擴展功能。

5. 檢查一些擴展異常的對象

如果有記錄返回，則這些對象的擴展已經(jīng)快達到它定義時的最大擴展值。對于這些對象要修改它的存儲結(jié)構(gòu)參數(shù)。

6. 檢查system表空間內(nèi)的內(nèi)容

如果記錄返回，則表明system表空間內(nèi)存在一些非system和sys用戶的對象。應(yīng)該進一步檢查這些對象是否與我們應(yīng)用相關(guān)。如果相關(guān)請把這些對象移到非System表空間，同時應(yīng)該檢查這些對象屬主的缺省表空間值。

7. 檢查對象的下一擴展與表空間的最大擴展值

如果有記錄返回，則表明這些對象的下一個擴展大于該對象所屬表空間的最大擴展值，需調(diào)整相應(yīng)表空間的存儲參數(shù)。

二、檢查數(shù)據(jù)庫的性能

檢查Oracle數(shù)據(jù)庫性能情況，包含：

• 檢查數(shù)據(jù)庫的等待事件
• 檢查死鎖及處理
• 檢查cpu、I/O、內(nèi)存性能
• 查看是否有僵死進程
• 檢查行鏈接/遷移
• 定期做統(tǒng)計分析
• 檢查緩沖區(qū)命中率
• 檢查共享池命中率
• 檢查排序區(qū)
• 檢查日志緩沖區(qū)

總共十個部分。

1. 檢查數(shù)據(jù)庫的等待事件

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%'; 

如果數(shù)據(jù)庫長時間持續(xù)出現(xiàn)大量像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個系統(tǒng)等待事件的獲取

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.  檢查文件系統(tǒng)的 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#'; 

操作系統(tǒng)級kill掉session：

#>kill -9 pid 

11.查看是否有僵死進程

select spid from v$process where addr not in (select paddr from v$session); 

有些僵尸進程有阻塞其他業(yè)務(wù)的正常運行，定期殺掉僵尸進程。

12.  檢查緩沖區(qū)命令中率

13. 檢查共享池命令中率

如低于95%，則需要調(diào)整應(yīng)用程序使用綁定變量，或者調(diào)整數(shù)據(jù)庫參數(shù)shared pool的大小。

14.檢查排序區(qū)

如果disk/(memoty+row)的比例過高，則需要調(diào)整sortareasize(workareasizepolicy=false)或pgaaggregatetarget(workareasizepolicy=true)。

15. 檢查日志緩中區(qū)

如果redo buffer allocation retries/redo entries 超過1% ，則需要增大log_buffer。

三、檢查數(shù)據(jù)庫cpu、I/O、內(nèi)存性能

1. CPU使用情況

top 

2. 內(nèi)存使用情況

free -m 

3. 系統(tǒng)io情況

四、檢查Oracle數(shù)據(jù)庫的安全性

主要檢查Oracle數(shù)據(jù)庫的安全性，包含：檢查系統(tǒng)安全信息，定期修改密碼，總共兩個部分。

1. 檢查系統(tǒng)安全日志信息

系統(tǒng)安全日志文件的目錄在/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 

在出現(xiàn)的日志信息中沒有錯誤(Invalid、refused)提示，如果沒有(Invalid、refused)視為系統(tǒng)正常，出現(xiàn)錯誤提示，應(yīng)作出系統(tǒng)告警通知。

2.  檢查用戶修改密碼

數(shù)據(jù)庫系統(tǒng)上往往存在很多的用戶，如：第三方數(shù)據(jù)庫監(jiān)控系統(tǒng)，初始安裝數(shù)據(jù)庫時的演示用戶，管理員用戶等等，這些用戶的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統(tǒng)甚至進行修改數(shù)據(jù)。需要修改密碼的用戶包括： 數(shù)據(jù)庫管理員用戶SYS，SYSTEM;其他用戶。

修改密碼方法：

Sql>alter user USER_NAME identified by PASSWORD;