Oracle 归档日志管理详解

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

1. 概述

归档日志是恢复基础[1]：

作用：

时间点恢复
Data Guard
Streams

2. 归档模式

2.1 查看

ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;

2.2 启用

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2.3 关闭

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

3. 归档目的地

3.1 单一

ALTER SYSTEM SET log_archive_dest_1 = 'location=/u01/archive';

3.2 多

ALTER SYSTEM SET log_archive_dest_1 = 'location=/u01/archive';
ALTER SYSTEM SET log_archive_dest_2 = 'location=/u02/archive';
ALTER SYSTEM SET log_archive_dest_3 = 'service=standby async';

3.3 FRA

ALTER SYSTEM SET log_archive_dest_1 = 'location=use_db_recovery_file_dest';
ALTER SYSTEM SET db_recovery_file_dest = '/u01/fra';
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;

3.4 状态

ALTER SYSTEM SET log_archive_dest_state_1 = enable;
ALTER SYSTEM SET log_archive_dest_state_2 = alternate;
ALTER SYSTEM SET log_archive_dest_state_3 = defer;

4. 归档进程

4.1 数量

ALTER SYSTEM SET log_archive_max_processes = 4;

4.2 查看

SELECT * FROM v$archive_processes;

5. 查看

5.1 v$archived_log

SELECT 
  thread#, 
  sequence#, 
  name, 
  archived, 
  status, 
  completion_time,
  blocks * block_size / 1024 / 1024 AS mb
FROM v$archived_log
ORDER BY sequence# DESC;

5.2 v$log_history

SELECT 
  thread#, 
  sequence#, 
  first_change#, 
  first_time,
  next_change#
FROM v$log_history
ORDER BY sequence# DESC;

5.3 v$archive_dest

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

6. 切换

6.1 手动

ALTER SYSTEM ARCHIVE LOG CURRENT;
-- 或
ALTER SYSTEM SWITCH LOGFILE;

6.2 强制注册

ALTER SYSTEM REGISTER;

7. 归档满

7.1 检查 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 reclaimable_gb
FROM v$recovery_file_dest;

7.2 清理

-- RMAN
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';
RMAN> DELETE ARCHIVELOG UNTIL SEQUENCE 100;

7.3 增大 FRA

ALTER SYSTEM SET db_recovery_file_dest_size = 500G;

8. 备份

8.1 RMAN

RMAN> BACKUP ARCHIVELOG ALL;
RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 100 UNTIL SEQUENCE 200;

8.2 删除已备份

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

详细见：Oracle RMAN 备份恢复。

9. 传输

9.1 Data Guard

ALTER SYSTEM SET log_archive_dest_2 = 'service=standby async valid_for=(online_logfiles,primary_role) db_unique_name=standby';

9.2 LogMiner

EXEC DBMS_LOGMNR.ADD_LOGFILE('/u01/archive/1_100.dbf');
EXEC DBMS_LOGMNR.START_LOGMNR;
SELECT * FROM v$logmnr_contents;

10. 监控

10.1 归档速率

SELECT 
  TO_CHAR(completion_time, 'YYYY-MM-DD HH24') AS hour,
  COUNT(*) AS log_count,
  SUM(blocks * block_size) / 1024 / 1024 / 1024 AS gb
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
GROUP BY TO_CHAR(completion_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC;

10.2 告警

-- FRA 使用率
SELECT 
  ROUND(space_used / space_limit * 100, 2) AS pct
FROM v$recovery_file_dest;
-- > 80% 告警

10.3 滞后

SELECT 
  thread#, 
  MAX(sequence#) AS latest_seq,
  MIN(sequence#) AS oldest_seq
FROM v$archived_log
GROUP BY thread#;

11. 常见坑与排错

11.1 ORA-00257

- 归档满
- 清理或增大 FRA

11.2 ORA-16014

- 目的地满
- 清理
- 切换归档

11.3 ORA-19809

- FRA 满
- 增大或清理

11.4 ORA-19815

- FRA 警告
- 监控

12. 最佳实践

归档模式生产必备：基础
多目的地：冗余
FRA 足够：避免满
定期清理：空间
RMAN 备份归档：保护
监控告警：80%
归档进程足够：性能
Redo 日志大小：合理切换
Flashback + 归档：恢复
文档化：流程

13. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Archived Redo Logs” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/