Log Miner | Oracle DBA Tutorial pdf

Log Miner

Using log miner, you can examine redo log files!

=> Running log miner

Specify a directory by UTL_FILE_DIR=E:\ORACLE\ORADATA\UNLOAD parameter and bounce database.
Create a dictionary file
EXECUTE DBMS_LOGMNR_D.BUILD
('dictionary.ora','E:\ORACLE\ORADATA\UNLOAD', OPTIONS =>
DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
Add log files
exec dbms_logmnr.add_logfile
('E:\oracle\oradata\MDB\Archive1\ARC00026.001',
dbms_logmnr.new);
exec dbms_logmnr.add_logfile
('E:\oracle\oradata\MDB\Archive1\ARC00027.001',
dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile
('E:\oracle\oradata\MDB\Archive1\ARC00028.001',
dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile
('E:\oracle\oradata\MDB\Archive1\ARC00029.001',
dbms_logmnr.addfile);
Start log miner session
exec dbms_logmnr.start_logmnr (dictfilename=>
'E:\ORACLE\ORADATA\UNLOAD\dictionary.ora');
Once the redo logs were analyzed, all the DDL (and some DML) statements applied in the source database will be found in the V$LOGMNR_CONTENTS view. Important columns of this view are - SQL_UNDO, SQL_REDO, USERNAME, SCN, TIMESTAMP, COMMIT_TIMESTAMP, TABLESPACE,
SEG_NAME, SEG_TYPE, and OPERATION.
The Log Miner session is closed by executing following command in the same session.
EXEC DBMS_LOGMNR.END_LOGMNR
After the session is complete, all data in the v$logmnr_contents table are deleted. Be sure to execute CREATE TABLE my_logmnr AS SELECT ... to copy the data before analyzing the contents.

=> Filtering data that is returned

Log Miner can potentially be dealing with large amounts of information. There are several methods you can use to limit the information that is returned to the V$LOGMNR_CONTENTS view, as well as the speed at which it is returned. 

These options are specified when you start LogMiner.
Showing Only Committed Transactions
At the time of starting
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.COMMITTED_DATA_ONLY);
Filtering Data By Time
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>
'/oracle/dictionary.ora', STARTTIME => TO_DATE('01-Jan-2004
08:30:00', 'DD-MON-YYYY HH:MI:SS'), ENDTIME => TO_DATE('01-
Jan-2004 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
Filtering Data By SCN
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>
'/oracle/dictionary.ora', STARTSCN => 100, ENDSCN => 150);

=> Querying on log miner

Some examples are shown below.
SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE USERNAME <> 'SYS';
SELECT OPERATION, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER = 'MKM' AND SEG_NAME = 'PRODUCT' AND OPERATION
= 'INSERT' AND USERNAME = 'MKM';

=> Log miner related data dictionary views

V$LOGMNR_CONTENTS - Shows changes made to user and table information.
V$LOGMNR_DICTIONARY - Shows information about the Log Miner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option.
V$LOGMNR_LOGS - Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS - Shows information about optional Log Miner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.