【此为本公众号第15篇文章】

《Oracle ADG同步技术，DBA必备的一种“后悔药”》

《ADG实操：如何吃下这颗“后悔药”》

此前的这两篇文章讲述了dataguard恢复人为误操作的方法；当时在文中也简单介绍了其用途“LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句。该工具特别适用于调试、审计或者回退某个特定的事务。”

今天，本文就来讲讲具体LogMiner 的使用方法。

LogMiner介绍

LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图（Oracle8i内置包的一部分）组成，它作为Oracle数据库的一部分来发布是8i产品提供的一个完全免费的工具。但该工具和其他Oracle内建工具相比使用起来显得稍显复杂，原因是LogMiner没有提供图形用户界面（GUI）。

LogMiner作用

在Oracle 8i之前，Oracle没有提供任何协助数据库管理员来读取和解释重作日志文件内容的工具。从8i以后，Oracle提供了这样一个强有力的工具LogMiner。LogMiner 可以用来分析在线/redo日志，也可以用来分析离线日志文件/归档日志。

划重点：

1. 一个实例中，LogMiner即可以分析本身实例的在线/离线重作日志文件；

2. 也可以分析其他实例的在线/离线重作日志文件。

LogMiner主要用途有2:

回溯库数据变化：分析在线/离线重作日志文件跟踪实例数据变化，且此操作不会影响生产实例。

恢复人为误操作：通过执行LogMiner分析重作日志文件的反向操作sql，来达到修正人为误操作的目的。

一、 操作步骤

1.  安装LogMiner

在使用LogMiner之前需要确认Oracle是否带有进行LogMiner分析包，一般来说Windows操作系统Oracle10g以上都默认包含。如果不能确认，可以DBA身份登录系统，查看系统中是否存在运行LogMiner所需要的dbms_logmnr、dbms_logmnr_d包，如果没有需要安装LogMiner工具，必须首先要运行下面这样两个脚本：

安装LogMiner工具，以下两个脚本以SYSDBA身份运行 

SQL> @?/rdbms/admin/dbmslmd.sql

SQL> @?/rdbms/admin/dbmslm.sql

第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。

第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

创建完毕后将包括如下过程和视图：

建议创建logmnr专用的tablespace

Oracle 官方解释如下：

官方文档创建logmnr专用的tablespace给的示例如下：

为了限制tbs的大小，我们使用如下命令：

1.创建logmnr专用tbs

SQL>  create tablespace logmnr datafile '/oradata/logmnr.dbf'

SQL>  size 500m reuse autoextend on maxsize 10G;

2.切换到logmnr专用tbs

SQL>  exec dbms_logmnr_d.set_tablespace(new_tablespace=>'logmnr');

2. LogMiner设置

操作系统层创建LogMiner目录/oradata/logminer，然后用新创建的用户logmnr 或者DBA用户登录，指定数据字典文件的位置，即设定UTL_FILE_DIR参数值，此参数为放置数据字典文件的目录，因是静态变量，故需重启库生效

SQL> CONN AS SYSDBA

SQL> CREATE DIRECTORY utlfile AS  '/oradata/logminer';

SQL> alter system set utl_file_dir=’/oradata/logminer’ scope=spfile;

3. 开启LogMiner日志补充模式

注意：最好开启LogMiner日志补充模式，如果没有开始LogMiner补充模式将无法查看DDL语句，按照测试结果看，只有开始LogMiner日志补充模式后，才能查看DDL语句，在此之前进行DDL将无法进行查看。

SQL> alter database add supplemental log data;

4. 重启数据库验证

修改完毕后，重启数据库

SQL > SHUTDOWN IMMEDIATE;

SQL > STARTUP;

查看Logminer文件夹utl_file_dir是否设置生效

SQL > SHOW PARAM53ETER utl_file_dir;

5. 创建数据同步用户

在数据库创建LOGMINER用户，该用户需要具有DBA权限；在源数据库创建LOGMINER用户，并赋予DBA权限

SQL > CREATE USER LOGMINER IDENTIFIED BY LOGMINER;

SQL > CONNECT, RESOURCE,DBA TO LOGMINER;

二、模拟误删除操作

1、 测试数据准备

--以ethan用户登录（非DBA登录）, 创建ethan_table表

SQL> sqlplus  ethan/oracle@ethanDB

SQL> CREATE TABLE ethan_table

(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

HIREDATE DATE,

SAL NUMBER(7,2));

 插入ethan_table表数据

1. INSERT INTO ethan_table VALUES (001,'ethan','checker',to_date('06-18-2019','dd-mm-yyyy'),10000);

2. INSERT INTO ethan_table VALUES (002,'simson','salesman',to_date('07-17-2019','dd-mm-yyyy'),15000);

3. COMMIT;

三、 执行恢复操作

1. 创建数据字典文件

因为ethan_table的创建为ddl语句，数据库对象发生变化，需要重新创建数据字典文件

--以logmnr用户（DBA权限）登录，生成字典文件

