189 8069 5689
作为Oracle DBA 我们有时候需要追踪数据误删除或用户的恶意操作情况 此时我们不仅需要查出执行这些操作的数据库账号 还需要知道操作是由哪台客户端（IP地址等）发出的 针对这些问题 一个最有效实用而又低成本的方法就是分析Oracle数据库的日志文件 本文将就Oracle日志分析技术做深入探讨 一 如何分析即LogMiner解释 从目前来看 分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner来进行 Oracle数据库的所有更改都记录在日志中 但是原始的日志信息我们根本无法看懂 而LogMiner就是让我们看懂日志信息的工具 从这一点上看 它和tkprof差不多 一个是用来分析日志信息 一个则是格式化跟踪文件 通过对日志的分析我们可以实现下面的目的 查明数据库的逻辑更改 侦察并更正用户的误操作 执行事后审计 执行变化分析 不仅如此 日志中记录的信息还包括 数据库的更改历史 更改类型（INSERT UPDATE DELETE DDL等） 更改对应的SCN号 以及执行这些操作的用户信息等 LogMiner在分析日志时 将重构等价的SQL语句和UNDO语句（分别记录在V$LOGMNR_CONTENTS视图的SQL_REDO和SQL_UNDO中） 这里需要注意的是等价语句 而并非原始SQL语句 例如 我们最初执行的是 delete a where c cyx ; 而LogMiner重构的是等价的 条DELETE语句 所以我们应该意识到V$LOGMNR_CONTENTS视图中显示的并非是原版的现实 从数据库角度来讲这是很容易理解的 它记录的是元操作 因为同样是 delete a where c cyx ; 语句 在不同的环境中 实际删除的记录数可能各不相同 因此记录这样的语句实际上并没有什么实际意义 LogMiner重构的是在实际情况下转化成元操作的多个单条语句 另外由于Oracle重做日志中记录的并非原始的对象（如表以及其中的列）名称 而只是它们在Oracle数据库中的内部编号（对于表来说是它们在数据库中的对象ID 而对于表中的列来说 对应的则是该列在表中的排列序号 COL COL 等） 因此为了使LogMiner重构出的SQL语句易于识别 我们需要将这些编号转化成相应的名称 这就需要用到数据字典（也就说LogMiner本身是可以不用数据字典的 详见下面的分析过程） LogMiner利用DBMS_LOGMNR_D BUILD()过程来提取数据字典信息 LogMiner包含两个PL/SQL包和几个视图 dbms_logmnr_d包 这个包只包括一个用于提取数据字典信息的过程 即dbms_logmnr_d build()过程 dbms_logmnr包 它有三个过程 add_logfile(name varchar options number) 用来添加/删除用于分析的日志文件 start_logmnr(start_scn number end_scn number start_time number end_time number dictfilename varchar options number) 用来开启日志分析 同时确定分析的时间/SCN窗口以及确认是否使用提取出来的数据字典信息 end_logmnr() 用来终止分析会话 它将回收LogMiner所占用的内存 与LogMiner相关的数据字典 v$logmnr_dictionary LogMiner可能使用的数据字典信息 因logmnr可以有多个字典文件 该视图用于显示这方面信息 v$logmnr_parameters 当前LogMiner所设定的参数信息 v$logmnr_logs 当前用于分析的日志列表 v$logmnr_contents 日志分析结果 二 Oracle i LogMiner的增强 支持更多数据/存储类型 链接/迁移行 CLUSTER表操作 DIRECT PATH插入以及DDL操作 在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL操作的原句（CREATE USER除外 其中的密码将以加密的形式出现 而不是原始密码） 如果TX_AUDITING初始化参数设为TRUE 则所有操作的数据库账号将被记录 提取和使用数据字典的选项 现在数据字典不仅可以提取到一个外部文件中 还可以直接提取到重做日志流中 它在日志流中提供了操作当时的数据字典快照 这样就可以实现离线分析 允许对DML操作按事务进行分组 可以在START_LOGMNR()中设置MITTED_DATA_ONLY选项 实现对DML操作的分组 这样将按SCN的顺序返回已经提交的事务 支持SCHEMA的变化 在数据库打开的状态下 如果使用了LogMiner的DDL_DICT_TRACKING选项 Oracle i的LogMiner将自动对比最初的日志流和当前系统的数据字典 并返回正确的DDL语句 并且会自动侦察并标记当前数据字典和最初日志流之间的差别 这样即使最初日志流中所涉及的表已经被更改或者根本已经不存在 LogMiner同样会返回正确的DDL语句 在日志中记录更多列信息的能力 例如对于UPDATE操作不仅会记录被更新行的情况 还可以捕捉更多前影信息 支持基于数值的查询 Oracle i LogMiner在支持原有基于元数据（操作 对象等）查询的基础上 开始支持基于实际涉及到的数据的查询 例如涉及一个工资表 现在我们可以很容易地查出员工工资由 变成 的原始更新语句 而在之前我们只能选出所有的更新语句 三 Oracle i/ i的日志分析过程 LogMiner只要在实例起来的情况下都可以运行 LogMiner使用一个字典文件来实现Oracle内部对象名称的转换 如果没有这个字典文件 则直接显示内部对象编号 例如我们执行下面的语句 delete from C A where C = gototop and ROWID = AAABg AAFAAABQaAAH ;如果没有字典文件 LogMiner分析出来的结果将是 delete from UNKNOWN OBJ# where COL = HEXTORAW( d a d ae ) and ROWID = AAABg AAFAAABQaAAH ; 如果想要使用字典文件 数据库至少应该出于MOUNT状态 然后执行dbms_logmnr_d build过程将数据字典信息提取到一个外部文件中 下面是具体分析步骤 确认设置了初始化参数 UTL_FILE_DIR 并确认Oracle对改目录拥有读写权限 然后启动实例 示例中UTL_FILE_DIR参数如下 SQL show parameter utlNAME TYPEvalue utl_file_dir string /data /cyx/logmnr 这个目录主要用于存放dbms_logmnr_d build过程所产生的字典信息文件 如果不用这个 则可以不设 也就跳过下面一步 生成字典信息文件 exec dbms_logmnr_d build(dictionary_filename = dic ora dictionary_location = /data /cyx/logmnr ); 其中dictionary_location指的是字典信息文件的存放位置 它必须完全匹配UTL_FILE_DIR的值 例如 假设UTL_FILE_DIR=/data /cyx/logmnr/ 则上面这条语句会出错 只因为UTL_FILE_DIR后面多了一个 / 而在很多其它地方对这一 / 是不敏感的 dictionary_filename指的是放于字典信息文件的名字 可以任意取 当然我们也可以不明确写出这两个选项 即写成 exec dbms_logmnr_d build( dic ora /data /cyx/logmnr ); 如果你第一步的参数没有设 而直接开始这一步 Oracle会报下面的错误 ERROR at line :ORA : initialization parameter utl_file_dir is not setORA : at SYS DBMS_LOGMNR_D line ORA : at SYS DBMS_LOGMNR_D line ORA : at line 需要注意的是 在oracle for Windows版中会出现以下错误 : : SQL execute dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log );BEGIN dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log ); END;*ERROR at line :ORA : Subscript outside of limitORA : at SYS DBMS_LOGMNR_D line ORA : at line 解决办法 编辑 $ORACLE_HOME/rdbms/admindbmslmd sql 文件 把其中的TYPE col_desc_array IS VARRAY( ) OF col_description;改成 TYPE col_desc_array IS VARRAY( ) OF col_description; 保存文件 然后执行一遍这个脚本 : : SQL @c:\oracle\ora \rdbms\admin\dbmslmd sqlPackage created Package body created No errors Grant succeeded 然后重新编译DBMS_LOGMNR_D包 : : SQL alter package DBMS_LOGMNR_D pile body;Package body altered 之后重新执行dbms_logmnr_d build即可 : : SQL execute dbms_logmnr_d build( oradict ora c:\oracle\admin\ora\log );PL/SQL procedure successfully pleted 添加需要分析的日志文件 SQLexec dbms_logmnr add_logfile( logfilename= /data /cyx/rac arch/arch_ _ arc options=dbms_logmnr new);PL/SQL procedure successfully pleted 这里的options选项有三个参数可以用 NEW 表示创建一个新的日志文件列表 ADDFILE 表示向这个列表中添加日志文件 如下面的例子 REMOV lishixinzhi/Article/program/Oracle/201311/18949
目前创新互联已为千余家的企业提供了网站建设、域名、网络空间、绵阳服务器托管、企业网站设计、库伦网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
四 如何利用LogMiner分析Oracle 的日志文件 虽然说LogMiner是Oracle i才推出来 但我们同样可以用它来分析Oracle 的日志文件 只不过稍微麻烦了一点 并且有一定的限制 下面是具体做法 我们首先复制Oracle i的$ORACLE_HOME/rdbms/admin/dbmslmd sql脚本到Oracle 数据库所在主机的同样目录 这个脚本用于创建dbms_logmnr_d包（注意 Oracle i中还将创建dbms_logmnr包） 如果是 脚本名字为dbmslogmnrd sql 然后在Oracle 的数据库上运行这个脚本 之后使用dbms_logmnr_d build过程创建字典信息文件 现在我们就可以把Oracle 的归档日志连同这个字典信息文件复制到Oracle i数据库所在的主机上 之后在Oracle i数据库中从上面分析过程的第三步开始分析Oracle 的日志 不过 dbms_logmnr start_logmnr()中使用的是Oracle 的字典信息文件 按照我前面所说的那样 如果不是字典文件 我们则可以直接将Oracle 的归档日志复制到Oracle i数据库所在主机 然后对它进行分析 其实这里涉及到了一个跨平台使用LogMiner的问题 笔者做过试验 也可以在Oracle i中来分析Oracle i的日志 但这些都是有所限制的 主要表现在 LogMiner所使用的字典文件必须和所分析的日志文件是同一个数据库所产生的 并且该数据库的字符集应和执行LogMiner数据库的相同 这很好理解 如果不是同一个数据库所产生就不存在对应关系了 生成日志的数据库硬件平台和执行LogMiner数据库的硬件平台要求一致 操作系统版本可以不一致 笔者做试验时（如果读者有兴趣可以到我网站上下载试验全过程 因为太长就不放在这里了） 所用的两个数据库操作系统都是Tru UNIX 但一个是 V A 另一个则是V F 如果操作系统不一致则会出现下面的错误 ORA : file /data /cyx/logmnr/arch_ _ arc cannot be openedORA : cannot open archived log /data /cyx/logmnr/arch_ _ arc ORA : skgfifi: file header information is invalidORA : at SYS DBMS_LOGMNR line ORA : at line 五 分析v$logmnr_contents 前面我们已经知道了LogMiner的分析结果是放在v$logmnr_contents中 这里面有很多信息 我们可以根据需要追踪我们感兴趣的信息 那么我们通常感兴趣的有哪些呢？ 追踪数据库结构变化情况 即DDL操作 如前所述 这个只有Oracle i才支持 SQL select timestamp sql_redo from v$logmnr_contents where upper(sql_redo) like %CREATE% ;TIMESTAMP SQL_REDO : : create table t (c number); 追踪用户误操作或恶意操作 例如我们现实中有这样需求 有一次我们发现一位员工通过程序修改了业务数据库信息 把部分电话的收费类型改成免费了 现在就要求我们从数据库中查出到底是谁干的这件事？怎么查？LogMiner提供了我们分析日志文件的手段 其中v$logmnr_contents的SESSION_INFO列包含了下面的信息 login_username=NEW_ client_info= OS_username=oracle Machine_name=phoenix OS_terminal=ttyp OS_process_id= OS_program name=sqlplus@phoenix (TNS V V ) 虽然其中信息已经很多了 但在我们的业务数据库中 程序是通过相同的login_username登录数据库的 这样单从上面的信息是很难判断的 不过我们注意到 因为公司应用服务器不是每个人都有权限在上面写程序的 一般恶意程序都是直接通过他自己的PC连到数据库的 这就需要一个准确的定位 IP追踪是我们首先想到的 并且也满足我们的实际要求 因为公司内部IP地址分配是统一管理的 能追踪到IP地址我们就可以准确定位了 但从面的SESSION_INFO中我们并不能直接看到IP 不过我们还是有办法的 因为这个SESSION_INFO里面的内容其实是日志从V$SESSION视图里提取的 我们可以在生产数据库中创建一个追踪客户端IP地址的触发器 create or replace trigger on_logon_triggerafter logon on databasebegin dbms_application_info set_client_info(sys_context( userenv ip_address ));end;/现在 我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了 那么上面的提出的问题就可以迎刃而解了 假如被更新的表名为HMLX 我们就可以通过下面的SQL来找到所需信息 SQL select session_info sql_redo from v$logmnr_contents where upper(operation) = UPDATE and upper(sql_redo) like %HMLX% /SESSION_INFO SQL_REDO login_username=C client_info= OS_username=sz xjs chengyx Machine_name=GDTEL\SZ XJS CHENGYXupdate C HMLX set NAME = free where NAME = and ROWID = AAABhTAAFAAABRaAAE ; lishixinzhi/Article/program/Oracle/201311/17810
1、因为oracle运行在Linux系统下，首先，要连接Linux系统。
2、切换到oracle安装用户下。 我的用户是 oracle。
3、运行oracle的环境变量，在oracle 的根目录下面，运行 soruce .bash_prfile 命令， 以便 输入相关命令。
4、运行命令： cd $ORACLE_HOME 进入oracle的安装目录。
5、在此输入命令： find -name listener.log ，查找监控日志文件。
6、运行命令 cd 到查看到的日志文件目录。
7、运行cat listener.log命令 查看日志文件。
logminer作为相关的日志分析工具集成与oracle中，我们可通过该工具清楚的分析重做相关日志和归档日志中的所有事物变化，并且可以准确的确定各种DML和DDL操作的具体时间和SCN值。
通过logminer我们可以实现：
1，确定数据的逻辑损坏的时间
2，跟踪用户执行的事务变化操作
3，跟踪表的DML操作
如果我们要分析归档日志，我们首先修改oracle归档日志的模式，我们要把默认的归档路径改成我们自己的路径：
start mount;
alter database archivelog;
alter database open;
alter system set log_archive_dest_1='location=d:\oracle' scope=spfile;
alter system set log_archive_format='arch_%t_%s_%r.trc' scope=spfile;
查看我们修改过的归档路径：
archive log list;
查看归档日志：
select name,dest_id from v$archived_log;
如果查询的没有更改，我们需要重启一下数据库
安装logminer，安装logminer需要我们安装下面的几个包：
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
$ORACLE_HOME/rdbms/admin/dbmslms.sql
这几个脚本必须是sys用户运行
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql
@$ORACLE_HOME/rdbms/admin/dbmslms.sql
添加数据字典，需要添加参数utl_file_dir，
alter system set utl_file_dir='/home/oracle/dir' scope=spfile;
添加supplement logging
首先查看
select name,supplemental_log_data_min from v$database;是否是yes
YES为打开状态，会记录session_info,username等信息
NO为关闭状态，不会记录sesion_info,username等信息
添加
alter database add supplemental log data;
关闭
alter database drop supplemental log data;
重启数据库，这样我们刚才的两个参数就会生效；
查看数据字典：
show parameter utl;
添加数据字典：
SQL begin
2 dbms_logmnr_d.build(
3 dictionary_filename='logminer_dict.dat',
4 dictionary_location='/home/oracle/logminer');
5 end;
6 /
PL/SQL procedure successfully completed.
或是：
execute dbms_logmnr_d.build(dictionary_filename='logminer_dict.dat',dictionary_location='/home/oracle/logminer');
创建登录触发器：
SQL create or replace trigger on_logon_tigger
2 after logon on database
3 begin
4 dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
5 end;
6 /
Trigger created.
我们就可以在V$SESSION视图的CLIENT_INFO列中看到新登录的客户端IP地址了。那么现在就可以在
添加要分析的归档日志文件
SQL begin
2 dbms_logmnr.add_logfile(
3 logfilename='/home/oracle/arch/arch_6_758944049_1.trc',options=dbms_logmnr.new);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL begin
2 dbms_logmnr.add_logfile(
3 logfilename='/home/oracle/arch/arch_7_758944049_1.trc',
4 options=dbms_logmnr.addfile);
5 end;
6 /
PL/SQL procedure successfully completed.
切换归档日志：
alter system switch logfile;
开启分析：
execute dmbs_logmnr.start_logmnr(dictfilename='/home/oracle/logminer/logminer_dict.dat');
或是:
execute dbms_logmnr.start_logmnr;
查询归档日志：
SQL select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/home/oracle/arch/arch_6_758944049_1.trc
/home/oracle/arch/arch_7_758944049_1.trc
/home/oracle/arch/arch_8_758944049_1.trc
为了节约pga的空间，当我们分析完日志后，移除不需要的日志：
SQL begin
2 dbms_logmnr.add_logfile(
3 logfilename='/home/oracle/arch/arch_7_758944049_1.trc',
4 options=dbms_logmnr.removefile);
5 end;
6 /
PL/SQL procedure successfully completed.
查询结果在v$logmnr_contents;
查询数据库上面的操作
select scn,sql_redo,timestamp from v$logmnr.contents;
关闭分析
execute dbms_logmnr.stop_logmnr;
查询的时候最好使用plsql查询。
使用job的所有者账号登陆，通过查询user_jobs视图查看；
我一般只关注三个字段；
LAST_DATE：最后次运行时间；
NEXT_DATE：下次运行时间；
TOTAL_TIME：总用时。具体语句如下：select s.LAST_DATE,s.NEXT_DATE,s.TOTAL_TIME from user_jobss。
日志
日志，是一个汉语词汇，汉语拼音是rì zhì。基本字义是指工作日志。日志主要发表在网络，详细介绍一个过程和经历的记录。
环境：
AIX6.1
Oracle 11g RAC
故障：
数据库频繁出现归档日志空间不够，导致数据库无法登陆的故障。一查发现原因是归档日志切换频繁，操作系统空间不够。
确定原因：
[aix01@oracle]/oracledf -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 0.50 0.28 44% 13674 17% /
/dev/hd2 3.00 0.67 78% 49208 23% /usr
/dev/hd9var 1.00 0.37 63% 9285 10% /var
/dev/hd3 2.00 1.03 49% 2407 1% /tmp
/dev/fwdump 1.00 0.99 2% 30 1% /var/adm/ras/platform
/dev/hd1 0.25 0.18 28% 465 2% /home
/dev/hd11admin 0.25 0.25 1% 5 1% /admin
/proc - - - - - /proc
/dev/hd10opt 0.50 0.28 44% 10241 14% /opt
/dev/livedump 0.25 0.25 1% 12 1% /var/adm/ras/livedump
/dev/oraclelv 30.00 11.29 63% 161681 6% /oracle
/dev/installlv 15.00 3.38 78% 6478 1% /install
/dev/crslv 10.00 3.35 67% 7807 1% /crs
/dev/wmsapplv 30.00 17.49 42% 15537 1% /wmprod
/dev/archivelv 29.25 29.25 1% 4 1% /arch1
/dev/backuplv 400.00 107.13 74% 306 1% /sysbackup
aix02:arch2 30.25 0.64 99% 3 1% /arch2
可以看到，/arch2里文件系统空间已经达到99%，/arch2是用来存放归档日志的文件系统，进而导致数据库出错。
提出问题：
这下问题来了，/arch2的空间是30G，每天备份脚本都会自动rman备份归档日志，并自动清除归档日志文件，按照正常情况下，数据库不可能一天产生这么大的归档日志量。
如何查询归档日志都是由什么应用产生的，这就是logminer的用途。
使用方法：
-- 1.指定要分析的日志文件
exec sys.dbms_logmnr.add_logfile(logfilename = '/arch2/2_825_733092736.dbf',options = dbms_logmnr.new);
-- 2.使用本地的在线数据字典分析归档日志
exec sys.dbms_logmnr.start_logmnr(options = sys.dbms_logmnr.dict_from_online_catalog);
-- 3.查询分析出来的归档日志内容，例如统计最大修改量的Schema
select seg_owner,count(*) from v$logmnr_contents group by seg_owner;
-- 4.增加别的日志文件
exec sys.dbms_logmnr.add_logfile(logfilename='/arch2/2_825_733092736.dbf');
-- 5.结束分析归档日志
exec sys.dbms_logmnr.end_logmnr;
下面是具体的过程：
SQL exec sys.dbms_logmnr.add_logfile(logfilename = '/arch2/2_825_733092736.dbf',options = dbms_logmnr.new);
PL/SQL procedure successfully completed
SQL exec sys.dbms_logmnr.start_logmnr(options = sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed
SQL select seg_owner,count(*) from v$logmnr_contents group by seg_owner;
SEG_OWNER COUNT(*)
-------------------------------- ----------
2237
SYS 688
TMS 60
SPHSY 70
SINOSYNEW 30
SINOSY 381
WAS 4551934
7 rows selected
SQL execute dbms_logmnr.end_logmnr ;
PL/SQL procedure successfully completed
结论：
从上面查询结果可以看出操作量最大的用户是WAS用户，再具体看下v$logmnr_contents可以发现基本修改的内容是一致的。
与开发人员沟通后，最终确认是一个执行update过程存在问题，where条件未正确定位到记录，每执行一次都会导致大规模的修改数据。