logminer的教程网上已经屡见不鲜，操作也都雷同，同时用到时有几点需要注意的地方，需要注意一下。
1，附加最小日志 supplemental_log_data_min，10G默认是没有打开的，所以在开始分析时会发现分析出的结果只有DDL记录，没有DML记录，启用操作执行ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ；启用一后会带来一定影响下篇日志中解释。禁用supplemental_log_data_min,执行alter database drop supplemental log data;
确认SELECT name, supplemental_log_data_min FROM v$database;
2，数据字典dictionary,logminer可以有多个数据字典，
分为三种：
A分析库上的online data dictionary(不用建数据字典),
B是独立的数据字典文件（flat file），可以打开直接查看
C把数据字典集成在redo log中
B和C要在源数据库通过dbms_logmnr_d来创建，可以分析其它数据库的redo;A查看分析的结果中用到内部对象logminer以16进制字符显示无法生成可执行sql，这也是为什么要创建数据字典的原因,并且它只能分析本地数据库的redo log;
3,分析其它数据库的REDO里对字符集、OS、硬件环境要求也非常严格
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
启动 最小附加日志才可以LOGMNR中看到DML，在做DG中也会提到这个设置，这个参数在ORACLE 9I的9.1版本是个界限，以前默认打开，新版本默认关闭。但SUPPLEMENTAL LOG会给系统带来什么影响呢？redo 增长速度会加快么？会
官方解释：
ou must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information
in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
Redo log files are generally used for instance recovery and media recovery. The data
needed for such operations is automatically recorded in the redo log files. However, a
redo-based application may require that additional columns be logged in the redo log
files. The process of logging these additional columns is called supplemental logging.
By default, Oracle Database does not provide any supplemental logging, which means
that by default LogMiner is not usable. Therefore, you must enable at least minimal
supplemental logging prior to generating log files which will be analyzed by
LogMiner.
There are two types of database-level supplemental logging: minimal supplemental
logging and identification key logging, as described in the following sections. Minimal
supplemental logging does not impose significant overhead on the database
generating the redo log files. However, enabling database-wide identification key
logging can impose overhead on the database generating the redo log files. Oracle
recommends that you at least enable minimal supplemental logging for LogMiner.
Minimal Supplemental Logging
Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Note:
In Oracle Database release 9.0.1, minimal supplemental logging was the default behavior. in LogMiner. In release 9.2 and later, the default is no supplemental logging. Supplemental logging must be specifically enabled.
Database-Level Identification Key Logging
Identification key logging is necessary when redo log files will not be mined at the source database instance, for example, when the redo log files will be mined at a logical standby database.
Using database identification key logging, you can enable database-wide before-image logging for all updates by specifying one or more of the following options to the SQL ALTER DATABASE ADD SUPPLEMENTAL LOG statement:
ALL system-generated uncondititional supplemental log group
This option specifies that when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file.
To enable all column logging at the database level, execute the following statement:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
PRIMARY KEY system-generated uncondititional supplemental log group
This option causes the database to place all columns of a row’s primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).
If a table does not have a primary key, but has one or more non-null unique index key constraints or index keys, then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.
If the table has neither a primary key nor a non-null unique index key, then all columns except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging for that row. Therefore, Oracle recommends that when you use database-level primary key supplemental logging, all or most tables be defined to have primary or unique index keys.
To enable primary key logging at the database level, execute the following statement:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
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’,-
> ptions=>dbms_logmnr_d.store_in_flat_file);
BEGIN dbms_logmnr_d.build(‘dictionary’, ‘d:\oracle\product’, ptions=>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’,-
> ptions=>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>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21601207/viewspace-701531/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21601207/viewspace-701531/
扫一扫
224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
