Oracle数据误删不用怕，跟我来学日志挖掘

今天主要分享利用logminer工具怎么恢复误删的数据。

一、logminer 相关概念

logminer 的作用

LogMiner是Oracle数据库中的一个工具，它可以用于分析数据库的重做日志文件，以了解数据库的操作历史和数据变化情况。LogMiner可以将重做日志文件中的SQL语句提取出来，并将其转换成易于理解的格式，以便用户进行分析和查询。

LogMiner的主要作用包括：

数据恢复：LogMiner可以用于恢复误删除或误修改的数据，通过分析重做日志文件中的SQL语句，可以找到被删除或修改的数据，并进行恢复操作。

数据审计：LogMiner可以用于审计数据库的操作，通过分析重做日志文件中的SQL语句，可以了解数据库的操作情况和操作者。

数据分析：LogMiner可以用于分析数据库的历史数据，通过分析重做日志文件中的SQL语句，可以了解数据库的操作历史和数据变化情况。

数据备份：LogMiner可以用于备份数据库的数据，通过分析重做日志文件中的SQL语句，可以保证备份数据的完整性和可靠性。

logminer 包的组成

1）dbms_logmnr.add_logfile

作用：用于为日志分析列表增加或删除日志文件，或者建立日志分析列表。

语法：dbms_logmnr.add_logfile(LogFileName in varchar2,Option in binary_integer default addfile);

其中LogFileName指定要增加或删除的日志文件名称，Option指定选项(dbms_logmnr.new建立日志分析列表，dbms_logmnr.addfile增加日志文件，dbms_logmnr.removefile删除文件)    

2）dbms_logmnr.start_logmnr

作用：用于启动logmnr会话

语法：dbms_logmnr.start_logmnr(startscn in number default o,endscn in number default 0,

starttime in date default '01-jan-1988',endtime in date default '01-jan-2988',

dictfilename in varchar2 default '',option in binary_integer default 0);

其中startscn指定日志分析的起始scn值，endscn指定日志分析的结束scn值，starttime指定日志分析的起始时间，endtime指定日志分析的结束时间，

dictfilename指定日志分析要使用的字典文件名，option指定logminer分析选项。      

3）dbms_logmnr.end_logmnr

作用：结束logminer会话

语法：dbms_logmnr.end_logmnr

4）dbms_logmnr.mine_value

作用：用于返回要摘取的列信息，该函数在启动logminer之后调用。

语法：dbms_logmner.mine_value(sql_redo_undo in raw,column_name in varchar2 default '') return varchar2;

其中sql_redo_undo用于指定要摘取的数据(redo_value或undo_value)，column_name用于指定要摘取的列(格式：schema.table.column);

5）dbms_logmnr.column_present

作用：用于确定列是否出现在数据的redo部分或undo部分

语法：dbms_logmnr.column_present(sql_redo_undo in raw,column_name in varchar2 default '') return number;

其中如果列在redo或undo部分存在，则返回1，否则返回0。        

6）dbms_logmnr_d.build

作用：用于建立字典文件

语法：dbms_logmnr_d.build(dictionary_filename in varcahr2,dictionary_location in varchar2,options in number);

其中dictionary_filename指定字段文件名，dictionary_location指定文件所在位置，options指定字典要写入位置(store_in_flat_file:文本文件,store_in_redo_log2:重新日志)    

7）dbms_logmnr_d.set_tablespace

作用：用于改变logminer表所在的表空间

语法：dbms_logmnr_d.set_tablespace(new_tablespace in default varchar2,dict_tablespace in default varchar2,spill_tablespace in default varchar2);

其中new_tablespace指定logminer表所在的表空间，dict_tablespace指定字典表所在表空间，spill_tablespace指定溢出表所在表空间。  

logminer 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。

LogMiner 的配置要求

(1)源数据库和分析数据库必须运行在相同硬件平台上；

(2)分析数据库可以是独立数据库或源数据库；

