LogMiner

If the database has a Logical Standby or is already mined by a tool like Infosphere CDC then Supplemental Logging is almost certainly already enabled.

Check

SELECT supplemental_log_data_min FROM v$database;

Enable

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Roles & Privileges

You must have the EXECUTE_CATALOG_ROLE role and the LOGMINING privilege to query the V$LOGMNR_CONTENTS view and to use the LogMiner PL/SQL packages.

Mining Logs - Step-by-Step

Specify a LogMiner dictionary

NOTE: If you will be mining in the database instance that is generating redo and you know that the object you are mining has not been modified with DDL since the DML change you are looking for then you can skip this step and use the Online Catalog when you start LogMiner.

If you need to mine DML across a DDL change then refer to the Oracle docs...

https://docs.oracle.com/database/121/SUTIL/GUID-8D74F78A-E7D1-48E6-8E65-5E8CC55F0757.htm#SUTIL1599

Specify a list of redo log files for analysis

NOTE: If you will be mining in the database instance that is generating the redo log files, then you can skip this step and use the continuous_mine option when you start LogMiner.

If you want to mine specific logs or mine in a different instance follow the steps below...

To start a new list...

EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/full/path/to/archivelog.arc', OPTIONS => DBMS_LOGMNR.NEW);

To add to an existing list...

EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/full/path/to/archivelog.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

If you need to remove a logfile from the list use...

EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE(LOGFILENAME => '/full/path/to/archivelog.arc');

To see the list query the V$LOGMNR_LOGS view...

SELECT * FROM v$logmnr_logs;

Start LogMiner

To Start LogMiner using the Online Catalog...

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

To Start LogMiner with the Online Catalog and the CONTINUOUS_MINE option...

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-Jan-2012 08:30:00', -

ENDTIME => '01-Jan-2012 08:45:00', -

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -

DBMS_LOGMNR.CONTINUOUS_MINE);

NOTE: ENDTIME can be in the future... i.e. you can mine logs in realtime (1)

To Start LogMiner with the Online Catalog and the CONTINUOUS_MINE option for Committed Data only...

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';Â

EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-Jan-2012 08:30:00', -

ENDTIME => '01-Jan-2012 08:45:00', -

OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -

DBMS_LOGMNR.CONTINUOUS_MINE + -

DBMS_LOGMNR.COMMITTED_DATA_ONLY);

NOTE: ENDTIME can be in the future... i.e. you can mine logs in realtime (1)

Request the redo data of interest

Query the V$LOGMNR_CONTENTS view. See the Examples below.

End the LogMiner session

EXECUTE DBMS_LOGMNR.END_LOGMNR();

Examples

Undoing a committed delete

NOTE: In this particular instance it would probably have been more appropriate to use Flashback Query, but this technique can be used to recover deleted data that is no longer in the UNDO segments as long as the appropriate archived redo logs are available.

NOTE: Extra steps are required if there has been any DDL on the object after the point in time you want to mine.. specifically, you cannot use the dict_from_online_catalog option.

SELECT * FROM mytable;

TEXT ADATE------------------------------ ---------First Entry 11-AUG-17Second Entry 11-AUG-17

We delete all rows and commit.

We switch logfile to be sure our change will be in the latest archived redo log...

DELETE mytable;

COMMIT;

ALTER SYSTEM SWITCH LOGFILE;

This sets up logminer to mine the archived redo log...

EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/fra/MYSID/archivelog/2017_08_11/o1_mf_1_4133_drv8r9kl_.arc', OPTIONS => DBMS_LOGMNR.NEW);

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

To show what we did (note that the delete of all rows is shown as individual row deletes)...

SELECT operation,

sql_redo

FROM v$logmnr_contents

WHERE seg_name = 'MYTABLE';

OPERATION SQL_REDO---------------- --------------------------------------------------------------------------------DELETE delete from "SYS"."MYTABLE" where "TEXT" = 'First Entry' and "ADATE" = TO_DATE('11-AUG-17', 'DD-MON-RR') and ROWID = 'AAA3ijAABAAACORAAC';DELETE delete from "SYS"."MYTABLE" where "TEXT" = 'Second Entry' and "ADATE" = TO_DATE('11-AUG-17', 'DD-MON-RR') and ROWID = 'AAA3ijAABAAACORAAD';

To see what we need to do to reverse this...

SELECT operation,

sql_undo

FROM v$logmnr_contents

WHERE seg_name = 'MYTABLE';

OPERATION SQL_UNDO-------------------------------- -------------------------------------------------------------------------------------------------------DELETE insert into "SYS"."MYTABLE"("TEXT","ADATE") values ('First Entry',TO_DATE('11-AUG-17', 'DD-MON-RR'));DELETE insert into "SYS"."MYTABLE"("TEXT","ADATE") values ('Second Entry',TO_DATE('11-AUG-17', 'DD-MON-RR'));

To find out who did these deletes...

SELECT operation,

commit_timestamp,

seg_name,

session#,

username,

session_info

FROM v$logmnr_contents

WHERE seg_name = 'MYTABLE';

OPERATION COMMIT_TI SEG_NAME SESSION# USERNAME SESSION_INFO--------- --------- -------- -------- -------- -----------------------------------------------------------------------------------------DELETE MYTABLE 62 SYS login_username=SYS client_info= OS_username=oracle Machine_name=myhost OS_terminal=pts/0 OS_process_id=19530236 OS_program_name=sqlplus@myhost (TNS V1-V3)
DELETE MYTABLE 62 SYS login_username=SYS client_info= OS_username=oracle Machine_name=myhost OS_terminal=pts/0 OS_process_id=19530236 OS_program_name=sqlplus@myhost (TNS V1-V3)

To end the LogMiner session...

EXECUTE DBMS_LOGMNR.END_LOGMNR();

Mining in realtime

In this example we will monitor the redo in realtime whilst we insert rows and commit in another session....

Start LogMiner...

EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => SYSDATE, -

ENDTIME => SYSDATE + 1/24, -

OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE + -

DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -

DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Query LogMiner...

Note that the query won't stop until you hit Ctrl-C or until the endtime is reached...Set arraysize to 1 so that results are flushed immediately to screen...

SET ARRAYSIZE 1;

SELECT username,

sql_redo

FROM v$logmnr_contents

WHERE seg_name = 'MYTABLE';

USERNAME SQL_REDO-------- ---------------------------------------------------------------------------------------------------------------------------------------------------

In another session insert some rows and commit them...

INSERT INTO MYTABLE(TEXT,ADATE)

VALUES ('Second Entry',

TO_DATE('11-AUG-17','DD-MON-RR'));

INSERT INTO MYTABLE(TEXT,ADATE)

VALUES ('Third Entry',

TO_DATE('11-AUG-17','DD-MON-RR'));

COMMIT;

SYS insert into "SYS"."MGTEMP"("TEXT","ADATE") values ('Second Entry',TO_DATE('11-AUG-17', 'DD-MON-RR'));SYS insert into "SYS"."MGTEMP"("TEXT","ADATE") values ('Third Entry',TO_DATE('11-AUG-17', 'DD-MON-RR'));
ERROR:ORA-01013: user requested cancel of current operation

To end the LogMiner session...

EXECUTE DBMS_LOGMNR.END_LOGMNR();

Bibliography & References

Database Reference (10.2)https://docs.oracle.com/cd/B19306_01/server.102/b14237/toc.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1154.htm#REFRN30132V$LOGMNR_CONTENTS

https://oracle-base.com/articles/11g/flashback-and-logminer-enhancements-11gr1https://stackoverflow.com/questions/23334495/recovering-deleted-rows-from-oracle-table