Oracle 数据库健康检查

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

数据库健康检查是日常运维重要环节[1]：

详细见：Oracle 数据库健康检查。

---

2. 检查清单

2.1 实例状态

-- 数据库
SELECT name, open_mode, log_mode, flashback_on 
FROM v$database;

-- 实例
SELECT instance_name, status, version, startup_time, host_name 
FROM v$instance;

-- 集群
SELECT inst_id, instance_name, status FROM gv$instance;

2.2 资源使用

-- SGA
SHOW PARAMETER sga
SELECT * FROM v$sgainfo;

-- PGA
SHOW PARAMETER pga
SELECT name, value FROM v$pgastat;

-- 进程
SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('sessions', 'processes', 'parallel_max_servers');

详细见：Oracle 内存管理 SGA/PGA。

---

3. 存储

3.1 表空间

SELECT 
  tablespace_name,
  ROUND(SUM(total)/1024/1024/1024, 2) AS total_gb,
  ROUND(SUM(used)/1024/1024/1024, 2) AS used_gb,
  ROUND(SUM(free)/1024/1024/1024, 2) AS free_gb,
  ROUND(SUM(used)/NULLIF(SUM(total),0)*100, 2) AS used_pct
FROM (
  SELECT tablespace_name, bytes AS total, 0 AS used, 0 AS free
  FROM dba_data_files
  UNION ALL
  SELECT tablespace_name, 0, bytes, 0
  FROM dba_segments
  UNION ALL
  SELECT tablespace_name, 0, 0, bytes
  FROM dba_free_space
)
GROUP BY tablespace_name
ORDER BY used_pct DESC;

3.2 FRA

SELECT name, 
       space_limit/1024/1024/1024 AS limit_gb,
       space_used/1024/1024/1024 AS used_gb,
       space_reclaimable/1024/1024/1024 AS reclaim_gb,
       ROUND(space_used/space_limit*100, 2) AS pct
FROM v$recovery_file_dest;

3.3 数据文件

SELECT file_id, file_name, tablespace_name, 
       bytes/1024/1024/1024 AS gb, 
       autoextensible, maxbytes/1024/1024/1024 AS max_gb, status
FROM dba_data_files
ORDER BY tablespace_name;

---

4. 备份

4.1 归档

ARCHIVE LOG LIST;

SELECT dest_id, status, destination, error 
FROM v$archive_dest WHERE status != 'INACTIVE';

4.2 备份历史

SELECT session_key, 
       input_type, status, 
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time,
       TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI') AS end_time,
       elapsed_seconds/60 AS minutes,
       output_bytes/1024/1024/1024 AS gb
FROM v$rman_backup_job_details
ORDER BY start_time DESC FETCH FIRST 10 ROWS ONLY;

4.3 恢复窗口

SELECT * FROM v$recovery_window;

详细见：Oracle 备份策略与最佳实践。

---

5. 性能

5.1 TOP 等待事件

SELECT event, waits, time_waited, 
       ROUND(time_waited/NULLIF(SUM(time_waited) OVER (), 0)*100, 2) AS pct
FROM (
  SELECT event, total_waits AS waits, time_waited_micro/1000000 AS time_waited
  FROM v$system_event
  WHERE wait_class != 'Idle'
  ORDER BY time_waited_micro DESC
  FETCH FIRST 10 ROWS ONLY
);

5.2 TOP SQL

SELECT sql_id, 
       executions, 
       elapsed_time/1000000 AS elapsed_sec,
       cpu_time/1000000 AS cpu_sec,
       buffer_gets, 
       disk_reads,
       rows_processed
FROM v$sqlstats
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

详细见：Oracle 等待事件详解。

5.3 AWR

-- 最近快照
SELECT snap_id, begin_interval_time, end_interval_time 
FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 5 ROWS ONLY;

-- 生成报告
@?/rdbms/admin/awrrpt.sql

详细见：Oracle AWR 详解。

---

6. 对象

6.1 无效对象

SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE status != 'VALID'
GROUP BY owner, object_type
ORDER BY COUNT(*) DESC;

6.2 重新编译

@?/rdbms/admin/utlrp.sql

-- 或单独
ALTER PROCEDURE scott.my_proc COMPILE;
ALTER PACKAGE scott.my_pkg COMPILE;
ALTER VIEW scott.my_view COMPILE;

6.3 统计信息

SELECT owner, table_name, last_analyzed, num_rows
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND last_analyzed < SYSDATE - 7
ORDER BY owner;

---

7. 安全

7.1 用户

