【Oracle数据库指南】第32篇：Oracle归档日志管理与LogMiner日志分析

上一篇【第31篇】Oracle重做日志文件管理操作详解下一篇【第33篇】Oracle表管理与分区表详解摘要归档日志Archive Log是Oracle数据库实现时间点恢复的核心机制也是数据库备份恢复策略的重要组成部分。本文详细讲解归档模式的开启与配置、归档目标的设置、归档日志的监控与管理以及Oracle内置的日志挖掘工具 LogMinerDBMS_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_1LOCATION/u03/archive/testdbSCOPEBOTH;-- 设置归档文件命名格式ALTERSYSTEMSETLOG_ARCHIVE_FORMATtestdb_%t_%s_%r.arcSCOPESPFILE;-- %t 线程号, %s 序列号, %r 重置日志ID-- 设置并行归档进程数ALTERSYSTEMSETLOG_ARCHIVE_MAX_PROCESSES4SCOPEBOTH;3.2 使用快速恢复区FRA作为归档目标-- 配置FRA作为归档目标推荐Oracle自动管理文件ALTERSYSTEMSETDB_RECOVERY_FILE_DEST/u04/fast_recovery_areaSCOPEBOTH;ALTERSYSTEMSETDB_RECOVERY_FILE_DEST_SIZE50G SCOPEBOTH;-- 将LOG_ARCHIVE_DEST_1设置为USE_DB_RECOVERY_FILE_DESTALTERSYSTEMSETLOG_ARCHIVE_DEST_1LOCATIONUSE_DB_RECOVERY_FILE_DESTSCOPEBOTH;3.3 多个归档目标-- 同时归档到多个位置本地 远程ALTERSYSTEMSETLOG_ARCHIVE_DEST_1LOCATION/u03/archive/testdb MANDATORY-- 本地必须成功SCOPEBOTH;ALTERSYSTEMSETLOG_ARCHIVE_DEST_2SERVICEstandby_db ASYNC-- 远程备库异步SCOPEBOTH;-- 设置归档最小成功数至少1个目标成功归档才允许日志切换ALTERSYSTEMSETLOG_ARCHIVE_MIN_SUCCEED_DEST1SCOPEBOTH;四、归档日志监控4.1 查看归档状态-- 查看最近的归档日志SELECTsequence#, name, first_time, next_time,blocks*block_size/1024/1024ASsize_mb,archived,deletedFROMv$archived_logWHEREarchivedYESORDERBYsequence# 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_timeTRUNC(SYSDATE)ANDarchivedYESGROUPBYTRUNC(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_FILENAMElogmnr_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,OPTIONSDBMS_LOGMNR.NEW);-- 步骤2启动 LogMiner使用在线数据字典最方便EXECUTEDBMS_LOGMNR.START_LOGMNR(OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);-- 步骤3查询 LogMiner 内容SELECTSCN,TIMESTAMP,SEG_OWNER,SEG_NAME,OPERATION,SQL_REDO,SQL_UNDO,USERNAME,SESSION#FROMv$logmnr_contentsWHERESEG_OWNERSCOTTANDSEG_NAMEEMPORDERBYSCN;-- 步骤4关闭 LogMinerEXECUTEDBMS_LOGMNR.END_LOGMNR;5.5 分析归档日志历史数据分析-- 分析特定时间段内的历史操作EXECUTEDBMS_LOGMNR.START_LOGMNR(STARTTIMETO_DATE(2024-01-15 09:00:00,YYYY-MM-DD HH24:MI:SS),ENDTIMETO_DATE(2024-01-15 10:30:00,YYYY-MM-DD HH24:MI:SS),OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOGDBMS_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_OWNERSCOTTANDSEG_NAMEEMPANDOPERATIONIN(INSERT,UPDATE,DELETE)ORDERBYSCN;EXECUTEDBMS_LOGMNR.END_LOGMNR;5.6 实战案例找回误删的数据-- 场景2024-01-15 10:00 有人执行了 DELETE FROM scott.emp WHERE deptno20-- 需要恢复被删除的数据-- 步骤1启动LogMiner分析删除操作前后的时间段EXECUTEDBMS_LOGMNR.START_LOGMNR(STARTTIMETO_DATE(2024-01-15 09:50:00,YYYY-MM-DD HH24:MI:SS),ENDTIMETO_DATE(2024-01-15 10:10:00,YYYY-MM-DD HH24:MI:SS),OPTIONSDBMS_LOGMNR.DICT_FROM_ONLINE_CATALOGDBMS_LOGMNR.CONTINUOUS_MINEDBMS_LOGMNR.COMMITTED_DATA_ONLY);-- 步骤2提取被删除数据的 SQL_UNDO即 INSERT 语句SELECTSQL_UNDOFROMv$logmnr_contentsWHERESEG_OWNERSCOTTANDSEG_NAMEEMPANDOPERATIONDELETEORDERBYSCN;-- 步骤3SQL_UNDO 的内容就是 INSERT 语句执行它恢复数据-- insert into SCOTT.EMP(EMPNO,ENAME,JOB,SAL,DEPTNO)-- values (7369,SMITH,CLERK,800,20);-- ...逐行执行EXECUTEDBMS_LOGMNR.END_LOGMNR;六、LogMiner 输出字段说明字段说明SCN系统变更号用于排序事务顺序TIMESTAMP操作时间OPERATIONDML操作类型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 FilesOracle官方文档Database Utilities - Using LogMiner to Analyze Redo Log Files