OracleDB‎ > ‎


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', -

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',;
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';