LOG MINER in Oracle
2 min readOracle LogMiner, a component of Oracle Database, provides a SQL interface for querying both live and archived redo log files. The history of activity on a database is stored in redo log files.
- Logminer is a utility provided with the Oracle database server which mines the redologs or Archivelogs and data dictionary information to build the SQL statements and keeps the contents of the redolog file in the fixed view called “V$logmnr_contents”.
LogMiner tool can help the DBA to the find changed records in redo log files by using a set of PL/SQL procedures and functions .
Enable Supplemental logging:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
Install logminer package :
SQL> @ORACLE_HOME/rdbms/admin/dbmslm.sql
Create a list of logs by specifying the NEW option when executing the DBMS_LOGMNR.ADD_LOGFILE procedure.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -LOGFILENAME => /opt/data/oracle/ITSMM/FRA/ITSMM/archivelog/2022_08_02/o1_mf_1_2514_9xpfvpf7_.arc',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
Starting logminer process :-
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
Querying v$logmnr_contents :-
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.
SELECT username AS USR,
(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
operation,
SQL_REDO,
SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE username IN ('');
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='ADMIN' AND UPPER(SQL_REDO) LIKE '%REQ000000023456%';
Example:
select username,table_name,sql_redo from v$logmnr_contents where seg_name='ASRB';
End the LogMiner session.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
VIEWS TO BE NOTED:
V$LOGMNR_CONTENTS : It contains log history information. To query this view, you must have the LOGMINING privilege. V$LOGMNR_DICTIONARY : Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information shown includes the database name and status information. V$LOGMNR_LOGS : Shows information about specified redo logs. There is one row for each redo log. V$LOGMNR_PARAMETERS : Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.
Hope it worked for you !! 🙂
