加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 大数据 > 正文

logminer使用测试库进行挖掘分析，10.2.0.5

发布时间：2020-12-14 05:15:20 所属栏目：大数据 来源：网络整理
导读：上一篇测试是在dg环境进行测试挖掘，但是如果客户存在一个测试库，那样使用日志挖掘的影响性更

上一篇测试是在dg环境进行测试挖掘，但是如果客户存在一个测试库，那样使用日志挖掘的影响性更小。本篇进行测试分析。

测试环境介绍：

oracle linux? 5.6，vmware虚拟机，安装两套单实例10.2.0.5数据库，一套模拟生产环境进行dml操作，另一套模拟测试环境进行日志相关挖掘。

?

测试流程说明：

1.生产环境，模拟dml操作，一个表A，产生一个delete 1行记录，执行两次，表B，执行一次delete操作， 30000条记录? ，日志切换（归档模式下），再次多切换几次日志

2.目标端，使用logminer 进行挖掘相关日志，最终得出相关分析。

?

?

一、生产环境日志模拟

SQL> show parameter name
NAME        VALUE
--------------------
db_name    test1

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/arch


SQL> create user test1 identified by test1;
SQL> create user test2 identified by test2;
SQL> grant dba to test1,test2;

SQL> conn test1/test1

SQL> create table a as select * from scott.emp;

SQL> delete a where rownum=1;

SQL> commit;

SQL> delete a where empno=7788;

SQL> commit;

SQL> alter system switch logfile;

SQL> conn test2/test2
SQL> create table b as select * from dba_objects;

SQL> insert into b select * from b;

SQL> insert into b select * from b

SQL> commit;

SQL> delete b where rownum<100000;

SQL> commit;

SQL> alter system archive log current;

 
 
SQL> select DEST_ID,THREAD#,SEQUENCE#,COMPLETION_TIME,NAME from v$archived_log where COMPLETION_TIME >sysdate-20/1440;
 
 
 DEST_ID THREAD# SEQUENCE# COMPLETION_TIME NAME
---------- ---------- ---------- ------------------- -------------------------------------------------------
 1 1 1 2019-01-12 12:02:42 /u02/app/oracle/arch/1_1_993126050.arc
 1 1 2 2019-01-12 12:14:46 /u02/app/oracle/arch/1_2_993126050.arc
 1 1 3 2019-01-12 12:16:46 /u02/app/oracle/arch/1_3_993126050.arc
 

 

?
 

二、测试库使用Logminer?
 

?
 

 
 
1）logminer前提准备
 
 
添加存储过程
 
 
@?/rdbms/admin/dbmslmd.sql
@?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslms.sql
@?/rdbms/admin/prvtlm.plb
 
 
开启最小补充日志
 
 
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
 
 
SQL> alter database add supplemental log data;
 
 
Database altered.
 
 
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_UI from v$database;
 
 
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
 
 
?
 
 
2）日志挖掘，在无数据字典的情况下，输出内容
 
 
SQL> exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
 
 
SQL> exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_1_993126050.arc‘,dbms_logmnr.new);
 
 
SQL> exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_2_993126050.arc‘,dbms_logmnr.addfile);
 
 
使用online 数据字典进行翻译，报错： dbid不同
 
 
 SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
 BEGIN dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); END;

 *
 ERROR at line 1:
 ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
 ORA-06512: at "SYS.DBMS_LOGMNR",line 58
 ORA-06512: at line 1
 
 
?
 
 
SQL> exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>‘‘,Options=>0);
 
 
SQL> select sql_redo,sql_undo from V$LOGMNR_CONTENTS where sql_redo like ‘%insert into%‘;
 
 
SQL_REDO SQL_UNDO
--------------------------------------------------------------------------------
insert into "UNKNOWN"."OBJ# 8869"("COL 1","COL 2","COL 3","COL 4","COL 5") value
s (HEXTORAW(‘c2020d‘),HEXTORAW(‘c104‘),HEXTORAW(‘7465737431584442‘),HEXTORAW(‘c1
06‘),HEXTORAW(‘c102‘));
delete from "UNKNOWN"."OBJ# 8869" where "COL 1" = HEXTORAW(‘c2020d‘) and "COL 2"
 = HEXTORAW(‘c104‘) and "COL 3" = HEXTORAW(‘7465737431584442‘) and "COL 4" = HEX
