首页 > 代码库 > cmds挖掘redolog

cmds挖掘redolog

 
select member from v$logfile;
 
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_11.447.846970727‘,options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>‘/testdata/cmds/redo03.log‘,options=>dbms_logmnr.addfile);
 
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
 
select SESSION#,SERIAL#,to_char(TIMESTAMP,‘MM-DD-YYYY HH24:MI:SS‘),sql_redo,USERNAME,session_info,MACHINE_NAME from v$logmnr_contents where username=‘CMDS‘ and table_name in (‘PURVIEWINFO‘,‘PASSWORDINFO‘,‘PASSWORDSTAT‘) and sql_redo like ‘delete%‘;
 
select count(*) from v$logmnr_contents where username=‘CMDS‘ and table_name = ‘PURVIEWINFO‘ or table_name =‘PASSWORDINFO‘ or table_name = ‘PASSWORDSTAT‘ and sql_redo like ‘delete%‘;
 
 
select SESSION#,SERIAL#,to_char(TIMESTAMP,‘MM-DD-YYYY HH24:MI:SS‘),sql_redo,USERNAME,session_info,MACHINE_NAME from v$logmnr_contents where username=‘CMDS‘ and table_name = ‘PURVIEWINFO‘ or table_name =‘PASSWORDINFO‘ or table_name = ‘PASSWORDSTAT‘ and sql_redo like ‘delete%‘;
 
 
 
exec dbms_logmnr.end_logmnr;
 
show parameter db_recove
exec dbms_logmnr.add_logfile(logfilename=>‘/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_13_7plvx3bc_.arc‘,options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>‘/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_14_7plw0bgo_.arc‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
 
 
 
PASSWORDINFO
PURVIEWINFO
PASSWORDSTAT
 
 
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_10.446.846970723‘,options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_10.261.846970725‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_11.447.846970727‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_11.262.846970727‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_12.448.846970729‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_12.263.846970731‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_13.449.846970731‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_13.264.846970733‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_14.450.846970735‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_14.265.846970735‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_20.451.846970737‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_20.266.846970739‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_21.452.846970739‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_21.267.846970741‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_22.453.846970743‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_22.268.846970745‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_23.454.846970745‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_23.269.846970747‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSDG/cmds/onlinelog/group_24.455.846970749‘,options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/onlinelog/group_24.270.846970749‘,options=>dbms_logmnr.addfile);
 
 
 
 
+CMDSFRADG/cmds/archivelog/2015_07_01/
 
 
exec dbms_logmnr.add_logfile(logfilename=>‘+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32462.319.883888023‘,options=>dbms_logmnr.new);
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32463.2038.883892119
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32464.1135.883892779
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32465.1546.883893831
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32466.986.883896937
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32467.442.883900069
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32468.930.883900309
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32469.688.883900809
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32470.721.883903207
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32471.1910.883903657
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32472.1473.883903945
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32473.1057.883906591
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32474.710.883906817
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32475.363.883907311
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32476.1131.883907315
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32477.1237.883910305
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32478.1653.883911181
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32479.295.883914679
+CMDSFRADG/cmds/archivelog/2015_07_01/thread_2_seq_32480.444.883918717
 
 
 
 
select spid os_sid from v$process where addr = (select paddr from v$session where sid = 1628);
 
 
 
set linesize 200
col machine format a30
col program format a50
col status format a10
col username format a10
select machine,program,username,status from v$session where sid = 4519;
select machine,program,username,status from v$session where sid=1846;
select sql_text from v$sqlarea where (hash_value,address)=(select sql_hash_value,sql_address from v$session where sid=4190);

cmds挖掘redolog