Oracle資料庫巡檢

適用於:

用途

本文章解釋如何執行對資料庫的基本健康狀況檢查，以檢查一些配置問題。本文章給出了一些一般準則，來指導使用者應該對哪些領域進行調查，才能更好地瞭解資料庫執行和變化的方式。這些準則會揭示關於配置的常見問題以及將來可能會發生的問題。

如果您想進行更深入的健康狀況檢查來檢查資料庫結構和資料字典完整性，請檢視第 11 章的相關連結。

此處調查的領域主要是基於一些指令碼，我們不保證這些指令碼的完整性和正確性。對於以後的資料庫版本和功能, 這些指令碼可能需要改寫。
本文章的內容可能需要進一步擴充，以適應特定應用程式檢查的需要。

儘管本文章中討論了一些效能方面的問題，但是本文的目的不在於提供最佳化資料庫效能的完整詳細解釋

適用範圍

1. 引數檔案
2. 控制檔案
3. 重做日誌檔案
4. 歸檔
5. 資料檔案
  5.1 Autoextend
  5.2 位置
6. 表空間
  6.1 SYSTEM 表空間
  6.2 SYSAUX 表空間（10g 及更高版本）
  6.3 本地管理表空間與字典管理表空間
  6.4 臨時表空間
  6.5 表空間碎片
7. 物件
  7.1 Extent 的數量
  7.2 下一個 Extent
  7.3 索引
8. AUTO 和 MANUAL undo
  8.1 AUTO UNDO
  8.2 MANUAL UNDO
9. 記憶體管理
  9.1 Oracle 9i 之前的版本
  9.2 Oracle 9i
  9.3 Oracle 10g
  9.4 Oracle 11g
10.日誌和跟蹤
  10.1 告警日誌
  10.2 Max_dump_file_size
  10.3 使用者和核心轉儲大小引數
  10.4 審計檔案
11. 高階健康狀況檢查

詳細資訊

1. 引數檔案

引數檔案有 2 種形式。首先，我們有基於文字的版本，通常稱為 init.ora 或 pfile，和一個基於二進位制的檔案，通常稱為 spfile。可以使用標準作業系統編輯器調整 pfile，而 spfile 需要透過例項本身進行管理。

一定要注意，spfile 優先於 pfile，即除非另有說明，只要有 spfile 可用，都將被自動應用。

注意：建議在資料庫配置發生變化後，生成一份 RDA 報告。保留歷史 RDA 報告，可以確保您隨著資料庫演化對資料庫配置有個大概瞭解。

參考：
Note 249664.1 Pfile vs SPfile

2. 控制檔案

強烈建議至少擁有兩份控制檔案。可以透過映象控制檔案完成該操作，並且強烈建議放在不同的物理磁碟上。如果由於磁碟崩潰等原因，導致控制檔案丟失，則您可以使用映象檔案啟動資料庫。透過這種方法，丟失的控制檔案可以
輕鬆簡單地被恢復。

控制檔案的位置和數量可以透過初始化引數 "control_files" 進行控制。

3. 重做日誌檔案

Oracle 伺服器維護聯機重做日誌檔案，以儘量減少資料庫中的資料丟失。重做日誌檔案用於示例失敗等情況，以恢復還未寫進資料檔案的已提交資料。強烈建議在不同物理磁碟上對重做日誌檔案進行映象，從而在因磁碟崩潰、使用者刪除等原因導致其中一個重做日誌檔案丟失時，恢復起來更加容易。

雖然至少需要兩個重做日誌組，但是在啟用歸檔時最好具有至少三個重做日誌組。（請參閱下一章）。在存在大量日誌切換的環境中，通常會看到 ARCHiver 後臺程式歸檔的速度落後於 LGWR 後臺程式生成日誌的速度。在這種情況下，LGWR 程式需要等待 ARCH 程式完成歸檔重做日誌檔案。

參考：
Note 102995.1 Maintenance of Online Redo Log Groups and Members

4. 歸檔

歸檔提供了備份資料庫變化所需的機制。歸檔檔案非常重要，它提供了恢復資料庫所必須的資訊。建議在歸檔日誌模式下執行資料庫，雖然可能有理由不這樣做，比如處於 TEST（測試）環境中時，丟失在當前時間與最後一次備份之間所做的更改對您來說是可以接受的。
當資料庫不處於歸檔日誌模式時，您可以略過本章。

檢查歸檔配置的方法有數種，以下是其中一種：

