日志挖掘概念：
结论如下：执行日志挖掘操作的可以使用DBA用户或SYSDBA用户，不能使用普通用户。
日志挖掘可以查看到当前用户自己的操作，也可以查看到其它用户的操作。
其它用户已经执行但未提交的操作，也可以查到。
可以挖掘到其它DBA用户或SYSDBA用户的操作。
—–有些语句的返回提示如下面一句连接后的“Connected.”这种为节约篇幅，删了。不要质疑哈哈。
对于DDL操作
：对于执行DDL前的操作无法挖掘，但是不报错。只显示DDL语句及DDL之后的DML操作。见：
logmnr挖掘中间有DDL的操作示例
BYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>desc v$logmnr_contents
ERROR:
ORA-04043: object “SYS”.”V_$LOGMNR_CONTENTS” does not exist
证明普通用户不能使用日志挖掘，连这个视图都看不到的。
##############################################
实验1：使用SYSDBA用户对SYS用户进行日志挖掘
在SYSDBA用户下建表插入数据进行日志挖掘。
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390465
SYS@ bys001>create table t(a number);
SYS@ bys001>insert into t values(1);
SYS@ bys001>commit;
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390508
可以直接使用这样一条语句：select a.group#,a.member,b.status from v$logfile a,v$log b where a.group#=b.group#;
SYS@ bys001>select group#,status from v$log;
GROUP# STATUS
———- —————-
1 INACTIVE
2 INACTIVE
3 CURRENT
SYS@ bys001>col member for a50
SYS@ bys001>select group#,member,type from v$logfile;
GROUP# MEMBER TYPE
———- ————————————————– ——-
3 /u01/app/oracle/oradata/bys001/redo03.log ONLINE
2 /u01/app/oracle/oradata/bys001/redo02.log ONLINE
1 /u01/app/oracle/oradata/bys001/redo01.log ONLINE
1 /u01/app/oracle/oradata/bys001/redo01a.log ONLINE
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14390465,endscn =>14390508);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’T’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
DDL
create table t(a number);
INSERT
insert into “SYS”.”T”(“A”) values (‘1’);
delete from “SYS”.”T” where “A” = ‘1’ and ROWID = ‘AAASuWAABAAAVS5AAA’;
#########################################################
实验二：使用SYSDBA用户对普通用户SCOTT下的操作进行挖掘
SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>create table test(a number);
SCOTT@ bys001>select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier —普通用户不能查询当前SCN
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390661
开始插入一条数据。
SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>insert into test values(3);
SCOTT@ bys001>commit;
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14390688
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName =>
‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,
startscn =>14390661,endscn =>14390688);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’TEST’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SCOTT”.”TEST”(“A”) values (‘3’);
delete from “SCOTT”.”TEST” where “A” = ‘3’ and ROWID = ‘AAASuXAAEAAAAlGAAA’;
####################################################
实验三：使用SYSDBA，SCOTT用户插入数据不提交，可以挖掘到相应日志————也证明了commit和写日志的无关性。
这里需要使用两个会话，因为在同一个SQLPLUS会话下，切换用户会引起COMMIT。—–实验得出
会话一：查询出当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391177
会话二：使用SCOTT用户插入一条记录
SCOTT@ bys001>insert into test values(99);
SCOTT@ bys001>select * from test;
A
———-
3
333
99
会话一：记录当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391194
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14391177,endscn =>14391194);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name=’TEST’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SCOTT”.”TEST”(“A”) values (’99’);
delete from “SCOTT”.”TEST” where “A” = ’99’ and ROWID = ‘AAASuXAAEAAAAlGAAC’;
会话二：回滚之前的插入，证明之前的插入确实是未提交的。
SCOTT@ bys001>rollback;
Rollback complete.
SCOTT@ bys001>select * from test;
A
———-
3
333
##########################################################
实验四：使用普通DBA用户可以挖掘出SYS用户的操作
SYS@ bys001>conn bys/bys
Connected.
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391461
BYS@ bys001>conn / as sysdba
SYS@ bys001>insert into t values(9);
SYS@ bys001>commit;
SYS@ bys001>conn bys/bys
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
14391482
BYS@ bys001>execute dbms_logmnr.add_logfile
(LogFileName => ‘/u01/app/oracle/oradata/bys001/redo03.log’,Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
BYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog
,startscn =>14391461,endscn =>14391482);
PL/SQL procedure successfully completed.
BYS@ bys001>select operation,
sql_redo,sql_undo from v$logmnr_contents where table_name=’T’;
OPERATION
——————————–
SQL_REDO
—————————————————————————————————-
SQL_UNDO
—————————————————————————————————-
INSERT
insert into “SYS”.”T”(“A”) values (‘9’);
delete from “SYS”.”T” where “A” = ‘9’ and ROWID = ‘AAASuWAABAAAVS5AAB’;
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.e1idc.net
