Useful shortcuts for vi editor

Showing posts with label log miner. Show all posts
Showing posts with label log miner. Show all posts

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'