使用logmnr执行日志挖掘的四个对比实验

长平狐 发布于 2013/09/17 15:10
阅读 365
收藏 0

日志挖掘概念:

结论如下:执行日志挖掘操作的可以使用DBA用户或SYSDBA用户,不能使用普通用户。
日志挖掘可以查看到当前用户自己的操作,也可以查看到其它用户的操作。
其它用户已经执行但未提交的操作,也可以查到。
可以挖掘到其它DBA用户或SYSDBA用户的操作。
-----有些语句的返回提示如下面一句连接后的“Connected.”这种为节约篇幅,删了。不要质疑哈哈。

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://blog.csdn.net/q947817003/article/details/11539737
加载中
返回顶部
顶部