Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g

Using LogMiner to Analyze Redo Log Files or archivelog files in oracle 10g
=====================================================
=====================================================


Enables you to query online redolog files and archived log files through a SQL interface.
Redo log and archived log files contain information about the history of activity on a database.
such as

1. you can find what errors made at the application level. These might include
errors such as those where the wrong rows were deleted because of incorrect
values in a WHERE clause, rows were updated with incorrect values, the wrong
index was dropped, and so forth.

2. Using LogMiner to Track Changes Made by a Specific User.

3. you can use it as a powerful audit tool.

4. You can determine which tables get the most updates and inserts.
5. Performance tuning and capacity planning through trend analysis.
6. Determining what actions you would have to take to perform fine-grained recovery at the transaction level
7. LogMiner can be used to track any data manipulation language (DML)
and data definition language (DDL) statements executed on the Database



Extract a LogMiner Dictionary

To use LogMiner, you must supply it with a dictionary by doing one of the following:

1. Specify use of the online catalog by using the DICT_FROM_ONLINE_CATALOG option when you start Log Miner.
(This is very convenient. i use it in the following )

2. Extract database dictionary information to the redo log files.
(When table structure change)

3. Extract database dictionary information to a flat file.



Steps are :=



1. Enable SUPPLEMENTAL Log to Database.
=======================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


2. Install logminer package (if not installed by default installed) from following path
============================================
ORACLE_HOME/rdbms/admin/dbmslm.sql



3. Determine which redo log file was most recently archived.
===============================================

SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

------H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

4. Specify the list of redo log files to be analyzed.
=========================================

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
OPTIONS => DBMS_LOGMNR.NEW);

5. Start LogMiner.
========================

Start LogMiner and specify the dictionary to use.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

OR
Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY option.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);

OR

Start LogMiner by specifying the dictionary to use and the COMMITTED_DATA_ONLY and PRINT_PRETTY_SQL options.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);


6. Query the V$LOGMNR_CONTENTS view.
==================================


SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

7. End the LogMiner session.
==============================

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();






Example:=

The easiest way to examine the modification history of a database is to mine at the source database and use the
online catalog to translate the redo log files. This example shows how to do the simplest analysis using LogMiner.

This example finds all modifications that are contained in the last archived redo log generated by the database
(assuming that the database is not an Oracle Real Application Clusters database)

=====================================
======================================


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 16 12:11:54 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys@test107 as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> grant dba to halim identified by halim ;

Grant succeeded.

SQL>
SQL> conn halim/halim@test107
Connected.
SQL>
SQL>
SQL> create table halim_table (id number,name varchar2(20));

Table created.

SQL>
SQL> insert into halim_table select rownum,rownum||'halim' from all_objects where rownum<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> delete from halim_table;

10 rows deleted.

SQL> rollback;

Rollback complete.

SQL> update halim_table
2 set name='change_halim' ;

10 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL>
SQL>
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------
H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC

SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => 'H:\FLASH_BACK\STLBAS\ARCHIVELOG\2010_08_16\O1_MF_1_2556_66KP9J6T_.ARC', -
> OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL>
SQL>
SQL>
===================================================================
===================================================================
Now you find all activity of above.
You can find here Original sql as well as Undo sql, you can undo the change by undo sql
===================================================================
===================================================================

SQL>
SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
2 SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HALIM');

USR XID
------------------------------ --------------------------------------------------------------------------------------------------------------------------
SQL_REDO
-----------------------------------------------------------------------
SQL_UNDO
-----------------------------------------------------------------------------------------------------------


HALIM 4.26.6403
create table halim_table (id number,name varchar2(20));


HALIM 7.21.8139
set transaction read write;


HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 7.21.8139
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 7.21.8139
commit;


HALIM 2.34.7758
set transaction read write;


HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '1' and "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '2' and "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '3' and "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '4' and "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '5' and "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '6' and "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '7' and "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '8' and "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '9' and "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');

HALIM 2.34.7758
delete from "HALIM"."HALIM_TABLE" where "ID" = '10' and "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');

HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('10','10halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('9','9halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('8','8halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('7','7halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('6','6halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('5','5halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('4','4halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('3','3halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('2','2halim');


HALIM 2.34.7758
insert into "HALIM"."HALIM_TABLE"("ID","NAME") values ('1','1halim');


HALIM 2.34.7758
rollback;


HALIM 10.20.11872
set transaction read write;


HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '1halim' and ROWID = 'AAATjnAAEAACJW0AAA';
update "HALIM"."HALIM_TABLE" set "NAME" = '1halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAA';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '2halim' and ROWID = 'AAATjnAAEAACJW0AAB';
update "HALIM"."HALIM_TABLE" set "NAME" = '2halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAB';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '3halim' and ROWID = 'AAATjnAAEAACJW0AAC';
update "HALIM"."HALIM_TABLE" set "NAME" = '3halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAC';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '4halim' and ROWID = 'AAATjnAAEAACJW0AAD';
update "HALIM"."HALIM_TABLE" set "NAME" = '4halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAD';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '5halim' and ROWID = 'AAATjnAAEAACJW0AAE';
update "HALIM"."HALIM_TABLE" set "NAME" = '5halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAE';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '6halim' and ROWID = 'AAATjnAAEAACJW0AAF';
update "HALIM"."HALIM_TABLE" set "NAME" = '6halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAF';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '7halim' and ROWID = 'AAATjnAAEAACJW0AAG';
update "HALIM"."HALIM_TABLE" set "NAME" = '7halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAG';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '8halim' and ROWID = 'AAATjnAAEAACJW0AAH';
update "HALIM"."HALIM_TABLE" set "NAME" = '8halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAH';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '9halim' and ROWID = 'AAATjnAAEAACJW0AAI';
update "HALIM"."HALIM_TABLE" set "NAME" = '9halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAI';

HALIM 10.20.11872
update "HALIM"."HALIM_TABLE" set "NAME" = 'change_halim' where "NAME" = '10halim' and ROWID = 'AAATjnAAEAACJW0AAJ';
update "HALIM"."HALIM_TABLE" set "NAME" = '10halim' where "NAME" = 'change_halim' and ROWID = 'AAATjnAAEAACJW0AAJ';

HALIM 10.20.11872
commit;



70 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Cheers.............