您好,欢迎光临! 请 |

10g utilities:How to use LogMiner II

作者: | 分类:Oracle | Tag: | 评论:1 
字号:T|T

在上一篇日志里:我们了解了怎么使用LogMiner工具的第一种使用方法。接着,我们来学习下LogMiner工具的第二种使用方式:
在使用LogMiner之前,我们要确定相关的参数,也就是简单配置一下LogMiner工具使用的“环境”
①配置一个用于将来存放LogMiner分析日志文件(online redo logs,archived logs)的结果路径和文件,这个路径和文件是映射在文件系统上的。这个文件可以认为是一个字典文件,但是这个字典文件跟数据库的数据字典文件完全是两码子事儿。对应的初始化参数是utl_file_dir,默认情况下,该参数没有配置。
②在调用LogMiner工具之前,在文件系统上生成这个字典文件。
③接下来调用LogMiner工具的方法跟第一种方法就完全类似了。
接下来,进入正式调用流程:
I 首先:以SYS用户登录数据库,检查utl_file_dir初始化参数是否设置,或者设置是否正确,捎带看下数据库版本,以及操作系统版本:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> !uname -a
Linux Oracle10g 2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux

确认初始化参数文件,utl_file_dir,并修改之,重启数据库使之生效,因为该参数不能直接动态修改:

SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
SQL> !mkdir -p /u01/app/logmnr
SQL> alter system set utl_file_dir='/u01/app/logmnr/' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area  343932928 bytes
Fixed Size                  1219304 bytes
Variable Size             125830424 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /u01/app/logmnr/

II 好了,前期准备工作就绪。其次,开始调用dbms_logmnr_d函数包在操作系统生生成"字典文件":

SQL> exec dbms_logmnr_d.build(dictionary_location=>'/u01/app/logmnr/',
dictionary_filename=>'dictionary.ora');
PL/SQL procedure successfully completed.
SQL>

至此,可以到操作系统上对应的路径上去瞅两眼,验证一下:

SQL> !
[oracle@Oracle10g ~]$ cd /u01/app/logmnr/ && ll
total 23724
-rw-r--r--  1 oracle oinstall 24257361 May 24 23:52 dictionary.ora
[oracle@Oracle10g logmnr]$

看到,达到预期目的。
III 然后,查看数据库当前日志文件情况,并开始调用LogMiner工具:

[oracle@Oracle10g logmnr]$ exit
exit
SQL> set linesize 120
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1          7   52428800          1 YES INACTIVE                718138 24-MAY-10
         3          1          9   52428800          1 NO  CURRENT                 758625 24-MAY-10
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1         10   52428800          1 NO  CURRENT                 759039 24-MAY-10
         3          1          9   52428800          1 YES ACTIVE                  758625 24-MAY-10
SQL> col member for a50
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oradata/helen/redo03.log                  NO
         2         ONLINE  /u01/app/oradata/helen/redo02.log                  NO
         1 STALE   ONLINE  /u01/app/oradata/helen/redo01.log                  NO
SQL>

上述操作中,发现当前日志组为第三组,强制日志切换到第二组,并验证日志成员为/u01/app/oradata/helen/redo02.log,这个信息时尤为重要的,因为在下面的操作中,我们要在数据库里另起一个会话,做DML操作,数据库会把日志记录到该日志文件里。这些信息供我们将来分析用。
IV 新开会话,做DML操作,产生日志信息,添加当前日志文件,并开始调用LogMiner分析日志文件:
新开回话:

SQL> show user;
USER is "HR"
SQL> select * from test2;
        ID
----------
         1
         2
SQL> delete from test2 where id=2;
1 row deleted.
SQL> insert into test2 values(3);
1 row created.
SQL>

返回原来的会话,执行下述操作:

SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.removefile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr
(dictfilename=>'/u01/app/logmnr/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> set linesize 160
SQL> col sql_redo for a40
SQL> col sql_undo for a40
SQL> col operation for a10
SQL> col table_name for a5
SQL> select table_name,sql_redo,sql_undo,operation from v$logmnr_contents where username='HR';

TABLE SQL_REDO                                 SQL_UNDO                                 OPERATION
----- ---------------------------------------- ---------------------------------------- ----------
      set transaction read write;                                                       START
TEST2 delete from "HR"."TEST2" where "ID" = '2 insert into "HR"."TEST2"("ID") values (' DELETE
      ' and ROWID = 'AAANB9AAEAAAAGHAAB';      2');

TEST2 insert into "HR"."TEST2"("ID") values (' delete from "HR"."TEST2" where "ID" = '3 INSERT
      3');                                     ' and ROWID = 'AAANB9AAEAAAAGFAAA';

SQL>

至此,达到最终目的。

顶一下
(1)
100%
踩一下
(0)
0%

原创内容,转载时请保留原文信息:
文章标题:10g utilities:How to use LogMiner II
↑上一篇↑:
↓下一篇↓:

1 篇回应 (访客:1 篇, 博主:0 篇)

  1. 路过,看过,留个痕迹,有空再来看看!

    #-49楼

插入图片

NOTICE1:请申请gravatar头像,没有头像的评论可能不会被回复|头像相关帮助!