Okay, this is nothing new as many of us are well aware and have experience on such situations where we used Log Mining utilities to understand/study analyzed archive log files. Next i will discuss easiest steps to analyze archives using oracle in-built Log Miner utility.

Okay so what it is — Once Again 🙂

LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data audit tool, as well as a tool for sophisticated data analysis.

1) Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun. These might include errors such as those where the wrong rows were deleted because of incorrect values in a WHERE clause, rows were updated with incorrect values, the wrong index was dropped, and so forth.

2) Determining what actions you would have to take to perform fine-grained recovery at the transaction level. Normally you would have to restore the table to its previous state, and then apply an archived redo log file to roll it forward.

3) Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts. That information provides a historical perspective on disk access statistics, which can be used for tuning purposes.

4) Performing postauditing. LogMiner can be used to track any data manipulation language (DML) and data definition language (DDL) statements executed on the database, the order in which they were executed, and who executed them.

STEPS.
=========

Step 1: Specify the list of redo log files to be analyzed.
—————————————————————–
Specify the redo log files which you want to analyze.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –
LOGFILENAME => ‘/opt/data/oracle4/xxxxxx/FRA/xxxxx/archivelog/2016_01_07/o1_mf_1_2514_9xpfvpf7_.arc’,OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

Step 2: Start LogMiner.
—————————–

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

Step 3: Query the V$LOGMNR_CONTENTS view.
———————————————-
Now when the mining is completed you can query the view in order to crept details fetched during mining session.
It will provide you details like username, XID, session details, actions, UNDO and REDO queries, timestamp,operation, session info etc.

Example:
SELECT username AS USR, (XIDUSN || ‘.’ || XIDSLT || ‘.’ || XIDSQN) AS XID,SQL_REDO, SQL_UNDO,TIMESTAMP,OS_USERNAME,MACHINE_NAME,SESSION# FROM V$LOGMNR_CONTENTS WHERE username=’XXXXX’ AND UPPER(SQL_REDO) LIKE ‘%xxxxxxxx%’;

Step 4: End the LogMiner session.
———————————————-
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

Hope It Helps
Prashant D