SQL>  CONN  logmnr/logmnr@ethanDB as sysdba

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/oradata/logminer',options => dbms_logmnr_d.store_in_flat_file);

使用LogMiner工具分析redo log文件/archive log 文件之前，强烈建议使用DBMS_LOGMNR_D 包将数据字典导出为一个文本文件。该字典文件是可选的，但如果没有它，LogMiner解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，无法直接理解的。

DBMS_LOGMNR_D 包将数据字典导出为一个文本文件的原因为：

1. 如要分析的数据库表有变化，库的数据字典也发生变化，需重新创建该字典文件；

2. 在新库中分析另外一个数据库的重作日志时，也须生成一个原库的数据字典文件。

划重点：使用logminner分析异常人为操作的重做日志/归档日志，可以在同一实例中，也可在另一个实例中；

2. 确认当前联机状态的redo日志

注意：这里数据库没有执行执行如下命令，

Alter system switch logfile;

或

alter system archive log current;

注意：

alter system switch logfile 是强制日志切换，不一定就归档当前的重做日志文件；

（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）alter system archive log current 是归档当前的重做日志文件，不管自动归档有没有打都归档。主要的区别在于:ALTER SYSTEM SWITCH LOGFILE对单实例数据库或RAC中的当前实例执行日志切换;而ALTER SYSTEM ARCHIVE LOG CURRENT会对数据库中的所有实例执行日志切换。

这里没有执行上述两条alter命令的一条，需要确认当前处于联机状态的日志文件，

从上图可以看出在线日志REDO03处于ACTIVE状态中。

如果执行，需要确认刚刚归档的日志文件

SQL> select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc;

3. 加入待分析的日志文件

使用dbms_logmnr.add_logfile过程加入分析日志文件，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。

1. 创建日志文件到列表

1 BEGIN

2 dbms_logmnr.add_logfile(logfilename=>'/oradata/REDO03.LOG',options=>dbms_logmnr.NEW);

3 END;

4

2. 添加其他日志文件到列表

BEGIN

dbms_logmnr.add_logfile(logfilename=>'/oradata/REDO04.LOG',options=>dbms_logmnr.ADDFILE);

END;

/

3. 使用LogMiner进行日志分析

Oracle的LogMiner分析时分为无限制条件和限制条件，无限制条件中分析所有加入到分析列表日志文件，限制条件根据限制条件分析指定范围日志文件。

 1、无限制条件

EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/oradata/logminer/dictionary.ora');

2、有限制条件

过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置（参数含义见表1），可以缩小要分析日志文件的范围。通过设置起止时间参数来限制分析某一时间范围的日志内容。

举例：如分析2019年7月17日全天的日志：

EXECUTE dbms_logmnr.start_logmnr(

DictFileName => dictfilename=>'/oradata/logminer/dictionary.ora', 

StartTime =>to_date('2019-7-19 00:00:00','YYYY-MM-DD HH24:MI:SS')

EndTime =>to_date(''2019-7-19 23:59:59','YYYY-MM-DD HH24:MI:SS ')); 

也可通过设置起始SCN和截至SCN来限制要分析日志的范围：

EXECUTE dbms_logmnr.start_logmnr(

DictFileName =>'/oradata/logminer/dictionary.ora',

StartScn =>476711192,

EndScn =>476711315); 

4. 结果分析

截止目前，已经得到了重作日志文件中的操作内容，动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。

SELECT sql_redo FROM v$logmnr_contents; 

如仅仅想知道某个用户对于某张表的操作，可以通过下面的SQL查询定位，该查询可以得到用户LOGMINER对表ethanyang所作的一切操作（sql_redo）和对应下反向操作（SQL_UNDO）。

SQL>  SELECT sql_redo,SQL_UNDO FROM v$logmnr_contents WHERE username='ETHANYANG' AND tablename='ETHAN_TABLE'; 

划重点：视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。

一个问题：如果我们想保留视图v$logmnr_contents的分析结果怎么？

最后一步：

找到误操作的SQL_REDO，然后执行对应的SQL_UNDO,执行SQL_UNDO的内容即可恢复表误操作之前的数据。

SQL> select sql_redo,SQL_UNDO 

from ethan_logmnr 

where USERNAME=’USER_NAME’ 

and TABLE_NAME=’USER_TABLE’；

或者如下命令：

SELECT sql_redo, sql_undo, seg_owner

FROM v$logmnr_contents

WHERE seg_name='AAAAA'

AND seg_owner='LOGMINER';

最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。

SQL>  EXECUTE dbms_logmnr.end_logmnr(dictfilename=>'/oradata/logminer/dictionary.ora');

【结语】

1. 本文主要讲述了LogMiner用途以及大致的操作步骤；以及具体实现一个恢复场景；

2. LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 在线/归档日志文件中的具体内容，特别是该工具可以分析出所有对于数据库操作的DML和DDL语句。该工具特别适用于调试、审计或者回退某个特定的事务。

【官方参考】

https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1573

如果大家觉得此文有帮助，欢迎关注个人微信公众号；

长按识别二维码或公众号搜索“一森咖记”