LogMiner学习笔记
本文是个实战，没有讲太多理论的东西，如需详细理解Oracle LogMiner，请移步：LogMiner详细讲解
首先介绍一下我的oracle环境：
第一步：
确定LogMiner已经安装。
安装LogMiner需要用SYS用户执行两个sql脚本：
1．$ORACLE_HOME/rdbms/admin/dbmslm.sql
2．$ORACLE_HOME/rdbms/admin/dbmslmd.sql
相应的，我的环境中执行的语句为：
1．@/u01/oracle/rdbms/admin/dbmslm.sql
2．@/u01/oracle/rdbms/admin/dbmslmd.sql
执行效果图：
第二步：
查看是否设置了初始化参数：UTL_FILE_DIR
执行语句：show parameterutl;
可以看到，我的日志分析目录已经指定为/u01/dataoracle/oracle/logminer
如果没有指定目录，可以修改数据库的initsid.ora文件（此文件在：$ORACLE_HOME/dbs/initSID.ora对应的，我本机的目录就是：/u01/oracle/dbs/
），或者可以使用如下命令进行修改：
alter system setutl_file_dir='/u01/dataoracle/oracle/logminer' scope=spifle;
重新启动数据库，是新加的参数生效。
注意:这个目录可以随意指定，但是这个目录必须存在，并且oracle用户拥有权限操作，否则会报错。
第三步：
首先执行一条insert语句，方便一会儿分析。执行用户是scott，执行时间是2013-06-29 11:46:35
第四步：
创建字典文件
执行语句：
exec sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location =>'/u01/dataoracle/oracle/logminer');
这里的dictionary.ora可以随便取。
第五步：
创建要分析的日志文件列表
我这里分析在线日志（online log）
首先你要知道当前正在使用的日志是哪个日志，查看试图v$logfile就能看到：
GROUP为1的就是当前在线日志。flas_recovery_area是闪回区，我们不用分析它。
1、创建列表：
EXECUTE dbms_logmnr.add_logfile(LogFileName=>'/u01/oradata/HDWKXT/onlinelog/o1_mf_1_8w30j2dn_.log',Options=>dbms_logmnr.new);
我的目录结构：
2、添加其他日志文件到日志列表：
我将三个日志都加入了分析，防止分析不到。
EXECUTE dbms_logmnr.add_logfile(LogFileName=>'/u01/oradata/HDWKXT/onlinelog/o1_mf_3_8w30jdyx_.log',Options=>dbms_logmnr.addfile); EXECUTE dbms_logmnr.add_logfile(LogFileName=>'/u01/oradata/HDWKXT/onlinelog/o1_mf_2_8w30j6lb_.log',Options=>dbms_logmnr.addfile);
………………………………离线日志文件分析：略…………………………………………
第六步：
使用LogMiner分析日志
执行语句：
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'/u01/dataoracle/oracle/logminer/dictionary.ora');
这里的dictionary.ora就是之前建立的数据字典。
上边是分析全部日志，数据量大的话，不方便分析，我们可以进行有限条件的分析
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'/u01/dataoracle/oracle/logminer/dictionary.ora',StartTime => to_date('2013-06-29 11:00:00','YYYY-MM-DD HH24:MI:SS'),EndTime=> to_date('2013-06-29 12:00:00','YYYY-MM-DDHH24:MI:SS '));
第七步：
查询V$logmnr_contents视图
格式调整：
col sql_redu for a50 col sql_undu for a50 col table_name for a10 set linesize 200 SELECT sql_redo,sql_undo,table_name,username FROM v$logmnr_contents WHERE table_name like '%TEST%';
这里就查到了建立表和插入表的语句。我查询了四个字段，其中最重要的就是SQL_REDO和SQL_UNDO, SQL_REDO是我们执行的语句，如果我们需要逆转这个数据，只要执行对应的SQL_UNDO里边的语句即可。
注意：如果你只查询到了DDL语句，而没有查到DML语句，请执行以下语句再进行分析
alter database add supplemental log data
第八步：
分析结束
execute dbms_logmnr.end_logmnr();
另附V$LOGMNR_CONTENTS的视图结构和使用
|
列名 |
数据类型 |
说明 |
|
SCN |
NUMBER |
系统更改号 |
|
CSCN |
NUMBER |
System change number (SCN) when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in aDBMS_LOGMNR.START_LOGMNR() invocation |
|
TIMESTAMP |
DATE |
时间戳 |
|
COMMIT_TIMESTAMP |
DATE |
Timestamp when the transaction committed; only meaningful if the COMMITTED_DATA_ONLY option was chosen in a DBMS_LOGMNR.START_LOGMNR()invocation |
|
THREAD# |
NUMBER |
线程号 |
|
LOG_ID |
NUMBER |
日志ID |
|
XIDUSN |
NUMBER |
事务处理ID撤消段号 |
|
XIDSLT |
NUMBER |
事务处理ID位置号 |
|
XIDSQN |
NUMBER |
事务处理ID日志序列号 |
|
PXIDUSN |
NUMBER |
Parent transaction ID undo segment number of a parallel transaction |
|
PXIDSLT |
NUMBER |
Parent transaction ID slot number of a parallel transaction |
|
PXIDSQN |
NUMBER |
Parent transaction ID sequence number of a parallel transaction |
|
RBASQN |
NUMBER |
RBA日志序列号 |
|
RBABLK |
NUMBER |
RBA块号 |
|
RBABYTE |
NUMBER |
RBA字节偏移量 |
|
UBAFIL |
NUMBER |
UBA文件号 |
|
UBABLK |
NUMBER |
UBA块号 |
|
UBAREC |
NUMBER |
UBA记录索引 |
|
UBASQN |
NUMBER |
UBA撤消块序列号 |
|
ABS_FILE# |
NUMBER |
数据块绝对文件号 |
|
REL_FILE# |
NUMBER |
数据块相对文件号 |
|
DATA_BLK# |
NUMBER |
数据块号 |
|
DATA_OBJ# |
NUMBER |
数据块对象号 |
|
DATA_OBJD# |
NUMBER |
数据块数据对象号 |
|
SEG_OWNER |
VARCHAR2(32) |
段拥有者 |
|
SEG_NAME |
VARCHAR2(256) |
段名 |
|
TABLE_NAME |
VARCHAR2(32) |
Name of the modified table (in case the redo pertains to a table modification) |
|
SEG_TYPE |
NUMBER |
段类型。可能的值有： · 0 = UNKNOWN · 1 = INDEX · 2 = TABLE · 19 = TABLE PARTITION · 20 = INDEX PARTITION · 34 = TABLE SUBPARTITION · All other values = UNSUPPORTED |
|
SEG_TYPE_NAME |
VARCHAR2(32) |
Segment type name. Possible values are: · UNKNOWN · INDEX · TABLE · TABLE PARTITION · UNSUPPORTED · TABLE_SPACE |
|
TABLE_SPACE |
VARCHAR2(32) |
段的表空间名 |
|
ROW_ID |
VARCHAR2(18) |
行ID |
|
SESSION# |
NUMBER |
会话号 |
|
SERIAL# |
NUMBER |
系列号 |
|
USERNAME |
VARCHAR2(30) |
用户名 |
|
SESSION_INFO |
VARCHAR2(4000) |
会话信息。可能的： · login_username = HR · client_info = · OS_username = jkundu · Machine_name = nirvan · OS_terminal = pts/31 · OS_program_name = sqlplus@nirvan (TNS V1-V3) |
|
TX_NAME |
VARCHAR2(256) |
Name of the transaction that made the change. This is only meaningful if the transaction is a named transaction. |
|
ROLLBACK |
NUMBER |
回退请求 |
|
OPERATION |
VARCHAR2(32) |
操作。可能的值： · INSERT = change was caused by an insert statement · UPDATE = change was caused by an update statement · DELETE = change was caused by a delete statement · DDL = change was caused by a DDL statement · START = change was caused by the start of a transaction · COMMIT = change was caused by the commit of a transaction · ROLLBACK = change was caused by a full rollback of a transaction · LOB_WRITE = change was caused by an invocation of DBMS_LOB.WRITE · LOB_TRIM = change was caused by an invocation of DBMS_LOB.TRIM · LOB_ERASE = change was caused by an invocation of DBMS_LOB.ERASE · SELECT_FOR_UPDATE = operation was a SELECT FOR UPDATE statement · SEL_LOB_LOCATOR = operation was a SELECT statement that returns a LOB locator · MISSING_SCN = LogMiner encountered a gap in the redo records. This is most likely because not all redo logs were registered with LogMiner. · INTERNAL = change was caused by internal operations initiated by the database · UNSUPPORTED = change was caused by operations not currently supported by LogMiner (for example, changes made to tables with ADT columns) |
|
OPERATION_CODE |
NUMBER |
Number of the operation code. Possible values are: · 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撤消 |
|
RS_ID |
VARCHAR2(32) |
记录集ID |
|
SEQUENCE# |
NUMBER |
序列号 |
|
SSN |
NUMBER |
SQL序列号 |
|
CSF |
NUMBER |
连续SQL标志。可能的值： · 0 = indicates SQL_REDO and SQL_UNDO is contained within the same row · 1 = indicates that either SQL_REDO or SQL_UNDO is greater than 4000 bytes in size and is continued in the next row returned by the view |
|
INFO |
VARCHAR2(32) |
通知信息 |
|
STATUS |
NUMBER |
状态 |
|
REDO_VALUE |
NUMBER |
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions |
|
UNDO_VALUE |
NUMBER |
Used as input to the DBMS_LOGMNR.MINE_VALUE() and DBMS_LOGMNR.COLUMN_PRESENT() functions |
|
SQL_COLUMN_TYPE |
VARCHAR2(30) |
This column is deprecated. |
|
SQL_COLUMN_NAME |
VARCHAR2(30) |
This column is deprecated. |
|
REDO_LENGTH |
NUMBER |
This column is deprecated. |
|
REDO_OFFSET |
NUMBER |
This column is deprecated. |
|
UNDO_LENGTH |
NUMBER |
This column is deprecated. |
|
UNDO_OFFSET |
NUMBER |
This column is deprecated. |
|
DATA_OBJV# |
NUMBER |
Version number of the table being modified |
|
SAFE_RESUME_SCN |
NUMBER |
Reserved for future use |
|
XID |
RAW(8) |
Raw representation of the transaction identifier |
|
PXID |
RAW(8) |
Raw representation of the parent transaction identifier |
|
AUDIT_SESSIONID |
NUMBER |
Audit session ID associated with the user session making the change |
