Logminer:oracle人为误操作之恢复神器

Ethan_Yang 2019-07-19
920

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



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

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


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

 

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


LogMiner介绍

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

 

LogMiner作用

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


划重点:

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

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

LogMiner主要用途有2:

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

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

 

一、 操作步骤

1.  安装LogMiner

在使用LogMiner之前需要确认Oracle是否带有进行LogMiner分析包,一般来说Windows操作系统Oracle10g以上都默认包含。如果不能确认,可以DBA身份登录系统,查看系统中是否存在运行LogMiner所需要的dbms_logmnrdbms_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$logmnr_dictionary

显示用来决定对象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目录/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进行日志分析

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

 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

字符型

 ’’

字典文件该文件包含一个数据库目录的快照。

 

举例:如分析2019717全天的日志:

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'; 

 

序号

名称

含义

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_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 在线/归档日志文件中的具体内容,特别是该工具可以分析出所有对于数据库操作的DMLDDL语句。该工具特别适用于调试、审计或者回退某个特定的事务。

 

【官方参考】

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


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

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


最后修改时间:2020-04-02 12:36:43
文章转载自Ethan_Yang,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论