LOGMNR简单而强大的工具
在Oracle数据库中,LGWR进程将数据库中进行的DML等操作信息记录在日志文件中,在归档模式下,日志文件还会写出到归档日志文件中。在数据库发生故障崩溃后,恢复时数据库可以根据日志信息来重演事务,完成恢复,从而保证成功提交的事务不丢失。
很多第三方工具以及Oracle的一些工作,都可以通过解析日志文件来实现数据复制和同步。Oracle随数据库软件提供了一个LOGMNR工具,可以很容易实现对于日志的解析,熟悉LOGMNR的使用在很多时候可以帮助我们分析数据库问题,找出根本原因。
从Oracle9i开始,LOGMNR的使用大大简化,可以使用LOGMNR在线分析和挖掘日志,使用当前在线的数据字典,非常方便,以下是一个简要的测试和说明。
首先执行一个ddl(或dml)操作,以记录重做信息:
- SQL> connect eygle/eygle
- Connected.
- SQL> alter system switch logfile;
- System altered.
- SQL> create table eygle as select * from dba_users;
- Table created.
- SQL> select count(*) from eygle;
- COUNT(*)
- ----------
- 19
然后可以执行LOGMNR解析工作:
- SQL> connect / as sysdba
- Connected.
- SQL> select * from v$log where status='CURRENT';
- GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC
- STATUS FIRST_CHANGE# FIRST_TIME
- ---------- ---------- ---------- ---------- ---------- ---
- ---------------- -------------
- 2 1 100 52428800 1 NO
- CURRENT 12729697 01-JUL-09
- SQL> SELECT MEMBER from v$logfile where group#=2;
- MEMBER
- ------------------------------------------------------------
- ----------------------------
- /opt/oracle/oradata/mmstest/redo02.log
- SQL>exec
- dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.
- log',dbms_logmnr.new);
- PL/SQL procedure successfully completed.
- SQL> exec
- dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_onli
- ne_catalog);
- PL/SQL procedure successfully completed.
- SQL> select count(*) from v$logmnr_contents;
- COUNT(*)
- ----------
- 136
解析之后,就可以通过V$LOGMNR_CONTENTS视图来查出数据库执行所有操作,以下是查询出来的SQL_REDO,通过这些SQL就可以重演CREATE TABLE的ddl操作,通过以下的重做信息也可以看到,ddl的后台操作实际上是转换为对字典表的一系列dml操作。
- SQL> select sql_redo from v$logmnr_contents;
- SQL_REDO
- -------------------------------------------------
- set transaction read write;
- insert into
- "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","
- SUBNAME",
- "TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINK
- NAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SP
- ARE5","SPARE6") values ('25847','25847','31','EYGLE',
- '1',NULL,'2',TO_DATE('01-JUL-09','DD-MON-RR'),TO_DATE('01-JU
- L-09','DD-MON-RR'),TO_DATE('01-JUL-09',
- 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NULL,NULL,NULL,N
- ULL);
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10823' where "CON#" =
- '10822' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10824' where "CON#" =
- '10823' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10825' where "CON#" =
- '10824' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10826' where "CON#" =
- '10825' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10827' where "CON#" =
- '10826' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10828' where "CON#" =
- '10827' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- set transaction read write;
- update "SYS"."CON$" set "CON#" = '10829' where "CON#" =
- '10828' and ROWID = 'AAAAAcAABAAAACqAAM';
- commit;
- create table eygle as select * from dba_users;
- set transaction read write;
查询完成之后,可以通过如下命令结束日志解析过程:
- SQL> exec dbms_logmnr.end_logmnr
- PL/SQL procedure successfully completed.
熟悉和熟练使用LOGMNR可以帮助我们解决很多棘手的问题,并加深对于数据库的理解。