sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库日志挖掘logminer使用案例

时间:2016-11-23 17:56   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库日志挖掘工具logminer使用笔记,该笔记介绍使用logminer验证在数据库关闭时日志是否归档的案例。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 使用logminer回答一个网友的问题

一个网友问了一个问题,其实非常的简单,如下:

大师 我想问下 正常关闭库的时候,当前的redo会归档吗? 

可能会可能不会  大师 为什么这么说呢   什么时候会  什么时候不会呢 
我问的是 正常关闭数据库的情况下

为什么我说可能会可能不会呢?其实我们知道在什么情况下oracle会将redo 数据写入archive文件即可。 下面通过实验来说明:
SQL> SELECT name, supplemental_log_data_min FROM v$database;

NAME      SUPPLEME
--------- --------
ROGER     NO

SQL> SHOW parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /home/ora10g/logs

SQL> ALTER DATABASE ADD supplemental log DATA;

DATABASE altered.

SQL> SELECT name, supplemental_log_data_min FROM v$database;

NAME      SUPPLEME
--------- --------
ROGER     YES

SQL> @  /rdbms/admin/dbmslm.SQL    

Package created.

GRANT succeeded.

SQL> @  /rdbms/admin/dbmslmd.SQL

Package created.

SQL> EXEC dbms_logmnr_d.build('logminer_dict.dat','/home/ora10g/logs');

PL/SQL PROCEDURE successfully completed.

SQL> SELECT * FROM v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /home/ora10g/oradata/roger/redo01.log              NO
         3         ONLINE  /home/ora10g/oradata/roger/redo03.log              NO
         2         ONLINE  /home/ora10g/oradata/roger/redo02.log              NO

SQL> SELECT * FROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          4   52428800          1 YES INACTIVE                695360 23-OCT-11
         2          1          5   52428800          1 YES INACTIVE                695362 23-OCT-11
         3          1          6   52428800          1 NO  CURRENT                 695364 23-OCT-11


SQL> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     4
NEXT log SEQUENCE TO archive   6
CURRENT log SEQUENCE           6

SQL> !ls -ltr /home/ora10g/archivelog

total 296
-rw-r-----  1 ora10g oinstall 277504 Oct 23 01:19 1_3_765013258.dbf
-rw-r-----  1 ora10g oinstall   1024 Oct 23 01:19 1_4_765013258.dbf
-rw-r-----  1 ora10g oinstall   2048 Oct 23 01:19 1_5_765013258.dbf

++++++ 另外开一个窗口 ++++++

SQL> conn roger/roger
Connected.

SQL> SELECT COUNT(*) FROM ht1;

  COUNT(*)
----------
   1022976

SQL> DELETE FROM ht1 WHERE rownum <10;

9 ROWS deleted.

SQL> commit;

Commit complete.

++++++ 正常shutdown immediate ++++++

SQL> shutdown immediate;

DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00000: normal, successful completion

SQL> conn /AS sysdba
Connected TO an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE              71304424 bytes
DATABASE Buffers           92274688 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
DATABASE opened.

SQL>  SELECT * FROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          4   52428800          1 YES INACTIVE                695360 23-OCT-11
         2          1          5   52428800          1 YES INACTIVE                695362 23-OCT-11
         3          1          6   52428800          1 NO  CURRENT                 695364 23-OCT-11


SQL> EXEC dbms_logmnr.add_logfile('/home/ora10g/oradata/roger/redo03.log' );
PL/SQL PROCEDURE successfully completed.

SQL> !ls -ltr /home/ora10g/archivelog

total 296
-rw-r-----  1 ora10g oinstall 277504 Oct 23 01:19 1_3_765013258.dbf
-rw-r-----  1 ora10g oinstall   1024 Oct 23 01:19 1_4_765013258.dbf
-rw-r-----  1 ora10g oinstall   2048 Oct 23 01:19 1_5_765013258.dbf

SQL> EXEC dbms_logmnr.add_logfile('/home/ora10g/archivelog/1_3_765013258.dbf' );
PL/SQL PROCEDURE successfully completed.

SQL> EXEC dbms_logmnr.start_logmnr(0,0,'','','/home/ora10g/logs/logminer_dict.dat',0);
PL/SQL PROCEDURE successfully completed.

SQL> col TABLE_NAME FOR a10
SQL> col sql_redo FOR a70
SQL> SET long 99999999

SQL> SELECT log_id,TABLE_NAME,SQL_REDO
  2  FROM v$logmnr_contents
  3  WHERE TABLE_NAME='HT1';

    LOG_ID TABLE_NAME SQL_REDO
