Oracle LogMiner enables you to query online redo logs and archived log files through a SQL interface, It helps in troubleshooting user activity in data missing conditions,
one of our customer data got delete between 5PM to 6PM, here we are troubleshooting user activity during that period,
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
PRODDB READ WRITE
Check archive details between 5 to 6PM:
SQL>alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select first_time, sequence#, thread# from v$log_history
where first_time between '30-OCT-2017 17:00:00' and '30-OCT-2017 18:00:00'
order by thread#,sequence# asc;
FIRST_TIME SEQUENCE# THREAD#
-------------------- ---------- ----------
30-oct-2017 17:00:05 52160 1
30-oct-2017 17:04:24 52161 1
30-oct-2017 17:19:24 52162 1
30-oct-2017 17:24:19 52163 1
30-oct-2017 17:35:44 52164 1
30-oct-2017 17:35:51 52165 1
30-oct-2017 17:44:20 52166 1
30-oct-2017 17:59:35 52167 1
8 rows selected.
Check archive files availability in host:
[oracle@jdepdora01 2017_10_30]$ ls -ltr *5216*
-rw-r----- 1 oracle oinstall 2016768 Oct 30 17:04 o1_mf_1_52160_dzfj78wd_.arc
-rw-r----- 1 oracle oinstall 9224192 Oct 30 17:19 o1_mf_1_52161_dzfk3dx6_.arc
-rw-r----- 1 oracle oinstall 3987456 Oct 30 17:24 o1_mf_1_52162_dzfkdmz1_.arc
-rw-r----- 1 oracle oinstall 200954880 Oct 30 17:35 o1_mf_1_52163_dzfl20q7_.arc
-rw-r----- 1 oracle oinstall 201385984 Oct 30 17:35 o1_mf_1_52164_dzfl27m1_.arc
-rw-r----- 1 oracle oinstall 110521344 Oct 30 17:44 o1_mf_1_52165_dzfll46n_.arc
-rw-r----- 1 oracle oinstall 8595968 Oct 30 17:59 o1_mf_1_52166_dzfmgqkr_.arc
-rw-r----- 1 oracle oinstall 5592576 Oct 30 18:04 o1_mf_1_52167_dzfmr01f_.arc
-rw-r----- 1 oracle oinstall 4830720 Oct 30 18:19 o1_mf_1_52168_dzfnn8cc_.arc
-rw-r----- 1 oracle oinstall 4549120 Oct 30 18:24 o1_mf_1_52169_dzfnx5h2_.arc
Note: if archivelogs are not available physically on host restore from backup,
Make sure supplemental log is enabled in db level:
SQL> select supplemental_log_data_min from v$database ;
SUPPLEME
--------
YES
Add archivelogs to logminor:
SQL>execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52160_dzfj78wd_.arc',options => dbms_logmnr.new);SQL>
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52161_dzfk3dx6_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52162_dzfkdmz1_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52163_dzfl20q7_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52164_dzfl27m1_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52165_dzfll46n_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52166_dzfmgqkr_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52167_dzfmr01f_.arc',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
Start logminor:
SQL> execute dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.
Useful logminor views:
V$LOGMNR_DICTIONARY - The dictionary file in use.
V$LOGMNR_PARAMETERS - Current parameter settings for LogMiner.
V$LOGMNR_LOGS - Which redo log files are being analyzed.
V$LOGMNR_CONTENTS - contents of the log files being analyzed.
SQL> select unique operation from v$logmnr_contents;
OPERATION
--------------------------------
DDL
START
COMMIT
SEL_LOB_LOCATOR
SELECT_FOR_UPDATE
ROLLBACK
UPDATE
DELETE
LOB_WRITE
DPI SAVEPOINT
INTERNAL
INSERT
LOB_TRIM
UNSUPPORTED
15 rows selected.
Total operations in db between 5 to 6PM:
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
486382
SQL> select count(*) from v$logmnr_contents where operation='DELETE';
COUNT(*)
----------
63811 ---- Total deletes
SQL> select count(*) from v$logmnr_contents where operation='DDL';
COUNT(*)
----------
28
Check Drop activity in db:
select session_info, sql_undo, sql_redo,username, timestamp from v$logmnr_contents where operation='DDL' and upper(sql_redo) like 'DROP%' order by timestamp desc;
Check TRUNCATE details:
select session_info, sql_redo,username, timestamp from v$logmnr_contents where operation='DDL' and upper(sql_redo) like 'TRUNCATE%' order by timestamp desc;
SESSION_INFO SQL_REDO USERNAME TIMESTAMP
--------------------- ----------- --------------- -------------
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9006D; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9001; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9002; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9005; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9005D; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9006; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9050; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9010; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9020; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9021; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9023; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9025; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9030; PRODCTL 30-oct-2017 17:35:16
login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer TRUNCATE TABLE PRODCTL.F9000; PRODCTL 30-oct-2017 17:35:15
14 rows selected.
from above command we got os user and machine name also db username who performed truncate operation during that time,
we got the details, lets end logminor
SQL> execute dbms_logmnr.end_logmnr;
No comments:
Post a Comment