DBA手記（學習） – LOGMNR 簡單而強大的工具

子魚猴發表於2018-05-29

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