【Oracle数据库指南】第32篇：Oracle归档日志管理与LogMiner日志分析
上一篇【第31篇】Oracle重做日志文件管理操作详解
下一篇【第33篇】Oracle表管理与分区表详解
摘要
归档日志（Archive Log）是Oracle数据库实现时间点恢复的核心机制，也是数据库备份恢复策略的重要组成部分。本文详细讲解归档模式的开启与配置、归档目标的设置、归档日志的监控与管理，以及Oracle内置的日志挖掘工具 LogMiner（DBMS_LOGMNR）的使用方法——通过分析重做/归档日志来追踪历史SQL操作，是DBA审计、排错和数据恢复的利器。
一、归档模式概述
1.1 非归档模式 vs 归档模式
| 特性 | 非归档模式（NOARCHIVELOG） | 归档模式（ARCHIVELOG） |
|---|---|---|
| 数据安全性 | 只能恢复到最近一次完整备份 | 可以恢复到任意时间点 |
| 在线热备份 | 不支持 | 支持 |
| 日志使用 | 循环覆盖（旧日志被覆盖） | 旧日志在归档后才能覆盖 |
| 磁盘要求 | 较低 | 较高（需要存储归档文件） |
| 适用场景 | 开发/测试环境 | 生产环境（必须） |
结论：生产数据库必须开启归档模式。
1.2 检查当前模式
-- 查看当前日志模式SELECTname,log_modeFROMv$database;-- LOG_MODE = 'ARCHIVELOG' 或 'NOARCHIVELOG'-- 也可以查看参数SHOWPARAMETER log_archive_dest_1;二、切换归档模式
2.1 从非归档切换到归档模式
-- 步骤1：关闭数据库（需要正常关闭）SHUTDOWNIMMEDIATE;-- 步骤2：启动到 MOUNT 状态（不打开数据文件）STARTUP MOUNT;-- 步骤3：切换到归档模式ALTERDATABASEARCHIVELOG;-- 步骤4：打开数据库ALTERDATABASEOPEN;-- 步骤5：验证SELECTlog_modeFROMv$database;-- LOG_MODE = ARCHIVELOG-- 步骤6：手动切换一次日志，测试归档是否正常工作ALTERSYSTEM SWITCH LOGFILE;ALTERSYSTEM ARCHIVE LOGALL;-- 步骤7：查看归档日志是否生成SELECTname,sequence#, archived, appliedFROMv$archived_logORDERBYsequence# DESCFETCHFIRST5ROWSONLY;2.2 从归档模式切换回非归档模式（不推荐）
SHUTDOWNIMMEDIATE;STARTUP MOUNT;ALTERDATABASENOARCHIVELOG;ALTERDATABASEOPEN;三、归档目标配置
3.1 本地归档目标
-- 设置本地归档目标ALTERSYSTEMSETLOG_ARCHIVE_DEST_1='LOCATION=/u03/archive/testdb'SCOPE=BOTH;-- 设置归档文件命名格式ALTERSYSTEMSETLOG_ARCHIVE_FORMAT='testdb_%t_%s_%r.arc'SCOPE=SPFILE;-- %t = 线程号, %s = 序列号, %r = 重置日志ID-- 设置并行归档进程数ALTERSYSTEMSETLOG_ARCHIVE_MAX_PROCESSES=4SCOPE=BOTH;3.2 使用快速恢复区（FRA）作为归档目标
-- 配置FRA作为归档目标（推荐，Oracle自动管理文件）ALTERSYSTEMSETDB_RECOVERY_FILE_DEST='/u04/fast_recovery_area'SCOPE=BOTH;ALTERSYSTEMSETDB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;-- 将LOG_ARCHIVE_DEST_1设置为USE_DB_RECOVERY_FILE_DESTALTERSYSTEMSETLOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'SCOPE=BOTH;3.3 多个归档目标
-- 同时归档到多个位置（本地 + 远程）ALTERSYSTEMSETLOG_ARCHIVE_DEST_1='LOCATION=/u03/archive/testdb MANDATORY'-- 本地，必须成功SCOPE=BOTH;ALTERSYSTEMSETLOG_ARCHIVE_DEST_2='SERVICE=standby_db ASYNC'-- 远程备库，异步SCOPE=BOTH;-- 设置归档最小成功数（至少1个目标成功归档才允许日志切换）ALTERSYSTEMSETLOG_ARCHIVE_MIN_SUCCEED_DEST=1SCOPE=BOTH;四、归档日志监控
4.1 查看归档状态
-- 查看最近的归档日志SELECTsequence#, name, first_time, next_time,blocks*block_size/1024/1024ASsize_mb,archived,deletedFROMv$archived_logWHEREarchived='YES'ORDERBYsequence# DESCFETCHFIRST20ROWSONLY;-- 查看归档目标状态SELECTdest_id,dest_name,status,target,archiver,scheduleFROMv$archive_dest_statusWHEREstatus!='INACTIVE';-- 查看今日归档量SELECTTRUNC(completion_time,'HH')AShour,COUNT(*)ASfiles,SUM(blocks*block_size)/1024/1024AStotal_mbFROMv$archived_logWHEREcompletion_time>TRUNC(SYSDATE)ANDarchived='YES'GROUPBYTRUNC(completion_time,'HH')ORDERBY1;4.2 归档目录空间监控
-- 查看FRA空间使用情况SELECTspace_limit/1024/1024/1024ASlimit_gb,space_used/1024/1024/1024ASused_gb,ROUND(space_used/space_limit*100,2)ASused_pct,space_reclaimable/1024/1024/1024ASreclaimable_gbFROMv$recovery_file_dest;-- 查看FRA中各类型文件占用SELECTfile_type,number_of_files,percent_space_used,percent_space_reclaimableFROMv$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），以获取足够的信息-- 查看当前状态SELECTsupplemental_log_data_min,supplemental_log_data_pkFROMv$database;-- 启用最小附加日志（必须）ALTERDATABASEADDSUPPLEMENTAL LOGDATA;-- 启用主键附加日志（推荐，记录WHERE条件中的主键值）ALTERDATABASEADDSUPPLEMENTAL LOGDATA(PRIMARYKEY)COLUMNS;-- 前提2：需要对 V$LOGMNR_CONTENTS 有查询权限（DBA 用户默认有）GRANTSELECTONv_$logmnr_contentsTOlogminer_user;GRANTEXECUTEONDBMS_LOGMNRTOlogminer_user;GRANTEXECUTEONDBMS_LOGMNR_DTOlogminer_user;5.3 提取数据字典
-- 方法1：将数据字典提取到日志文件（在线数据库）EXECUTEDBMS_LOGMNR_D.BUILD(DICTIONARY_FILENAME=>'logmnr_dict.ora',DICTIONARY_LOCATION=>'/tmp');-- 方法2：使用重做日志自带字典（ONLINE_CATALOG，最简单）-- 直接在 START_LOGMNR 时指定 DICT_FROM_ONLINE_CATALOG 选项5.4 分析在线重做日志（实时查看）
-- 步骤1：添加要分析的日志文件EXECUTEDBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/u01/redo1/redo01a.log',OPTIONS=>DBMS_LOGMNR.NEW);-- 步骤2：启动 LogMiner（使用在线数据字典，最方便）EXECUTEDBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);-- 步骤3：查询 LogMiner 内容SELECTSCN,TIMESTAMP,SEG_OWNER,SEG_NAME,OPERATION,SQL_REDO,SQL_UNDO,USERNAME,SESSION#FROMv$logmnr_contentsWHERESEG_OWNER='SCOTT'ANDSEG_NAME='EMP'ORDERBYSCN;-- 步骤4：关闭 LogMinerEXECUTEDBMS_LOGMNR.END_LOGMNR;5.5 分析归档日志（历史数据分析）
-- 分析特定时间段内的历史操作EXECUTEDBMS_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操作SELECTTO_CHAR(TIMESTAMP,'HH24:MI:SS')AStime,USERNAME,OPERATION,SQL_REDO,SQL_UNDOFROMv$logmnr_contentsWHERESEG_OWNER='SCOTT'ANDSEG_NAME='EMP'ANDOPERATIONIN('INSERT','UPDATE','DELETE')ORDERBYSCN;EXECUTEDBMS_LOGMNR.END_LOGMNR;5.6 实战案例：找回误删的数据
-- 场景：2024-01-15 10:00 有人执行了 DELETE FROM scott.emp WHERE deptno=20-- 需要恢复被删除的数据-- 步骤1：启动LogMiner，分析删除操作前后的时间段EXECUTEDBMS_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 语句）SELECTSQL_UNDOFROMv$logmnr_contentsWHERESEG_OWNER='SCOTT'ANDSEG_NAME='EMP'ANDOPERATION='DELETE'ORDERBYSCN;-- 步骤3：SQL_UNDO 的内容就是 INSERT 语句，执行它恢复数据-- insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","SAL","DEPTNO")-- values ('7369','SMITH','CLERK',800,20);-- ...（逐行执行）EXECUTEDBMS_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 |
七、最佳实践
- 生产环境必须开启归档模式：无归档 = 无法做时间点恢复
- 配置多个归档目标：本地 + FRA，提高可靠性
- 监控归档目录空间：FRA 使用率超过80%时告警
- 定期清理归档日志：用RMAN DELETE，不要手工删除（会影响恢复目录）
- 提前开启附加日志：生产环境预先启用 SUPPLEMENTAL LOG DATA，方便事后分析
- LogMiner 使用完毕及时关闭：END_LOGMNR 释放资源
八、总结
归档日志管理与LogMiner的核心要点：
- 归档模式：生产必须开启，支持热备和时间点恢复
- 切换方式：SHUTDOWN → STARTUP MOUNT → ALTER DATABASE ARCHIVELOG → OPEN
- 归档目标：本地目录或FRA，支持多个目标
- 监控：关注归档频率、FRA空间使用
- LogMiner：分析日志、找回误操作数据、生成 SQL_UNDO
- 附加日志：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