Oracle 傳統架構核心原理解析：實例、資料庫與邏輯結構全景圖

學習目標

掌握 Oracle 實例與資料庫的關係、SGA 記憶體結構、背景進程職責、物理檔案組成、邏輯儲存層次與核心 SCN 機制，建立 DBA 核心內功基礎。

一、核心概念辨析：實例 vs 資料庫

二、系統全局區 (SGA) 詳細解剖

透過 SELECT * FROM V$SGA 觀察主要組件：

1. 共用池 —— 效能與擴展性的核心

• Library Cache (SQL 區)：緩存解析後的 SQL/PLSQL 執行計劃
  • 硬解析：語法檢查、語義分析、優化器生成計劃 → 耗 CPU、鎖定共用池鎖
  • 軟解析：直接複用 Library Cache 現有計劃 → 極快
  • 驗證：ALTER SYSTEM FLUSH SHARED_POOL 清空後首次執行 0.08s，二次執行 0.00s，差值即硬解析成本
• Data Dictionary Cache：緩存物件定義、權限、統計資訊等字典資料

最佳實踐：使用綁定變數提高 SQL 重用率；避免字面值 SQL 導致共用池碎片化；適度設定 SHARED_POOL_SIZE，過小導致 ORA-04031，過大浪費記憶體。

2. 資料庫緩衝區快取 —— 讀寫效能的關鍵

• 緩存所有類型資料塊 (系統、使用者、回滾、臨時)，無檔案類型區分
• 修改在記憶體完成，由 DBWR 異步寫入磁盤
• LRU 列表管理：熱塊靠前，冷塊靠後被淘汰
• 三大子池：DEFAULT / KEEP (熱門物件) / RECYCLE (大掃描物件)
• 32 位系統上限 ~1.7GB，64 位系統上限 ~10GB (受 OS 定址限制)

3. 重做日誌緩衝區

• 順序記錄所有變更 (DML/DDL)，LGWR 寫入線上日誌檔
• 記憶體到記憶體極快，但為一致性不長駐留，通常 1-3MB 即足夠
• 超過 3MB 邊際效益遞減

4. Java Pool & Large Pool

• Java Pool：Oracle 8i+ 內核支援 Java 存儲過程/觸發器所需
• Large Pool：分配大塊記憶體給 MTS/UGA、平行查詢協調、RMAN I/O 緩衝，不與共用池爭搶鎖

三、背景進程職責全覽

五大核心進程 (必須存在)

關鍵修正觀念

• DBWR 不 在提交時寫入資料檔，也不觸發 LGWR/CKPT
• LGWR 才是提交同步的關鍵：寫完日誌才回覆用戶「提交成功」
• CKPT 更新檔案頭 SCN，不寫入資料塊本身

輔助進程 (按需啟動)

• ARCH：歸檔模式必備，複製滿日誌至歸檔目的地
• RECO：分散式交易故障自動恢復
• Server Process：專用/共享模式服務用戶請求

四、物理檔案架構

1. 控制檔 —— 資料庫大腦

• 記錄：資料庫名、建立時間、檢查點、所有資料檔/日誌檔/歸檔檔資訊、備份記錄
• 多路鏡像 (Multiplexing) 必做：不同磁碟/控制器/機架
• 查詢：SELECT * FROM V$CONTROLFILE

2. 資料檔分類

3. 重做日誌檔組與成員

• 至少 2 組 (Group)，輪流寫入 (Log Switch)
• 每組至少 1 成員 (Member)，鏡像關係保護日誌不遺失
• 切換引發檢查點，若歸檔/檢查點未完成導致「掛起」
• 解決：增加日誌組數、增大日誌檔尺寸
• 監控：V$LOG (組狀態)、V$LOGFILE (成員路徑)

4. 歸檔日誌檔

• ARCHIVELOG 模式強制生產環境必開，NOARCHIVELOG 僅限測試
• 歸檔目的地可多路鏡像、可遠端傳輸 (Data Guard 基礎)
• 空間管理：定期備份至磁帶後刪除舊歸檔，保留最近完整備份後的所有歸檔

5. 參數檔與診斷檔

• PFILE (initSID.ora) / SPFILE (二進制、動態修改持久化)
• Alert Log：啟動/關閉/錯誤/切換記錄，DBA 每日必讀
• Trace Files：背景/前台進程錯誤診斷，配合 TKPROF 分析

五、邏輯儲存結構層次

表空間 (Tablespace) 
  └─ 段 
       └─ 區 
            └─ 塊 (DB_BLOCK_SIZE，OS 區塊整數倍，安裝時決定不可變)

六、核心表空間運維指南

系統表空間 (SYSTEM)

• 存放資料字典、內部基表、用戶程式碼 (包/過程/函數)
• 特徵：大量讀、少量寫
• 禁止存放使用者業務資料 (限額設 0)
• 核心字典查詢：DICT, V$FIXED_VIEW_DEFINITION

臨時表空間

• 排序、哈希、索引建立等無法在記憶體完成的作業
• 重啟時空間自動釋放
• 本地管理 + 統一區段大小 (1MB 推薦)

回滾段表空間 (Undo Tablespace 9i+)

• 功能：儲存 Before Image、讀一致性構建、交易回滾、實例恢復
• 特徵：大量寫、少量讀 → 獨立磁碟/RAID 避免 I/O 爭用
• 運作模式：循環使用區段 (Extent 1→2→3→4→1...)，活躍交易阻擋區段重用觸發新區分配
• 自動管理模式 (9i+)：UNDO_MANAGEMENT=AUTO，由系統自動調整保留時間

七、SCN：貫穿始終的全局時鐘

• 系統維護的遞增序列號，更新時自動遞增
• 查詢不增加 SCN，同時發生的更新各有唯一 SCN → 保證恢復順序
• 檢查點、日誌切換、備份/恢復、分散式交易皆以 SCN 為同步基準
• 查詢當前 SCN (9i-)：SELECT MAX(KTUXESCND*POWER(2,32)+KTUXESCNW) FROM X$KTUXE
• 查詢當前 SCN (10g+)：SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

結語

Oracle 傳統架構雖歷經多版本演進，但「實例=記憶體+進程、資料庫=物理檔案、SGA 共享/PGA 私有、五大核心進程分工協作、SCN 全局排序」這套核心邏輯始終未變。深度理解這些基本面，是 DBA 從「會用參數」進化為「懂原理、能架構、善診斷」的必經之路。