ORACLE中logminer通过归档日志或者在线重做日志解析回归sql和原sql的注意事项

ORACLE通过归档日志或者在线重做日志解析回归sql和原sql的注意事项

目录

• ORACLE通过归档日志或者在线重做日志解析回归sql和原sql的注意事项
  • 核心配置：开启归档模式与强制日志
  • 增强配置：启用补充日志
  • 说明：
    • 开启补充日志（推荐的生产环境配置）
    • 未开启补充日志
    • ❌ 差异与问题：
  • 总结对比
  • 建议

解析方式：logminer(略)，以下介绍注意事项

要让 Oracle 数据库将所有增删改（DML）和结构变更（DDL）的日志完整地写入在线重做日志，并持久化保存到归档日志中，你需要确保数据库配置了以下三个核心要素：归档模式 (ARCHIVELOG Mode)、强制日志 (Force Logging) 和 补充日志 (Supplemental Logging)

以下是详细的配置步骤和参数说明：

核心配置：开启归档模式与强制日志

这是保证日志被保存和可恢复的基础。归档模式确保日志历史不丢失，强制日志则保证即使是NOLOGGING操作（如某些批量插入）也会被记录。

步骤1：启用归档模式
数据库必须运行在归档模式下，否则日志切换后，在线重做日志的内容会被覆盖，无法保留历史变更记

-- 1. 关闭数据库
SHUTDOWN IMMEDIATE;
-- 2. 启动到挂载状态（MOUNT）
STARTUP MOUNT;
-- 3. 开启归档模式
ALTER DATABASE ARCHIVELOG;
-- 4. 打开数据库
ALTER DATABASE OPEN;

-- 5. 验证是否生效
ARCHIVE LOG LIST;
-- 或使用：
SELECT LOG_MODE FROM V$DATABASE; -- 应返回 'ARCHIVELOG'

步骤2：配置归档路径与格式
设置归档日志的存放位置和命名格式，确保系统知道将归档文件存到哪里

-- 设置归档路径（示例：本地目录）
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog' SCOPE=BOTH;
-- 设置归档文件命名格式（%t:线程号, %s:序列号, %r:重置日志ID）
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='arch_%t_%s_%r.arc' SCOPE=SPFILE;

步骤3：启用强制日志 (Force Logging)
有些操作（如使用/*+ APPEND */提示的INSERT）默认可以不记录日志。启用强制日志可以覆盖对象级别的NOLOGGING设置，确保所有变更都产生日志

-- 启用强制日志
ALTER DATABASE FORCE LOGGING;

-- 验证是否生效
SELECT FORCE_LOGGING FROM V$DATABASE; -- 应返回 'YES'

注意：即使启用了强制日志模式，也要检查表空间/表的日志属性：因为即使开启了数据库级别的强制日志，你也应该检查特定表空间或表的日志记录设置。确认它们没有被设置为NOLOGGING。
SELECT TABLESPACE_NAME, LOGGING FROM DBA_TABLESPACES;
SELECT TABLE_NAME, LOGGING FROM DBA_TABLES WHERE OWNER = 'YOUR_SCHEMA';


管理日志组：确保有足够的在线重做日志组，且大小配置合理，避免因日志切换频繁或检查点未完成导致的性能问题（如LOG FILE SWITCH (CHECKPOINT INCOMPLETE)等待）。通常建议至少配置3组在线重做日志。

增强配置：启用补充日志

默认情况下，重做日志只记录恢复所需的最小信息，这对于通过工具（如LogMiner、GoldenGate）解析“增删改”的前后值（Before and After values）是不够的。补充日志会在重做记录中添加额外的列信息，使你能够清楚地看到一条UPDATE语句修改了哪一列的哪个值。

步骤4：启用最小补充日志
这是使用LogMiner等工具进行分析的前提。

-- 启用最小补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 验证是否启用
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; -- 应返回 'YES' 或 'IMPLICIT'[citation:10]

说明：

