欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

DBA手记(学习) - LOGMNR 简单而强大的工具

程序员文章站 2022-07-04 19:38:41
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.