(3)分析数据库的版本不能低于源数据库的版本；

(4)分析数据库与源数据库必须具有相同的字符集；

注意：异机分析数据库日志时候，要注意。需要用字典文件的格式来分析。

LogMiner 使用注意事项

(1)LogMiner 字典必须在源数据库中生成

(2)当分析多个重做日志和归档日志时，它们必须是同一个源数据库的重做日志和归档日志

(3)当分析多个重做日志和归档日志时，它们必须具有相同的 resetlogs scn

(4)当分析的重做日志和归档日志必须在 Oracle8.0 版本以上  

什么是补充日志，补充日志的作用是什么？

logminer日志支持80%左右的日志挖掘，如果开启附加日志，大概支持98%的日志挖掘

重做日志用于实现例程恢复和介质恢复，这些操作所需要的数据被自动记录在重做日志中。但是，重做应用可能还需要记载其他列信息到重做日志中，记录其他列的日志过程被称为补充日志

默认情况下，Oracle 数据库没有提供任何补充日志，从而导致默认情况下LogMiner 无法支持以下特征：

（1）索引簇、链行和迁移行；

（2）直接路径插入；

（3）摘取 LogMiner 字典到重做日志；

（4）跟踪 DDL；

（5）生成键列的 SQL_REDO 和 SQL_UNDO 信息；

（6）LONG 和 LOB 数据类型。

激活数据库补充日志

因此，为了充分利用 LogMiner 提供的特征，必须激活补充日志。注意：激活不用重启数据库，数据库联机即可。在做之前，检查下 suppplemental logging：

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from V$database;

SUPPLEME

--------

NO

alter database add supplemental log data;

如果是 YES 或者 IMPLICIT 则表明已经生效了，否则需要启动：

删除 supplemental log

alter database drop supplemental log data;

supplemental logging（扩充日志）：在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用 redo log 进行一些其他应用时是不够的。例如在 redo log 中使用 rowid 唯一标识一行而不是通过 Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些 dml 时就可能会有问题，因为不同的数据库其 rowid 代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入 redo log，这就是 supplemental logging。

supplemental logging分为两个级别database_level and table_level，默认不开启，建议开启数据库级别。

二、logminer 使用详解

LogMiner 两种使用类型，一种是使用源数据库的数据字典分析 DML 操作，另一种是提取 LogMiner 数据字典到字典文件分析 DDL 操作。

安装 logminer

$ORACLE_HOME/rdbms/admin/dbmslm.sql : DBMS_LOGMNR

$ORACLE_HOME/rdbms/admin/dbmslmd.sql :DBMS_LOGMNR_D

--过程

dbms_logmnr_d.build 创建一个数据字典文件

dbms_logmnr.add_logfile

dbms_logmnr.start_logmnr

dbms_logmnr.end_logmnr

--视图

select * from v$logmnr_dictionary;

select * from v$logmnr_logs;

select * from v$logmnr_contents;

使用源数据库的数据字典（Online catalog)来分析 DML 操作

--01.开启补充日志

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

alter database add supplemental log data;

--02.建立日志分析列表

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.new)

--继续添加

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.addfile)

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.addfile)

这样写也可以。

//execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile)

--移除

execute dbms_logmnr.remove_logfile(logfilename=>'日志文件')

--03.启动分析

execute  dbms_logmnr.start_logmnr(Options  =>dbms_logmnr.dict_from_online_catalog)

分析开始scn到结束scn的分析

execute  dbms_logmnr.start_logmnr(Options  =>dbms_logmnr.dict_from_online_catalog,startscn=>123,endScn => 124);

分析时间段之间的分析：

