电力电缆排管:日志挖掘过程
来源:百度文库 编辑:偶看新闻 时间:2024/04/28 07:55:56
工具可以分析出所有对于数据库操作的DML(insert、update、delete等)语句,9i 后可以分
析DDL语句,另外还可分析得到一些必要的回滚SQL语句。其中一个最重要的用途就是不
用全部恢复数据库就可以恢复数据库的某个变化。该工具特别适用于调试、审计或者回退某
个特定的事务。
LogMiner 工具即可以用来分析在线,也可以用来分析离线日志文件,即可以分析本身
自己数据库的重作日志文件,也可以用来分析其他数据库的重作日志文件。
总的说来,LogMiner 工具的主要用途有:
1. 跟踪数据库的变化:可以离线的跟踪数据库的变化,而不会影响在线系统的性能。
2. 回退数据库的变化:回退特定的变化数据,减少point-in-time recovery的执行。
3. 优化和扩容计划:可通过分析日志文件中的数据以分析数据增长模式。
4. 确定数据库的逻辑损坏时间:准确定位操作执行的时间和SCN ==> 基于时间和
SCN 的恢复
5. 确定事务级要执行的精细逻辑恢复操作 //取得相应的UNDO 操作
6. 执行后续审计 //DML DDL DCL 执行时间、用户
注意事项:
1. LogMiner 可以帮你确定在某段时间所发的各种DML,DDL操作的具体时间和SCN
号,它所依据的是归档日志文件及联机日志文件。
2. 它只能在Oracle8i及以后的版本中使用,不过它可以分析Oracle8 的日志。
3. Oracle8i只能用于分析DML操作,到Oracle9i 则可以分析DDL操作了。
4. LogMiner 不支持索引组织表、Long、LOB及集合类型。
5. 不支持MTS的环境
6. LogMiner 必须使用被分析数据库实例产生的字典文件,而不是安装LogMiner 的数
据库产生的字典文件,另外必须保证安装LogMiner 数据库的字符集和被分析数据库的
字符集相同。
7. 源数据库(Source Database)平台必须和分析数据库(Mining Database)平台一样
6.2安装LogMiner
要安装LogMiner 工具,必须首先要运行下面这样两个脚本:
l. $ORACLE_HOME/rdbms/admin/dbmslm.sql
2. $ORACLE_HOME/rdbms/admin/dbmslmd.sql
这两个脚本必须均以SYS 用户身份运行。其中第一个脚本用来创建
DBMS_LOGMNR 包,该包用来分析日志文件。第二个脚本用来创建
DBMS_LOGMNR_D 包,该包用来创建数据字典文件。
6.3基本对象
Source Database: 日志所属的数据库
Mining Database: 执行LogMiner 操作要使用的数据库,相同硬件平台,相同字符集,
版本不低于Source Database
LogMiner字典: 将内部对象ID 号和数据类型转换为对象名和外部数据格式,在Source
Database上生成,有三种方式:
1. 使用源数据库数据字典
// 表结构无变化 S-DB必须OPEN,只能跟踪DML不能为DDL
SQL> exec DBMS_LOGMNR.START_LOGMNR(
OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );
2. 摘取LogMiner 字典到重做日志
// Source-DB 必须OPEN, Archivelog模式
SQL> exec DBMS_LOGMNR_D.BUILD(
OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS );
3. 摘取LogMiner 字典到字典文件
配置字典文件所在目录: 静态参数: UTL_FILE_DIR
建立字典文件:
SQL> exec dbms_logmnr_d.build (
dictionary_filename => 'logminer.dat',
dictionary_location => 'F:\0_WorkSpace\1_Database\1_Oracle\Log_Miner' );
SQL> exec dbms_logmnr_d.build ('dict.ora', '/xxx',
OPTIONS=>DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
与LogMiner相关的数据字典:
v$loghist 显示历史日志文件的一些信息
v$logmnr_dictionary 因logmnr 可以有多个字典文件,显示字典文件信息
v$logmnr_parameters 显示logmnr 的参数
v$logmnr_logs 显示用于分析的日志列表信息
v$logmnr_contents LogMiner结果
6.4使用LogMiner 进行分析
6.4.1 设定用于LogMiner分析的日志文件存放的位置
设置 UTL_FILE_DIR,需要重启数据库。
设置 utl_file_dir=* 表示你能操作任何目录
在initsid.ora文件中加入utl_file_dir 参数或者:
SQL> alter system set utl_file_dir='d:' scope=spfile;
SQL> shutdown immediate
SQL> starup
6.4.2 生成数据字典文件
SQL> BEGIN
dbms_logmnr_d.build(
dictionary_filename => 'logminer_dict.dat',
dictionary_location => 'd:'
);
END;
/
dictionary_location 指的是Logminer 数据字典文件存放的位置,它必须匹配utl_file_dir 的
设定。
dictionary_filename 指的是放于存放位置的字典文件的名字,名字可以任意取。
6.4.3 建立日志分析表
建立日志分析表数据库必须在mount或nomount状态
建立日志分析表,使用dbms_logmnr.add_logfile()
SQL> BEGIN
dbms_logmnr.add_logfile(
ptions => dbms_logmnr.new,
logfilename => 'D:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_07_10\O1_MF_1_2055_47BT0W2G_.ARC'
);
END;
/
其中的options有三种取值,
dbms_logmnr.new 用于建一个日志分析表
dbms_logmnr.addfile 用于加入用于分析的的日志文件
dbms_logmnr.removefile用于移出用于分析的日志文件
6.4.4 添加用于分析的日志文件
SQL> BEGIN
2 dbms_logmnr.add_logfile(
3 ptions => dbms_logmnr.addfile,
4 logfilename => '/u01/arch/arc_ctc_0504.arc'
5 );
6 END;
7 /
6.4.5 启动LogMiner进行分析
SQL> BEGIN
dbms_logmnr.start_logmnr(
dictfilename => 'd:/logminer_dict.dat',
starttime => to_date('20080710 09:15:00','yyyymmdd hh24:mi:ss'),
endtime => to_date('20080710 10:00:00','yyyymmdd hh24:mi:ss')
);
END;
/
即分析2003 年5 月1 日这天12:15 至15:40 这段时间,并把分析结果放到数据字典中
以用于查询。
还有两个参数startscn (起始SCN号) 及endscn (终止SCN) 号。
6.4.6 查看日志分析的结果
查看DML操作:
SELECT operation, sql_redo, sql_undo
FROM V$logmnr_contents WHERE seg_name = 'QIUYB';
OPERATION SQL_REDO SQL_UNDO
---------- -------------------------- --------------------------
INSERT inser into qiuyb.qiuyb ... delete from qiuyb.qiuyb...
其中operation指的是操作,sql_redo指的是实际的操作,sql_undo指的是用于取消的相
反的操作。
查看DDL操作:
SELECT timstamp, sql_redo FROM v$logmnr_contents
WHERE upper(sql_redo) like '%TRUNCATE%';
6.4.7结束LogMiner的分析
SQL>BEGIN
2 dbms_logmnr.end_logmnr();
3 end;
4 /
====================以下是测试===========================
C:\Documents and Settings\pan>sqlplus sys/oracle as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 7月 10 10:31:32 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\dbmslm.sql
程序包已创建。
授权成功。
SQL> @D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\dbmslmd.sql
会话已更改。
PL/SQL 过程已成功完成。
会话已更改。
程序包已创建。
SQL> alter system set utl_file_dir='d:' scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 209715200 bytes
Fixed Size 788188 bytes
Variable Size 162265380 bytes
Database Buffers 46137344 bytes
Redo Buffers 524288 bytes
数据库装载完毕。
数据库已经打开。
SQL> BEGIN
2 dbms_logmnr_d.build(
3 dictionary_filename => 'logminer_dict.dat',
4 dictionary_location => 'd:'
5 );
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 dbms_logmnr.add_logfile(
3 ptions => dbms_logmnr.new,
4 logfilename => 'D:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVE
LOG\2008_07_10\O1_MF_1_2055_47BT0W2G_.ARC'
5 );
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 dbms_logmnr.start_logmnr(
3 dictfilename => 'd:/logminer_dict.dat',
4 starttime => to_date('20080710 09:15:00','yyyymmdd hh24:mi:ss'),
5 endtime => to_date('20080710 10:00:00','yyyymmdd hh24:mi:ss')
6 );
7 END;
8 /
PL/SQL 过程已成功完成。
SQL> SELECT operation, sql_redo, sql_undo from V$logmnr_contents WHERE seg_name
= 'AREA' and rownum < 2;
OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
INSERT
insert into "CHARGE"."AREA"("AREACODE","YZCODE","AREANAME","FULLNAME","PARENTCODE","AREALEVEL","UNIT") values ('1.1.1.3.12','48','12村','1省1市1县3乡12村','1.1.1.3','5','0');
delete from "CHARGE"."AREA" where "AREACODE" = '1.1.1.3.12' and "YZCODE" = '48' and "AREANAME" = '12村' and "FULLNAME" = '1省1市1县3乡12村' and "PARENTCODE" = '1.1.1.3' and "AREALEVEL" = '5' and "UNIT" = '0' and ROWID = 'AAANcVAAHAAAAQiAAw'
OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------;
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL 过程已成功完成。
=========================================================
Logmnr在Oracle 9i以后做了众多的改进,如可以不需要build flat文本文件就可以分析日志了,也就表示可以不需要修改参数utl_file_dir就可以分析日志了,避免了修改参数utl_file_dir导致的重起数据库问题。另外也开始支持把字典信息build到联机日志中,在异地分析归档日志。
在flashback也不能帮上忙的时候,logmnr却是非常有用的。因为只要误操作时期的归档日志存在,就可以通过归档日志来恢复误删除(delete)的数据。
如果在Oracle 9i以上,采用在本地的在线数据字典分析归档日志,就这么简单:
- Piner@9iR2>exec -
- sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',-
- ptions => dbms_logmnr.new);
- PL/SQL procedure successfully completed.
- Piner@9iR2>exec sys.dbms_logmnr.start_logmnr( -
- ptions => sys.dbms_logmnr.dict_from_online_catalog);
- PL/SQL procedure successfully completed.
可以看到,在线分析其实就只需要这两步,添加日志并分析日志。注意以上OPTIONS => DBMS_LOGMNR.NEW,表示添加第一个日志,如需要另外添加更多的日志,可以用如下方式即可。
- Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');
如果日志分析完成,可以把需要的信息保存到临时表,如
- Piner@9iR2>create table tmp_logmnr as
- 2 select operation,sql_redo,sql_undo from v$logmnr_contents
- 3 where seg_name='TEST';
然后,终止日志分析过程。
- Piner@9iR2>exec sys.dbms_logmnr.end_logmnr
对于临时表中的SQL_UNDO,可以选择性的恢复,如采用如下的脚本来恢复,这里为了减少阻塞,每1000条提交一次。另外,需要注意的是,SQL_UNDO中的分号,如果想用动态SQL来执行的话,是需要去掉的。
- declare
- mysqlvarchar2(4000);
- num number :=0;
- begin
- for c_tmp in (select sql_undo from tmp_logmnr where peration = 'DELETE') loop
- --去掉语句中的分号,这里假定只有语句结尾有分号,语句中间并没有分号。
- --如果语句中也有分号,则可以考虑替换语句结尾的;'(分号单引号)为'(单引号)。
- mysql := replace(c_tmp.sql_undo,';','');
- execute immediate mysql;
- num := num + 1;
- if mod(num,1000)=0 then
- commit;
- end if;
- end loop;
- commit;
- exception
- when others then
- --异常处理
- end;
以上的PL/SQL代码其实还可以加强,如恢复成功一条,就设置状态值为1,否则,设置状态值为-1,方便跟踪那些记录恢复成功,哪些记录恢复失败了。
另外,需要注意的是,在Oracle 10g以下,LOGMNR的临时表v$logmnr_contents,使用的是system表空间,在Oracle 10g以后改为sysaux表空间。可以使用如下的命令,更改logmnr的特定表空间,防止system表空间出现空间不够。
----------------------------------------------------------------------
以上的操作太繁琐了,11g开始支持em界面操作日志挖掘了,不过有限制,还需要启用附加日志,alter database add supplemental log data。