Sharing My Database Experience

Menu

Example of using Logminer

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.

— 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;

— 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;

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