對於 10g 之前的版本，如果資料庫在歸檔日誌模式下執行，但是禁用了自動歸檔程式程式，則您需要手動歸檔重做日誌檔案。
如果未及時執行該操作，則資料庫會被凍結，任何活動都會被阻止。
因此，當資料庫處於歸檔日誌模式時，您應該啟動自動歸檔。透過在引數檔案中將 "log_archive_start" 引數設定為 true，可以完成此操作。
從 10g 開始，該引數已經棄用，不再要求明確進行設定。歸檔檔案的指定磁碟上必須有足夠的可用空間，否則 ARCHiver 程式無法寫入，必定會導致資料庫崩潰。

參考：
Note 69739.1 How to Turn Archiving ON and OFF
Note 122555.1 Determine how many disk space is needed for the archive files

5. 資料檔案

5.1 Autoextend

autoextend 命令選項可以啟用或禁用資料檔案的自動擴充套件。如果自動擴充套件的資料檔案無法分配所需的空間，它會自動增加資料檔案的大小以獲取更多空間來給物件增長使用。

標準的 Oracle 資料檔案最多可以包含 4194303 個 Oracle 資料塊。
所以這也表示單個資料檔案大小的上限取決於所用的 Oracle 塊大小。

從 Oracle 10g 開始，我們增加了一個稱為 BIGFILE 的新功能，該功能允許建立更大的檔案。請注意，每個作業系統都有其一定的限制，因此您需要確保資料檔案的最大大小不超過作業系統允許的限制。

要確定資料檔案進而表空間是否具有 AUTOEXTEND 功能：

參考：
Note 112011.1 ALERT: RESIZE or AUTOEXTEND can "Over-size" Datafiles and Corrupt the Dictionary
Note 262472.1 10g BIGFILE Type Tablespaces Versus SMALLFILE Type

5.2 位置

驗證資料檔案的位置。隨著時間推移，資料庫可能會增長，並會向資料庫中新增資料檔案。要避免基於“哪兒有空間就放哪兒”來隨意放置資料檔案，因為這會使備份策略和維護變得複雜。

以下為不良使用的一個示例：

參考：
Note 115424.1 How to Rename or Move Datafiles and Logfiles

6. 表空間

6.1 SYSTEM 表空間

使用者物件不應在系統表空間中建立。如果建立，將導致不必要的碎片，並阻止系統表的增長。以下查詢會返回一個列表，列出在系統表空間中已經建立的，但不屬於 SYS 或 SYSTEM的物件。

6.2 SYSAUX 表空間（10g 及更高版本）

建立或升級資料庫時，SYSAUX 表空間會被自動建立，作為SYSTEM表空間的輔助表空間。之前建立並使用獨立表空間的一些資料庫元件，現在也開始使用 SYSAUX 表空間。

如果 SYSAUX 表空間不可用，則核心資料庫功能將仍可操作。使用 SYSAUX 表空間的資料庫功能可能會失敗，或是功能受限。

如果配置不當，則此表空間中儲存的資料量可能巨大，並隨著時間推移而增長到無法管理的大小。有一些元件需要特別注意。

要檢查哪些元件正佔用空間：

參考：
Note 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER

6.3 本地管理表空間與字典管理表空間

從 Oracle 8i 起，Oracle 引入了本地管理表空間，但是是從 Oracle 9i 起它才變為預設設定。本地管理表空間，也稱為 LMT，相對於資料字典管理表空間有一定的優勢。

要驗證哪個表空間為 Locally Managed（本地管理）或 Dictionary Managed（字典管理），您可以執行以下查詢：

參考：
Note 93771.1 Introduction to Locally-Managed Tablespaces
Note 105120.1Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

6.4 臨時表空間

* 本地管理空間表將臨時檔案用於臨時表空間，而字典管理表空間使用臨時型別的表空間。當您執行較早的版本時（早於 Oracle 9i），一定要檢查用於儲存臨時 segment 的表空間型別。預設情況下，所有表空間建立時都為 PERMANENT，因此您應確保專用於臨時 segment 的表空間為 TEMPORARY 型別。

* 確保資料庫上的使用者都分配了臨時型別的表空間。以下查詢列出了將永久表空間指定為其預設臨時表空間的所有使用者。

注意：使用者 SYS 和 SYSTEM 會將 SYSTEM 表空間顯示為它們的預設臨時表空間。該值也可以改變，以防止 SYSTEM 表空間中產生碎片。

*臨時表空間中分配的空間是可以重複使用的。這是因為基於效能的考慮，以避免由於持續分配和取消分配 extent 和 segment所產生瓶頸。因此，當檢視臨時表空間中的可用空間時，可能會始終顯示為已滿的狀態。以下查詢可以列出關於臨時 segment 使用情況的更多有用資訊：

這將給出臨時表空間的大小：

這將給出臨時表空間的“高水位”（=單次使用的最大值）：

