做日志分析（log MNR）
第一步：修改初始参数文件
修改utl_file_dir路径
第二步：重新启动数据库
第三步：建立目录文件
建立目录dbms_logmnr_d.build
第四步：添加或移动日志文件
dbms_logmnr.addfile
第五步：启动日志挖掘
dbms_logmnr.start_logmnr
第六步：分析日志结果查询
v$logmnr_content--sqlredo/sqlundo两个内容非常重要

--------

1、连接数据库
C:\>sqlplus "/ as sysdba"

2、修改参数
SQL> alter system set utl_file_dir='D:\oracle\oradata\log'scope =spfile;
系统已更改。

3、重新启动数据库使参数生效
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  167772160 bytes
Fixed Size                  1247876 bytes
Variable Size              83887484 bytes
Database Buffers           75497472 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
数据库已经打开。

4、准备数据
SQL> create table atest as select ts#,name from v$tablespace;
表已创建。

SQL> insert into atest select * from atest;
已创建11行。

SQL> /
已创建22行。

SQL> /
已创建44行。

SQL> commit;
提交完成。

5、查看当前日志文件，切换日志文件：
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS              
---------- ---------- ---------- ---------- ---------- --- ----------------    
FIRST_CHANGE# FIRST_TIME                                                       
------------- ----------                                                       
         1          1         20    5242880          1 YES INACTIVE            
       451531 12-12月-07                                                       
                                                                               
         2          1         21    5242880          1 YES INACTIVE            
       460030 12-12月-07                                                       
                                                                               
         3          1         22    5242880          1 NO  CURRENT             
       473938 12-12月-07                                                       

切换日志文件：

SQL> alter system switch logfile;
系统已更改。

6、创建目录文件
SQL>execute dbms_logmnr_d.build('redo.ora','D:\oracle\oradata\log');
PL/SQL 过程已成功完成

7、加入需要分析的日志文件
SQL> execute dbms_logmnr.add_logfile('D:\oracle\oradata\an92\REDO03.LOG',dbms_logmnr.new);
PL/SQL 过程已成功完成。

8、进行日志分析
SQL> execute dbms_logmnr.start_logmnr(DICTFILENAME=>'D:\oracle\oradata\log\redo.ora');
PL/SQL 过程已成功完成。

9、查看动态性能视图v$logmnr_contents
SQL> desc v$logmnr_contents;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SCN                                                NUMBER
 CSCN                                               NUMBER
 TIMESTAMP                                          DATE
 COMMIT_TIMESTAMP                                   DATE
 THREAD#                                            NUMBER
 LOG_ID                                             NUMBER
 XIDUSN                                             NUMBER
 XIDSLT                                             NUMBER
 XIDSQN                                             NUMBER
 PXIDUSN                                            NUMBER
 PXIDSLT                                            NUMBER
 PXIDSQN                                            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_OBJD#                                         NUMBER
 SEG_OWNER                                          VARCHAR2(32)
 SEG_NAME                                           VARCHAR2(256)
 SEG_TYPE                                           NUMBER
 SEG_TYPE_NAME                                      VARCHAR2(32)
 TABLE_SPACE                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 SESSION#                                           NUMBER
 SERIAL#                                            NUMBER
 USERNAME                                           VARCHAR2(30)
 SESSION_INFO                                       VARCHAR2(4000)
 TX_NAME                                            VARCHAR2(256)
 ROLLBACK                                           NUMBER
 OPERATION                                          VARCHAR2(32)
 OPERATION_CODE                                     NUMBER
 SQL_REDO                                           VARCHAR2(4000)
 SQL_UNDO                                           VARCHAR2(4000)
 RS_ID                                              VARCHAR2(32)
 SEQUENCE#                                          NUMBER
 SSN                                                NUMBER
 CSF                                                NUMBER
 INFO                                               VARCHAR2(32)
 STATUS                                             NUMBER
 REDO_VALUE                                         RAW(4)
 UNDO_VALUE                                         RAW(4)
 SQL_COLUMN_TYPE                                    VARCHAR2(32)
 SQL_COLUMN_NAME                                    VARCHAR2(32)
 REDO_LENGTH                                        NUMBER
 REDO_OFFSET                                        NUMBER
 UNDO_LENGTH                                        NUMBER
 UNDO_OFFSET                                        NUMBER

其中：
SESSION_INFO：用户会话信息，包括登录用户名，客户端机器名等。
SQL_REDO：已经处理的SQL语句，不止是用户发出的，还包括database的处理信息。
SQL_UNDO：对应SQL_REDO的反操作的SQL语句，如果发现SQL_REDO的操作是错误的，可以利用这条语句来恢复。

<注意>：DDL 语句是没有反操作的。

10、查看日志分析出的内容

SQL>select SESSION_INFO,SQL_REDO,SQL_UNDO from v$logmnr_contents where username='AN' and operAtION in ('DDL','UPDATE','INSERT','DELETE');

11、停止分析
SQL>execute dbms_logmnr.end_logmnr();
PL/SQL 过程已成功完成。