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.