exec  dbms_logmnr.start_logmnr(Options  =>dbms_logmnr.dict_from_online_catalog,starttime  =>to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime  =>to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');

--04.查看日志分析结果-----（注意，这里一般创建中间表）

select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

--05.结束分析

dbms_logmnr.end_logmnr;

使用 LogMiner 字典到字典文件来分析 DDL 操作

--01.提取 logminer 字典

--设置一个字典文件路径：

说明：字典文件用于存放表及对象ID号之间的对应关系。从9i开始，字典信息既可被摘取到字段文件中，也可被摘取到重做日志中。摘取字典信息到字典文件方法如下：

show parameter utl_file_dir --需要重启 DB

alter system set utl_file_dir='/oracle' scope=spfile;

--创建一个数据字典文件

exec dbms_logmnr_d.build('dict.ora','/oracle');

--02.建立日志分析列表

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.new)

--继续添加

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.addfile)

execute  dbms_logmnr.add_logfile(logfilename=>' 日 志 文 件 ' ，options=>dbms_logmnr.addfile)

这样写也可以。

//execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile)

--移除

execute dbms_logmnr.remove_logfile(logfilename=>'日志文件')

--03.启动分析

exec dbms_logmnr.start_logmnr(DictFileName => '/oracle/dict.ora');---无条件

分析

exec  dbms_logmnr.start_logmnr(DictFileName  =>'/oracle/dict.ora',startscn=>123,endScn => 124); --有条件分析

exec dbms_logmnr.start_logmnr(DictFileName => '/oracle/dict.ora',starttime  =>to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime  =>to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');

--有条件分析：

scn: startscn,endScn time: starttime,endtime

--04.查看日志分析结果

select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

--05.结束分析

dbms_logmnr.end_logmnr;  

--2.5 查看 logminer 分析结果

select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents;

SQL> desc v$logmnr_contents;

名称 类型

----------------------------------------- ----------------------------

TIMESTAMP DATE               //SQL 执行时间

COMMIT_TIMESTAMP DATE        //事务提交时间

SEG_OWNER VARCHAR2(32)       //被修改对象创建者

SEG_NAME VARCHAR2(256)       //被修改对象的名字，如表名

SEG_TYPE NUMBER              //被修改对象类型

SEG_TYPE_NAME VARCHAR2(32)  //被修改对象类型名

TABLE_SPACE VARCHAR2(32)    //被修改对象所属表空间

ROW_ID VARCHAR2(19)         //被修改行的 ROWID，如果

SESSION# NUMBER             //执行修改的 SESSION 号

SERIAL# NUMBER             //执行修改的 SESSION 序号

USERNAME VARCHAR2(30)      //执行事务的用户名

SESSION_INFO VARCHAR2(4000) //执行 修 改 的 SESSION 信 息 , 例 如 ：login_username=  client_info=OS_username=SYSTEM Machine_name=ZFMISERVER OS_terminal=ZFMISERVER OS_process_id=1812 OS_program name=ORACLE.EXE

TX_NAME VARCHAR2(256)       //执行的事务名，当该事务被命名时

ROLLBACK NUMBER            //回滚标记

OPERATION VARCHAR2(32)    //操作类型

INSERT

UPDATE

DELETE

DDL

START

COMMIT

ROLLBACK

LOB_WRITE

LOB_TRIM

LOB_ERASE

SELECT_FOR_UPDATE

SEL_LOB_LOCATOR

MISSING_SCN

INTERNAL

UNSUPPORTED

OPERATION_CODE NUMBER //操作类型代码

0 = INTERNAL

1 = INSERT

2 = DELETE

3 = UPDATE

5 = DDL

6 = START

7 = COMMIT

9 = SELECT_LOB_LOCATOR

10 = LOB_WRITE

11 = LOB_TRIM

25 = SELECT_FOR_UPDATE

28 = LOB_ERASE

34 = MISSING_SCN

36 = ROLLBACK

255 = UNSUPPORTED

SQL_REDO VARCHAR2(4000)   //重做日志 SQL

SQL_UNDO VARCHAR2(4000)   //相反操作 SQL

SEQUENCE# NUMBER         //重做日志的序号