這將給出當前使用情況：

6.5 表空間碎片

表空間碎片過多會對效能產生影響，特別是系統上正進行許多 Full Table Scans（全表掃描）時。碎片的另一個缺點是，當所有可用空間的總和遠遠超出您請求的空間時，您會得到空間不足的錯誤訊息。

解決碎片的唯一辦法是重新建立物件。從 Oracle8i 開始，您可以使用 "alter table .. move" 命令。在 Oracle8i 之前，您可以使用 export/import。

如果您需要對系統表空間消除碎片，則必須重建整個資料庫，因為無法刪除系統表空間。

參考：
Note 1020182.6 - SCRIPT to detect tablespace fragmentation
Note 1012431.6 - Common causes of Fragmentation
Note 147356.1 - How to Move Tables from One Tablespace to Another.

7. 物件

7.1 Extent 的數量

儘管過度擴充套件物件的效能影響不是很大，但是很多過度擴充套件物件積聚起來確實會影響效能。以下查詢將列出分配的 extent 超過了指定最小量的所有物件。將

7.2 下一個 extent

非常重要的是，segment 可以增長，因此在需要時它們可以分配下一個 extent。如果表空間中沒有足夠的可用空間，則無法分配下一個 extent，且物件無法增長。以下查詢返回了所有無法分配其下一個 extent 的 segment：

請注意，如果表空間中有許多碎片，則此查詢可能會返回仍然可以增長的物件。上述查詢基於可以表空間中的最大可用塊。如果 有許多這樣彼此相連的“較小”可用塊，則 Oracle 將合併這些塊以提供 extent 分配。

因此，需要改寫 Note 1020182.6 'SCRIPT to detect tablespace fragmentation' 中的指令碼以將每個物件的下一個 extent 與表空間中的“連續”位元組（表 space_temp）進行比較。

7.3 索引

基本不需要重建索引，更多時候建議您選擇合併索引。有關完整說明，請參閱以下文章：

參考：
Note 989093.1: Index Rebuild, the Need vs the Implications 
Note 989186.1: Script to investigate a b-tree index structure

8. AUTO 和 MANUAL undo

從 Oracle 9i 開始，我們引入了一種管理前映象的新方式。之前，這是透過 RollBack Segment 進行的，或稱為 manual undo（手動 undo）。當引數 UNDO_MANAGEMENT 設定為 AUTO 時，使用 automatic undo（自動 undo）。未設定或設定為 MANUAL 時，我們使用“過去”的 rollback segment 機制。儘管當前版本中，兩個版本都可用，我們建議使用自動 undo。

8.1 AUTO UNDO

AUM（自動 undo 管理，Automatic Undo Management）幾乎不需要配置。您基本上只需要定義將前映像保持可用的時間量。這是透過引數 UNDO_RETENTION 控制的，以秒為單位定義。因此，值 900 表示 15 分鐘。

一定要意識到，如果 undo 表空間中存在空間壓力時，我們不保證前映象一定會保留這麼長時間。

因此，以下公式可用於計算最佳 undo 表空間大小：

Note 262066.1: How To Size UNDO Tablespace For Automatic Undo Management

從 Oracle 10g 開始，您可以選擇使用 GUARANTEE 選項，以確保在定義的 undo_retention 時間之前，undo 資訊不會被覆蓋。

Note 311615.1: Oracle 10G new feature - Automatic Undo Retention Tuning

8.2 MANUAL UNDO

* 損壞的 rollback segment 會阻止例項開啟資料庫。僅當知道 rollback segment 名稱時，我們才有可能採取更正操作。因此，請在init.ora 中引數 "rollback_segments" 中列出所有的 rollback segment。

* rollback segment 太小或不足可能會對資料庫的行為有嚴重影響。因此，有些問題必須要考慮到。以下查詢會顯示線上 rollback segment 是否足夠，或 rollback segment 是否太小。

WAITS 表示在哪些 rollback segment header上有等待。通常，透過新增 rollback segment可以減少這類爭用。

如果 SHRINKS 非零，則說明該特定 rollback segment 設定了引數 OPTIMAL，或者 DBA顯式執行了命令來縮小該 rollback segment。SHRINKS 表示因為事務擴充套件 rollback segment 超出了 OPTIMAL 大小而隨後縮小 rollback segment 的次數。如果該值過高，則 OPTIMAL 大小的值以及 rollback segment 的總大小應該增加（minextents 或 extent 大小本身可以增加，這主要取決於 WRAPS 列的值）。

WRAPS 列說明 rollback segment 切換到另一 extent 以操作事務的次數。如果該值巨大，則需要增加 rollback segment 的 extent 大小。

