Using LogMiner to Analyze Redo Log Files

Posted on Posted in Oracle DB

In this post I’ll explain how using the LogMiner tool to Analyze Redo LogFiles, this tool extract the information from Redo Log’s and we can see the sql sentence that have changed data in the DataBase. It is convenient when we need to know more information about sentences in a specific point in time, like who modified one table, when, or from what machine.

utl_file_dir

Define the utl_file_dir, that is the directory where we’ll save the dictionary. After that, reboot the DataBase

SQL> alter system set utl_file_dir='/my/for/the/dictionary_dir' scope=spfile;

Create the directory

Create the directory in the database, like:

SQL> create directory my_logminer as '/my/for/the/dictionary_dir';

Build the Dictionary

Ok, now build your Database Dictionary:

SQL> execute DBMS_LOGMNR_D.BUILD('dictionary.ora', '/my/for/the/dictionary_dir', options => dbms_logmnr_d.store_in_flat_file);

Obtain Archive Logs

For example, If we’ll analyze what happened between 10:00 and 12:00 hrs on 29 of July, we’ll need the name’s of the archive logs:

SQL> select thread#, name, first_time, sequence# from v$archived_log
where first_time between to_date('29/06/2012 10:00:00','DD/MM/YYYY HH24:MI:SS')
and to_date('29/06/2012 12:00:00','DD/MM/YYYY HH24:MI:SS')
 
SEQUENCE    #NAME                                                          #FIRST_TIME    #SEQUENCE
---------- --------------------------------------------------------------------------- - ---
1           +FRA/db/archivelog/2012_06_29/thread_1_seq_9178.816.787228635 29/06/2012 10:07:13 9178
2           +FRA/db/archivelog/2012_06_29/thread_2_seq_8132.10625.787228635 29/06/2012 10:07:16 8132
1           +FRA/db/archivelog/2012_06_29/thread_1_seq_9179.10210.787230435 29/06/2012 10:37:14 9179
2           +FRA/db/archivelog/2012_06_29/thread_2_seq_8133.9802.787230437 29/06/2012 10:37:15 8133
2           +FRA/db/archivelog/2012_06_29/thread_2_seq_8134.10384.787232235 29/06/2012 11:07:16 8134
1           +FRA/db/archivelog/2012_06_29/thread_1_seq_9180.10686.787232235 29/06/2012 11:07:15 9180
1           +FRA/db/archivelog/2012_06_29/thread_1_seq_9181.11295.787234035 29/06/2012 11:37:14 9181
2           +FRA/db/archivelog/2012_06_29/thread_2_seq_8135.5141.787234041 29/06/2012 11:37:14 8135

Add to the analysis

Now, add the previous archive logs to the analysis, sort by date

execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_1_seq_9178.816.787228635',options=>dbms_logmnr.new);

The remainder with “addfile” option:

execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_2_seq_8132.10625.787228635',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_1_seq_9179.10210.787230435',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_2_seq_8133.9802.787230437',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_2_seq_8134.10384.787232235',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_1_seq_9180.10686.787232235',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_1_seq_9181.11295.787234035',options=>dbms_logmnr.addfile);
execute DBMS_LOGMNR.ADD_LOGFILE('+FRA/bd/archivelog/2012_06_29/thread_2_seq_8135.5141.787234041',options=>dbms_logmnr.addfile);

Run the Analysis

Run the analysis, define the time interval, note that the start time must be after the first log file time (10:07:13)

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR ( STARTTIME => to_date('29-06-2012 10:10','DD-MM-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('29-06-2012 12:00:00','DD-MM-YYYY HH:MI:SS'), dictfilename =>'/my/for/the/dictionary_dir/dictionary.ora');

Extract the information

Now obtain the information:

SQL> alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';
SQL> set pagesize 10000
SQL> set termout off
SQL> spool mine.out
SQL> column scn format 9999999999999999
SQL> column cscn format 9999999999999999

SQL> select seg_name,operation, timestamp, scn, cscn, xidusn || '.' || xidslt || '.' || xidsqn trnsid, sql_redo, sql_undo from v$logmnr_contents;

 Closes the logminer session

SQL> exec dbms_logmnr.end_logmnr;

Leave a Reply

Your email address will not be published. Required fields are marked *