首页 » ORACLE 9i-23c » oracle 10g logminer笔记(三)

oracle 10g logminer笔记(三)

logminer实战篇

1，session 1 ，建立几个对象，并做更新
2，session 2, 进行logmnr分析
3, session 3,等session2分析出来后,能不能查看他的结果?
4，先不启用supplemental log,进行分析
5, 用其中两种数据字典分析online data dictionary、flat file dictionary
6， 启用supplemental log,进行分析

———————–session 1———————-

SQL> conn zhang/zhang;
Connected.

SQL> create table tlogmnr(id int,name varchar2(20),lastmdf date);

Table created.

SQL> alter table tlogmnr modify lastmdf default sysdate;

Table altered.

SQL> insert into tlogmnr values(1,'anbob.com',sysdate);

1 row created.

SQL> insert into tlogmnr values(2,'weejar.com',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> update tlogmnr set id=3 where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete tlogmnr where id=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> create sequence logseq ;

Sequence created.

SQL> select logseq.nextval from dual;

NEXTVAL
----------
1

SQL> create or replace procedure p_insert_tlog(p_name varchar2)
2 is
3 begin
4 insert into tlogmnr(id,name) values(logseq.nextval,p_name);
5 commit;
6* end;

Procedure created.

SQL> exec p_insert_tlog('sesebook.com');

PL/SQL procedure successfully completed.

SQL>

—————————————————session 2——————————–

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 26 22:20:29 2011

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 150
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 29 10485760 1 NO INACTIVE 597605 25-4月 -11
2 1 30 10485760 1 NO INACTIVE 605227 25-4月 -11
3 1 31 10485760 1 NO CURRENT 625392 26-4月 -11

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col member for a80
SQL> run
1* select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG NO
2 STALE ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG NO
1 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG NO

SQL> execute dbms_logmnr.add_logfile(LOGFILENAME=>'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM V$LOGMNR_LOGS;

LOG_ID
----------
FILENAME
------------------------------------------------------------------------------------------------------------------------------------------------------
LOW_TIME HIGH_TIME DB_ID DB_NAME RESET_SCN RESET_SCN_TIME THREAD_ID THREAD_SQN LOW_SCN NEXT_SCN DIC DIC TYPE
------------------- ------------------- ---------- -------- ---------- ------------------- ---------- ---------- ---------- ---------- --- --- -------
BLOCKSIZE FILESIZE INFO STATUS
---------- ---------- -------------------------------- ----------
31
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
2011-04-26 22:01:46 1988-01-01 00:00:00 1275624653 ORCL 318842 2011-04-07 14:25:18 1 31 625392 2.8147E+14 NO NO ONLINE
512 0 0

SQL> EXECUTE DBMS_LOGMNR.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
6400

SQL> select seg_owner,seg_name,username,sql_redo,sql_undo from v$logmnr_contents where seg_owner='ZHANG';

SEG_OWNER SEG_NAME USERNAME SQL_REDO SQL_UNDO
---------- -------------------- -------- -------------------------------------------------------------------------------- -------
ZHANG TLOGMNR create table tlogmnr(id int,name varchar2(20),lastmdf date);
ZHANG TLOGMNR alter table tlogmnr modify lastmdf default sysdate;
ZHANG LOGSEQ create sequence logseq ;
ZHANG P_INSERT_TLOG create or replace procedure p_insert_tlog(p_name varchar2)
is
begin
insert into tlogmnr(id,name) values(logseq.nextval,p_name);
commit;
end;;

—————————————session 3—————————–

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 4月 26 22:44:16 2011

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from v$logmnr_contents;
select count(*) from v$logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from
v$logmnr_contents

–note: logmnr分析结果是存放在pga内存中的，其它session 是无法查看的

–上面没有看到dml修改只有ddl,下面启动独立的flat文件的数据字典如果没有显示为16进制，实验没做

—————-session 2—————————

SQL> alter system set utl_file_dir='D:\oracle\product' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> show parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string D:\oracle\product
SQL> exec dbms_logmnr_d.build('dictionary',-
> 'd:\oracle\product',-
> options=>dbms_logmnr_d.store_in_flat_file);
BEGIN dbms_logmnr_d.build('dictionary', 'd:\oracle\product', options=>dbms_logmnr_d.store_in_flat_file); END;

*
ERROR at line 1:
ORA-06550: line 1, column 71:
PLS-00201: identifier 'DBMS_LOGMNR_D.STORE_IN_FLAT_FILE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> alter database open
2 ;

Database altered.

SQL> exec dbms_logmnr_d.build('dictionary',-
> 'd:\oracle\product',-
> options=>dbms_logmnr_d.store_in_flat_file);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\product\dictionary')

PL/SQL procedure successfully completed.

SQL> select seg_owner,seg_name,username,sql_redo,sql_undo from v$logmnr_contents where sql_redo like 'update tlogmnr%'
2 ;

no rows selected

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

-------------------------------session 1--------------------------

SQL> conn zhang/zhang
Connected.
SQL> insert into tlogmnr values(6,'itpub.net',

1 row created.

SQL> commit;

Commit complete.

SQL> update tlogmnr set id=7 where id=6;

1 row updated.

SQL> commit;

Commit complete.

SQL> exec p_insert_tlog('oracle.com');

PL/SQL procedure successfully completed.

————————————session 2—————————–

SQL> delete zhang.tlogmnr where id=7;

1 row deleted.

SQL> commit;

Commit complete.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'d:\oracle\product\dictionary');

PL/SQL procedure successfully completed.

SQL> set pagesize 1000
SQL> run
1* select seg_owner,seg_name,username,sql_redo,sql_undo from v$logmnr_contents where seg_owner='ZHANG'

SEG_OWNER SEG_NAME USERNAME SQL_REDO SQL_UNDO
---------- -------------------- -------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------
--
ZHANG TLOGMNR create table tlogmnr(id int,name varchar2(20),lastmdf date);
ZHANG TLOGMNR alter table tlogmnr modify lastmdf default sysdate;
ZHANG LOGSEQ create sequence logseq ;
ZHANG P_INSERT_TLOG create or replace procedure p_insert_tlog(p_name varchar2)
is
begin
insert into tlogmnr(id,name) values(logseq.nextval,p_name);
commit;
end;;

ZHANG TLOGMNR ZHANG insert into "ZHANG"."TLOGMNR"("ID","NAME","LASTMDF") values ('6','itpub.net',TO_ delete from "ZHANG"."TLOGMNR" where "ID" = '6' and "NAME" = 'itpub.net' and "L
AS
DATE('26-4月 -11', 'DD-MON-RR')); TMDF" = TO_DATE('26-4月 -11', 'DD-MON-RR') and ROWID = 'AAAMXeAAEAAAAGPAAD';

ZHANG TLOGMNR ZHANG update "ZHANG"."TLOGMNR" set "ID" = '7' where "ID" = '6' and ROWID = 'AAAMXeAAEA update "ZHANG"."TLOGMNR" set "ID" = '6' where "ID" = '7' and ROWID = 'AAAMXeAA
EA
AAAGPAAD'; AAAGPAAD';

ZHANG TLOGMNR ZHANG insert into "ZHANG"."TLOGMNR"("ID","NAME","LASTMDF") values ('3','oracle.com',TO delete from "ZHANG"."TLOGMNR" where "ID" = '3' and "NAME" = 'oracle.com' and "
LA
_DATE('26-4月 -11', 'DD-MON-RR')); STMDF" = TO_DATE('26-4月 -11', 'DD-MON-RR') and ROWID = 'AAAMXeAAEAAAAGPAAB';

ZHANG TLOGMNR delete from "ZHANG"."TLOGMNR" where "ID" = '7' and "NAME" = 'itpub.net' and "LAS insert into "ZHANG"."TLOGMNR"("ID","NAME","LASTMDF") values ('7','itpub.net',T
O_
TMDF" = TO_DATE('26-4月 -11', 'DD-MON-RR') and ROWID = 'AAAMXeAAEAAAAGPAAD'; DATE('26-4月 -11', 'DD-MON-RR'));

8 rows selected.

SQL>

常见问题
1 ， 如果想要v$logmnr_contents 增加IP 信息,需要增加logon trigger

CREATE OR REPLACE TRIGGER on_logon_trigger
  AFTER logon ON DATABASE
BEGIN
  dbms_application_info.set_client_info(sys_context('userenv',
                                                    'ip_address'));
END;

2， 如果v$logmnr_contents USERNAME或session_info 为”UNKNOW”

If supplemental logging was not active at the time when the redo records were created, then LogMiner won’t be able to obtain all the required information.

we have to enable supplemental logging by using a SQL statement similar to the following:

SQL> CONNECT / AS SYSDBA
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Then the information necessary to populate the USERNAME and SESSION_INFO columns will be stored in the redo stream.
The redo stream does not contain the USERNAME and SESSION_INFO data for every transaction. This information is only stored for the first transaction executed in the user’s session. So in order to be able to see this information in V$LOGMNR_CONTENTS, all the redo generated during the entire session must be added to the mining session. Should this not be done, then the USERNAME and SESSION_INFO columns will remain empty.

打赏

, ,

目前这篇文章有1条评论(Rss)评论关闭。

  1. Nena Chrostowski | #1
    2011-12-21 at 06:08

    Thanks a lot for sharing this with all of us you actually know what you are talking about! Bookmarked. Kindly also visit my web site =). We could have a link exchange arrangement between us!

上一篇：

下一篇：