今天主要分享利用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 //重做日志的序号