LogMiner Step by Step(一)

环境:
OS:Window XP+SP2
oracle 9.2.0.1.0 Enterprise Edition
SID:RMAN
ORACLE_BASE: G:\ oracle \
ORACLE_HOME : G:\ oracle \ora92

一．安装LogMiner
以SYS身份登陆，执行如下脚本:
SQL>@%ORACLE_HOME%\rdbms\admin\dbmslm.sql
Package created.
Grant succeeded.
SQL>@%ORACLE_HOME%\rdbms\admin\dbmslmd.sql
Procedure created.
No errors.
Grant succeeded.
PL/SQL procedure successfully completed.
Package created.
注：第一个脚本是创建DBMS_Logmnr包的，用来分析日志文件，第二个脚本用来创建DBMS_Logmnr_D包，该包用来创建数据字典。
二．使用LogMiner
1.创建数据字典
创建数据字典的目的是让LogMner引用涉及到内部数据字典中的部分时为他们实际的名字，而不是系统内部的16进制。
1.1设定初始化参数
以SYS帐号登陆，执行如下命令：
SQL>alter system set utl_file_dir=’f:\bright\logs’ scope=spfile;
System altered.
SQL>shutdown immediate
Database closed.
Database dismounted.
oracle instance shut down
SQL>startup
oracle instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
1.2执行脚本，创建数据库字典文件
SQL> execute dbms_logmnr_d.build(dictionary_filename=>’v92.ora’,dictionary_location=>’f:\bright\logs’)
PL/SQL procedure successfully completed.
2.创建要分析的日志文件列表
2.1创建在线重做日志文件列表
A.创建列表
SQL>execute dbms_logmnr.add_logfile(LogFileName=>’g:\ oracle \oradata\rman\redo01.log’,Options=>dbms_logmnr.new)
PL/SQL procedure successfully completed.
B.添加其他日志文件到列表
SQL>execute dbms_logmnr.add_logfile(LogFileName=>’g:\ oracle \oradata\rman\redo02.log’,Options=>dbms_logmnr.addfile)
PL/SQL procedure successfully completed.
2.2创建离线重做日志文件列表
A.创建列表
SQL> execute dbms_logmnr.add_logfile(LogFileName=>’f:\bright\logs\ARC00015.001′,Options=>dbms_logmnr.new)
PL/SQL procedure successfully completed.
B.添加其他日志文件到列表
SQL>execute dbms_logmnr.add_logfile(LogFileName=>’f:\bright\logs\ARC00016.001′,Options=>dbms_logmnr.addfile)
PL/SQL procedure successfully completed.
3.移除文件列表
SQL>execute dbms_logmnr.add_logfile(LogFileName=>’f:\bright\logs\ARC00016.001’,Options=>dbms_logmnr.removefile)
PL/SQL procedure successfully completed.
4.分析日志
4.1无限制条件
SQL>execute dbms_logmnr.start_logmnr(DictFileName=>’f:\bright\logs\v92.ora’)
4.2有条件限制
A.根据时间范围分析
SQL>execute dbms_logmnr.start_logmnr(DictFileName=>’f:\bright\logs\v92.ora’,
startTime=>‘2004-12-20 16:10:40’,
endTime=>‘2004-12-20 20:00:00’)
B.根据SCN范围分析
SQL>execute dbms_logmnr.start_logmnr(
DictFileName=>’f:\bright\logs\v92.ora’,
StartSCN=>704275,
EndSCN=>739509)
4.3查看结果
SQL>SELECT SQL_REDO FROM V$LOGMNR_CONTENTS;
SQL>SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE Operation=’DELETE’ AND SEG_NAME=’EMP’;
SQL>select sql_redo from v$logmnr_contents where
operation = ‘UPDATE” and
owner_name = ‘SCOTT’ and seg_name = ‘EMP’ and
dbms_logmnr.mine_value(redo_value, ‘SCOTT.EMP.SAL’) >
2* dbms_logmnr.mine_value(undo_value, ‘SCOTT.EMP.SAL’);
5．结束日志分析
SQL>execute dbms_logmnr.end_logmnr()