本文章解釋如何執行對資料庫的基本健康狀況檢查，以檢查一些配置問題。本文章給出了一些一般準則，來指導使用者應該對哪些領域進行調查，才能更好地瞭解資料庫執行和變化的方式。這些準則會揭示關於配置的常見問題以及將來可能會發生的問題。
本文章不適合更深入的健康狀況檢查來檢查資料庫結構和資料字典完整性。
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 審計檔案
本文主要從以上10點來進行基本的健康體檢。

1. 引數檔案

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

一定要注意，spfile 優先於 pfile，即除非另有說明，只要有 spfile 可用，都將被自動應用。
注意：建議在資料庫配置發生變化後，生成一份 RDA 報告。保留歷史 RDA 報告，可以確保您隨著資料庫演化對資料庫配置有個大概瞭解。spfile在進行RMAN備份時，會自動進行備份。

2. 控制檔案

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

SQL>connect as sysdba
SQL> select status, name from v$controlfile;

STATUS NAME
------- ---------------------------------
/u01/oradata/L102/control01.ctl
/u02/oradata/L102/control02.ctl
控制檔案的位置和數量可以透過初始化引數 "control_files" 進行控制。

3. 重做日誌檔案

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

SQL>connect as sysdba
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
--------- ------- ------ -----------------------------------
1 ONLINE /u01/oradata/L102/redo01_A.log
1 ONLINE /u02/oradata/L102/redo01_B.log

2 ONLINE /u01/oradata/L102/redo02_A.log
2 ONLINE /u02/oradata/L102/redo02_B.log

3 ONLINE /u01/oradata/L102/redo03_A.log
3 ONLINE /u02/oradata/L102/redo03_B.log

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

4. 歸檔

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

SQL>connect as sysdba
SQL> archive log list

Database log mode No Archive Mode --OR-- Archive Mode
Automatic archival Disabled --OR-- Enabled
Archive destination --OR-- USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence seq. no
Current log sequence seq. no

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

5. 資料檔案

5.1 Autoextend

autoextend 命令選項可以啟用或禁用資料檔案的自動擴充套件。如果自動擴充套件的資料檔案無法分配所需的空間，它會自動增加資料檔案的大小以獲取更多空間來給物件增長使用。
標準的 Oracle 資料檔案最多可以包含 4194303 個 Oracle 資料塊。
所以這也表示單個資料檔案大小的上限取決於所用的 Oracle 塊大小。

DB_BLOCK_SIZE Max Mb value to use in any command
~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2048 8191 M
4096 16383 M
8192 32767 M
16384 65535 M
從 Oracle 10g 開始，我們增加了一個稱為 BIGFILE 的新功能，該功能允許建立更大的檔案。請注意，每個作業系統都有一定的限制，因此您需要確保資料檔案的最大大小不超過作業系統允許的限制。
要確定資料檔案進而表空間是否具有 AUTOEXTEND 功能：

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files
where autoextensible = 'YES';

5.2 位置

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

SQL> select * from v$dbfile;

FILE# NAME
--------- --------------------------------------------------
1 D:\DATABASE\SYS1D806.DBF
2 D:\DATABASE\D806\RBS1D806.DBF
3 D:\DATABASE\D806\TMP1D806.DBF
5 D:\DATABASE\D806\USR1D806.DBF
6 D:\USR2D806.DBF
7 F:\ORACLE\USR3D806.DBF

6. 表空間

6.1 SYSTEM 表空間

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

SQL> select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

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

建立或升級資料庫時，SYSAUX 表空間會被自動建立，作為SYSTEM表空間的輔助表空間。之前建立並使用獨立表空間的一些資料庫元件，現在也開始使用 SYSAUX 表空間。
如果 SYSAUX 表空間不可用，則核心資料庫功能將仍可操作。使用 SYSAUX 表空間的資料庫功能可能會失敗，或是功能受限。
如果配置不當，則此表空間中儲存的資料量可能巨大，並隨著時間推移而增長到無法管理的大小。有一些元件需要特別注意。
檢查哪些元件正佔用空間：

SQL> select space_usage_kbytes, occupant_name, occupant_desc
from v$sysaux_occupants
order by 1 desc;

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

從 Oracle 8i 起，Oracle 引入了本地管理表空間，但是是從 Oracle 9i 起它才變為預設設定。本地管理表空間，也稱為 LMT，相對於資料字典管理表空間有一定的優勢。
要驗證哪個表空間為 Locally Managed（本地管理）或 Dictionary Managed（字典管理），您可以執行以下查詢：

SQL> select tablespace_name, extent_management
from dba_tablespaces;

6.4 臨時表空間

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

SQL> select tablespace_name, contents
from dba_tablespaces;

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

SQL> select u.username, t.tablespace_name
from dba_users u, dba_tablespaces t
where u.temporary_tablespace = t.tablespace_name
and t.contents <> 'TEMPORARY';

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

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

SQL> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;
這將給出臨時表空間的“高水位”（=單次使用的最大值）：

