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

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 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         //重做日志的序号

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
2月前
|
SQL 运维 Oracle
【迁移秘籍揭晓】ADB如何助你一臂之力,轻松玩转Oracle至ADB的数据大转移?
【8月更文挑战第27天】ADB(Autonomous Database)是由甲骨文公司推出的自动化的数据库服务,它极大简化了数据库的运维工作。在从传统Oracle数据库升级至ADB的过程中,数据迁移至关重要。
44 0
|
22小时前
|
Oracle 关系型数据库 数据库
oracle数据创建同义词
oracle数据创建同义词
|
2月前
|
SQL Oracle 关系型数据库
Oracle误删数据怎么恢复?
Oracle误删数据怎么恢复?
32 0
|
2月前
|
SQL Oracle Java
实时计算 Flink版产品使用问题之采集Oracle数据时,为什么无法采集到其他TABLESPACE的表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
存储 自然语言处理 Oracle
Oracle数据库字符集概述及修改方式
【8月更文挑战第15天】Oracle 数据库字符集定义了数据的编码方案,决定可存储的字符类型及其表示方式。主要作用包括数据存储、检索及跨系统传输时的正确表示。常见字符集如 AL32UTF8 支持多语言,而 WE8MSWIN1252 主用于西欧语言。修改字符集风险高,可能导致数据问题,需事先备份并评估兼容性。可通过 ALTER DATABASE 语句直接修改或采用导出-导入数据的方式进行。完成后应验证数据完整性。此操作复杂,须谨慎处理。
|
2月前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用问题之怎么实现从Oracle数据库读取多个表并将数据写入到Iceberg表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
25天前
|
Oracle 关系型数据库 数据库
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
打开oracle数据库报错“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。 数据库没有备份,无法通过备份去恢复数据库。用户方联系北亚企安数据恢复中心并提供Oracle_Home目录中的所有文件,急需恢复zxfg用户下的数据。 出现“system01.dbf需要更多的恢复来保持一致性”这个报错的原因可能是控制文件损坏、数据文件损坏,数据文件与控制文件的SCN不一致等。数据库恢复工程师对数据库文件进一步检测、分析后,发现sysaux01.dbf文件损坏,有坏块。 修复并启动数据库后仍然有许多查询报错,export和data pump工具使用报错。从数据库层面无法修复数据库。
数据库数据恢复—Oracle数据库文件出现坏块的数据恢复案例
|
13天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—异常断电导致Oracle数据库数据丢失的数据恢复案例
Oracle数据库故障: 机房异常断电后,Oracle数据库启库报错:“system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。数据库没有备份,归档日志不连续。用户方提供了Oracle数据库的在线文件,需要恢复zxfg用户的数据。 Oracle数据库恢复方案: 检测数据库故障;尝试挂起并修复数据库;解析数据文件。
|
1月前
|
Oracle 安全 关系型数据库
Oracle数据恢复—Oracle数据库误删除的数据恢复方法探讨
删除Oracle数据库数据一般有以下2种方式:delete、drop或truncate。下面针对这2种删除oracle数据库数据的方式探讨一下oracle数据库数据恢复方法(不考虑全库备份和利用归档日志)。
|
1月前
|
存储 Oracle 关系型数据库
Oracle同一台服务器创建多个数据库
【8月更文挑战第30天】在 Oracle 中,可在同一服务器上创建多个数据库。首先确保已安装 Oracle 软件并具有足够资源,然后使用 DBCA 工具按步骤创建,包括选择模板、配置存储及字符集等。重复此过程可创建多个数据库,需确保名称、SID 和存储位置唯一。创建后,可通过 Oracle Enterprise Manager 进行管理,注意服务器资源分配与规划。
45 10

热门文章

最新文章

推荐镜像

更多