« 恩墨科技为ChinaCache提供紧急救援服务 | Blog首页 | Granule 与 Redo Log Buffer (log_buffer) 的关系 »
Oracle 10g LOGMNR挖掘日志很方便
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2009/07/oracle10g_logmnr_easyuse.html
Oracle 10g可以使用LOGMNR在线分析和挖掘日志,使用当前在线的数据字典,非常方便。链接:https://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.
历史上的今天...
>> 2013-07-01文章:
>> 2012-07-01文章:
>> 2010-07-01文章:
>> 2008-07-01文章:
>> 2007-07-01文章:
By eygle on 2009-07-01 11:09 | Comments (2) | FAQ | 2325 |
呵呵,我就用LOGMNR做数据同步用过。
hi,我发现10g中,同一事务的v$logmnr_contents的scn总是一样的,而在9i中,同一SQL产生的scn才相同,会不会是10g的bug?