LOGMINER :-
- 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 .
- Internally Oracle uses the Log Miner technology for several other features,such as Flashback Transaction Backout,Streams, and Logical Standby Databases .Most often LogMiner is used for recovery purposes when the data consists of just a few tables or a single code change .
Steps for Configuring Logminer :-
In this Scenario,we are checking that Username who Dropped the Table using LogMiner utility.
Setting parameter UTL_FILE_DIR :
Normally we set the UTL_FILE_DIR parameter where you need to create dictionary file.From 12.2,we need to create directory object.
[oracle@orcl:~ orcldemo] mkdir -p /oradb/logminer
SQL> alter system set utl_file_dir='/u01/logminer' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1006633808 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> show parameter UTL_FILE_DIR;
NAME TYPE VALUE
------------- ----------- -------------
utl_file_dir string /u01/logminer
Create and grant acccess to directory :
SQL> CREATE OR REPLACE DIRECTORY LOG_DIR AS '/oradb/logminer'; Directory created. SQL> GRANT READ, WRITE ON DIRECTORY LOG_DIR TO hari; Grant succeeded.
Enable Supplemental logging:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database; SUPPLEME -------- NO SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered.
Build Logminer :
Creation of Dictionary file :
The dictionary file is used to translate this data into a more meaningful format. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file.
The dictionary file is created using the BUILD procedure in the DBMS_LOGMNR_D package.
SQL> BEGIN sys.DBMS_LOGMNR_D.build ( dictionary_filename => 'lgmnrdict.ora', dictionary_location => 'LOG_DIR'); END; / PL/SQL procedure successfully completed.
SQL> select member from v$logfile; MEMBER ------------------------------------------------------------------ /oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log /oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log /oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log 3 rows selected.
Adding logfiles to get analyzed :
Adding logfiles using NEW procedure first and add all logfiles using ADD_LOGFILE procedure.
SQL> BEGIN DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.new, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log'); END; / PL/SQL procedure successfully completed.
Starting logminer process :-
SQL> BEGIN -- Start using all logs DBMS_LOGMNR.start_logmnr ( dictfilename => '/oradb/logminer/lgmnrdict.ora'); END; / PL/SQL procedure successfully completed.
Querying v$logmnr_contents :-
When the LogMiner session ends then v$logmnr_contents is no more accessible . Its always better to copy contents of v$logmnr_contents to a user table and then perform the analysis as it is quite expensive to query v$logmnr_contents .
Now we can catch that user who dropped the table, the user is ‘HARI’ and also we can check who created the table.
SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='SAN'; USERNAME TABLE_NAME SQL_REDO ---------- ----------- ------------------------------------- HARI SAN create table san(num number,name varchar2(10)); SYS SAN ALTER TABLE "HARI"."SAN" RENAME TO "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ; HARI SAN drop table hari.san AS "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ; 3 rows selected.
Filtering Data by SCN:
To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters,
Demo :-
Check the current_scn before perform DML transaction
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) -------------------- 2619410
Execute DML statements
SQL> conn hari/hari; Connected. SQL> insert into emp values(1,100); 1 row created. SQL> insert into emp values(2,200); 1 row created. SQL> insert into emp values(3,300); 1 row created. SQL> commit; Commit complete.
Check the current_scn before perform DML transaction
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) -------------------- 2619497
Add list of logfiles to get analyzed
SQL> BEGIN DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.new, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log'); END; / PL/SQL procedure successfully completed.
Specify SCN range to start logminer process
SQL> begin DBMS_LOGMNR.start_logmnr ( dictfilename => '/oradb/logminer/lgmnrdict.ora', startscn => 2619410, endscn => 2619497); END; / PL/SQL procedure successfully completed.
Check the log information v$logmnr_contents
SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';
OPERATION STATUS SQL_REDO
---------- ------ -----------------------------------------------
INSERT 0 insert into "HARI"."EMP"("EMPNO","SAL") values ('1','100');
INSERT 0 insert into "HARI"."EMP"("EMPNO","SAL") values
('2','200');
INSERT 0 insert into "HARI"."EMP"("EMPNO","SAL") values
('3','300');
To filter data by time, set the STARTTIME and ENDTIME parameters in the DBMS_LOGMNR.START_LOGMNR procedure.
Demo :-
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> select sysdate from dual; SYSDATE -------------------- 27-SEP-2018 03:13:13
Perform DML operations
SQL> conn hari/hari; Connected. SQL> insert into emp values(5,500); 1 row created. SQL> delete emp where empno=1; 1 row deleted. SQL> commit; Commit complete.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> select sysdate from dual; SYSDATE -------------------- 27-SEP-2018 03:15:00
Add list of logfiles to get analyzed
SQL> BEGIN DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.new, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log'); END; / PL/SQL procedure successfully completed.
Specify time range to start the logminer process
begin dbms_logmnr.start_logmnr ( dictfilename => '/oradb/logminer/lgmnrdict.ora', starttime => TO_DATE('27-SEP-2018 03:13:13', 'DD-MON-YYYY HH:MI:SS'), endtime => TO_DATE('27-SEP-2018 03:15:00', 'DD-MON-YYYY HH:MI:SS')); end; / PL/SQL procedure successfully completed.
Check v$logmnr_contents
SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';
OPERATION STATUS SQL_REDO
---------- ------ --------------------------------------------------
INSERT 0 insert into "HARI"."EMP"("EMPNO","SAL") values
('5','500');
DELETE 0 delete from "HARI"."EMP" where "EMPNO" = '1' and
"SAL" = '100' and ROWID = 'AAASWiAACAAAAqYAAA';
Stopping logminer process:
Once the analysis is complete, logminer should be stopped using the END_LOGMNR procedure.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR(); PL/SQL procedure successfully completed.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba