Oracle LogMiner

It’s an old Oracle feature but still very exciting! Analyzing redo log data gives you very detailed intel about what was going on in your database.

Here is a simple example how to start a logminer session:

-- Requires supplemental logging
alter database add supplemental log data;

-- Create test data
create table test (id number, name varchar2(100));
insert into test values (1,'asdf');
commit;

-- lookup current logfile
select * from v$log;
-- get logfile name of current logfile
select group#,member from v$logfile;

-- switch logfile
alter system switch logfile;

-- load the logfile and start a logminer session
exec dbms_logmnr.add_logfile('/opt/oracle/oradata/CLOUDREP/onlinelog/o1_mf_3_82swzo8k_.log', DBMS_LOGMNR.NEW);
exec DBMS_LOGMNR.START_LOGMNR(Options => DBMS_LOGMNR.PRINT_PRETTY_SQL+DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

-- query the data and do all the crazy things...
select * from v$logmnr_contents;

-- end logminer session
exec DBMS_LOGMNR.END_LOGMNR;

That’s it!

Advertisement