巡檢內容

1. 檢查資料庫基本狀況

在本節中主要對資料庫的基本狀況進行檢查，其中包含：檢查Oracle例項狀態，檢查Oracle服務程式，檢查Oracle監聽程式，共三個部分。

1.1. 檢查Oracle例項狀態

SQL> select instance_name,host_name,startup_time,status,database_status from v$instance;

INSTANCE_NAME HOST_NAME STARTUP_TIME STATUS DATABASE_STATUS

---------------- ------------------- -------------------- ---------- ------------ ----

CKDB AS14 2009-5-7 9:3 OPEN ACTIVE

其中“STATUS”表示Oracle當前的例項狀態，必須為“OPEN”；“DATABASE_STATUS”表示Oracle當前資料庫的狀態，必須為“ACTIVE”。

SQL> select name,log_mode,open_mode from v$database;

NAME LOG_MODE OPEN_MODE

--------- ------------ -----------------

CKDB ARCHIVELOG READ WRITE

其中“LOG_MODE”表示Oracle當前的歸檔方式。“ARCHIVELOG”表示資料庫執行在歸檔模式下，“NOARCHIVELOG”表示資料庫執行在非歸檔模式下。在我們的系統中資料庫必須執行在歸檔方式下。

1.2. 檢查Oracle服務程式

oracle 2960 1 0 May07 ? 00:01:02 ora_pmon_CKDB

oracle 2962 1 0 May07 ? 00:00:22 ora_psp0_CKDB

oracle 2964 1 0 May07 ? 00:00:00 ora_mman_CKDB

oracle 2966 1 0 May07 ? 00:03:20 ora_dbw0_CKDB

oracle 2968 1 0 May07 ? 00:04:29 ora_lgwr_CKDB

oracle 2970 1 0 May07 ? 00:10:31 ora_ckpt_CKDB

oracle 2972 1 0 May07 ? 00:03:45 ora_smon_CKDB

oracle 2974 1 0 May07 ? 00:00:00 ora_reco_CKDB

oracle 2976 1 0 May07 ? 00:01:24 ora_cjq0_CKDB

oracle 2978 1 0 May07 ? 00:06:17 ora_mmon_CKDB

oracle 2980 1 0 May07 ? 00:07:26 ora_mmnl_CKDB

oracle 2982 1 0 May07 ? 00:00:00 ora_d000_CKDB

oracle 2984 1 0 May07 ? 00:00:00 ora_s000_CKDB

oracle 2994 1 0 May07 ? 00:00:28 ora_arc0_CKDB

oracle 2996 1 0 May07 ? 00:00:29 ora_arc1_CKDB

oracle 3000 1 0 May07 ? 00:00:00 ora_qmnc_CKDB

oracle 3625 1 0 May07 ? 00:01:40 ora_q000_CKDB

oracle 31594 1 0 Jul20 ? 00:00:00 ora_q003_CKDB

oracle 23802 1 0 05:09 ? 00:00:33 ora_j000_CKDB

19

在檢查Oracle的程式命令輸出後，輸出顯示至少應包括以下一些程式：
. Oracle寫資料檔案的程式，輸出顯示為：“ora_dbw0_CKDB”
. Oracle寫日誌檔案的程式，輸出顯示為：“ora_lgwr_ CKDB”
. Oracle監聽例項狀態的程式，輸出顯示為：“ora_smon_ CKDB”
. Oracle監聽客戶端連線程式狀態的程式，輸出顯示為：“ora_pmon_ CKDB”
. Oracle進行歸檔的程式，輸出顯示為：“ora_arc0_ CKDB”
. Oracle進行檢查點的程式，輸出顯示為：“ora_ckpt_ CKDB”
. Oracle進行恢復的程式，輸出顯示為：“ora_reco_ CKDB”

1.3. 檢查Oracle監聽狀態

/home/oracle>lsnrctl status

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 23-JUL-2009 14:11:53

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.2.0 - Production

Start Date 07-MAY-2009 09:35:52