---------- ---------- ----------------------------------------------------------------------
         6 HT1        DELETE FROM "ROGER"."HT1" WHERE "OWNER" = 'SYS' AND "OBJECT_NAME" = 'I
                      COL$' AND "SUBOBJECT_NAME" IS NULL AND "OBJECT_ID" = '20' AND "DATA_OB
                      JECT_ID" = '2' AND "OBJECT_TYPE" = 'TABLE' AND "CREATED" = TO_DATE('15
                      -APR-10', 'DD-MON-RR') AND "LAST_DDL_TIME" = TO_DATE('15-APR-10', 'DD-
                      MON-RR') AND "TIMESTAMP" = '2010-04-15:13:14:44' AND "STATUS" = 'VALID
                      ' AND "TEMPORARY" = 'N' AND "GENERATED" = 'N' AND "SECONDARY" = 'N' an
                      d ROWID = 'AAAMpRAAFAAAAAUAAA';

         6 HT1        DELETE FROM "ROGER"."HT1" WHERE "OWNER" = 'SYS' AND "OBJECT_NAME" = 'I
                      _USER1' AND "SUBOBJECT_NAME" IS NULL AND "OBJECT_ID" = '44' AND "DATA_
                      OBJECT_ID" = '44' AND "OBJECT_TYPE" = 'INDEX' AND "CREATED" = TO_DATE(

      ... ... ... ... 省略部分内容

                      OBJECT_ID" = '51' and "OBJECT_TYPE" = 'INDEX' and "CREATED" = TO_DATE(
                      '15-APR-10', 'DD-MON-RR') and "LAST_DDL_TIME" = TO_DATE('15-APR-10', '
                      DD-MON-RR') and "TIMESTAMP" = '2010-04-15:13:14:44' and "STATUS" = 'VA
                      LID' and "TEMPORARY" = 'N' and "GENERATED" = 'N' and "SECONDARY" = 'N'
                       and ROWID = 'AAAMpRAAFAAAAAUAAI';

9 rows selected.
我们可以看到,数据仍在在current redo log中,并未归档。

其实这个问题本身是非常简单的,我们只有明白数据库何时归档,归档有哪些条件就行了。

1. redo log写满以后,switch 到另外一个
2. 手工触发alter system switch logfile等命令


下面来验证下第一个。
SQL> SELECT * FROM v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  /home/ora10g/oradata/roger/redo01.log              NO
         3         ONLINE  /home/ora10g/oradata/roger/redo03.log              NO
         2         ONLINE  /home/ora10g/oradata/roger/redo02.log              NO

SQL> SELECT * FROM v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          4   52428800          1 YES INACTIVE                695360 2Oracleoracleplus.net3-OCT-11
         2          1          5   52428800          1 YES INACTIVE                695362 23-OCT-11
         3          1          6   52428800          1 NO  CURRENT                 695364 23-OCT-11

SQL> conn roger/roger
Connected.

SQL> SELECT bytes/1024/1024
  2  FROM sys.dba_segments
  3  WHERE segment_name='HT1';

BYTES/1024/1024
---------------
            168

Elapsed: 00:00:00.10

SQL> DELETE FROM ht1 WHERE rownum <900000;

899999 ROWS deleted.

Elapsed: 00:01:11.43

SQL> SELECT * FROM v$log;  

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         16   52428800          1 NO  CURRENT                 711575 23-OCT-11
         2          1         14   52428800          1 YES ACTIVE                  708373 23-OCT-11
         3          1         15   52428800          1 YES ACTIVE                  710102 23-OCT-11

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> !ls -ltr /home/ora10g/archivelog

total 501216
-rw-r-----  1 ora10g oinstall   277504 Oct 23 01:19 1_3_765013258.dbf
-rw-r-----  1 ora10g oinstall     1024 Oct 23 01:19 1_4_765013258.dbf
-rw-r-----  1 ora10g oinstall     2048 Oct 23 01:19 1_5_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:58 1_6_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:58 1_7_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:58 1_8_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:58 1_9_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:58 1_10_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:59 1_11_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:59 1_12_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:59 1_13_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:59 1_14_765013258.dbf
-rw-r-----  1 ora10g oinstall 51229184 Oct 23 01:59 1_15_765013258.dbf
可以看到,从log id 6~15 都已经归档。

所以针对该网友的问题,从上面的实验就可以进行很好的回答了,我猜测他可能存在一个误区,以为是shutdown immediate会触发一个完全检查点,然后完全检查点会将数据写入到归档文件中。

这里需要说明一下的是,完全检查点其实就是把cache buffer中的脏数据写入到datafile中,另外会去更新controlfile和datafile header,当然更新的仅仅是SCN。

另外,关于dbms_logminer,还有几个过程,大家可以了解下。
SQL> DESC dbms_logmnr

PROCEDURE ADD_LOGFILE
Argument Name                  TYPE                    IN/OUT DEFAULT
------------------------------ ----------------------- ------ --------
LOGFILENAME                    VARCHAR2                IN
OPTIONS                        BINARY_INTEGER          IN     DEFAULT

FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
Argument Name                  TYPE                    IN/OUT DEFAULT
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
COLUMN_NAME                    VARCHAR2                IN     DEFAULT

PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
Argument Name                  TYPE                    IN/OUT DEFAULT
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO                  NUMBER                  IN     DEFAULT
COLUMN_NAME                    VARCHAR2                IN     DEFAULT

PROCEDURE REMOVE_LOGFILE
Argument Name                  TYPE                    IN/OUT DEFAULT
------------------------------ ----------------------- ------ --------
LOGFILENAME                    VARCHAR2                IN

PROCEDURE START_LOGMNR
Argument Name                  TYPE                    IN/OUT DEFAULT
------------------------------ ----------------------- ------ --------
STARTSCN                       NUMBER                  IN     DEFAULT
ENDSCN                         NUMBER                  IN     DEFAULT
STARTTIME                      DATE                    IN     DEFAULT
ENDTIME                        DATE                    IN     DEFAULT
DICTFILENAME                   VARCHAR2                IN     DEFAULT
OPTIONS                        BINARY_INTEGER          IN     DEFAULT
当然,logminer其实在很多情况下还是非常有用的,我们可以根据条件去进行分析,比如根据时间段,根据scn等等,这不在本文的讨论范围。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库日志挖掘logminer使用案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1313.html

Oracle研究中心

关键词:

Oracle logminer

Oracle日志挖掘使用笔记

logminer验证正常关闭数据库redo是否会归档