參考：
Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments

9. 記憶體管理

這一章與版本密切相關。根據正在執行的版本，可用的選項會有所不同。一直以來，Oracle 投入了大量的時間和精力來為終端使用者更加有效、透明地管理記憶體。因此，我們建議儘量使用自動功能。

9.1 Oracle 9i 之前的版本

不同的記憶體元件 (SGA & PGA)的大小需要在資料庫啟動時定義。這些值是靜態的。因此，如果一個記憶體元件大小過低，則需要重新啟動資料庫使更改生效。
本文件不討論如何確定不同元件的最優值或最佳值，這已遠遠超出本文件的範圍。但是，這些版本中常被誤用的一個引數是 sort_area_size。

init.ora 中的引數 "sort_area_size" 定義了可用於排序的記憶體量。應當仔細選擇該值，因為這是 User Global Area (UGA) 的一部分，因此將分別為每個使用者進行分配。

如果有許多使用者同時對資料庫執行大量排序操作，則系統可能會耗盡記憶體。

例：您的 sort_area_size 為 1Mb，資料庫上同時有 200 個使用者。儘管記憶體是動態分配的，但是可能會分配至 200Mb，從而會導致系統上產生大量交換。

9.2 Oracle 9i

從 Oracle 9i 開始，我們使用以下引數：

workarea_size_policy = [AUTO | MANUAL]
pga_aggregate_target =

這允許您為 PGA 記憶體定義 1 個 pool，可以在各個會話之間共享。
如果您經常收到 ORA-4030 錯誤，則表明該值指定的過低.

9.3 Oracle 10g

在 10g 中，引入了 Automatic Shared Memory Management（自動共享記憶體管理，ASMM）。自動共享記憶體
管理功能，透過將引數 SGA_TARGET 設定為非零值來啟用。

此功能的優勢在於，您可以在不同元件之間共享記憶體資源。
資源可以根據需要由 Oracle 自動進行分配和取消分配。

Automatic PGA Memory（自動 PGA 記憶體）管理仍可透過引數 "workarea_size_policy" 和
"pga_aggregate_target" 進行使用。

9.4 Oracle 11g

在 11g 中，引入 Automatic Memory Management（自動記憶體管理，AMM）。透過使用兩個引數，MEMORY_MAX_TARGET 和 MEMORY_TARGET，可以啟用
 PGA 和 SGA 的自動調整。

參考：
Note 443746.1 Automatic Memory Management(AMM) on 11g

10. 日誌和跟蹤

10.1 告警日誌

資料庫的告警日誌是按時間順序寫入的。始終會新增資料，因此該檔案大小會增長到非常巨大。應定期清除或截斷該檔案，因為大型告警日誌會佔用不必要的磁碟空間，從而會降低 OS 向檔案寫入的速度。


11g 之前的版本：

11g 和更高版本：

10.2 Max_dump_file_size

Oracle Server 程式會生成特定錯誤或衝突的跟蹤檔案。這些跟蹤檔案可用於進一步分析問題。init.ora 引數 "max_dump_file_size" 限制了這些跟蹤檔案的大小。該引數值應指定為作業系統塊大小的整數倍。
應確保磁碟空間可以處理指定的最大大小，否則，應更改該值。

10.3 使用者和核心轉儲大小引數

引數 "user_dump_dest" 和 "core_dump_dest" 可以包含許多跟蹤資訊。
一定要定期清除此目錄，因為此目錄會佔用大量磁碟空間。

注意：從 Oracle 11g 開始，該位置由引數 "diagnostic_dest" 控制。

參考：
Note 564989.1 How To Truncate a Background Trace File Without Bouncing the Database

10.4 審計檔案

預設情況下，每次以 SYS 或 SYSDBA 進行的連線，都會記錄在作業系統檔案中。
存放位置是透過引數 "audit_file_dest" 進行控制的。如果未設定該引數，則位置預設為 $ORACLE_HOME/rdbms/audit。
隨著時間推移，該位置可能會包含很多審計資訊，從而佔用大量空間。

11. 高階健康狀況檢查

前面的章節已經說明了要檢查的基本專案，從而避免常見的資料庫警示。在這一部分中，您可以參考幾篇文章，瞭解如何進行更深入的分析和監控。這些文章主要說明資料字典完整性 (Data Dictionary Integrity) 和資料庫結構驗證。

Oracle 11g 之前的版本：

Note 456468.1 - Identify Data Dictionary Inconsistency 
NOTE 136697.1 - "hcheck8i.sql" script to check for known problems in Oracle8i,Oracle9i, and Oracle10g

Oracle 11g

Note 466920.1: 11g New Feature Health monitor

參考