本文是个实战,没有讲太多理论的东西,如需详细理解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 |
转载于:https://blog.51cto.com/xiaoliutolaoliu/1235898