DB / Oracle / 性能诊断

Oracle 数据库健康检查

2026.09.06

Oracle 数据库健康检查

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

1. 概述

数据库健康检查覆盖[1]：

  • 实例状态
  • 存储空间
  • 性能指标
  • 备份状态
  • 安全配置

2. 实例状态

2.1 实例

SELECT 
  instance_name,
  host_name,
  status,
  database_status,
  version,
  startup_time,
  archiver
FROM v$instance;

2.2 数据库

SELECT 
  name,
  log_mode,
  open_mode,
  protection_mode,
  database_role,
  switchover_status
FROM v$database;

2.3 数据库版本

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
SELECT * FROM product_component_version;

3. 存储空间

3.1 表空间使用

SELECT 
  df.tablespace_name,
  ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) AS size_gb,
  ROUND(SUM(df.bytes - fs.bytes) / 1024 / 1024 / 1024, 2) AS used_gb,
  ROUND(SUM(fs.bytes) / 1024 / 1024 / 1024, 2) AS free_gb,
  ROUND((SUM(df.bytes - fs.bytes) / SUM(df.bytes)) * 100, 2) AS pct_used
FROM dba_data_files df, dba_free_space fs
WHERE df.file_id = fs.file_id(+)
GROUP BY df.tablespace_name
ORDER BY pct_used DESC;

3.2 数据文件

SELECT 
  file_name,
  tablespace_name,
  bytes / 1024 / 1024 AS mb,
  autoextensible,
  maxbytes / 1024 / 1024 AS max_mb,
  status
FROM dba_data_files
ORDER BY tablespace_name;

3.3 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
FROM v$recovery_file_dest;

3.4 FRA 使用详情

SELECT 
  file_type,
  percent_space_used,
  percent_space_reclaimable,
  number_of_files
FROM v$flash_recovery_area_usage;

4. 性能指标

4.1 命中率

-- Buffer Cache
SELECT 
  1 - SUM(decode(name, 'physical reads cache', value, 0)) /
      NULLIF(SUM(decode(name, 'consistent gets from cache', value, 
                        'db block gets from cache', value, 0)), 0) AS buffer_hit
FROM v$sysstat;

-- Library Cache
SELECT SUM(gets - gethits) / NULLIF(SUM(gets), 0) AS miss_rate
FROM v$librarycache;

-- Data Dictionary Cache
SELECT SUM(getmisses) / NULLIF(SUM(gets), 0) AS miss_rate
FROM v$rowcache;

4.2 排序

SELECT 
  name,
  value
FROM v$sysstat
WHERE name LIKE '%sort%';
-- 期望 sorts (disk) / sorts (memory) < 5%

4.3 解析

SELECT 
  name,
  value
FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)', 'execute count');

4.4 等待事件

SELECT 
  event,
  total_waits,
  time_waited,
  average_wait,
  wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

5. 备份状态

5.1 RMAN 备份

SELECT 
  bs_type,
  completion_time,
  status,
  bytes / 1024 / 1024 AS mb
FROM v$backup_set
WHERE completion_time > SYSDATE - 7
ORDER BY completion_time DESC;

5.2 最后备份

SELECT 
  file_type,
  MAX(completion_time) AS last_backup
FROM v$backup_set
GROUP BY file_type;

5.3 归档状态

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

5.4 归档日志

SELECT 
  thread#,
  sequence#,
  name,
  completion_time,
  blocks * block_size / 1024 / 1024 AS mb,
  archived,
  deleted
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY sequence# DESC;

6. 归档模式与恢复

6.1 归档模式

SELECT log_mode FROM v$database;
-- ARCHIVELOG 或 NOARCHIVELOG

6.2 闪回

SELECT 
  flashback_on,
  log_mode
FROM v$database;

6.3 备份策略

-- 增量
SELECT * FROM v$backup_set_detail
WHERE incremental_level IS NOT NULL;

7. 安全检查

7.1 用户

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

7.2 密码策略

SELECT 
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD';

7.3 权限

-- 关键权限
SELECT * FROM dba_sys_privs WHERE privilege IN ('DBA', 'SYSDBA');
SELECT * FROM dba_role_privs WHERE granted_role = 'DBA';

7.4 审计

SELECT * FROM dba_stmt_audit_opts;
SELECT * FROM dba_priv_audit_opts;

8. 作业与调度

-- DBMS_SCHEDULER 作业
SELECT 
  job_name,
  enabled,
  state,
  last_start_date,
  next_run_date
FROM dba_scheduler_jobs
WHERE owner NOT IN ('SYS', 'SYSTEM');

-- 失败作业
SELECT 
  job_name,
  status,
  error#,
  actual_start_date
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
  AND actual_start_date > SYSDATE - 1;

9. 数据文件完整性

-- 损坏块
SELECT * FROM v$database_block_corruption;

-- 数据文件状态
SELECT 
  file_name,
  status,
  online_status
FROM dba_data_files
WHERE status != 'AVAILABLE';

10. 无效对象

SELECT 
  owner,
  object_name,
  object_type,
  status
FROM dba_objects
WHERE status != 'VALID'
  AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN')
ORDER BY owner, object_type;

10.1 重编译

-- 单个
ALTER PROCEDURE my_proc COMPILE;

-- 全部
@?/rdbms/admin/utlrp.sql

11. 告警日志

-- 11g+
SELECT * FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1
  AND message_text LIKE '%ORA-%'
ORDER BY originating_timestamp DESC;

12. 健康检查脚本

12.1 综合脚本

-- 健康检查脚本
SET LINESIZE 200
SET PAGESIZE 100

PROMPT === 实例状态 ===
SELECT instance_name, status, version FROM v$instance;

PROMPT === 数据库状态 ===
SELECT name, log_mode, open_mode FROM v$database;

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

PROMPT === Top 等待事件 ===
SELECT event, time_waited FROM v$system_event 
WHERE wait_class != 'Idle' 
ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;

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

PROMPT === 损坏块 ===
SELECT COUNT(*) FROM v$database_block_corruption;

13. 常见问题

13.1 表空间满

-- 1. 增加数据文件
ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/orcl/users02.dbf' SIZE 1G;

-- 2. 启用 AUTOEXTEND
ALTER DATABASE DATAFILE '...' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

13.2 FRA 满

-- 1. 清理过期备份
RMAN> DELETE OBSOLETE;
RMAN> DELETE EXPIRED BACKUP;

-- 2. 增大 FRA
ALTER SYSTEM SET db_recovery_file_dest_size = 200G;

13.3 无效对象多

@?/rdbms/admin/utlrp.sql

14. 最佳实践

  1. 定期检查：每日/每周
  2. 监控告警：自动化
  3. 表空间预警：80%
  4. 备份验证：定期恢复
  5. 无效对象修复：utlrp
  6. 密码策略：合规
  7. 审计开启：安全
  8. 告警日志监控：错误
  9. 文档化：可重复
  10. 历史对比：趋势

15. 参考资料

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