如果不开补充日志（Supplemental Logging），LogMiner 通常无法获得正确的、可直接执行的回滚 SQL，并且原始 SQL 的解析结果也可能存在歧义或丢失关键信息。

案例：
场景：执行一条 UPDATE 语句。

-- 原始SQL
UPDATE TEST SET NAME = 'B' WHERE ID = 1;

开启补充日志（推荐的生产环境配置）

当开启了最小补充日志（ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;）后，Oracle 会在重做日志中记录足够的信息来唯一标识被修改的行以及修改前的值。

• SQL_REDO (重做SQL)：清晰地记录了将 NAME 改为 'B'。
• SQL_UNDO (回滚SQL)：清晰地记录了如何将数据改回原样。

LogMiner 解析结果示例：

列名	值
OPERATION	UPDATE
SQL_REDO	update "SCOTT"."TEST" set "NAME" = 'B' where "ID" = 1 and "NAME" = 'A';
SQL_UNDO	update "SCOTT"."TEST" set "NAME" = 'A' where "ID" = 1 and "NAME" = 'B';

✅ 优点：
回滚 SQL 准确：SQL_UNDO 中的 WHERE 条件包含了 ID 和 NAME 的原值，能精确定位到修改前的那一行。
可读性强：直接显示了列名 ID 和 NAME。
逻辑完整：即使表没有主键，LogMiner 也会尝试使用 ROWID 或其他列组合来唯一标识行，但有了补充日志，它会优先使用逻辑主键（如索引列），使回滚语句更可靠。

未开启补充日志

当没有开启补充日志时，Oracle 为了减少日志量，默认只在重做日志中记录了恢复数据块所需的最小信息。对于一条 UPDATE 语句，它可能只记录了哪个数据块发生了变化，以及新值是什么，但不一定记录了完整的旧值

LogMiner 解析结果可能如下：

列名	值 (问题所在)
OPERATION	UPDATE
SQL_REDO	update "SCOTT"."TEST" set "NAME" = 'B' where "ROWID" = 'AAAF1dAABAAAL3FAAA';
SQL_UNDO	update "SCOTT"."TEST" set "NAME" = NULL where "ROWID" = 'AAAF1dAABAAAL3FAAA';

❌ 差异与问题：

1.回滚 SQL 错误/丢失 (SQL_UNDO)

• 现象：如上所示，SQL_UNDO 中的 NAME 原值可能显示为 NULL（或者其他不可预知的值），或者干脆没有 SQL_UNDO 记录。因为日志里没记原值 'A'，LogMiner 不知道改回去应该是什么。
• 后果：如果拿着这个 SQL_UNDO 去执行，会把 NAME 更新成 NULL，而不是原来的 'A'，导致数据错误。

依赖 ROWID 定位

• 现象：SQL_REDO 和 SQL_UNDO 的 WHERE 条件不再包含业务主键（ID=1），而是使用了物理位置的 ROWID。
• 后果：ROWID 是物理地址，如果在其他数据库（如备库、恢复后的库）执行这条 SQL，ROWID 已经变了，语句会执行失败或定位到错误的数据。

信息缺失

• 如果没有补充日志，LogMiner 无法可靠地重建包含所有列值的完整 SQL。对于审计需求（谁在什么时候把哪个字段从什么改成了什么），不开补充日志基本无法实现。

总结对比

维度	开启补充日志	未开启补充日志
SQL_UNDO (回滚 SQL)	准确、完整，包含修改前的值。	错误 / 缺失，原值可能为 NULL 或乱码。
WHERE 条件	包含逻辑列（如主键、索引列），语句具有逻辑独立性。	主要依赖 ROWID（物理位置），跨库执行会失效。
字段旧值	清晰记录每一列修改前的内容。	未记录或记录不全，审计价值低。
结论	适合数据恢复、审计、同步	仅适合简单的变更追踪，无法用于可靠的回滚操作

建议

在生产环境中，如果你期望 LogMiner 能用于闪回、审计或数据同步，必须开启最小补充日志。