LogMiner can be used to recover data and audit database, it has 2 packages one of them named “DBMS_LOGMNR_D” to build dictonary and other setup to read from archive logs.

— Using DBMS_LOGMNR_D.BUILD – one creates a information on objects in the database, it is useful as when looking at contents of archive logs it will list the object names instead of object ids. One would need to set UTL_FILE_DIR if using the store_in_flat_file option.
SQL> exec DBMS_LOGMNR_D.BUILD ( 'dict.ora', '/tmp', options => dbms_logmnr_d.store_in_flat_file );

PL/SQL procedure successfully completed.

Note: The Logmnr is applied at the session level so one can't view the information of logminer contents through another session.

— Add archive log, the first one is DBMS_LOGMNR.NEW
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ( '/u01/oradata/TEST/arch/1_1205_694732375.dbf', DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

— Add additional archive log(s)
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ( '/u01/oradata/TEST/arch/1_1204_694732375.dbf', DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

— to get the timestamp of start or SCN# of the archive log
SQL> select first_change#, first_time from v$log_history where sequence# = 1204;

FIRST_CHANGE# FIRST_TIME
————- ——————–
6293397 16-APR-2010 02:34:15

— get the current timestamp for end timestamp or SCN# as in this case one is looking at the most recent change
SQL> select first_change#, first_time, sequence# from V$log where status = 'CURRENT';

FIRST_CHANGE# FIRST_TIME SEQUENCE#
————- ——————– ———-
6303785 16-APR-2010 09:00:42 1206

— start logminer with the above time, if one specifies the start time less than the first archive log this procedure will return “ORA-01291: missing logfile”, if one specifies end time greater than the log file it will not report an error
SQL> exec DBMS_LOGMNR.START_LOGMNR (startTime => TO_DATE('16-APR-2010 02:34:15', 'DD-MON-YYYY HH24:MI:SS'), endTime => TO_DATE('16-APR-2010 09:00:42', 'DD-MON-YYYY HH24:MI:SS'), DictFileName => '/tmp/dict.ora');

PL/SQL procedure successfully completed.

— get the period of logminer
SQL> select min(timestamp), max(timestamp) from V$logmnr_contents;

MIN(TIMESTAMP) MAX(TIMESTAMP)
——————– ——————–
16-APR-2010 02:34:15 16-APR-2010 09:00:42

One can check the start time/scn and end time/scn through the view V$LOGMNR_PARAMETERS.
START_DATE END_DATE START_SCN END_SCN INFO STATUS
——————– ——————– ———- ———- ——————————– ———-
16-APR-2010 02:34:15 16-APR-2010 09:00:42 0 0 0

— print the objects modified during the above period
SQL> SELECT distinct seg_name from v$logmnr_contents;

SEG_NAME
—————————–
WRH$_PROCESS_MEMORY_SUMMARY
..

— shows the operation performed on the object, if dictonary is not available the one can query using object_id show below
SQL> select operation, count(1) from v$logmnr_contents where seg_name = 'WRH$_PROCESS_MEMORY_SUMMARY' group by operation;

OPERATION COUNT(1)
——————————– ———-
INSERT 24

—
SQL> select object_id from dba_objects where object_name = 'WRH$_PROCESS_MEMORY_SUMMARY';

OBJECT_ID
———-
9003

SQL> select operation, count(1) from v$logmnr_contents where DATA_OBJ# = 9003 group by operation;

OPERATION COUNT(1)
——————————– ———-
INSERT 24

— end log miner
SQL> exec DBMS_LOGMNR.END_LOGMNR;

PL/SQL procedure successfully completed.

Note:
— If start_logmnr not set, the procedure will return ORA-01306
SQL> select count(1) from v$logmnr_contents;
select count(1) from v$logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

If start being called but no archive logs defined, then dbms_logmnr will return ORA-01292
RROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at “SYS.DBMS_LOGMNR”, line 58
ORA-06512: at line 1