SQL> select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;

這將給出當前使用情況：

SQL> select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

6.5 表空間碎片

表空間碎片過多會對效能產生影響，特別是系統上正進行許多 Full Table Scans（全表掃描）時。碎片的另一個缺點是，當所有可用空間的總和遠遠超出您請求的空間時，您會得到空間不足的錯誤訊息。
解決碎片的唯一辦法是重新建立物件。從 Oracle8i 開始，您可以使用 "alter table .. move" 命令。在 Oracle8i 之前，您可以使用 export/import。
如果您需要對系統表空間消除碎片，則必須重建整個資料庫，因為無法刪除系統表空間。

7. 物件

7.1 Extent 的數量

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

SQL> select owner, segment_type, segment_name, tablespace_name,
count(blocks), SUM(bytes/1024) "BYTES K", SUM(blocks)
from dba_extents
where owner NOT IN ('SYS','SYSTEM')
group by owner, segment_type, segment_name, tablespace_name
having count(*) >
order by segment_type, segment_name;

7.2 下一個 extent

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

select s.owner, s.segment_name, s.segment_type,
s.tablespace_name, s.next_extent
from dba_segments s
where s.next_extent > (select MAX(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name);
請注意，如果表空間中有許多碎片，則此查詢可能會返回仍然可以增長的物件。上述查詢基於可以表空間中的最大可用塊。如果有許多這樣彼此相連的“較小”可用塊，則 Oracle 將合併這些塊以提供 extent 分配。
因此，可以使用以下指令碼將每個物件的下一個 extent 與表空間中的“連續”位元組（表 space_temp）進行比較。

create table SPACE_TEMP (
TABLESPACE_NAME        CHAR(30),
CONTIGUOUS_BYTES       NUMBER)
/

declare
cursor query is select *
          from dba_free_space
                  order by tablespace_name, block_id;
this_row        query%rowtype;
previous_row    query%rowtype;
total           number;

begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
     exit when query%notfound;
     if this_row.block_id = previous_row.block_id + previous_row.blocks then
        total := total + this_row.bytes;
        insert into SPACE_TEMP (tablespace_name)
                  values (previous_row.tablespace_name);
     else
        insert into SPACE_TEMP values (previous_row.tablespace_name,
               total);
        total := this_row.bytes;
     end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
                           total);
end;
.
/

set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES"       format 999,999,999
column "COUNT"                  format 999
column "TOTAL BYTES"            format 999,999,999
column "TODAY"   noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;

select tablespace_name, count(*) "# OF EXTENTS",
         sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;

spool off

drop table SPACE_TEMP
/

===================================
Sample output from the above script:
===================================

Contiguous Extents Report 
 
 
TABLESPACE NAME                CONTIGUOUS BYTES 
------------------------------ ---------------- 
RBS                                  52,426,752 
RBS                                   2,662,400 
RBS                                     798,720 
RBS                                     266,240 
 
... 
 
TABLESPACE NAME                CONTIGUOUS BYTES 
------------------------------ ---------------- 
USERS                                    20,480 
USERS                                    16,384 
USERS                                    10,240 
USERS                                    10,240 
USERS                                    10,240 
USERS                                     4,096 
 
 
TABLESPACE_NAME                # OF EXTENTS  TOTAL BYTES 
------------------------------ ------------ ------------ 
RBS                                      15   56,154,112 
SYSTEM                                   10      927,744 
TEMP                                      5      665,600 
TOOLS                                    10   89,397,248 
USERS                                     6       71,680

7.3 索引

基本不需要重建索引，更多時候建議您選擇合併索引。

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 表空間中存在空間壓力時，我們不保證前映象一定會保留這麼長時間。
從 Oracle 10g 開始，您可以選擇使用 GUARANTEE 選項，以確保在定義的 undo_retention 時間之前，undo 資訊不會被覆蓋。

8.2 MANUAL UNDO

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

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

SQL> select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
s.wraps, s.status
from v$rollstat s, dba_rollback_segs d
where s.usn = d.segment_id
order by 1;

SEGMENT_NAME TABLESPACE_NAME WAITS SHRINKS WRAPS STATUS
--------------- ------------------ ----- --------- --------- --------
RB1 ROLLBACK_DATA 1 0 160 ONLINE
RB2 ROLLBACK_DATA 31 1 149 ONLINE
SYSTEM SYSTEM 0 0 0 ONLINE
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 大小。

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 的自動調整。

10. 日誌和跟蹤

10.1 告警日誌

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

SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
background_dump_dest string D:\Oradata\Admin\PROD\Trace\BDump
11g 和更高版本：

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------ ------- ----------------------------------
diagnostic_dest string /oracle/admin/L111

10.2 Max_dump_file_size

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

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
---------------------------------- ------- ---------------------
max_dump_file_size integer 10240

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

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

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

10.4 審計檔案

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

2014.09.04 13:39
share you knowledge with the world.

為資料庫做基本的健康體檢