参考dave的博客
http://blog.csdn.net/tianlesoftware/archive/2011/06/19/6554674.aspx
LogMiner 提供了一个处理重做日志文件并将其内容翻译成代表对数据库的逻辑操作的SQL语句
的过程。LogMiner 运行在Oracle 版本8.1 或者更高版本中。
一,如何使用Logminer:
首先查询有没有安装logminer需要的包
select *
from dba_objects
where object_type='PACKAGE'
and object_name in('DBMS_LOGMNR','DBMS_LOGMNR_D');
如果没有要先安装logminer 的两个包;以SYS 用户运行下面两个脚本,其中第一个脚本
dbmslm.sql 用来创建DBMS_LOGMNR 包,该包用来分析日志文件。第二个脚本dbmslmd.sql 用来
创建DBMS_LOGMNR_D 包,该包用来创建数据字典文件
这两个包在($ORACLE_HOME/rdbms/admin下)
二,创建数据字典文件
数据字典文件是一个文本文件,使用包DBMS_LOGMNR_D 来创建,如果我们要分析的数据
库中的表有变化(比如表结构有变化等),影响到库的数据字典也发生变化。另外一种情况是在分
析另外一个数据库文件的重做日志时,也必须要重新生成一遍被分析数据库的数据字典文件。
首先需要修改参数UTL_FILE_DIR ,该参数值为服务器中放置数据字典文件的目录,我
们通过动态修改参数的方式来修改,然后重新启动数据库生效。其中logs_utl_file 目录先期建立
好。
SQL> alter system set UTL_FILE_DIR='D:/logmnr' scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1295608 bytes
Variable Size 125831944 bytes
Database Buffers 33554432 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter utl_file_dir;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string D:/logmnr
然后创建数据字典文件:
BEGIN
dbms_logmnr_d.build(
dictionary_filename => 'logmin_dict.ora',
dictionary_location => 'D:/logmnr');
END;
/
OK ,可以看到在logmnr中已经生成了logmin_dict.ora 数据字典文件。数据字典文件是可以
打开的文本文件。有兴趣可以打开看看。
三、补充日志(supplemental logging)
先看一下补充日志都包含哪些信息和特性:
(1)索引簇、链行和迁移行;
(2)直接路径插入;
(3)摘取LogMiner字典到重做日志;
(4)跟踪DDL;
(5)生成键列的SQL_REDO和SQL_UNDO信息;
(6)LONG和LOB数据类型。
这里我们重点看一下:track DDL 和 generate sql_redo and sql_undo.
Oracle 的online redo 会记录DB的所有操作,包括DDL 和 DML。 supplemental log支持track DDL。
也就是说,我们可以直接去Mining DML的内容。 但是如果要去Mining DDL 内容,
就必须先启动supplemental log,oracle 收集有关更多的DDL 信息之后,我们才可以去Mining它的信息。
因为我们可以根据归档和online redo 去恢复数据,所以这些DDL 的内容,即使不启动 supplemental log,
对与Oracle 内部来说肯定是可以识别的,只是我们不能Mining出来。 只有启动supplemental log之后,我们也就可以Mining出来了。
默认情况下Oracle 并没有启动supplemental log。因为记录太多的内容会增加写log的压力。
SQL_REDO 和 SQL_UNDO 是我们操作的SQL(DDL和DML)和用于回滚的SQL。 我们的恢复就是使用SQL_UNDO 来进行的。
在我们的DML 和DDL操作之前,需要先启动supplemental log。 不然生成的SQL_REDO 和 SQL_UNDO 是没有经过数据字典转换过的,
这样不具可读性。 都是Oracle 内部的ID。
启动supplemental log:
SQL>alter database add supplemental log data;
关闭supplemental log:
SQL>alter database drop supplemental log data;
查看 supplemental log:
SQL>select supplemental_log_data_min from v$database;
一般生产环境不会启动supplemental log。 所以用Logminer 方法来做数据恢复不是一种常用的方法。
对于DML 操作,还是有一定的可行性。 DDL 就不行。
而且在没有启动supplemental log的情况下,Mining出来的SQL_REDO和SQL_UNDO数据是没有进过数据字典进行转换的,可读性很差。如:
delete from "UNKNOWN"."OBJ# 54173"
where "COL 1" = HEXTORAW('53434f5454') and "COL 2" = HEXTORAW('504b5f44455054')
and "COL 3" IS NULL and "COL 4" = HEXTORAW('c3060c30') and "COL 5" = HEXTORAW('c3060c30')
and "COL 6" = HEXTORAW('494e444558') and "COL 7" = HEXTORAW('7869061e14303a')
and "COL 8" = HEXTORAW('7869061e14303a') and "COL 9" = HEXTORAW('323030352d30362d33303a31393a34373a3537')
and "COL 10" = HEXTORAW('56414c4944') and "COL 11" = HEXTORAW('4e') and "COL 12" = HEXTORAW('4e')
and "COL 13" = HEXTORAW('4e') and ROWID = 'AAANOdAABAAATEmAAc';
insert into "UNKNOWN"."OBJ# 54173"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6","COL 7","COL 8","COL 9","COL 10","COL 11","COL 12","COL 13") values (HEXTORAW('53434f5454'),HEXTORAW('504b5f44455054'),NULL,HEXTORAW('c3060c30'),HEXTORAW('c3060c30'),HEXTORAW('494e444558'),HEXTORAW('7869061e14303a'),HEXTORAW('7869061e14303a'),HEXTORAW('323030352d30362d33303a31393a34373a3537'),HEXTORAW('56414c4944'),HEXTORAW('4e'),HEXTORAW('4e'),HEXTORAW('4e'));
启动supplemental log:
SQL>alter database add supplemental log data;
查看 supplemental log(不需重启数据库)
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
--假如scott用户对自己的表做了些误操作。
SQL> conn scott/tiger@primary;
SQL> delete from emp where deptno=10;
已删除3行。
SQL> commit;
提交完成。
SQL> update emp set ename='aspen' where deptno=20;
已更新5行。
SQL> commit;
提交完成。
--利用sys对日志进行分析,(当然做这些误操作的日志一定要有,如果这期间日志归档了,也要把
相应的归档日志进行分析)
SQL> conn / as sysdba;
已连接。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'D:/oradata/primary/redo01.log',o
ptions=>dbms_logmnr.new);
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'D:/oradata/primary/redo02.log',o
ptions=>dbms_logmnr.addfile);
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'D:/oradata/primary/redo03.log',o
ptions=>dbms_logmnr.addfile);
PL/SQL 过程已成功完成。
添加归档日志的方法一样,只是logfilename参数的值不同而已.例如:
SQL> exec dbms_logmnr.add_logfile(logfilename=>'D:/app/263370/flash_recovery_are
a/PRIMARY/ARCHIVELOG/ARC00108_0753443662.001',options=>dbms_logmnr.addfile);
PL/SQL 过程已成功完成。
如果你觉得不需要分析已经在列表中的在线或归档日志,可以通过removefile 命令删除:
SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/app/263370/flash_recovery_are
a/PRIMARY/ARCHIVELOG/ARC00108_0753443662.001', options=>dbms_logmnr.removefile);
PL/SQL 过程已成功完成。
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'D:/logmnr/logmin_dict.ora');
PL/SQL 过程已成功完成。
--注意logminer分析出来的保存在v$logmnr_contents里的结果只对当前会话有效
--如果想要保存这些信息的话可以重新建立一个表,把这些信息保存到新表中
SQL> select sql_redo,sql_undo
2 from v$logmnr_contents
3 where seg_name='EMP' and seg_owner='SCOTT'
4 order by timestamp desc;
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "ENAME" = 'aspen' where "ENAME" = 'SMITH' and ROWID = '
AAAQ+jAAEAAAAAeAAA';
update "SCOTT"."EMP" set "ENAME" = 'SMITH' where "ENAME" = 'aspen' and ROWID = '
AAAQ+jAAEAAAAAeAAA';
update "SCOTT"."EMP" set "ENAME" = 'aspen' where "ENAME" = 'ADAMS' and ROWID = '
AAAQ+jAAEAAAAAeAAK';
update "SCOTT"."EMP" set "ENAME" = 'ADAMS' where "ENAME" = 'aspen' and ROWID = '
AAAQ+jAAEAAAAAeAAK';
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "ENAME" = 'aspen' where "ENAME" = 'SCOTT' and ROWID = '
AAAQ+jAAEAAAAAeAAH';
update "SCOTT"."EMP" set "ENAME" = 'SCOTT' where "ENAME" = 'aspen' and ROWID = '
AAAQ+jAAEAAAAAeAAH';
update "SCOTT"."EMP" set "ENAME" = 'aspen' where "ENAME" = 'FORD' and ROWID = 'A
AAQ+jAAEAAAAAeAAM';
update "SCOTT"."EMP" set "ENAME" = 'FORD' where "ENAME" = 'aspen' and ROWID = 'A
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
AAQ+jAAEAAAAAeAAM';
update "SCOTT"."EMP" set "ENAME" = 'aspen' where "ENAME" = 'JONES' and ROWID = '
AAAQ+jAAEAAAAAeAAD';
update "SCOTT"."EMP" set "ENAME" = 'JONES' where "ENAME" = 'aspen' and ROWID = '
AAAQ+jAAEAAAAAeAAD';
delete from "SCOTT"."EMP" where "EMPNO" = '7934' and "ENAME" = 'MILLER' and "JOB
" = 'CLERK' and "MGR" = '7782' and "HIREDATE" = TO_DATE('23-1月 -82', 'DD-MON-RR
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
') and "SAL" = '1300' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAQ+j
AAEAAAAAeAAN';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
EPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('23-1月 -82', 'DD-MON-RR'
),'1300',NULL,'10');
delete from "SCOTT"."EMP" where "EMPNO" = '7839' and "ENAME" = 'KING' and "JOB"
= 'PRESIDENT' and "MGR" IS NULL and "HIREDATE" = TO_DATE('17-11月-81', 'DD-MON-R
R') and "SAL" = '5000' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAQ+
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
jAAEAAAAAeAAI';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
EPTNO") values ('7839','KING','PRESIDENT',NULL,TO_DATE('17-11月-81', 'DD-MON-RR'
),'5000',NULL,'10');
delete from "SCOTT"."EMP" where "EMPNO" = '7782' and "ENAME" = 'CLARK' and "JOB"
= 'MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE('09-6月 -81', 'DD-MON-R
R') and "SAL" = '2450' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAAQ+
jAAEAAAAAeAAG';
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
EPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-6月 -81', 'DD-MON-RR
'),'2450',NULL,'10');
已选择8行。
通过sql_undo我们就可以恢复相应的数据了,order by timestamp desc;
这个很重要哦,我们的通过sql_undo得到的sql语句要按照时间从后往前执行
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL 过程已成功完成。