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>