logminer 使用

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_PL from v$database;

SUPPLEMEN SUPPLEMEN SUPPLEMEN
--------- --------- ---------
NO        NO        NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>

set linesize 500
col username format a12
col machine_name format a12
col operation format a12
col session_info format a50
set pagesize 500
select SQL_REDO ,username,os_username,machine_name from v$logmnr_contents where rownum <50 and SQL_REDO<>'UNKNOWN' and SQL_REDO  like 'insert%';

EXECUTE dbms_logmnr_d.build(dictionary_filename =>'logmn_ora817.dat',dictionary_location => '/opt/soft/op11g/xxxxx');

execute dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile,logfilename=>'/opt/soft/op11g/xxxxx/o1_mf_1_317_7kozbmvj_.arc');

a) 使用导出的文本文件数据字典。
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'/opt/soft/op11g/xxxxx/logmn_ora817.dat');

insert into "SYS"."WRH$_ACTIVE_SESSION_HISTORY"("SNAP_ID","DBID","INSTANCE_NUMBER","SAMPLE_ID","SAMPLE_TIME","SESSION_ID","SESSION_SERIAL#","USER_ID","SQL_ID","SQL_CHILD_NUMBER","SQL_PLAN_HASH_VALUE","SERVICE_HASH","SESSION_TYPE","SQL_OPCODE","QC_SESSION_ID","QC_INSTANCE_ID","CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","SEQ#","EVENT_ID","P1","P2","P3","WAIT_TIME","TIME_WAITED","PROGRAM","MODULE","ACTION","CLIENT_ID","FORCE_MATCHING_SIGNATURE","BLOCKING_SESSION","BLOCKING_SESSION_SERIAL#","XID
","CONSUMER_GROUP_ID","PLSQL_ENTRY_OBJECT_ID","PLSQL_ENTRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_SESSION_SERIAL#","REMOTE_INSTANCE#","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION#","SQL_PLAN_OPTIONS#","SQL_EXEC_ID","SQL_EXEC_START","TIME_MODEL","TOP_LEVEL_SQL_ID","TOP_LEVEL_SQL_OPCODE","CURRENT_ROW#","FLAGS","BLOCKING_INST_ID","ECID","TM_DELTA_TIME","TM_DELTA_CPU_TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_REQUESTS","DELTA_READ_IO_BYTES","DELTA_WR
ITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYTES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED","TOP_LEVEL_CALL#","MACHINE","PORT") values ('161','882525440','1','454454',TO_TIMESTAMP('21-JAN-12 12.44.51.471 PM'),'15','1','0','92f47aa2q2rmd','0','4271017901','165959219','2','3','0','0','6069','2','3599','537','2190647165','0','0','0','0','1001323','oracle@localhost.localdomain (MMON)',NULL,NULL,NULL,'2243177294000505793','4294967292','0',NULL,'0','0','0','0','0','0','0','0','0','0','0',NULL,'0','fm1gpkftdrr1
j','3','0',NULL,'255148614',NULL,'10382106','19585052548505','17279242907479474176','10029745','181',NULL,'1613824',NULL,'1613824','2571264',NULL,'59','localhost.localdomain','0');
SYS
op11g
localhost.lo
caldomain

 

b)直接使用在线的数据字典。
EXECUTE dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

insert into "SYS"."WRH$_SYSMETRIC_HISTORY"("SNAP_ID","DBID","INSTANCE_NUMBER","BEGIN_TIME","END_TIME","INTSIZE","GROUP_ID","METRIC_ID","VALUE") values ('161','882525440','1',TO_DATE('21-JAN-12', 'DD-MON-RR'),TO_DATE('21-JAN-12', 'DD-MON-RR'),'6018','2','2003','.0166168162180126');
SYS
op11g
localhost.lo
caldomain

c)使用导出到log文件中的数据字典。
EXECUTE DBMS_LOGMNR_D.BUILD (options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
EXECUTE dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_REDO_LOGS);

SQL> EXECUTE DBMS_LOGMNR_D.BUILD (options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_REDO_LOGS);
BEGIN dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_REDO_LOGS); END;

*
ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

SQL> EXECUTE dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_REDO_LOGS);
BEGIN dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_REDO_LOGS); END;

*
ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

d)不指定数据字典
EXECUTE dbms_logmnr.start_logmnr();

SQL> execute dbms_logmnr.add_logfile(options=>dbms_logmnr.addfile,logfilename=>'/opt/soft/op11g/xxxxx/o1_mf_1_312_7kowf85y_.arc');

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_logmnr.start_logmnr();

PL/SQL procedure successfully completed.

SQL> select SQL_REDO ,username,os_username,machine_name from v$logmnr_contents where rownum <50 and SQL_REDO<>'UNKNOWN' and SQL_REDO  like 'insert%';

SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------
OS_USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MACHINE_NAME
------------
insert into "UNKNOWN"."OBJ# 6056"("COL 1","COL 2","COL 3","COL 4","COL 5") values (HEXTORAW('c20b55'),HEXTORAW('c104'),HEXTORAW('7465737433584442'),HEXTORAW('c106'),HEXTORAW('c102'));
UNKNOWN
UNKNOWN
UNKNOWN

                                        UNSUPPORTED

set linesize 500
col username format a12
col machine_name format a12
col operation format a12
col session_info format a50
set pagesize 500
select SQL_REDO ,username,os_username,machine_name from v$logmnr_contents where rownum <50 and SQL_REDO<>'UNKNOWN' and SQL_REDO  like 'insert%';
select username,machine_name,session_info,operation from  v$logmnr_contents t where t.username<>'UNKNOWN';
select username,machine_name,session_info,operation from  v$logmnr_contents t where t.machine_name<>'UNKNOWN';
select username,machine_name,session_info,operation from  v$logmnr_contents t where t.session_info<>'UNKNOWN';

exec DBMS_LOGMNR.END_LOGMNR();

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10113559/viewspace-715214/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10113559/viewspace-715214/