TORAW(‘c106‘) and "COL 5" = HEXTORAW(‘c102‘) and ROWID = ‘AAACKlAABAAAFRSAAA‘;
 
 
在使用异机使用logminer挖掘，在没有数据字典的情况下，挖掘出来的只能是obj#，无法获取对象名称
 
 
select * from (
select rownum,username,SEG_OWNER,SEG_NAME,seg_type_name,OPERATION,a from (
select username,count(*) a
from V$LOGMNR_CONTENTS where OPERATION in(‘UPDATE‘,‘DELETE‘,‘INSERT‘) group by username,
OPERATION order by 6 desc)) where rownum<20
 
 
M USERNAME SEG_OWNER SEG_NAME SEG_TYPE_NAME OPERATION A
- ---------- --------------- --------------- --------------- -------------------------------- ----------
1 UNKNOWN UNKNOWN OBJ# 51696 UNKNOWN INSERT 5925
2 UNKNOWN UNKNOWN OBJ# 51724 UNKNOWN INSERT 5820
3 UNKNOWN UNKNOWN OBJ# 51728 UNKNOWN INSERT 3990
4 UNKNOWN UNKNOWN OBJ# 9115 UNKNOWN INSERT 3240
 
 
https://blog.csdn.net/cuiyan1982/article/details/80333013
 
 
?
 
 
?
 
 
3）生产环境，修改参数使用文件存储数据字典，数据字典文件拷贝至测试库进行注册使用。
 
 
alter system set?utl_file_dir=/abc scope=spfile;? 生产环境需要重启，代价太高
 
 
startup force --实际环境不能这么干
 
 
SQL> exec dbms_logmnr_d.build(dictionary_filename=>‘ar1.dic‘,dictionary_location=>‘/abc‘,options=>dbms_logmnr_d.STORE_IN_FLAT_FILE);
 
 
测试环境，使用该数据字典，进行解析。
 
 
SQL> exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
 
 
SQL>? exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_1_993126050.arc‘,dbms_logmnr.new);
 
 
SQL>? exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_2_993126050.arc‘,dbms_logmnr.addfile);
 
 
SQL> exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>‘/abc/ar1.dic‘,Options=>0);
 
 
?select * from (
?select rownum,a from (
?select username,count(*) a
?from V$LOGMNR_CONTENTS where OPERATION in(‘UPDATE‘,
 5OPERATION order by 6 desc)) where rownum<20
 
 
 ROWNUM USERNAME SEG_OWNER SEG_NAME SEG_TYPE_NAME OPERATION A
---------- ---------- ---------- -------------------- -------------------- ---------- ----------
 1 UNKNOWN SYS WRH$_LATCH,WRH$_LATC TABPART INSERT 5925
 H_1370159887_0
 
 
 2 UNKNOWN SYS WRH$_SYSSTAT,WRH$_SY TABPART INSERT 5820
 SSTA_1370159887_0
 
 
 3 UNKNOWN SYS WRH$_PARAMETER,WRH$_ TABPART INSERT 3990
 PARAME_1370159887_0
 
 
#查询数据字典文件（oracle根据数据字典，进行解析obj，转换为我们熟悉的用户名，表对象名称等）
 
 
CREATE_TABLE DICTIONARY_TABLE ( DB_NAME VARCHAR2(9),DB_ID NUMBER(20),DB_CREATED VARCHAR2(20),DB_DICT_CREATED VARCHAR2(20),DB_RESETLOGS_CHANGE# NUMBER(22
),DB_RESETLOGS_TIME VARCHAR2(20),DB_VERSION_TIME VARCHAR2(20),DB_REDO_TYPE_ID VARCHAR2(8),DB_REDO_RELEASE VARCHAR2(60),DB_CHARACTER_SET VARCHAR2(30),D
B_VERSION VARCHAR2(64),DB_STATUS VARCHAR2(64),DB_DICT_MAXOBJECTS NUMBER(22),DB_DICT_OBJECTCOUNT NUMBER(22),DB_DICT_SCN NUMBER(22),DB_THREAD_MAP RAW(8),
 DB_TXN_SCNBAS NUMBER(22),DB_TXN_SCNWRP NUMBER(22));
 
 
INSERT_INTO DICTIONARY_TABLE VALUES (‘TEST1‘,1370159887,‘11/25/2018 09:58:39‘,‘01/12/2019 13:56:03‘,420491,‘11/25/2018 12:20:50‘,‘11/25/2018 11:36:00‘,‘‘,‘‘
,‘AL32UTF8‘,‘10.2.0.5.0‘,‘Production‘,53160,50195,493175,493793,0);
 
 

