欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle 11g 之LogMiner分析重做日志实践

程序员文章站 2022-06-15 17:54:17
...

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.


oracle 11g 之LogMiner分析重做日志实践

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.

oracle 11g 之LogMiner分析重做日志实践

重启数据库,生效上面的设置。

SQL> shutdown immediate;

SQL> startup;

SQL> show parameters utl_file_dir;

oracle 11g 之LogMiner分析重做日志实践

3.启动补充日志

补充日志不是LogMiner日志分析必须的步骤,但是如果不启用补充日志,分析日志的的很多结果集信息就会显示为“UNKNOWN”,下面是开启最小补充日志。

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

 --------

 YES

oracle 11g 之LogMiner分析重做日志实践

4.创建专门的LogMiner用户

不是必须,实为管理安全方便。

oracle 11g 之LogMiner分析重做日志实践

5.创建数据字典

SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logminer');

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.

oracle 11g 之LogMiner分析重做日志实践

7. 使用字典分析归档日志文件

SQL> EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');

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/logminer/dictionary.ora');

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

oracle 11g 之LogMiner分析重做日志实践

9.关闭LogMiner会话

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;