上一篇【第31篇】Oracle重做日志文件管理操作详解
下一篇【第33篇】Oracle表管理与分区表详解

---

摘要

归档日志（Archive Log）是Oracle数据库实现时间点恢复的核心机制，也是数据库备份恢复策略的重要组成部分。本文详细讲解归档模式的开启与配置、归档目标的设置、归档日志的监控与管理，以及Oracle内置的日志挖掘工具 LogMiner（DBMS_LOGMNR）的使用方法——通过分析重做/归档日志来追踪历史SQL操作，是DBA审计、排错和数据恢复的利器。

---

一、归档模式概述

1.1 非归档模式 vs 归档模式

特性	非归档模式（NOARCHIVELOG）	归档模式（ARCHIVELOG）
数据安全性	只能恢复到最近一次完整备份	可以恢复到任意时间点
在线热备份	不支持	支持
日志使用	循环覆盖（旧日志被覆盖）	旧日志在归档后才能覆盖
磁盘要求	较低	较高（需要存储归档文件）
适用场景	开发/测试环境	生产环境（必须）

结论：生产数据库必须开启归档模式。

1.2 检查当前模式

-- 查看当前日志模式
SELECT name, log_mode FROM v$database;
-- LOG_MODE = 'ARCHIVELOG' 或 'NOARCHIVELOG'

-- 也可以查看参数
SHOW PARAMETER log_archive_dest_1;

---

二、切换归档模式

2.1 从非归档切换到归档模式

-- 步骤1：关闭数据库（需要正常关闭）
SHUTDOWN IMMEDIATE;

-- 步骤2：启动到 MOUNT 状态（不打开数据文件）
STARTUP MOUNT;

-- 步骤3：切换到归档模式
ALTER DATABASE ARCHIVELOG;

-- 步骤4：打开数据库
ALTER DATABASE OPEN;

-- 步骤5：验证
SELECT log_mode FROM v$database;
-- LOG_MODE = ARCHIVELOG

-- 步骤6：手动切换一次日志，测试归档是否正常工作
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG ALL;

-- 步骤7：查看归档日志是否生成
SELECT name, sequence#, archived, applied
FROM v$archived_log
ORDER BY sequence# DESC
FETCH FIRST 5 ROWS ONLY;

2.2 从归档模式切换回非归档模式（不推荐）

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

---

三、归档目标配置

3.1 本地归档目标

-- 设置本地归档目标
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 
    'LOCATION=/u03/archive/testdb'
    SCOPE=BOTH;

-- 设置归档文件命名格式
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 
    'testdb_%t_%s_%r.arc'
    SCOPE=SPFILE;
-- %t = 线程号, %s = 序列号, %r = 重置日志ID

-- 设置并行归档进程数
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 4 SCOPE=BOTH;

3.2 使用快速恢复区（FRA）作为归档目标

-- 配置FRA作为归档目标（推荐，Oracle自动管理文件）
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/fast_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G SCOPE=BOTH;

-- 将LOG_ARCHIVE_DEST_1设置为USE_DB_RECOVERY_FILE_DEST
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;

3.3 多个归档目标

-- 同时归档到多个位置（本地 + 远程）
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 
    'LOCATION=/u03/archive/testdb MANDATORY'  -- 本地，必须成功
    SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 
    'SERVICE=standby_db ASYNC'                -- 远程备库，异步
    SCOPE=BOTH;

-- 设置归档最小成功数（至少1个目标成功归档才允许日志切换）
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST = 1 SCOPE=BOTH;

---

四、归档日志监控

4.1 查看归档状态

-- 查看最近的归档日志
SELECT sequence#, name, first_time, next_time, 
       blocks * block_size / 1024 / 1024 AS size_mb,
       archived, deleted
FROM v$archived_log
WHERE archived = 'YES'
ORDER BY sequence# DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看归档目标状态
SELECT dest_id, dest_name, status, target, archiver, schedule
FROM v$archive_dest_status
WHERE status != 'INACTIVE';