Uptime 77 days 4 hr. 36 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /data/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File /data/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AS14)(PORT=1521)))

Services Summary...

Service "CKDB" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDBXDB" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

Service "CKDB_XPT" has 1 instance(s).

Instance "CKDB", status READY, has 1 handler(s) for this service...

The command completed successfully

“Services Summary”項表示Oracle的監聽程式正在監聽哪些資料庫例項，輸出顯示中至少應該有“CKDB”這一項。

檢查監聽程式是否存在：

[oracle@AS14 ~]$ ps -ef|grep lsn|grep -v grep

oracle 2954 1 0 May07 ? 00:01:17 /data/oracle/product/10.2.0/bin/tnslsnr LISTENER –inherit

2. 檢查系統和oracle日誌檔案

在本節主要檢查相關的日誌檔案，包含：檢查作業系統的日誌檔案，檢查Oracle日誌檔案，檢查Oracle核心轉儲目錄，檢查Root使用者和Oracle使用者的email，總共四個部分。

2.1. 檢查作業系統日誌檔案

# cat /var/log/messages |grep failed

檢視是否有與Oracle使用者相關的出錯資訊。

2.2. 檢查oracle日誌檔案

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep ora-

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep err

[oracle@AS14 ~]$ cat /data/oracle/admin/CKDB/bdump/alert_CKDB.log |grep fail

Oracle在執行過程中，會在警告日誌檔案(alert_SID.log)中記錄資料庫的一些執行情況：資料庫的啟動、關閉，啟動時的非預設引數；資料庫的重做日誌切換情況，記錄每次切換的時間，及如果因為檢查點(checkpoint)操作沒有執行完成造成不能切換，會記錄不能切換的原因；對資料庫進行的某些操作，如建立或刪除表空間、增加資料檔案；資料庫發生的錯誤，如表空間不夠、出現壞塊、資料庫內部錯誤(ORA－600)等。定期檢查日誌檔案，根據日誌中發現的問題及時進行處理：

問題	處理
啟動引數不對	檢查初始化引數檔案
因為檢查點操作或歸檔操作沒有完成造成重做日誌不能切換	如果經常發生這樣的情況，可以考慮增加重做日誌檔案組；想辦法提高檢查點或歸檔操作的效率；
有人未經授權刪除了表空間	檢查資料庫的安全問題，是否密碼太簡單；如有必要，撤消某些使用者的系統許可權
出現壞塊	檢查是否是硬體問題(如磁碟本生有壞塊)，如果不是，檢查是那個資料庫物件出現了壞塊，對這個物件進行重建
表空間不夠	增加資料檔案到相應的表空間
出現ORA-600	根據日誌檔案的內容檢視相應的TRC檔案，如果是Oracle的bug，要及時打上相應的補丁

Listener日誌：$ORACLE_HOME/network/log

2.3. 檢查Oracle核心轉儲目錄

