LOGMNR工具
来源:互联网 发布:人体衣服设计软件 编辑:程序博客网 时间:2024/06/05 20:35
在oracle数据库,LOGWR进程将数据库中进行的DML等操作心理记录在日志文件中,在归档模式下,日志文件还会写出到归档日志文件中。
在数据库发生故障崩溃后,恢复时数据库可以根据日志信息还重演事务,完成恢复,从而保证成功提交的事务不丢失。
oracle数据库软件提供了一个LOGMNR工具,可以很容易实现对日志的解析,熟悉LOGMNR的使用在很多时候可以帮助我们分析数据库
问题,找出根本原因。
从oracle9i开始,LOGMNR的使用大大简化,可以使用LOGMNR在线分析和挖掘日志,使用当前在线的数据字典,非常方便。
首先执行一个 DDL(或 DML)操作,以记录重做信息:
SQL> connect hrr/hrr
Connected.
SQL> alter system switch logfile;
System altered.
SQL> create table eygle as select * from dba_users;
Table created.
SQL> select count(*) from hrr;
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_online_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","LINKNAME","FLAGS","OID$","SPARE1"
,"SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('25847','25847','31','EYGLE',
'1',NULL,'2',TO_DATE('01-JUL-09','DD-MON-RR'),TO_DATE('01-JUL-09','DD-MON-RR'),TO_DATE('0
1-JUL-09', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NULL,NULL,NULL,NULL);
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 可以帮助我们解决很多棘手的问题
在数据库发生故障崩溃后,恢复时数据库可以根据日志信息还重演事务,完成恢复,从而保证成功提交的事务不丢失。
oracle数据库软件提供了一个LOGMNR工具,可以很容易实现对日志的解析,熟悉LOGMNR的使用在很多时候可以帮助我们分析数据库
问题,找出根本原因。
从oracle9i开始,LOGMNR的使用大大简化,可以使用LOGMNR在线分析和挖掘日志,使用当前在线的数据字典,非常方便。
首先执行一个 DDL(或 DML)操作,以记录重做信息:
SQL> connect hrr/hrr
Connected.
SQL> alter system switch logfile;
System altered.
SQL> create table eygle as select * from dba_users;
Table created.
SQL> select count(*) from hrr;
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_online_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","LINKNAME","FLAGS","OID$","SPARE1"
,"SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('25847','25847','31','EYGLE',
'1',NULL,'2',TO_DATE('01-JUL-09','DD-MON-RR'),TO_DATE('01-JUL-09','DD-MON-RR'),TO_DATE('0
1-JUL-09', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1',NULL,NULL,NULL,NULL);
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 可以帮助我们解决很多棘手的问题
0 0
- LOGMNR工具
- Oracle的LOGMNR工具
- logmnr
- logmnr
- logmnr
- 通过logmnr工具实现数据恢复
- LOGMNR日志挖掘工具的使用
- ORACLE LOGMNR简单而强大的工具
- 使用oracle的logmnr分析工具
- LOGMNR简单而强大的工具
- Oracle的LOGMNR工具的使用及注意事项
- oracle学习之:使用logmnr工具挖掘日志
- logmnr使用方法
- LOGMNR使用
- Logmnr 介绍
- Oracle logmnr
- logmnr简介
- logmnr使用
- 第七章 复用类
- NC二次开发问题集锦
- jquery ajax后台执行成功却执行error回调函数
- 多实例学习
- 命名空间的使用和优缺点
- LOGMNR工具
- keepalived:记一次VRRP_Instance ignoring received advertisment错误
- fclose 陷入死循环
- thinkphp+Pjax Demo 实现无刷新改变URL的方式
- oracle 命令 笔记
- TPC_mcgs打印方案
- 《uCOS51移植心得》---七年前之《快快乐乐跟我学51单片机操作系统和IP栈》
- Spring中配置事务的几种方式
- BOOST_AUTO