前言
資料庫穩定運行依賴系統化的日常維護。本手冊涵蓋表空間監控、會話管理、死鎖排查、SPFILE/PFILE 管理、備份恢復策略五大核心維護領域，提供標準化操作流程與 SQL 腳本。
一、表空間監控與擴容
1.1 關鍵監控指標
- 使用率 > 85%：觸發告警，規劃擴容
- 使用率 > 95%：緊急擴容，防止業務中斷
- 自動擴展 (AUTOEXTEND) 狀態檢查
- 碎片程度 (可用空間 vs 最大連續空間)
1.2 監控查詢腳本
-- 表空間使用率總覽
SELECT tablespace_name,
ROUND(total_mb, 2) total_mb,
ROUND(used_mb, 2) used_mb,
ROUND(free_mb, 2) free_mb,
ROUND(pct_used, 2) pct_used,
autoextensible
FROM (
SELECT df.tablespace_name,
SUM(df.bytes)/1024/1024 total_mb,
SUM(df.bytes)/1024/1024 - NVL(SUM(fs.bytes)/1024/1024, 0) used_mb,
NVL(SUM(fs.bytes)/1024/1024, 0) free_mb,
ROUND((1 - NVL(SUM(fs.bytes)/SUM(df.bytes), 0)) * 100, 2) pct_used,
MAX(df.autoextensible) autoextensible
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
GROUP BY df.tablespace_name
)
ORDER BY pct_used DESC;
1.3 擴容操作 (Enterprise Manager / SQL)
方法一：Enterprise Manager 圖形化操作
- 進入「儲存」→「表空間」→ 點選目標表空間
- 「編輯」→「新增資料檔」
- 關鍵：目錄路徑需與現有資料檔一致 (避免誤放本地磁碟導致切換失效)
- 設定檔案大小、啟用 AUTOEXTEND、MAXSIZE
- 「顯示 SQL」取得 DDL，以 SYSDBA 執行或直接套用
方法二：SQL 直接執行
-- 新增單一資料檔
ALTER TABLESPACE ccatsdialindex
ADD DATAFILE 'd:\ccatsdb\ccatsindex\ccatsdialindex02.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
-- 批次新增多檔案
ALTER TABLESPACE ccatsdialindex
ADD DATAFILE
'd:\ccatsdb\ccatsindex\ccatsdialindex03.dbf' SIZE 500M,
'd:\ccatsdb\ccatsindex\ccatsdialindex04.dbf' SIZE 500M;
二、會話監控與資源管理
2.1 檢視所有會話
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine,
s.program, s.module, s.action, s.logon_time, s.status,
s.sql_id, s.prev_sql_id
FROM v$session s
WHERE s.type = 'USER'
ORDER BY s.logon_time;
2.2 Top 10 資源消耗會話
-- CPU 消耗
SELECT * FROM (
SELECT s.sid, s.serial#, s.username, s.sql_id,
ss.value/100 cpu_seconds
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic#
AND sn.name = 'CPU used by this session'
ORDER BY ss.value DESC
) WHERE ROWNUM <= 10;
-- 邏輯讀 (Buffer Gets)
SELECT * FROM (
SELECT s.sid, s.serial#, s.username, s.sql_id,
ss.value buffer_gets
FROM v$session s, v$sesstat ss, v$statname sn
WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic#
AND sn.name = 'session logical reads'
ORDER BY ss.value DESC
) WHERE ROWNUM <= 10;
2.3 長時間執行 SQL 識別
SELECT s.sid, s.serial#, s.username, s.sql_id,
sq.elapsed_time/1e6 elapsed_sec,
sq.executions, sq.buffer_gets,
SUBSTR(sq.sql_text, 1, 100) sql_text
FROM v$session s, v$sql sq
WHERE s.sql_id = sq.sql_id
AND s.status = 'ACTIVE'
AND sq.elapsed_time > 60e6 -- 超過 60 秒
ORDER BY sq.elapsed_time DESC;
三、死鎖偵測與解除
3.1 鎖等待鏈查詢
-- 完整阻塞鏈
SELECT blocking_session, sid, serial#, username,
sql_id, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session, seconds_in_wait DESC;
-- 物件級鎖詳情
SELECT lo.session_id, lo.oracle_username, lo.os_user_name,
lo.locked_mode, o.object_name, o.object_type
FROM v$locked_object lo, dba_objects o
WHERE lo.object_id = o.object_id
ORDER BY lo.session_id;
3.2 鎖模式對照表
| LOCKED_MODE | 模式名稱 | 含義 |
|---|
| 0 | None | 無鎖 |
| 1 | Null (NULL) | 無實際限制 |
| 2 | Row-S (SS) | 列共享 |
| 3 | Row-X (SX) | 列排他 (SELECT FOR UPDATE / UPDATE / DELETE) |
| 4 | Share (S) | 共享 (CREATE INDEX) |
| 5 | S/Row-X (SSX) | 共享列排他 |
| 6 | Exclusive (X) | 排他 (DDL / DROP TABLE) |
3.3 解除死鎖標準流程
- 確認被鎖物件：
SELECT * FROM dba_objects WHERE object_id =
- 定位阻塞 Session：
SELECT sid, serial#, username, machine FROM v$session WHERE sid =
- 評估影響：檢查 SQL_TEXT、執行時間、業務重要性
- 殺除 Session：
ALTER SYSTEM KILL SESSION ',' [IMMEDIATE];
- 驗證釋放：重新查詢 v$locked_object 確認鎖釋放
3.4 Enterprise Manager 圖形化解除
- 「效能」→「鎖」→ 查看鎖樹狀圖
- 右鍵阻塞 Session →「中斷會話」→ 確認
四、SPFILE / PFILE 參數檔管理
4.1 兩種參數檔對比
| 特性 | PFILE (文字) | SPFILE (二進位) |
|---|
| 格式 | 文字 (可用編輯器修改) | 二進位 (不可直接編輯) |
| 修改生效 | 需重啟 | ALTER SYSTEM 即時生效 |
| 持久化 | 手動維護 | 自動持久化 |
| 預設名稱 | init.ora | spfile.ora |
| 預設路徑 | $ORACLE_HOME/dbs (Unix) / database (Win) | 同左 |
| 啟動優先序 | 低 | 高 (SPFILE 優先) |
4.2 互轉操作
-- PFILE → SPFILE (需以 PFILE 啟動)
CREATE SPFILE FROM PFILE;
CREATE SPFILE='/custom/path/spfileORCL.ora' FROM PFILE='/custom/path/initORCL.ora';
-- SPFILE → PFILE (備份/遷移/審計)
CREATE PFILE FROM SPFILE;
CREATE PFILE='/backup/initORCL.ora' FROM SPFILE;
4.3 關鍵參數說明與建議值
| 參數 | 說明 | 建議值範圍 | 修改方式 |
|---|
| db_cache_size | 標準區塊緩衝區大小 | ≥ 16M，通常 SGA 30-50% | ALTER SYSTEM SET db_cache_size=500M SCOPE=BOTH; |
| shared_pool_size | 共享池 (SQL/PLAN/字典) | 64位: 64M+，32位: 16M+ | ALTER SYSTEM SET shared_pool_size=200M SCOPE=BOTH; |
| large_pool_size | 大型池 (並行/備份/RMAN) | 0 (自動) 或 16M-20G | 需 SCOPE=SPFILE 重啟 |
| java_pool_size | Java 池 | 0 (無 Java) 或 24M+ | 需重啟 |
| pga_aggregate_target | PGA 總目標 (排序/雜湊/位圖) | ≥ 10M，通常記憶體 20-30% | ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=BOTH; |
| sga_target / memory_target | 自動記憶體管理 (10g/11g+) | 啟用後子池自動調整 | SCOPE=BOTH |
4.4 動態修改 vs 靜態修改
-- 即時生效 + 持久化 (SPFILE)
ALTER SYSTEM SET parameter=value SCOPE=BOTH;
-- 僅即時生效 (重啟失效)
ALTER SYSTEM SET parameter=value SCOPE=MEMORY;
-- 僅持久化 (重啟生效)
ALTER SYSTEM SET parameter=value SCOPE=SPFILE;
五、資料庫備份策略
5.1 備份分類矩陣
| 維度 | 分類 | 特點 |
|---|
| 邏輯/物理 | 邏輯備份 (EXP/IMP/EXPDP/IMPDP) | 跨版本/平台、物件級、較慢 |
| 物理備份 (OS Copy / RMAN) | 區塊級、快速、需同版本恢復 |
| 線上/離線 | 冷備份 (離線) | 關閉資料庫、一致性最佳、需停機 |
| 熱備份 (線上) | ARCHIVELOG 模式、業務不中斷、點對點恢復 |
| 工具 | EXP/IMP、OS Copy、RMAN、第三方 (Veritas/NetBackup) | RMAN 為原生首選 |
5.2 作業系統冷備份 (離線)
# 1. 正常關閉資料庫
SHUTDOWN IMMEDIATE;
# 2. 複製所有關鍵檔案 (腳本化)
cp $ORACLE_HOME/dbs/orapw* /backup/control/
cp $ORACLE_HOME/dbs/spfile*.ora /backup/control/
cp $ORACLE_BASE/oradata/ORCL/*.dbf /backup/datafiles/
cp $ORACLE_BASE/oradata/ORCL/*.log /backup/redolog/
cp $ORACLE_BASE/oradata/ORCL/control*.ctl /backup/control/
# 3. 啟動資料庫
STARTUP;
5.3 作業系統熱備份 (線上，需 ARCHIVELOG)
# 單表空間熱備份流程
ALTER TABLESPACE users BEGIN BACKUP;
# OS 複製該表空間所有 .dbf 檔案
cp /u01/app/oracle/oradata/ORCL/users01.dbf /backup/hot/users01.dbf
ALTER TABLESPACE users END BACKUP;
# 控制檔備份 (二進位)
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control/control01.ctl';
# 控制檔備份 (文字，可編輯)
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/backup/control/control_trace.sql';
5.4 RMAN 備份 (推薦標準)
優勢
- 區塊級增量備份 (BLOCK CHANGE TRACKING)
- 並行備份/恢復
- 壓縮備份集
- 區塊校驗 (防止壞塊)
- 目錄管理 (RCAT) 支援多資料庫
RMAN 目錄建立流程
-- 1. 建立獨立表空間
CREATE TABLESPACE rman_ts DATAFILE '/u01/oradata/RCAT/rman_ts01.dbf' SIZE 100M AUTOEXTEND ON;
-- 2. 建立目錄擁有者
CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE rman_ts TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO rman;
-- 3. 啟動 RMAN 並建立目錄
$ rman
RMAN> CONNECT CATALOG rman/rman@rcat_db;
RMAN> CREATE CATALOG TABLESPACE rman_ts;
-- 4. 註冊目標資料庫
RMAN> CONNECT TARGET sys/oracle@target_db;
RMAN> REGISTER DATABASE;
常用 RMAN 指令
# 完整備份 (含控制檔、SPFILE)
BACKUP DATABASE PLUS ARCHIVELOG;
# 增量備份 (Level 0 基準 / Level 1 增量)
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;
# 累積增量 (Level 1 CUMULATIVE)
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
# 驗證備份
BACKUP VALIDATE DATABASE;
# 列出備份
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;
5.5 備份排程建議
| 頻率 | 類型 | 時間窗 | 保留策略 |
|---|
| 每日 | 增量 Level 1 | 凌晨 01:00-03:00 | 保留 7 天 |
| 每週 | 完整 Level 0 | 週日 00:00-04:00 | 保留 4 週 |
| 每月 | 完整 + 歸檔 | 月初 00:00 | 保留 12 月 |
| 即時 | 歸檔日誌備份 | 每 15-30 分鐘 | 保留 24-48 小時 |
六、維護檢查清單 (每日/每週/每月)
每日 (自動化腳本 + 告警)
- [ ] 表空間使用率 < 85%
- [ ] Archive Log 磁碟空間充足
- [ ] 備份作業成功 (檢查 RMAN/腳本日誌)
- [ ] Alert Log 無 ORA- 錯誤
- [ ] 監聽器狀態正常
- [ ] 核心資源 (CPU/MEM/DISK) 正常
每週
- [ ] 統計資訊收集 (DBMS_STATS.GATHER_DATABASE_STATS)
- [ ] 索引重建評估 (INDEX_USAGE + HEIGHT/BLK_GETS)
- [ ] 無效物件檢查與編譯 (UTLRP.SQL)
- [ ] 密碼過期/鎖定帳號審查
- [ ] AWR/ADDM 報告審閱
每月
- [ ] 完整備份驗證恢復演練 (測試環境)
- [ ] 容量規劃 (成長趨勢預測)
- [ ] 安全稽核 (權限/審計/補丁)
- [ ] 參數檔備份與版本控管
- [ ] 文件更新 (拓撲/流程/聯絡人)
結語：維護無小事，標準化流程 + 自動化監控 + 定期演練 = 高可用資料庫。將本手冊轉化為團隊 SOP，建立巡檢儀表板，讓問題在業務感知前被發現並解決。
