首页 » ORACLE » 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条评论(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!