Oracle 10g LOGMNR挖掘日志很方便
作者:
eygle |
English Version 【转载时请以超链接形式标明文章
出处和作者信息及
本声明】
链接: http://www.eygle.com/archives/2009/07/oracle10g_logmnr_easyuse.html
Oracle 10g可以使用LOGMNR在线分析和挖掘日志,使用当前在线的数据字典,非常方便。
链接: http://www.eygle.com/archives/2009/07/oracle10g_logmnr_easyuse.html
首先执行一些DDL或DML操作:
SQL> connect eygle/eygle然后可以执行LOGMNR解析工作:
Connected.
SQL> alter system switch logfile;
System altered.
SQL> create table eygle as select * from dba_users;
Table created.
SQL> set autotrace on
SQL> select count(*) from eygle;
COUNT(*)
----------
19
Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 19 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
5 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> connect / as sysdba很多时候拿LOGMNR来追踪一些误操作是很有效的方式,甚至在自己定制的数据同步中,LOGMNR也大有可为。
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
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('01-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;
SQL_REDO
------------------------------------------------------------------------------------------------------------------------
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;
Unsupported
update "SYS"."TSQ$" set "TS#" = '0', "GRANTOR#" = '43080', "BLOCKS" = '0', "MAXBLOCKS" = '0', "PRIV1" = '0', "PRIV2" = '
0' where "TS#" = '0' and "GRANTOR#" = '43072' and "BLOCKS" = '0' and "MAXBLOCKS" = '0' and "PRIV1" = '0' and "PRIV2" = '
0' and ROWID = 'AAAAAKAABAAAABbAAF';
commit;
set transaction read write;
SQL> exec dbms_logmnr.end_logmnr
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16856446/viewspace-659930/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16856446/viewspace-659930/