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!
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: