LOGMNR简单而强大的工具

在ORACLE数据库中，LGWR进程将数据库中进行的DML等操作信息记录在日志文件中，在归档模式下，日志文件还会写入到归档日志文件中。在数据库发生故障崩溃后，恢复时数据库可以根据日志信息来重演事务，完成恢复，从而保证成功提交的事务不丢失。
很多第三方工具以及Oracle的一些工作，都可以通过解析日志文件来实现数据复制和同步。Oracle随数据库软件提供了一个LOGMNR工具，可以很容易实现对于日志的解析，熟悉LOGMNR的使用在很多时候可以帮助我们分析数据库问题，找出根本原因。

从Oracle9i开始，LOGMNR的使用大大简化，可以使用LOGMNR在线分析和挖掘日志，使用当前在线的数据字典，非常方便。在10g，我们需要激活补充日志。

alter database drop supplemental log data(primary key,unique index)columns;
alter database add supplemental log data;

在11g 的11.2.0.3版本中，可以不用再激活补充日志了。这个真的比10g的要好很多。下面是在11g下的一个简单测试。
这个测试场景也可以应对表误删除数据的恢复。

测试版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

补充日志没有激活

SQL> select name,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk from v$database;

NAME      SUP SUP SUP
--------- --- --- ---
CSDB      NO  NO  NO

检查当前日志文件

SQL> select * from v$log where status='CURRENT';

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME              NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------------------- ------------
NEXT_TIME
-----------------------
         2          1         68   52428800        512          1 NO  CURRENT                1477707 29-APR-2014 08:59:20      2.8147E+14


         4          2         10   52428800        512          1 NO  CURRENT                1385229 28-APR-2014 16:47:19      2.8147E+14
28-APR-2014 16:47:19


SQL> select member from v$logfile where group#=2;

MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA1/csdb/redo02.log

将redo日志文件添加到logmnr分析日志列表