CREATE_TABLE OBJ$_TABLE (OBJ# NUMBER(22),DATAOBJ# NUMBER(22),OWNER# NUMBER(22),NAME VARCHAR2(30),NAMESPACE NUMBER(22),SUBNAME VARCHAR2(30),TYPE# NUMBE
R(22),CTIME DATE,MTIME DATE,STIME DATE,STATUS NUMBER(22),REMOTEOWNER VARCHAR2(30),LINKNAME VARCHAR2(128),FLAGS NUMBER(22),OID$ RAW(16),SPARE1 NUMBE
R(22),SPARE2 NUMBER(22),SPARE3 NUMBER(22),SPARE4 VARCHAR2(1000),SPARE5 VARCHAR2(1000),SPARE6 DATE ); 
 
 
INSERT_INTO OBJ$_TABLE VALUES (20,2,‘ICOL$‘,1,to_date(‘04/20/2010 08:24:28‘,‘MM/DD/YYYY HH24:MI:SS‘),to_date(‘04/20/2010 08:32:25‘,‘MM/DD/YYYY HH24
:MI:SS‘),);
 
 
?
 
 
4）生产环境，将数据字典，写入Online redo文件中，切换归档，拷贝至测试库，注册后使用。
 
 
?实际环境中，根本不允许生产环境随便重启库修改参数文件，因此在线操作更可取。
 
 
SQL> alter database add supplemental log data;? --需要开启最小补充日志
 
 
SQL> exec DBMS_LOGMNR_D.BUILD(dictionary_filename=>NULL,dictionary_location=>NULL,options=>dbms_logmnr_d.STORE_IN_REDO_LOGS);
 
 
?col name format a90
set linesize 150
select name,ARCHIVED,DICTIONARY_BEGIN,DICTIONARY_END from v$archived_log where name like ‘%.arc‘;
 
 
NAME ARC DIC DIC
--------------------------------------------- --- --- ---
 
 
/u02/app/oracle/arch/1_9_993126050.arc? ? ? ? YES YES YES
 
 
选择将此归档文件进行copy，无需手工切换，因为执行exec写入redo，会自动切换产生归档日志
 
 
?
 
 
测试环境，导入这个归档日志：
 
 
SQL> exec dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);
 
 
PL/SQL procedure successfully completed.
 
 
SQL> exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_1_993126050.arc‘,dbms_logmnr.new);
 
 
PL/SQL procedure successfully completed.
 
 
SQL> exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_2_993126050.arc‘,dbms_logmnr.addfile);
 
 
PL/SQL procedure successfully completed.
 
 
SQL> exec dbms_logmnr.add_logfile(‘/u02/app/oracle/arch/1_9_993126050.arc‘,dbms_logmnr.addfile);
 
 
PL/SQL procedure successfully completed.
 
 
SQL> exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>‘‘,Options=>DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
 
 
PL/SQL procedure successfully completed.
 
 
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
Sat Jan 12 14:20:14 CST 2019
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
 

 

?
 

?
 

?
 

三、挖掘对比
 

?
 

 
 
select rownum,a from (
 select username,count(*) a
 from V$LOGMNR_CONTENTS where seg_owner IN (‘TEST1‘,‘TEST2‘) group by username,OPERATION order by 6 desc);

ROWNUM USERNAME SEG_OWNER SEG_NAME SEG_TYPE_N OPERATION A
---------- -------------------- -------------------------------- ---------- ---------- ---------- ----------
1 UNKNOWN TEST2 B TABLE INSERT 151942
2 UNKNOWN TEST2 B TABLE DELETE 99983
3 UNKNOWN TEST1 A TABLE DDL 1
4 UNKNOWN TEST2 B TABLE DDL 1
5 SYS TEST2 USER DDL 1
6 SYS TEST1 USER DDL 1

6 rows selected.

[email?protected]>select sql_redo,sql_undo from V$LOGMNR_CONTENTS where seg_owner IN (‘TEST1‘);

SQL_REDO             SQL_UNDO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create user test1 identified by VALUES ‘22F2E341BF4B8764‘ ;

create table a as select * from scott.emp;

[email?protected]>select sql_redo,sql_undo from V$LOGMNR_CONTENTS where seg_owner IN (‘TEST2‘) and OPERATION=‘DELETE‘ and rownum=1;

SQL_REDO                                                         SQL_UNDO

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from "TEST2"."B" where "OWNER" = ‘SYS‘ and "OBJECT_NAME" = ‘I_TS#‘ and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = ‘7‘ and "DATA_OBJECT_ID" = ‘7‘ and "OBJECT_TYPE" = ‘INDEX‘ and "CREATED" = TO_D
ATE(‘2010-04-20 08:24:28‘,‘yyyy-mm-dd hh24:mi:ss‘) and "LAST_DDL_TIME" = TO_DATE(‘2010-04-20 08:24:28‘,‘yyyy-mm-dd hh24:mi:ss‘) and "TIMESTAMP" = ‘2010-04-20:08:24:28‘ and "STATUS" = ‘VALID‘ and "TE
MPORARY" = ‘N‘ and "GENERATED" = ‘N‘ and "SECONDARY" = ‘N‘ and ROWID = ‘AAAM+oAAEAAAALcAAQ‘;
insert into "TEST2"."B"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") values (‘S
YS‘,‘I_TS#‘,NULL,‘7‘,‘INDEX‘,TO_DATE(‘2010-04-20 08:24:28‘,‘yyyy-mm-dd hh24:mi:ss‘),‘2010-04-20:08:24:28‘,‘VALID‘,‘N‘,‘N‘);

第一，delete 一条记录，删除9999行记录被抓取到，并且 count(*)说明oracle 底层delete操作，是逐行进行删除，虽然自己写的是一条delete where rownum<100000;

第二，虽然oracle logminer挖掘日志，能够挖掘很细腻，但是delete一行记录在本次操作中，挖掘消失了！！！

第三，ddl操作都被明确记录
 


        

            

        	




                        
（编辑：李大同）


【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

                    		
                  

                

            

            
    
            
    
            
    
            	
    相关内容
    

      

            
      
        
      
    
      
    
      
    	
      
        
      推荐文章
      
            
        
        
        
    	
        
        
        站长推荐
        
            
        
        
        
        
        
    	
        
        
        热点阅读
        
            
          
        
          
        
          
        
          
	
          

          

          

          

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。
          

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果
          

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同