-- 查看今日归档量
SELECT TRUNC(completion_time, 'HH') AS hour,
       COUNT(*) AS files,
       SUM(blocks * block_size) / 1024 / 1024 AS total_mb
FROM v$archived_log
WHERE completion_time > TRUNC(SYSDATE)
AND archived = 'YES'
GROUP BY TRUNC(completion_time, 'HH')
ORDER BY 1;

4.2 归档目录空间监控

-- 查看FRA空间使用情况
SELECT space_limit / 1024 / 1024 / 1024 AS limit_gb,
       space_used / 1024 / 1024 / 1024 AS used_gb,
       ROUND(space_used / space_limit * 100, 2) AS used_pct,
       space_reclaimable / 1024 / 1024 / 1024 AS reclaimable_gb
FROM v$recovery_file_dest;

-- 查看FRA中各类型文件占用
SELECT file_type, number_of_files,
       percent_space_used, percent_space_reclaimable
FROM v$recovery_area_usage;

4.3 清理过期归档日志

# 使用RMAN删除过期归档（推荐方式）
rman target / << EOF

-- 删除所有已备份的归档日志
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;

-- 删除超过保留窗口的归档日志
DELETE NOPROMPT OBSOLETE;

-- 删除7天前的归档日志
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';

EOF

---

五、LogMiner——日志挖掘工具

5.1 LogMiner 概述

LogMiner 是 Oracle 内置的日志分析工具（通过 DBMS_LOGMNR 包调用），可以从重做/归档日志中提取出历史 SQL 语句，用于：

• 数据审计：追踪谁在什么时间修改了什么数据
• 误操作恢复：找到误删/误改前的原始数据（生成 UNDO SQL）
• 故障诊断：分析复制延迟、数据不一致等问题
• 数据同步：基于日志的增量同步（OGG 等工具的底层原理）

5.2 LogMiner 使用前提

-- 前提1：启用附加日志（Supplemental Logging），以获取足够的信息
-- 查看当前状态
SELECT supplemental_log_data_min, supplemental_log_data_pk
FROM v$database;

-- 启用最小附加日志（必须）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 启用主键附加日志（推荐，记录WHERE条件中的主键值）
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- 前提2：需要对 V$LOGMNR_CONTENTS 有查询权限（DBA 用户默认有）
GRANT SELECT ON v_$logmnr_contents TO logminer_user;
GRANT EXECUTE ON DBMS_LOGMNR TO logminer_user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO logminer_user;

5.3 提取数据字典

-- 方法1：将数据字典提取到日志文件（在线数据库）
EXECUTE DBMS_LOGMNR_D.BUILD(
    DICTIONARY_FILENAME => 'logmnr_dict.ora',
    DICTIONARY_LOCATION => '/tmp'
);

-- 方法2：使用重做日志自带字典（ONLINE_CATALOG，最简单）
-- 直接在 START_LOGMNR 时指定 DICT_FROM_ONLINE_CATALOG 选项

5.4 分析在线重做日志（实时查看）

-- 步骤1：添加要分析的日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => '/u01/redo1/redo01a.log',
    OPTIONS     => DBMS_LOGMNR.NEW
);

-- 步骤2：启动 LogMiner（使用在线数据字典，最方便）
EXECUTE DBMS_LOGMNR.START_LOGMNR(
    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);

-- 步骤3：查询 LogMiner 内容
SELECT SCN, TIMESTAMP, SEG_OWNER, SEG_NAME, OPERATION,
       SQL_REDO, SQL_UNDO, USERNAME, SESSION#
FROM v$logmnr_contents
WHERE SEG_OWNER = 'SCOTT' AND SEG_NAME = 'EMP'
ORDER BY SCN;

-- 步骤4：关闭 LogMiner
EXECUTE DBMS_LOGMNR.END_LOGMNR;

5.5 分析归档日志（历史数据分析）

