OracleDB‎ > ‎

LogMiner

Archivelog file analyzer
To review sql queries in archivelogs, one can utilize logminer:

First set the utl_file_dir

alter system set utl_file_dir='/opt/oracle/utl' scope=spfile;

restart the db

startup force

create a dictionary of the db:
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', - 
   '/opt/oracle/utl', -
    DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

Place the archive logs that are in need of analysis in your utl folder then execute:

execute dbms_logmnr.add_logfile('/opt/oracle/utl/1_30800_730041065.dbf',dbms_logmnr.new);
execute dbms_logmnr.add_logfile('/opt/oracle/utl/1_30801_730041065.dbf',dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('/opt/oracle/utl/1_30802_730041065.dbf',dbms_logmnr.addfile);
...
EXECUTE DBMS_LOGMNR.START_LOGMNR( DICTFILENAME => '/opt/oracle/utl/dictionary.ora');


Look at the contents:

set pagesize 0;
column sql_redo format a50 word_wrapped
column sql_undo format a50 word_wrapped
select to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS'), sql_redo, sql_undo from v$logmnr_contents where seg_owner='SCHEMA_NAME';


Comments