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

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！