Logminer

Its a pl/sql based utility.

Steps:

alter database add supplemental log data;

To confirm this change:

select name, SUPPLEMENTAL_LOG_DATA_MIN from v$database;


Create a directory to store dictionary file.

mkdir /u01/logs

Specify the location of dictionary file at OS level.

show parameter utl;

alter system set utl_file_dir = '/u01/logs' scope = spfile;
(we must change only in spfile)

Bounce back the database.

startup force;

show parameter utl;


Create a dictionary file.

exec dbms_logmnr_d.build ('dfile', '/u01/logs');


Connect to a sys user and specify the all logfiles to Logminer session.

exec dbms_logmnr.add_logfile ('/u01/prod/redo01.log');
exec dbms_logmnr.add_logfile ('/u01/prod/redo02.log');
exec dbms_logmnr.add_logfile ('/u01/prod/redo03.log');

Start the mining process.

exec dbms_logmnr.start_logmnr (dictfilename => '/u01/logs/dfile');

spool abc.sql
select sql_undo, sql_redo from v$logmnr_contents
where seg_owner = 'SCOTT'
and seg_name = 'DEPT';
spool off;