人资管控数据库日志挖掘&DBA权限

日志挖掘


SQL> @?/rdbms/admin/dbmslm.sql


Package created.




Grant succeeded.


SQL> @?/rdbms/admin/dbmslmd.sql


Package created.


SQL> @?/rdbms/admin/dbmslms.sql


Package created.


No errors.


Grant succeeded.




SQL> exec dbms_logmnr.add_logfile(logfilename=>'/arch1/arch_1_18462_824833441.arc',options=>dbms_logmnr.addfile);


PL/SQL procedure successfully completed.


SQL> exec dbms_logmnr.start_logmnr;


PL/SQL procedure successfully completed.


SQL> select count(*) from v$logmnr_contents;


  COUNT(*)
----------
    838639




v$logmnr_contents看挖掘结果
SQL> select sql_redo from v$logmnr_contents where rownum<=10;


结束挖掘
SQL> exec dbms_logmnr.end_logmnr;


PL/SQL procedure successfully completed.










挖掘结果查到了TABLE_NAEM是OBJ#213139  （OBJECT_ID=213139去dba_objects查表名）
SQL> select table_name ,sql_redo from v$logmnr_contents where rownum<=10;




TABLE_NAME                 SQL_REDO
------------------------------------------------------


OBJ# 213139
update "UNKNOWN"."OBJ# 213139" set "COL 4" = HEXTORAW('30'), "COL 5" = HEXTORAW('30'), "COL 6" = HEXTORAW('6e756c6c'), "COL 7" = HEXTORAW('80'), "COL 8" = HEXTORAW('80'), "COL 9" = HEXTORAW('323031353
0333233313930333332'), "COL 10" = HEXTORAW('312e302e38'), "COL 11" = HEXTORAW('3230313530333230323330343139'), "COL 12" = HEXTORAW('312e302e36') where "COL 4" = HEXTORAW('30') and "COL 5" = HEXTORAW('
30') and "COL 6" = HEXTORAW('6e756c6c') and "COL 7" = HEXTORAW('80') and "COL 8" = HEXTORAW('80') and "COL 9" = HEXTORAW('3230313530333233313930333332') and "COL 10" = HEXTORAW('312e302e38') and "COL
11" = HEXTORAW('3230313530333230323330343139') and "COL 12" = HEXTORAW('312e302e36') and ROWID = 'AAA0CTAABAAAT8GABM';










已知OBJECT_ID到dba_objects查表名
SQL> select object_name from dba_objects where  OBJECT_ID=213139;


OBJECT_NAME
------------------------------
STRATEGY


ORIGINAL




desc v$logmnr_contents


 SESSION#                                                                                                                   
 SERIAL#                                                                                                                    
 USERNAME 执行事物的






dba_role_privs查有dba权限的用户
SQL> select * from sys.dba_role_privs where granted_role='DBA';


GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
JJFL                           DBA                            NO  YES
SYSTEM                         DBA                            YES YES
BUF_HRYW                       DBA                            NO  YES
SG186_ND                       DBA                            NO  YES
HRADS_ERP                      DBA                            NO  YES
PORTALPRODUCT                  DBA                            NO  YES
SYSMAN                         DBA                            NO  YES
SYS                            DBA                            YES YES


8 rows selected.












---------------------------网上资料---------------------------------
dict 


dba_objects


http://blog.csdn.net/liqfyiyi/article/details/6991159


http://www.orasql.com/blog/archives/2013/04/01/104.htm#rd?sukey=cbbc36a2500a2e6cb14dd043ee57c6996f5b93e32e575db75d9e740088ffc02929849aa09af8f9640caec7ca4ff5434a


Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句， 另外还可分析得到一些必要的回滚SQL语句。 
数据的变化过程基本上都在日志里面，在日常问题跟踪，数据恢复等等都可能用到，所以Logminer是每个DBA都应熟悉的工具。 
下面是logminer的使用步骤，具体的分析还需要根据各自的情况灵活应用。 


step 1安装logmnr包 
C:\Documents and Settings\Administrator>sqlplus / as sysdba 


SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 26 09:35:56 2013 


Copyright (c) 1982, 2005, Oracle.  All rights reserved. 




连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
With the Partitioning, OLAP and Data Mining options 


SQL> @?/rdbms/admin/dbmslm.sql 


程序包已创建。 




授权成功。 


SQL> @?/rdbms/admin/dbmslmd.sql 


程序包已创建。 


SQL> @?/rdbms/admin/dbmslms.sql 


程序包已创建。 


没有错误。 


授权成功。 




step2  设置utl_file_dir参数 
SQL> alter system set utl_file_dir='C:\oracle\product\10.2.0\logmnr' scope=both; 


alter system set utl_file_dir='C:\oracle\product\10.2.0\logmnr' scope=both 
                 * 
第 1 行出现错误: 
ORA-02095: 无法修改指定的初始化参数 




SQL> alter system set utl_file_dir='C:\oracle\product\10.2.0\logmnr' scope=spfil 
e; 


系统已更改。 
SQL> startup force; 
ORACLE 例程已经启动。 


Total System Global Area  335544320 bytes 
Fixed Size                  1248888 bytes 
Variable Size             251658632 bytes 
Database Buffers           79691776 bytes 
Redo Buffers                2945024 bytes 
数据库装载完毕。 
数据库已经打开。 
SQL> show parameter utl 


NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
create_stored_outlines               string 
utl_file_dir                         string      C:\oracle\product\10.2.0\logmnr 


step3 创建数据字典 
SQL> exec dbms_logmnr_d.build('dictionary.ora','C:\oracle\product\10.2.0\logmnr' 
); 


PL/SQL 过程已成功完成。 




step4 添加日志 
SQL> exec dbms_logmnr.add_logfile(logfilename=>'C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG\2013_03_26\O1_MF_1_79_8O1YBVHY_.ARC',options=>dbms_logmnr.new); 


PL/SQL 过程已成功完成。 


SQL> exec dbms_logmnr.add_logfile(logfilename=>'C:\oracle\product\10.2.0\flash_r 
ecovery_area\ORCL\ARCHIVELOG\2013_03_26\O1_MF_1_80_8O1ZFNGP_.ARC',options=>dbms_ 
logmnr.addfile); 


PL/SQL 过程已成功完成。 


step5 开始分析 
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'C:\oracle\product\10.2.0\logmn 
r\dictionary.ora'); 


PL/SQL 过程已成功完成。 


step6 查询结果  
SQL> select count(*) from v$logmnr_contents; 


  COUNT(*) 
---------- 
     50464 


由于logmnr session限制，且需要用到SQL Developer查询 v$logmnr_contents，可以create table as select，在新的table中具体分析。 


SQL> create table logmnr_0326 as select * from v$logmnr_contents; 


表已创建。 




step 7 分析后释放内存： 
SQL> exec sys.dbms_logmnr.end_logmnr();