【精品篇】oracle库手动误操后，如何使用Logminer进行恢复

【引言】
《ADG实操：如何吃下这颗“后悔药”》
《Oracle ADG同步技术，DBA必备的一种“后悔药”》

之前的这两篇文章讲述了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】
1.回溯库数据变化：分析在线/离线重作日志文件跟踪实例数据变化，且此操作不会影响生产实例。
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包，该包用来创建数据字典文件。

创建完毕后将包括如下过程和视图：
类型 过程名 用途
过程 Dbms_logmnr_d.build 创建一个数据字典文件
过程 Dbms_logmnr.add_logfile 在类表中增加日志文件以供分析
过程 Dbms_logmnr.start_logmnr 使用一个可选的字典文件和前面确定要分析日志文件来启动LogMiner
过程 Dbms_logmnr.end_logmnr 停止LogMiner分析
视图 V$logmn{r}_{d}ictionary显示用来决定对象ID名称的字典文件的信息视图V$logmnr_logs 在LogMiner启动时显示分析的日志列表
视图 V$logmnr_contents LogMiner启动后，可以使用该视图在SQL提示符下输入SQL语句来查询重做日志的内容

建议创建logmnr专用的tablespace
Oracle 官方解释如下：
SET_TABLESPACE Procedure
By default, all LogMiner tables are created to use the SYSAUX tablespace. However, it may be desirable to have LogMiner tables use an alternate tablespace. Use this procedure to move LogMiner tables to an alternate tablespace.

官方文档创建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目录，然后用新创建的用户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文件夹是否设置
SQL > SHOW PARAMETER utl_file_dir;

5. 创建数据同步用户

在数据库创建LOGMINER用户，该用户需要具有DBA权限
–在源数据库创建LOGMINER用户，并赋予DBA权限
SQL > CREATE USER LOGMINER IDENTIFIED BY LOGMINER;
SQL > CONNECT, RESOURCE,DBA TO LOGMINER;

二、模拟误删除操作
测试数据准备
–以LOGMINER用户登录（非DBA登录）创建ethan_table表
1 sqlplus ethanYang/ethan_yang@ethanDB
2 CREATE TABLE ethan_table
3 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2));

–插入ethan_table表数据

1 INSERT INTO ethan_table VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
2 INSERT INTO ethan_table VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
3 INSERT INTO ethan_table VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
4 INSERT INTO ethan_table VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
5 COMMIT;

三、 执行恢复操作
创建数据字典文件
创建数据字典文件
因为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);

确认当前处于联机状态的日志文件
–需要确认当前处于联机状态的日志文件

SELECT group#, sequence#, status, first_change#, first_time \
FROM V$log ORDER BY first_change#;

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

1.3.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、添加其他日志文件到列表
Example:

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

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

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

2、有限制条件

过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置（参数含义见表1），可以缩小要分析日志文件的范围。通过设置起始时间和终止时间参数我们可以限制只分析某一时间范围的日志。
START_LOGMNR Procedure
This procedure starts LogMiner by loading the dictionary that LogMiner will use to translate internal schema object identifiers to names.
Syntax

DBMS_LOGMNR.START_LOGMNR ( 
   startScn           IN NUMBER default 0,
   endScn             IN NUMBER default 0,
   startTime          IN DATE default '01-jan-1988',
   endTime            IN DATE default '31-dec-2110',
   DictFileName       IN VARCHAR2 default '',
   Options            IN BINARY_INTEGER default 0 );

参数 参数类型 默认值 含义
StartScn 数字型 0 分析重作日志中SCN≥StartScn日志文件部分
EndScn 数字型 0 分析重作日志中SCN≤EndScn日志文件部分
StartTime 日期型 default ‘01-jan-1988’
分析重作日志中时间戳≥StartTime的日志文件部分
EndTime 日期型 default ‘31-dec-2110’ 分析重作日志中时间戳≤EndTime的日志文件部分
DictFileName 字符型 ’’ 字典文件该文件包含一个数据库目录的快照。

举例：如分析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); 

1.3.5 结果观察分析
截止目前，我们已经得到了重作日志文件中的操作内容。动态性能视图v$logmn{r}_{c}ontents包含LogMiner分析得到的所有的信息。SELECTsq{l}_{r}edoFROMv$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'; 

序号 名称 含义
1 SCN 特定数据变化的系统更改号
2 SEG_OWNER 数据发生改变的段名称
3 SEG_NAME 段的所有者名称
4 SQL_REDO 可以为重做记录重做指定行变化的SQL语句(正向操作)
5 SQL_UNDO 可以为重做记录回退或恢复指定行变化的SQL语句(反向操作)

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

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

答案是：使用CTAS方式创建一个table保存下来啊，命令如下：

SQL>  create table ethan_logmnr as select * from v$logmnr_contents;

这样便可以落盘，在任何一个session回话中均可以查看。

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

SQL>  select sql_redo,SQL_UNDO 
from ethan_logmnr 
where USERNAME=’USER_NAEM’ 
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