$ls $ORACLE_BASE/admin/CKDB/cdump/*.trc|wc -l

$ls $ORACLE_BASE/admin/CKDB/udump/*.trc|wc –l

如果上面命令的結果每天都在增長，則說明Oracle程式經常發生核心轉儲。這說明某些使用者程式或者資料庫後臺程式由於無法處理的原因而異常退出。頻繁的核心轉儲特別是資料庫後臺程式的核心轉儲會導致資料庫異常終止。

2.4. 檢查Root使用者和Oracle使用者的email

#tail –n 200 /var/mail/root

#tail –n 200 /var/mail/oracle

檢視有無與Oracle使用者相關的出錯資訊。

3. 檢查Oracle物件狀態

在本節主要檢查相關Oracle物件的狀態，包含：檢查Oracle控制檔案狀態，檢查Oracle線上日誌狀態，檢查Oracle表空間的狀態，檢查Oracle所有資料檔案狀態，檢查Oracle所有表、索引、儲存過程、觸發器、包等物件的狀態，檢查Oracle所有回滾段的狀態，總共六個部分。

3.1. 檢查Oracle控制檔案狀態

SQL> select status,name from v$controlfile;

STATUS NAME

------- --------------------------------------------------------------------------------

/data/oradata/CKDB/control01.ctl

/data/oradata/CKDB/control02.ctl

/data/oradata/CKDB/control03.ctl

輸出結果應該有3條以上（包含3條）的記錄，“STATUS”應該為空。狀態為空表示控制檔案狀態正常。

3.2. 檢查Oracle線上日誌狀態

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER

---------- ------- ------- -----------

3 ONLINE /data/oradata/CKDB/redo03.log

2 ONLINE /data/oradata/CKDB/redo02.log

1 ONLINE /data/oradata/CKDB/redo01.log

4 ONLINE /data/oradata/CKDB/redo04.log

5 ONLINE /data/oradata/CKDB/redo05.log

6 ONLINE /data/oradata/CKDB/redo06.log

6 rows selected

輸出結果應該有3條以上（包含3條）記錄，“STATUS”應該為非“INVALID”，非“DELETED”。注：“STATUS”顯示為空表示正常。

3.3. 檢查Oracle表空間的狀態

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

SJ1 ONLINE

ADM_INDEX ONLINE

HOME_DATA ONLINE

HOME_INDEX ONLINE

PHOTO_DATA ONLINE

PHOTO_INDEX ONLINE

。。。。。。。

輸出結果中STATUS應該都為ONLINE。

3.4. 檢查Oracle所有資料檔案狀態

SQL> select name,status from v$datafile;

NAME STATUS

-------------------------------------------------- -------

/data/oradata/CKDB/system01.dbf SYSTEM

/data/oradata/CKDB/undotbs01.dbf ONLINE

/data/oradata/CKDB/sysaux01.dbf ONLINE

/data/oradata/CKDB/users01.dbf ONLINE

/data/oradata/CKDB/sj.dbf ONLINE

/data/oradata/CKDB/HOME_DATA1.dbf ONLINE

/data/oradata/CKDB/HOME_INDEX1.dbf ONLINE

/data/oradata/CKDB/PHOTO_DATA1.dbf ONLINE

/data/oradata/CKDB/PHOTO_INDEX1.dbf ONLINE

/data/oradata/CKDB/BLOG_DATA1.dbf ONLINE

/data/oradata/CKDB/BLOG_INDEX1.dbf ONLINE

/data/oradata/CKDB/AUDIO_DATA1.dbf ONLINE

/data/oradata/CKDB/AUDIO_INDEX1.dbf ONLINE

/data/oradata/CKDB/VIDEO_DATA1.dbf ONLINE

/data/oradata/CKDB/VIDEO_INDEX1.dbf ONLINE

/data/oradata/CKDB/SYS_DATA1.dbf ONLINE

/data/oradata/CKDB/SYS_INDEX1.dbf ONLINE

/data/oradata/CKDB/ADM_DATA1.dbf ONLINE

/data/oradata/CKDB/ADM_INDEX1.dbf ONLINE

/data/oradata/CKDB/perfstat.dbf ONLINE

輸出結果中“STATUS”應該都為“ONLINE”。或者：

SQL> select file_name,status from dba_data_files;

FILE_NAME STATUS

--------------------------------------------- ---------

/data/oradata/CKDB/users01.dbf AVAILABLE

/data/oradata/CKDB/sysaux01.dbf AVAILABLE

/data/oradata/CKDB/undotbs01.dbf AVAILABLE

/data/oradata/CKDB/system01.dbf AVAILABLE

/data/oradata/CKDB/sj.dbf AVAILABLE

/data/oradata/CKDB/perfstat.dbf AVAILABLE

/data/oradata/CKDB/HOME_DATA1.dbf AVAILABLE

/data/oradata/CKDB/HOME_INDEX1.dbf AVAILABLE

/data/oradata/CKDB/PHOTO_DATA1.dbf AVAILABLE

輸出結果中“STATUS”應該都為“AVAILABLE”。

3.5. 檢查無效物件

sql>select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';

no rows selected

如果有記錄返回，則說明存在無效物件。若這些物件與應用相關，那麼需要重新編譯生成這個物件，或者：

SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';

3.6. 檢查所有回滾段狀態

SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

_SYSSMU1$ ONLINE

_SYSSMU2$ ONLINE

_SYSSMU3$ ONLINE

_SYSSMU4$ ONLINE

_SYSSMU5$ ONLINE

_SYSSMU6$ ONLINE

_SYSSMU7$ ONLINE

_SYSSMU8$ ONLINE

_SYSSMU9$ ONLINE

_SYSSMU10$ ONLINE

11 rows selected

輸出結果中所有回滾段的“STATUS”應該為“ONLINE”。

4. 檢查Oracle相關資源的使用情況

在本節主要檢查Oracle相關資源的使用情況，包含：檢查Oracle初始化檔案中相關的引數值，檢查資料庫連線情況，檢查系統磁碟空間，檢查Oracle各個表空間使用情況，檢查一些擴充套件異常的物件，檢查system表空間內的內容，檢查物件的下一擴充套件與表空間的最大擴充套件值，總共七個部分。

4.1. 檢查Oracle初始化檔案中相關引數值

SQL> select resource_name,max_utilization,initial_allocation,

limit_value from v$resource_limit;

RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE

-------------------- --------------- --------------- ---------------

processes 162 500 500

sessions 168 555 555

enqueue_locks 136 6930 6930

enqueue_resources 111 2660 UNLIMITED

ges_procs 0 0 0

ges_ress 0 0 UNLIMITED

ges_locks 0 0 UNLIMITED

ges_cache_ress 0 0 UNLIMITED

ges_reg_msgs 0 0 UNLIMITED

ges_big_msgs 0 0 UNLIMITED

ges_rsv_msgs 0 0 0

gcs_resources 0 0 0

gcs_shadows 0 0 0

dml_locks 76 2440 UNLIMITED

temporary_table_locks 26 UNLIMITED UNLIMITED

transactions 13 610 UNLIMITED

branches 0 610 UNLIMITED

cmtcallbk 3 610 UNLIMITED

sort_segment_locks 5 UNLIMITED UNLIMITED

max_rollback_segments 11 610 65535

RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE

-------------------- --------------- --------------- ---------------

max_shared_servers 1 UNLIMITED UNLIMITED

parallel_max_servers 16 80 3600

22 rows selected

若LIMIT_VALU-MAX_UTILIZATION<=5，則表明與RESOURCE_NAME相關的Oracle初始化引數需要調整。可以通過修改Oracle初始化引數檔案$ORACLE_BASE/admin/CKDB/pfile/initORCL.ora來修改。

4.2. 檢查資料庫連線情況

檢視當前會話連線數，是否屬於正常範圍。

SQL> select count(*) from v$session;

COUNT(*)

----------

29

select sid,serial#,username,program,machine,status from v$session;

SID SERIAL# USERNAME PROGRAM MACHINE STATUS

---- ---------- ------------ ---------------------------- ------------ --------

1 3 oracle@xz15saledb (PMON) xz15saledb ACTIVE

2 3 oracle@xz15saledb (DBW0) xz15saledb ACTIVE

3 3 oracle@xz15saledb (DBW1) xz15saledb ACTIVE

4 3 oracle@xz15saledb (LGWR) xz15saledb ACTIVE

5 3 oracle@xz15saledb (CKPT) xz15saledb ACTIVE

6 3 oracle@xz15saledb (SMON) xz15saledb ACTIVE

7 3 oracle@xz15saledb (RECO) xz15saledb ACTIVE

8 1 oracle@xz15saledb (CJQ0) xz15saledb ACTIVE

9 3 oracle@xz15saledb (ARC0) xz15saledb ACTIVE

10 3 oracle@xz15saledb (ARC1) xz15saledb ACTIVE

11 11319 ZK AccPrtInv_svr@xz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE

13 48876 ZG upload@xz15saleap (TNS V1-V3) xz15saleap INACTIVE

17 20405 ZK AccCreateRpt@xz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE

20 12895 ZK OweScanSvr@xz15billdb (TNS V1-V3) xz15billdb INACTIVE

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

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

USERNAME 建立該會話的使用者名稱；

PROGRAM 這個會話是用什麼工具連線到資料庫的；

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

如果建立了過多的連線，會消耗資料庫的資源，同時，對一些“掛死”的連線可能需要手工進行清理。如果DBA要手工斷開某個會話，則執行：（一般不建議使用這種方式去殺掉資料庫的連線，這樣有時候session不會斷開。容易引起死連線。建議通過sid查到作業系統的spid,使用ps –ef|grep spidno的方式確認spid不是ORACLE的後臺程式。使用作業系統的kill -9命令殺掉連線）

alter system kill session 'SID,SERIAL#';

注意：上例中SID為1到10(USERNAME列為空)的會話，是Oracle的後臺程式，不要對這些會話進行任何操作。

4.3. 檢查系統磁碟空間

如果檔案系統的剩餘空間過小或增長較快，需對其進行確認並刪除不用的檔案以釋放空間。

[oracle@AS14 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda5 9.7G 3.9G 5.4G 42% /

/dev/sda1 479M 16M 438M 4% /boot

/dev/sda2 49G 19G 28G 41% /data

none 1014M 0 1014M 0% /dev/shm

4.4. 檢查表空間使用情況

SQL> select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"

from

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

WHERE a.tablespace_name = f.tablespace_name(+)

order by "% Free";

TABLESPACE_NAME TOTAL FREE % Free

------------------------------ ---------- ---------- ----------

OPERATION_DATA 1800 547 30

WAPWEB_DATA 100 36 36

OPERATION_INDEX 500 186 37

SYSTEM 1024 515 50

SYSAUX 1024 534 52

SALE8_TEMP 100 62 62

SJ1 500 348 70

PERFSTAT 500 356 71

……….

HOME_DATA 100 77 77

SYS_INDEX 100 100 100

VIDEO_INDEX 100 100 100

VIDEO_DATA 100 100 100

BLOG_DATA 100 100 100

39 rows selected

如果空閒率%Free小於10%以上（包含10%），則注意要增加資料檔案來擴充套件表空間而不要是用資料檔案的自動擴充套件功能。請不要對錶空間增加過多的資料檔案，增加資料檔案的原則是每個資料檔案大小為2G或者4G，自動擴充套件的最大限制在8G。

4.5. 檢查一些擴充套件異常的物件

sql>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;

no rows selected

如果有記錄返回，則這些物件的擴充套件已經快達到它定義時的最大擴充套件值。對於這些物件要修改它的儲存結構引數。

4.6. 檢查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';

no rows selected

如果記錄返回，則表明system表空間記憶體在一些非system和sys使用者的物件。應該進一步檢查這些物件是否與我們應用相關。如果相關請把這些物件移到非System表空間，同時應該檢查這些物件屬主的預設表空間值。

4.7. 檢查物件的下一擴充套件與表空間的最大擴充套件值

sql>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;

no rows selected

如果有記錄返回，則表明這些物件的下一個擴充套件大於該物件所屬表空間的最大擴充套件值，需調整相應表空間的儲存引數。

5. 檢查Oracle資料庫備份結果

在本節主要檢查Oracle資料庫備份結果，包含：檢查資料庫備份日誌資訊，檢查backup卷中檔案產生的時間，檢查oracle使用者的email，總共三個部分。

5.1. 檢查資料庫備份日誌資訊

假設：備份的臨時目錄為/backup/hotbakup，我們需要檢查2009年7月22日的備份結果，則用下面的命令來檢查：

#cat /backup/hotbackup/hotbackup-09-7-22.log|grep –i error

備份指令碼的日誌檔案為hotbackup-月份-日期-年份.log，在備份的臨時目錄下面。如果檔案中存在“ERROR:”，則表明備份沒有成功，存在問題需要檢查。

5.2. 檢查backup卷中檔案產生的時間

#ls –lt /backup/hotbackup

backup卷是備份的臨時目錄，檢視輸出結果中檔案的日期，都應當是在當天凌晨由熱備份指令碼產生的。如果時間不對則表明熱備份指令碼沒執行成功。

5.3. 檢查oracle使用者的email

#tail –n 300 /var/mail/oracle

熱備份指令碼是通過Oracle使用者的cron去執行的。cron執行完後作業系統就會發一條Email通知Oracle使用者任務已經完成。檢視Oracle email中今天凌晨部分有無ORA-，Error，Failed等出錯資訊，如果有則表明備份不正常。

6. 檢查Oracle資料庫效能

在本節主要檢查Oracle資料庫效能情況，包含：檢查資料庫的等待事件，檢查死鎖及處理，檢查cpu、I/O、記憶體效能，檢視是否有僵死程式，檢查行連結/遷移，定期做統計分析，檢查緩衝區命中率，檢查共享池命中率，檢查排序區，檢查日誌緩衝區，總共十個部分。

6.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等等待事件時，需要對其進行分析，可能存在問題的語句。

6.2. Disk Read最高的SQL語句的獲取

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

WHERE ROWNUM<=5 desc;

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

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

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

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

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

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

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

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

6.11. 檢查資料庫cpu、I/O、記憶體效能

記錄資料庫的cpu使用、IO、記憶體等使用情況，使用vmstat,iostat,sar,top等命令進行資訊收集並檢查這些資訊，判斷資源使用情況。

1. CPU使用情況：

[root@sale8 ~]# top

top - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29

Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombie

Cpu(s): 1.2% us, 0.1% sy, 0.0% ni, 98.8% id, 0.0% wa, 0.0% hi, 0.0% si

Mem: 16404472k total, 12887428k used, 3517044k free, 60796k buffers

Swap: 8385920k total, 665576k used, 7720344k free, 10358384k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle

32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle

32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle

。。。。。。。

注意上面的藍色字型部分，此部分內容表示系統剩餘的cpu，當其平均值下降至10%以下的時視為CPU使用率異常，需記錄下該數值，並將狀態記為異常。

2. 記憶體使用情況：

# free -m

total used free shared buffers cached

Mem: 2026 1958 67 0 76 1556

-/+ buffers/cache: 326 1700

Swap: 5992 92 5900

如上所示，藍色部分表示系統總記憶體，紅色部分表示系統使用的記憶體，黃色部分表示系統剩餘記憶體，當剩餘記憶體低於總記憶體的10%時視為異常。

3. 系統I/O情況：

# iostat -k 1 3

Linux 2.6.9-22.ELsmp (AS14) 07/29/2009

avg-cpu: %user %nice %sys %iowait %idle

0.16 0.00 0.05 0.36 99.43

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sda 3.33 13.16 50.25 94483478 360665804

avg-cpu: %user %nice %sys %iowait %idle

0.00 0.00 0.00 0.00 100.00

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sda 0.00 0.00 0.00 0 0

如上所示，藍色字型部分表示磁碟讀寫情況，紅色字型部分為cpu IO等待情況。

4. 系統負載情況：

#uptime

12:08:37 up 162 days, 23:33, 15 users, load average: 0.01, 0.15, 0.10

如上所示，藍體字部分表示系統負載，後面的3個數值如果有高於2.5的時候就表明系統在超負荷運轉了，並將此值記錄到巡檢表，視為異常。

6.12. 檢視是否有僵死程式

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

有些殭屍程式有阻塞其他業務的正常執行，定期殺掉殭屍程式。

6.13. 檢查行連結/遷移

Sql>select table_name,num_rows,chain_cnt From dba_tables Where owner='CTAIS2' And chain_cnt<>0;

注：含有long raw列的表有行連結是正常的,找到遷移行儲存到chained_rows表中,如沒有該表執行../rdbms/admin/utlchain.sql
Sql>analyze table tablename list chained rows;
可通過表chained_rows中table_name,head_rowid看出哪些行是遷移行
如:Sql>create table aa as select a.* from sb_zsxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name ='SB_ZSXX';
sql>delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = 'SB_ZSXX');
sql>insert into sb_zsxx select * from chained_row where table_name = 'SB_ZSXX';

6.14. 定期做統計分析

對於採用Oracle Cost-Based-Optimizer的系統，需要定期對資料物件的統計資訊進行採集更新，使優化器可以根據準備的資訊作出正確的explain plan。在以下情況更需要進行統計資訊的更新：
1、應用發生變化
2、大規模資料遷移、歷史資料遷出、其他資料的匯入等
3、資料量發生變化
檢視錶或索引的統計資訊是否需更新，如：
Sql>Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX'
sql>select count(*) from DJ_NSRXX如num_rows和count(*)
如果行數相差很多,則該表需要更新統計資訊，建議一週做一次統計資訊收集，如：
Sql>exec sys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade => TRUE,degree => 4);

6.15. 檢查緩衝區命中率

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

LOGICAL_READS PHYS_READS HIT_RATIO

------------- ---------- ----------

1273645705 71191430 94.4104

如果命中率低於90% 則需加大資料庫引數db_cache_size。

6.16. 檢查共享池命中率

SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;

SUM(PINHITS)/SUM(PINS)*100

--------------------------

99.5294474716798

如低於95%，則需要調整應用程式使用繫結變數，或者調整資料庫引數shared pool的大小。

6.17. 檢查排序區

SQL> select name,value from v$sysstat where name like '%sort%';

NAME VALUE

---------------------------------------------------------------- ----------

sorts (memory) 6135534

sorts (disk) 8

sorts (rows) 2264742084

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

6.18. 檢查日誌緩衝區

SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

NAME VALUE

---------------------------------------------------------------- ----------

redo entries 27663705

redo buffer allocation retries 880

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

7. 檢查資料庫安全性

在本節主要檢查Oracle資料庫的安全性，包含：檢查系統安全資訊，定期修改密碼，總共兩個部分。

7.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 10 22:44:38 rac2 sshd[21611]: Failed password for root from ::ffff:10.10.10.6 port 1723 ssh2

在出現的日誌資訊中沒有錯誤(Invalid、refused)提示，如果沒有(Invalid、refused)視為系統正常，出現錯誤提示，應作出系統告警通知。

7.2. 檢查使用者修改密碼

在資料庫系統上往往存在很多的使用者，如：第三方資料庫監控系統，初始安裝資料庫時的演示使用者，管理員使用者等等，這些使用者的密碼往往是寫定的，被很多人知道，會被別有用心的人利用來攻擊系統甚至進行修改資料。需要修改密碼的使用者包括：
資料庫管理員使用者SYS，SYSTEM；其他使用者。
登陸系統後，提示符下輸入cat /etc/passwd，在列出來的使用者中檢視是否存在已經不再使用的或是陌生的帳號。若存在，則記錄為異常。

修改密碼方法：

Sql>alter user USER_NAME identified by PASSWORD;

8. 其他檢查

在本節主要檢查當前crontab任務是否正常，檢查Oracle Job是否有失敗等共六個部分。

8.1. 檢查當前crontab任務是否正常

[oracle@AS14 ~]$ crontab -l

8.2. Oracle Job是否有失敗

Sql>select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';

如有問題建議重建job，如：
exec sys.dbms_job.remove(1);
commit;
exec sys.dbms_job.isubmit(1,'REFRESH_ALL_SNAPSHOT;',SYSDATE+1/1440,'SYSDATE+4/1440');
commit;

8.3. 監控資料量的增長情況

SQL> select

2 A.tablespace_name,(1-(A.total)/B.total)*100 used_percent

3 from (select tablespace_name,sum(bytes) total

4 from dba_free_space group by tablespace_name) A,

5 (select tablespace_name,sum(bytes) total

6 from dba_data_files group by tablespace_name) B

7 where A.tablespace_name=B.tablespace_name;

TABLESPACE_NAME USED_PERCENT

------------------------------ ------------

HOME_INDEX 1.5

BLOG_DATA 0.375

VIDEO_DATA 0.25

VIDEO_INDEX 0.25

SYS_DATA 9.5

SYS_INDEX 0.4375

CURRENCY_INDEX 13

UNDOTBS1 2.3055555555

SYSAUX 47.875976562

根據本週每天的檢查情況找到空間擴充套件很快的資料庫物件,並採取相應的措施：

--- 刪除歷史資料

移動規定資料庫中至少保留6個月的歷史資料，所以以前的歷史資料可以考慮備份然後進行清除以便釋放其所佔的資源空間。

--- 擴表空間

alter tablespace add datafile ‘’ 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命令。

8.4. 檢查失效的索引

Sql>select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';

注：分割槽表上的索引status為N/A是正常的，如有失效索引則對該索引做rebuild，如：

Sql>alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;

8.5. 檢查不起作用的約束

SELECT owner, constraint_name, table_name, constraint_type, status

FROM dba_constraints

WHERE status ='DISABLE' and constraint_type='P';

如有失效約束則啟用，如：
Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;

8.6. 檢查無效的trigger

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

如有失效觸發器則啟用，如：
Sql>alter Trigger TRIGGER_NAME Enable;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28698327/viewspace-1062644/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 巡檢手冊

1. 檢查資料庫基本狀況

1.1. 檢查Oracle例項狀態

1.2. 檢查Oracle服務程式

1.3. 檢查Oracle監聽狀態

2. 檢查系統和oracle日誌檔案

2.1. 檢查作業系統日誌檔案

2.2. 檢查oracle日誌檔案

2.3. 檢查Oracle核心轉儲目錄

2.4. 檢查Root使用者和Oracle使用者的email

3. 檢查Oracle物件狀態

3.1. 檢查Oracle控制檔案狀態

3.2. 檢查Oracle線上日誌狀態

3.3. 檢查Oracle表空間的狀態

3.4. 檢查Oracle所有資料檔案狀態

3.5. 檢查無效物件

3.6. 檢查所有回滾段狀態

4. 檢查Oracle相關資源的使用情況

4.1. 檢查Oracle初始化檔案中相關引數值

4.2. 檢查資料庫連線情況

4.3. 檢查系統磁碟空間

4.4. 檢查表空間使用情況

4.5. 檢查一些擴充套件異常的物件

4.6. 檢查system表空間內的內容

4.7. 檢查物件的下一擴充套件與表空間的最大擴充套件值

5. 檢查Oracle資料庫備份結果

5.1. 檢查資料庫備份日誌資訊

5.2. 檢查backup卷中檔案產生的時間

5.3. 檢查oracle使用者的email

6. 檢查Oracle資料庫效能

6.1. 檢查資料庫的等待事件

6.2. Disk Read最高的SQL語句的獲取

6.3. 查詢前十條效能差的sql

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

6.5. 檢查執行很久的SQL

6.6. 檢查消耗CPU最高的程式

6.7. 檢查碎片程度高的表

6.8. 檢查表空間的 I/O 比例

6.9. 檢查檔案系統的 I/O 比例

6.10. 檢查死鎖及處理

6.11. 檢查資料庫cpu、I/O、記憶體效能

6.12. 檢視是否有僵死程式

6.13. 檢查行連結/遷移

6.14. 定期做統計分析

6.15. 檢查緩衝區命中率

6.16. 檢查共享池命中率

6.17. 檢查排序區

6.18. 檢查日誌緩衝區

7. 檢查資料庫安全性

7.1. 檢查系統安全日誌資訊

7.2. 檢查使用者修改密碼

8. 其他檢查

8.1. 檢查當前crontab任務是否正常

8.2. Oracle Job是否有失敗

8.3. 監控資料量的增長情況

8.4. 檢查失效的索引

8.5. 檢查不起作用的約束

8.6. 檢查無效的trigger

相關文章