logmnr分析在线重做日志

一 分析在线重作日志文件(无条件限制)

SQL> conn chen/chen

 

SQL> alter system switch logfile;

 

SQL> create table chen as select * from dba_users;

 

SQL> select count(*) from chen;

 

  COUNT(*)

----------

        39

 

SQL> conn /as sysdba

 

SQL> select group#,status from v$log where status='CURRENT';

 

    GROUP# STATUS

---------- --------------------------------

         2 CURRENT

 

SQL> select member from v$logfile where group#=2;

 

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ogg1/redo02.log

创建列表
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ogg1/redo02.log',dbms_logmnr.new);

 

PL/SQL procedure successfully completed.

无限制条件

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

 

PL/SQL procedure successfully completed.

观察分析结果（v$logmnr_contents）

SQL> spool /home/oracle/0629.txt

SQL> select sql_redo from v$logmnr_contents;

SQL> spool off

 

SQL> ho vim 0629.txt

......

SQL_REDO

--------------------------------------------------------------------------------

create table chen as select * from dba_users;

set transaction read write;

 

set transaction read write;

 

commit;

 

insert into "CHEN"."CHEN"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK

_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROF

ILE","INITIAL_RSRC_CONSUMER_GROUP","EXTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS

_ENABLED","AUTHENTICATION_TYPE") values ('MGMT_VIEW','74',NULL,'OPEN',NULL,TO_DA

TE('03-OCT-15', 'DD-MON-RR'),'SYSTEM','TEMP',TO_DATE('15-AUG-09', 'DD-MON-RR'),'

DEFAULT','DEFAULT_CONSUMER_GROUP',NULL,'10G 11G ','N','PASSWORD');

......

 

 退出

SQL> exec dbms_logmnr.end_logmnr

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1718726/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1718726/