Useful shortcuts for vi editor

Tuesday, 21 February 2017

How to use logminer?

Determine the archive log path:
SQL> archive log list;

If "Archive destination" is "DB_RECOVERY_FILE_DEST":
SQL> show parameter DB_RECOVERY_FILE_DEST;

Select the archive log file to use for logminer 
such as "C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC"

e.g.
delete from employees where employee_id = 206

Run following codes as SYS user:

BEGIN
   DBMS_LOGMNR.add_logfile (
      logfilename   => 'C:\oraclexe\app\oracle\oradata\XE\LOG_ARCH_1\LOG1_28_934210760.ARC', options => DBMS_LOGMNR.new);
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
/

Run these codes for examining the logs in example table (as SYS):

SQL>
CREATE TABLE HR.ex934210760 AS SELECT * FROM v$logmnr_contents;
SQL> 
GRANT SELECT ON HR.ex934210760 TO PUBLIC;

To find DML (delete) SQL in example table:

SQL>
select * from HR.ex934210760 where table_name='EMPLOYEES'