SELECT username, account_status, lock_date, expiry_date, default_tablespace
FROM dba_users
ORDER BY username;

7.2 密码过期

SELECT username, expiry_date, 
       expiry_date - SYSDATE AS days_left
FROM dba_users
WHERE expiry_date IS NOT NULL
  AND expiry_date < SYSDATE + 7
ORDER BY expiry_date;

7.3 权限

SELECT grantee, privilege 
FROM dba_sys_privs 
WHERE grantee IN ('PUBLIC')
ORDER BY privilege;

详细见：Oracle 安全深度解析。

---

8. 锁

8.1 锁

SELECT s.sid, s.serial#, s.username, s.osuser,
       l.type, l.lmode, l.request, l.block,
       o.object_name
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid AND l.id1 = o.object_id(+)
  AND l.type = 'TM';

8.2 等待

SELECT event, sid, seconds_in_wait, blocking_session
FROM v$session
WHERE wait_class != 'Idle';

详细见：Oracle 锁与闩锁诊断。

---

9. 告警日志

9.1 adrci

adrci
show alert -tail -f

show problem
show incident

9.2 错误

SELECT message_text, originating_timestamp, message_level
FROM v$diag_alert_ext
WHERE message_text LIKE 'ORA-%'
ORDER BY originating_timestamp DESC FETCH FIRST 20 ROWS ONLY;

---

10. Data Guard（如适用）

10.1 Broker

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE orcl_pri;
DGMGRL> SHOW DATABASE orcl_std;

10.2 同步

SELECT name, value FROM v$dataguard_stats;
-- transport lag
-- apply lag

详细见：Oracle Data Guard 部署详解。

---

11. RAC（如适用）

11.1 集群

crsctl stat res -t
crsctl check cluster -all

11.2 实例

SELECT inst_id, instance_name, status, host_name FROM gv$instance;

详细见：Oracle RAC 19c 部署详解。

---

12. ASM（如适用）

12.1 Disk Group

SELECT group_number, name, state, type, 
       total_mb/1024 AS total_gb, free_mb/1024 AS free_gb
FROM v$asm_diskgroup;

12.2 I/O

SELECT group_number, disk_number, reads, writes, read_errs, write_errs
FROM v$asm_disk_iostat;

详细见：Oracle ASM 安装详解。

---

13. 健康检查脚本

#!/bin/bash
# health_check.sh
set -e

echo "=== Oracle 健康检查 ==="
echo "时间: $(date)"

su - oracle << 'EOF'
sqlplus -S / as sysdba << EOSQL
SET LINESIZE 200
SET PAGESIZE 100

PROMPT === 1. 数据库状态 ===
SELECT name, open_mode, log_mode FROM v$database;
SELECT instance_name, status, startup_time FROM v$instance;

PROMPT === 2. 表空间使用 ===
SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_gb
FROM dba_data_files GROUP BY tablespace_name;

PROMPT === 3. FRA ===
SELECT name, 
       ROUND(space_used/1024/1024/1024, 2) AS used_gb,
       ROUND(space_limit/1024/1024/1024, 2) AS limit_gb
FROM v$recovery_file_dest;

PROMPT === 4. 归档 ===
ARCHIVE LOG LIST;

PROMPT === 5. 无效对象 ===
SELECT COUNT(*) AS invalid_count 
FROM dba_objects WHERE status != 'VALID';

PROMPT === 6. TOP 等待事件 ===
SELECT event, total_waits 
FROM v$system_event 
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC FETCH FIRST 5 ROWS ONLY;

EXIT;
EOSQL
EOF

echo "=== 健康检查完成 ==="

---

14. 报告

14.1 日报

- 数据库状态
- 备份状态
- 性能概况
- 告警

14.2 周报

- 性能趋势
- 容量增长
- 备份验证
- 优化建议

14.3 月报

- 综合评估
- 容量规划
- 升级建议
- 安全审查

---

15. 常见问题

15.1 ORA-01555

- Undo 不足
- 增大 Undo
- 长查询

15.2 ORA-04031

- Shared Pool 不足
- 调整
- 绑定变量

15.3 ORA-00060

- 死锁
- 分析 trace
- 调整

---

16. 最佳实践

1. 定期检查：日常
2. 自动化脚本：效率
3. 告警阈值：及时
4. 趋势分析：规划
5. 报告机制：透明
6. 关键指标：监控
7. 历史对比：基线
8. 团队培训：能力
9. 文档化：流程
10. 持续改进：优化

---

17. 参考资料

[1] Oracle Database 2 Day DBA 19c, “Monitoring the Database” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/