ORACLE LOGMINER的学习

logminer的功能：
logminer用于将日志中对象的ID号和数据类型转变为对象名和外部格式，如果不使用该工具，ORACLE 日志无法被读懂。通过LOGMINER分析数据库日志主要可以完成以下功能：
（1） 精细逻辑恢复操作。通过执行得到的UNDO语句进行相应的事务进行精确恢复。
（2） 优化和扩容计划，可通过分析日志文件中的数据得出数据的增长模式，以此为依据进行优化扩容。
（3） 执行后续审计，通过LOGMINER可以跟踪所有的DML,DDL,DCL操作，能够得到这些操作的时间顺序和执行这些操作的用户信息等。

LOGMINER 基本组成对像：
（1） 源数据库（source database）：被分析日志的所属数据库。
（2） 分析数据库：执行LOGMINER操作要使用的数据库，要求与源库外于相同的硬件平台，版本不低于源数据库且具有相同字符集，分析数据库和源数据库可以是同一数据库。
（3） LOGMINER字典：奖内部对象ID号和数据类型转换为可读的对象名和外部数据格式。

限制： 不能够跨会话， 只在当前会话中生效。

测试：
1.查询SUPPLEMENTAL_LOG_DATA_MIN的模式，如是NO的话就无法查出DML操作的数据，所以要追加日志，将其状态变为YES。
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA；

2. 执行SQL 脚本：
@?/rdbms/admin/dbmslm.sql    //创建DBMS_LOGMNR包
@?/rdbms/admin/dbmslmd.sql   //创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

3.做一个测试语句。
create table test1 as select * from employees;

Table created.

SQL> commit;

Commit complete.

truncate table test1;

Table truncated.

SQL> commit;

Commit complete.

4.建立存储过程。
SQL> execute dbms_logmnr.add_logfile('/home/oracle/oradata/orcl/redo01.log',dbms_logmnr.new); //这里的日志可以是归档也可以是REDO LOG。

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

5. 查询刚刚的CREATE与TRUNCATE操作。
col seg_name for a10
SQL> col username for a10
SQL> col sql_redo for a80
SQL> select seg_name,username,sql_redo from v$logmnr_contents where seg_owner='HR';

下面省略了很多，可以分析得知ORACLE 把create table test1 as select * from employees; 分成了若干份插入的语句。
包括连TRUNCATE的操作也记录了下来。
SEG_NAME   USERNAME
---------- ----------
SQL_REDO
--------------------------------------------------------------------------------
D") values ('195','Vance','Jones','VJONES','650.501.4876',TO_DATE('17-MAR-07', '
DD-MON-RR'),'SH_CLERK','2800',NULL,'123','50');

TEST1      HR
insert into "HR"."TEST1"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_N
UMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_I
D") values ('196','Alana','Walsh','AWALSH','650.507.9811',TO_DATE('24-APR-06', '
DD-MON-RR'),'SH_CLERK','3100',NULL,'124','50');


SEG_NAME   USERNAME
---------- ----------
SQL_REDO
--------------------------------------------------------------------------------
TEST1      HR
insert into "HR"."TEST1"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_N
UMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_I
D") values ('197','Kevin','Feeney','KFEENEY','650.507.9822',TO_DATE('23-MAY-06',
 'DD-MON-RR'),'SH_CLERK','3000',NULL,'124','50');

TEST1      HR
truncate table test1;

v$logmnr_contents
desc v$logmnr_contents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCN                                                NUMBER
 START_SCN                                          NUMBER
 COMMIT_SCN                                         NUMBER
 TIMESTAMP                                          DATE
 START_TIMESTAMP                                    DATE
 COMMIT_TIMESTAMP                                   DATE
 XIDUSN                                             NUMBER
 XIDSLT                                             NUMBER
 XIDSQN                                             NUMBER
 XID                                                RAW(8)
 PXIDUSN                                            NUMBER
 PXIDSLT                                            NUMBER
 PXIDSQN                                            NUMBER
 PXID                                               RAW(8)
 TX_NAME                                            VARCHAR2(256)
 OPERATION                                          VARCHAR2(32)
 OPERATION_CODE                                     NUMBER
 ROLLBACK                                           NUMBER
 SEG_OWNER                                          VARCHAR2(32)
 SEG_NAME                                           VARCHAR2(256)
 TABLE_NAME                                         VARCHAR2(32)
 SEG_TYPE                                           NUMBER
 SEG_TYPE_NAME                                      VARCHAR2(32)
 TABLE_SPACE                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(18)
 USERNAME                                           VARCHAR2(30)
 OS_USERNAME                                        VARCHAR2(4000)
 MACHINE_NAME                                       VARCHAR2(4000)
 AUDIT_SESSIONID                                    NUMBER
 SESSION#                                           NUMBER
 SERIAL#                                            NUMBER
 SESSION_INFO                                       VARCHAR2(4000)
 THREAD#                                            NUMBER
 SEQUENCE#                                          NUMBER
 RBASQN                                             NUMBER
 RBABLK                                             NUMBER
 RBABYTE                                            NUMBER
 UBAFIL                                             NUMBER
 UBABLK                                             NUMBER
 UBAREC                                             NUMBER
 UBASQN                                             NUMBER
 ABS_FILE#                                          NUMBER
 REL_FILE#                                          NUMBER
 DATA_BLK#                                          NUMBER
 DATA_OBJ#                                          NUMBER
 DATA_OBJV#                                         NUMBER
 DATA_OBJD#                                         NUMBER
 SQL_REDO                                           VARCHAR2(4000)
 SQL_UNDO                                           VARCHAR2(4000)
 RS_ID                                              VARCHAR2(32)
 SSN                                                NUMBER
 CSF                                                NUMBER
 INFO                                               VARCHAR2(32)
 STATUS                                             NUMBER
 REDO_VALUE                                         NUMBER
 UNDO_VALUE                                         NUMBER
 SAFE_RESUME_SCN                                    NUMBER
 CSCN                                               NUMBER
 OBJECT_ID                                          RAW(16)
 EDITION_NAME                                       VARCHAR2(30)
 CLIENT_ID                                          VARCHAR2(64)

最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。

SQL> execute dbms_logmnr.end_logmnr;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22740983/viewspace-734632/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22740983/viewspace-734632/