oracle11g⽇志分析,oracle11g之LogMiner分析重做⽇志实践1.安装LogMiner 以DBA⽤户⾝份运⾏下⾯2个脚本： dbmslm.sql⽤来创建DBMS_LOGMNR包，该包⽤来分析⽇志⽂件。 SQL
@$ORACLE_HOME/rdbms/admin/dbmslm.sql;Package created.Grant succeeded.Synonym created. dbmslmd.sql⽤来创建DBMS_LOGMNR_D包，该包⽤来创
1.安装LogMiner
以DBA⽤户⾝份运⾏下⾯2个脚本：
dbmslm.sql⽤来创建DBMS_LOGMNR包，该包⽤来分析⽇志⽂件。
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;
Package created.
Grant succeeded.
Synonym created.
dbmslmd.sql⽤来创建DBMS_LOGMNR_D包，该包⽤来创建数据字典⽂件。
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
Package created.
Synonym created.
2.设置参数UTL_FILE_DIR
添加参数UTL_FILE_DIR，该参数值为服务器中放置数据字典⽂件的⽬录，
SQL> CREATE DIRECTORY utlfile AS '/home/oracle/logminer';
Directory created.
SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;
System altered.
重启数据库，⽣效上⾯的设置。
SQL> shutdown immediate;
SQL> startup;
SQL> show parameters utl_file_dir;
3.启动补充⽇志
补充⽇志不是LogMiner⽇志分析必须的步骤，但是如果不启⽤补充⽇志，分析⽇志的的很多结果集信息就会显⽰为“UNKNOWN”，下⾯是开启最⼩补充⽇志。
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
4.创建专门的LogMiner⽤户
不是必须，实为管理安全⽅便。
5.创建数据字典
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'a', dictionary_location =>'/home/oracle/logminer');oracle11g 创建数据库
6.添加需要分析的在线⽇志或者归档⽇志
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'
/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_199_ckv04o6w_.arc',OPTIONS =>
DBMS_LOGMNR.NEW);
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'
/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_201_ckv08jyp_.arc',OPTIONS =>
DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'
/home/oracle/flash_recovery_area/PRIMARY/archivelog/2016_04_25/o1_mf_1_200_ckv05pmp_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
7. 使⽤字典分析归档⽇志⽂件
SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/a');
8.在线⽇志分析实例
插⼊数据
SQL> insert into nn.t1 values(1000,'succ');
1 row created.
SQL> commit;
Commit complete.
查询当前⽇志序列号
SQL> SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORD ER BY first_change#;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------------- ------------- ---------
1 208 INACTIVE 2825768 25-APR-16
2 209 INACTIVE 2825872 25-APR-16
3 210 CURRENT 2845771 25-APR-16
加⼊当前⽇志组
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/oradata/orcl/redo03.log',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
使⽤LogMiner进⾏分析
SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/a');
PL/SQL procedure successfully completed.
查询分析结果
SQL> SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='T1';
SQL_REDO SQL_UNDO SEG_OWNER
insert into "NN"."T1"("TID","TNAME") values ('1000','succ');
delete from "NN"."T1" where "TID" = '1000' and "TNAME" = 'succ' and ROWID = 'AAAR7YAAEAAAACrAAD';
NN
9.关闭LogMiner会话
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR; 本条技术⽂章来源于互联⽹，如果⽆意侵犯您的权益请点击此处反馈版权投诉 本⽂系统来源：php中⽂⽹

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。