高效進行 Oracle 日常巡檢

本文出自頭條號老王談運維，轉載請說明出處。

前言

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

主要檢查Oracle相關資源的使用情況，包含：檢查Oracle初始化文件中相關的參數值檢查數據庫連接情況檢查系統磁盤空間檢查Oracle各個表空間使用情況檢查一些擴展異常的對象檢查system表空間內的內容檢查對象的下一擴展與表空間的最大擴展值

總共七個部分。

1.1 檢查oracle初始化文件中相關參數

若LIMITVALU-MAXUTILIZATION<=5，則表明與RESOURCENAME相關的Oracle初始化參數需要調整。可以通過修改Oracle初始化參數文件$ORACLE

BASE/admin/ORCL/pfile/initORCL.ora來修改。

1.2 檢查數據庫連接情況

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

其中：SID 會話(session)的ID號；

SERIAL# 會話的序列號，和SID一起用來唯一標識一個會話；

USERNAME 建立該會話的用戶名；

PROGRAM 這個會話是用什么工具連接到數據庫的；

STATUS 當前這個會話的狀態，ACTIVE表示會話正在執行某些任務，INACTIVE表示當前會話沒有執行任何操作;

1.3 檢查系統磁盤空間

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

南通數據恢復

1.4 檢查表空間的使用情況

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

1.5 檢查一些擴展異常的對象

如果有記錄返回，則這些對象的擴展已經快達到它定義時的最大擴展值。對于這些對象要修改它的存儲結構參數。

1.6 檢查system表空間內的內容

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

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

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

檢查Oracle數據庫性能情況，包含：檢查數據庫的等待事件檢查死鎖及處理檢查cpu、I/O、內存性能查看是否有僵死進程檢查行鏈接/遷移定期做統計分析檢查緩沖區命中率檢查共享池命中率檢查排序區檢查日志緩沖區

總共十個部分。

2.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.2 Disk Read最高的SQL語句的獲取

SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)

WHERE ROWNUM<=5 desc;

2.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 ;

2.4 等待時間最多的5個系統等待事件的獲取

SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;

2.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;

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

2.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);

2.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;

2.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#;

2.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

2.11查看是否有僵死進程

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

有些僵尸進程有阻塞其他業務的正常運行，定期殺掉僵尸進程。

2.12 檢查緩沖區命令中率

2.13 檢查共享池命令中率

如低于95%，則需要調整應用程序使用綁定變量，或者調整數據庫參數shared pool的大小。

2.14檢查排序區

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

2.15 檢查日志緩中區

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

3.1 CPU使用情況

top

3.2 內存使用情況

free -m

3.3 系統io情況

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

4.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)視為系統正常，出現錯誤提示，應作出系統告警通知。

4.2 檢查用戶修改密碼

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

修改密碼方法：

Sql>alter user USER_NAME identified by PASSWORD;

網站崩潰找不到原因？平臺搭建無從下手？熱門技術不想落伍？想要變強找不到資料？

加入[IT拯救聯盟]，讓大牛和同伴拯救你，帶你裝x帶你飛。定期干貨分享、大牛專業解答、實用IT工具優選.....