SQL> exec dbms_logmnr.add_logfile(logfilename=>'+DATA1/csdb/redo02.log',options=>sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

开始日志分析

SQL> exec dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

从v$logmnr_contents视图中检索所需要的信息，标红的语句正式上一篇中《通过闪回恢复误删除数据》中实验过程中添加的语句和删除语句。有了这些信息，我们同样可以恢复误删除的数据。

SQL> select sql_redo from v$logmnr_contents where sql_redo like '%T1%';

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FL
AGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('72782','72782','64','T1','1',NULL,'2',TO_DATE('29-APR-2014 09:06:06', 'DD-
MON-YYYY HH24:MI:SS'),TO_DATE('29-APR-2014 09:06:06', 'DD-MON-YYYY HH24:MI:SS'),TO_DATE('29-APR-2014 09:06:06', 'DD-MON-YYYY HH24:MI:SS'),'1',NULL,NUL
L,'0',NULL,'6','1','64',NULL,NULL,NULL);

insert into "LIUTY"."T1"("ID","UNAME") values ('1','zhangsan');
insert into "LIUTY"."T1"("ID","UNAME") values ('3','wangwu');
insert into "LIUTY"."T1"("ID","UNAME") values ('4','xiaoqiang');
delete from "LIUTY"."T1" where "ID" = '1' and "UNAME" = 'zhangsan' and ROWID = 'AAARxOAAGAAAACFAAA';
delete from "LIUTY"."T1" where "ID" = '3' and "UNAME" = 'wangwu' and ROWID = 'AAARxOAAGAAAACFAAB';
delete from "LIUTY"."T1" where "ID" = '4' and "UNAME" = 'xiaoqiang' and ROWID = 'AAARxOAAGAAAACFAAC';

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FL
AGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('72783','72783','64','T1_RECOVER','1',NULL,'2',TO_DATE('29-APR-2014 09:18:5
2', 'DD-MON-YYYY HH24:MI:SS'),TO_DATE('29-APR-2014 09:18:52', 'DD-MON-YYYY HH24:MI:SS'),TO_DATE('29-APR-2014 09:18:52', 'DD-MON-YYYY HH24:MI:SS'),'1',
NULL,NULL,'0',NULL,'6','1','64',NULL,NULL,NULL);

insert into "LIUTY"."T1_RECOVER"("ID","UNAME") values ('1','zhangsan');
insert into "LIUTY"."T1_RECOVER"("ID","UNAME") values ('3','wangwu');
insert into "LIUTY"."T1_RECOVER"("ID","UNAME") values ('4','xiaoqiang');
insert into "SYS"."WRH$_SQL_PLAN"("SNAP_ID","DBID","SQL_ID","PLAN_HASH_VALUE","ID","OPERATION","OPTIONS","OBJECT_NODE","OBJECT#","OBJECT_OWNER","OBJEC
T_NAME","OBJECT_ALIAS","OBJECT_TYPE","OPTIMIZER","PARENT_ID","DEPTH","POSITION","SEARCH_COLUMNS","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_S
TART","PARTITION_STOP","PARTITION_ID","OTHER","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","T

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------
IME","QBLOCK_NAME","REMARKS","TIMESTAMP","OTHER_XML") values ('187','3068580827','0jmbx24s37wvu','3617692013','1','LOAD TABLE CONVENTIONAL',NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,'0','1','1','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SEL$1',NULL,TO_DATE('29
-APR-2014 09:19:23', 'DD-MON-YYYY HH24:MI:SS'),'<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["LIUTY"]]></info>
<info type="plan_hash">3617692013</info><info type="plan_hash_2">3688435342</info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><h
int><![CDATA[OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')]]></hint><hint><![CDATA[DB_VERSION(''11.2.0.3'')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint>
<![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"INS$1")]]></hint><hint><![CDATA[FULL(@"INS$1" "T1_RECOVER"@"INS$1")]]></hint><h
int><![CDATA[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></other_xml>');

insert into "SYS"."WRH$_SQL_PLAN"("SNAP_ID","DBID","SQL_ID","PLAN_HASH_VALUE","ID","OPERATION","OPTIONS","OBJECT_NODE","OBJECT#","OBJECT_OWNER","OBJEC
T_NAME","OBJECT_ALIAS","OBJECT_TYPE","OPTIMIZER","PARENT_ID","DEPTH","POSITION","SEARCH_COLUMNS","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_S
TART","PARTITION_STOP","PARTITION_ID","OTHER","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","T

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------
IME","QBLOCK_NAME","REMARKS","TIMESTAMP","OTHER_XML") values ('187','3068580827','0jmbx24s37wvu','3617692013','2','TABLE ACCESS','FULL',NULL,'72782','
LIUTY','T1','T1@SEL$1','TABLE',NULL,'1','2','1','0','2','82','2050',NULL,NULL,NULL,NULL,NULL,NULL,'21061','2',NULL,NULL,NULL,NULL,'1','SEL$1',NULL,TO_
DATE('29-APR-2014 09:19:23', 'DD-MON-YYYY HH24:MI:SS'),NULL);

insert into "SYS"."WRH$_SQL_PLAN"("SNAP_ID","DBID","SQL_ID","PLAN_HASH_VALUE","ID","OPERATION","OPTIONS","OBJECT_NODE","OBJECT#","OBJECT_OWNER","OBJEC
T_NAME","OBJECT_ALIAS","OBJECT_TYPE","OPTIMIZER","PARENT_ID","DEPTH","POSITION","SEARCH_COLUMNS","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_S
TART","PARTITION_STOP","PARTITION_ID","OTHER","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","T
IME","QBLOCK_NAME","REMARKS","TIMESTAMP","OTHER_XML") values ('187','3068580827','btzjwg8gvzxrm','3354408166','1','LOAD AS SELECT',NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,'0','1','1','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'SEL$1',NULL,TO_DATE('29-APR-2014
 09:18:52', 'DD-MON-YYYY HH24:MI:SS'),'<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["LIUTY"]]></info><info typ
e="dynamic_sampling">2</info><info type="plan_hash">3354408166</info><info type="plan_hash_2">1475130108</info><outline_data><hint><![CDATA[IGNORE_OPT

SQL_REDO
------------------------------------------------------------------------------------------------------------------------------------------------------
IM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')]]></hint><hint><![CDATA[DB_VERSION(''11.2.0.3'')]]></hint><hint><![C
DATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></other_xml>'
);

insert into "SYS"."WRH$_SQL_PLAN"("SNAP_ID","DBID","SQL_ID","PLAN_HASH_VALUE","ID","OPERATION","OPTIONS","OBJECT_NODE","OBJECT#","OBJECT_OWNER","OBJEC
T_NAME","OBJECT_ALIAS","OBJECT_TYPE","OPTIMIZER","PARENT_ID","DEPTH","POSITION","SEARCH_COLUMNS","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_S
TART","PARTITION_STOP","PARTITION_ID","OTHER","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","T
IME","QBLOCK_NAME","REMARKS","TIMESTAMP","OTHER_XML") values ('187','3068580827','btzjwg8gvzxrm','3354408166','3','TABLE ACCESS','FULL',NULL,'72782','
LIUTY','T1','T1@SEL$1','TABLE',NULL,'2','3','1','0','3','1','25',NULL,NULL,NULL,NULL,NULL,NULL,'35607','3',NULL,NULL,NULL,NULL,'1','SEL$1',NULL,TO_DAT
E('29-APR-2014 09:18:52', 'DD-MON-YYYY HH24:MI:SS'),NULL);


15 rows selected.

停止日志分析

SQL> exec dbms_logmnr.end_logmnr

PL/SQL procedure successfully completed.

熟悉和熟练使用LOGMNR可以帮助我们解决很多棘手问题，并加深对数据库的理解。