LogMiner实验手册

1.修改系统参数文件
alter system set utl_file_dir='f:\share\' scope=spfile;
2.关闭数据库
shutdown immediate;
3.启动数据库
startup;
4.将数据字典提取到平面数据字典文件
execute dbms_logmnr_d.build('logmnr_dict.ora','f:\share\',options=>DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
5.指定要分析的重做日志文件
语法格式：execute dbms_logmnr.add_logfile('LogFileName',Options); Options=>dbms_logmnr.NEW ADDFILE REMOVEFILE
execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO01.LOG',Options=>dbms_logmnr.NEW);
execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO02.LOG',Options=>dbms_logmnr.ADDFILE);
execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO03.LOG',Options=>dbms_logmnr.ADDFILE);

5.查询要分析的重做日志文件
SQL> desc v$logmnr_logs
Name                                      Null?    Type
----------------------------------------- -------- -----------------

LOG_ID                                             NUMBER
FILENAME                                           VARCHAR2(512)
LOW_TIME                                           DATE
HIGH_TIME                                          DATE
DB_ID                                              NUMBER
DB_NAME                                            VARCHAR2(8)
RESET_SCN                                          NUMBER
RESET_SCN_TIME                                     DATE
THREAD_ID                                          NUMBER
THREAD_SQN                                         NUMBER
LOW_SCN                                            NUMBER
NEXT_SCN                                           NUMBER
DICTIONARY_BEGIN                                   VARCHAR2(3)
DICTIONARY_END                                     VARCHAR2(3)
TYPE                                               VARCHAR2(7)
BLOCKSIZE                                          NUMBER
FILESIZE                                           NUMBER
INFO                                               VARCHAR2(32)
STATUS                                             NUMBER

SQL> select filename from v$logmnr_logs;

FILENAME
-----------------------------------------------------

D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO02.LOG
D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO03.LOG
D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO01.LOG

6.启动LogMiner会话
语法格式：execute DBMS_LOGMNR.start_logmnr(startScn,endScn,DictFileName,Options);
或execute DBMS_LOGMNR.start_logmnr('startTime','endTime',DictFileName,Options);
execute dbms_logmnr.start_logmnr(DictFileName=>'F:\share\logmnr_dict.ora');

execute dbms_logmnr.start_logmnr(Options=>dbms_logmnr.NO_ROWID_IN_STMT + dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

7.以上准备工作于基础知识看完，应该进行实例分析了


a.创建数据表与添加实验数据
SQL> create table mytest(myid int,name varchar(15),constraint pk_mytest primary key(myid) validate);
SQL> desc mytest;
Name                                      Null?    Type
----------------------------------------- -------- ------------

MYID                                      NOT NULL NUMBER(38)
NAME                                               VARCHAR2(15)

SQL> insert into mytest(myid,name) values(1,'fistname');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into mytest(myid,name) values(2,'secondname');

1 row created.

SQL> insert into mytest(myid,name) values(3,'thirdname');

1 row created.

SQL> delete from mytest where myid=3;

1 row deleted.

SQL> commit;

Commit complete.

execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO01.LOG',Options=>dbms_logmnr.NEW);
execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO02.LOG',Options=>dbms_logmnr.ADDFILE);
execute dbms_logmnr.add_logfile('D:\oracle\product\10.1.0\oradata\zhuhaidb\REDO03.LOG',Options=>dbms_logmnr.ADDFILE);
select filename from v$logmnr_logs;

SQL> execute dbms_logmnr.start_logmnr(Options=>dbms_logmnr.NO_ROWID_IN_STMT + dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> desc v$logmnr_contents;

select sql_redo,sql_undo from v$logmnr_contents where seg_name='mytest';