LOGMNR首先執行一個ddl（或dml）操作，以記錄重做信息：SYS@ ora11g>conn scott/tigerConnected.SCOTT@ ora11g>alter system switch logfile;System altered.SCOTT@ ora11g>create ta ...
LOGMNR
首先執行一個ddl（或dml）操作，以記錄重做信息：
SYS@ ora11g>conn scott/tiger
Connected.
SCOTT@ ora11g>alter system switch logfile;
System altered.
SCOTT@ ora11g>create table user1 as select * from dba_users;
Table created.
SCOTT@ ora11g>select count(*) from user1;
COUNT(*)
----------
33
然後執行LOGMNR解析工作：
SCOTT@ ora11g>conn / as sysdba
Connected.
SYS@ ora11g>select * from v$log where status = 'CURRENT';
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------
NEXT_CHANGE# NEXT_TIME
------------ ------------------
1 1 163 52428800 512 1 NO CURRENT 2454437 29-MAY-18
2.8147E+14
SYS@ ora11g>select member from v$logfile where group#=1;
MEMBER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/oracle/newdb/redo01.log
SYS@ ora11g>exec dbms_logmnr.add_logfile('/home/oracle/newdb/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
添加文件完成後，可以查看scn和time等信息，進行分析時，也可以指定scn：
alter sessin set nls_date_format="yyyy-dd-mm hh24:mi:ss";
select log_id,low_scn,low_time,next_scn,high_time from v$logmnr_logs;
SYS@ ora11g>exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SYS@ ora11g>select count(*) from v$logmnr_contents;
COUNT(*)
----------
133
分析之後就可以查看結果了，redo log記載的信息都能從v$logmnr_contents視圖查到，該視圖的內容只對當前session有效:
select timestamp,username,session#,sql_redo,operation from v$log_contents;
解析之後，可以通過v$logmnr_contents視圖來查詢資料庫執行所有操作。通過SQL_REDO的這些SQL就可以重演create table的ddl操作。
ddl的後臺操作實際上是轉換為對字典表呃一系列dml操作。
SYS@ ora11g>select sql_redo from v$logmnr_contents;
SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------
set transaction read write;
insert into "SYS"."AUD$"("SESSIONID","ENTRYID","STATEMENT","TIMESTAMP#","USERID","USERHOST","TERMINAL","ACTION#","RETURNCODE","OBJ$CREATOR","OBJ$NAME","AUTH$PRI
VILEGES","AUTH$GRANTEE","NEW$OWNER","NEW$NAME","SES$ACTIONS","SES$TID","LOGOFF$LREAD","LOGOFF$PREAD","LOGOFF$LWRITE","LOGOFF$DEAD","LOGOFF$TIME","COMMENT$TEXT",
"CLIENTID","SPARE1","SPARE2","OBJ$LABEL","SES$LABEL","PRIV$USED","SESSIONCPU","NTIMESTAMP#","PROXY$SID","USER$GUID","INSTANCE#","PROCESS#","XID","AUDITID","SCN"
,"DBID","SQLBIND","SQLTEXT","OBJ$EDITION") values ('720848','2','11',NULL,'SCOTT','wuku','pts/1','49','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,'oracle',NULL,NULL,NULL,'3',NULL,TO_TIMESTAMP('29-MAY-18 02.39.26.552375 AM'),NULL,NULL,'0','5745',NULL,NULL,NULL,'4090761500',NULL,NULL,NU
LL);
commit;
set transaction read write;
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$
","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('80448','80448','83','USER1','1',NULL,'2',TO_DATE('29-MAY-18', 'DD-MON-RR'),TO_DATE('29-MAY-18'
, 'DD-MON-RR'),TO_DATE('29-MAY-18', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1','83',NULL,NULL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014249','14249','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014250','14250','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014251','14251','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014252','14252','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014253','14253','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014254','14254','0',NULL,NULL,NULL,NU
LL,NULL);
set transaction read write;
Unsupported
commit;
insert into "SYS"."CON$"("OWNER#","NAME","CON#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('83','SYS_C0014255','14255','0',NULL,NULL,NULL,NU
LL,NULL);
create table user1 as select * from dba_users;
set transaction read write;
查詢完成之後，結束日誌解析過程：
SYS@ ora11g>exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