-- 分析特定时间段内的历史操作
EXECUTE DBMS_LOGMNR.START_LOGMNR(
    STARTTIME => TO_DATE('2024-01-15 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    ENDTIME   => TO_DATE('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS'),
    OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
                 DBMS_LOGMNR.CONTINUOUS_MINE +     -- 自动追加归档日志
                 DBMS_LOGMNR.COMMITTED_DATA_ONLY   -- 只显示已提交的事务
);

-- 查询：找出指定时间段内对EMP表的所有DML操作
SELECT TO_CHAR(TIMESTAMP, 'HH24:MI:SS') AS time,
       USERNAME, OPERATION, SQL_REDO, SQL_UNDO
FROM v$logmnr_contents
WHERE SEG_OWNER = 'SCOTT'
  AND SEG_NAME  = 'EMP'
  AND OPERATION IN ('INSERT', 'UPDATE', 'DELETE')
ORDER BY SCN;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

5.6 实战案例：找回误删的数据

-- 场景：2024-01-15 10:00 有人执行了 DELETE FROM scott.emp WHERE deptno=20
-- 需要恢复被删除的数据

-- 步骤1：启动LogMiner，分析删除操作前后的时间段
EXECUTE DBMS_LOGMNR.START_LOGMNR(
    STARTTIME => TO_DATE('2024-01-15 09:50:00', 'YYYY-MM-DD HH24:MI:SS'),
    ENDTIME   => TO_DATE('2024-01-15 10:10:00', 'YYYY-MM-DD HH24:MI:SS'),
    OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
                 DBMS_LOGMNR.CONTINUOUS_MINE +
                 DBMS_LOGMNR.COMMITTED_DATA_ONLY
);

-- 步骤2：提取被删除数据的 SQL_UNDO（即 INSERT 语句）
SELECT SQL_UNDO
FROM v$logmnr_contents
WHERE SEG_OWNER = 'SCOTT'
  AND SEG_NAME  = 'EMP'
  AND OPERATION = 'DELETE'
ORDER BY SCN;

-- 步骤3：SQL_UNDO 的内容就是 INSERT 语句，执行它恢复数据
-- insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","SAL","DEPTNO") 
--   values ('7369','SMITH','CLERK',800,20);
-- ...（逐行执行）

EXECUTE DBMS_LOGMNR.END_LOGMNR;

---

六、LogMiner 输出字段说明

字段	说明
SCN	系统变更号，用于排序事务顺序
TIMESTAMP	操作时间
OPERATION	DML操作类型（INSERT/UPDATE/DELETE/DDL等）
SEG_OWNER	对象所属用户
SEG_NAME	对象名（表名等）
SQL_REDO	重做SQL（操作本身）
SQL_UNDO	撤销SQL（操作的逆操作，用于恢复数据）
USERNAME	执行操作的数据库用户
SESSION#	会话号
COMMIT_SCN	提交SCN

---

七、最佳实践

1. 生产环境必须开启归档模式：无归档 = 无法做时间点恢复
2. 配置多个归档目标：本地 + FRA，提高可靠性
3. 监控归档目录空间：FRA 使用率超过80%时告警
4. 定期清理归档日志：用RMAN DELETE，不要手工删除（会影响恢复目录）
5. 提前开启附加日志：生产环境预先启用 SUPPLEMENTAL LOG DATA，方便事后分析
6. LogMiner 使用完毕及时关闭：END_LOGMNR 释放资源

---

八、总结

归档日志管理与LogMiner的核心要点：

1. 归档模式：生产必须开启，支持热备和时间点恢复
2. 切换方式：SHUTDOWN → STARTUP MOUNT → ALTER DATABASE ARCHIVELOG → OPEN
3. 归档目标：本地目录或FRA，支持多个目标
4. 监控：关注归档频率、FRA空间使用
5. LogMiner：分析日志、找回误操作数据、生成 SQL_UNDO
6. 附加日志：LogMiner 准确工作需开启 SUPPLEMENTAL LOG DATA

---

上一篇【第31篇】Oracle重做日志文件管理操作详解
下一篇【第33篇】Oracle表管理与分区表详解

---

参考资料

• 《Oracle 11g数据库管理员指南》— 刘宪军著
• Oracle官方文档：Database Administrator’s Guide - Managing Archived Redo Log Files
• Oracle官方文档：Database Utilities - Using LogMiner to Analyze Redo Log Files