DBA手记（学习） - LOGMNR 简单而强大的工具

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.