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';
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';