logmnr分析歸檔重做日誌

SQL> create table t2(id number);

 

Table created.

 

SQL> alter table t2 add(sal number);

 

Table altered.

 

SQL> drop table t2 cascade constraints purge;

 

Table dropped.

 

SQL> alter system switch logfile;

 

System altered.

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

 

SQL> set linesize 100

 

SQL> show parameter db_recovery_file_dest

 

NAME                                 TYPE                   VALUE

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

db_recovery_file_dest                string                 /u01/app/oracle/flash_recovery

                                                            _area

db_recovery_file_dest_size           big integer            3882M

 

SQL> ho ls -lrth /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/

total 5.2M

-rw-r----- 1 oracle oinstall 5.1M Jun 30 13:22 o1_mf_1_23_bs49wylc_.arc

-rw-r----- 1 oracle oinstall  41K Jun 30 13:24 o1_mf_1_24_bs4b0ybp_.arc

 

建立列表

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_24_bs4b0ybp_.arc',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/0630.txt

SQL> select sql_redo from v$logmnr_contents;

SQL> spool off

 

SQL> ho vim /home/oracle/0630.txt

SQL_REDO     

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

......

create table t2(id number);  

alter table t2 add(sal number); 

......

SQL> exec dbms_logmnr.end_logmnr

 

 

新增歸檔日誌

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_23_bs49wylc_.arc',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.

 

--檢視分析結果

SQL> spool /home/oracle/0630_1.txt

SQL> select sql_redo from v$logmnr_contents;

SQL> spool off

SQL> ho vim /home/oracle/0630_1.txt

SQL_REDO

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

.......

set transaction read write;

insert into "CHEN"."LOG_TABLE"("USERNAME","LOGON_TIME","LOGOFF_TIME","ADDRESS")

values ('SYS',NULL,TO_DATE('30-JUN-15', 'DD-MON-RR'),NULL);

 

commit;

set transaction read write;

update "SYS"."JOB$" set "LAST_DATE" = TO_DATE('30-JUN-15', 'DD-MON-RR'), "THIS_D

ATE" = NULL, "NEXT_DATE" = TO_DATE('30-JUN-15', 'DD-MON-RR'), "TOTAL" = '.005324

07407407407407407407407407407407404', "FAILURES" = '0', "FLAG" = '0' where "LAST

_DATE" = TO_DATE('30-JUN-15', 'DD-MON-RR') and "THIS_DATE" = TO_DATE('30-JUN-15'

, 'DD-MON-RR') and "NEXT_DATE" = TO_DATE('30-JUN-15', 'DD-MON-RR') and "TOTAL" =

 '.00532407407407407407407407407407407407404' and "FAILURES" = '0' and "FLAG" =

'0' and ROWID = 'AAAAEfAABAAAAfZAAC';

 

 

commit;

 

13043 rows selected.

 

-- 用來終止分析會話 , 它將回收 LogMiner 所挪借的記憶體

SQL> exec dbms_logmnr.end_logmnr

 

PL/SQL procedure successfully completed.

 
檢視警告日誌檔案

SQL> ho vim /u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/alert_ogg1.log

......

Thread 1 advanced to log sequence 25 (LGWR switch)

  Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/ogg1/redo01.log

Tue Jun 30 13:24:46 2015

Archived Log entry 21 added for thread 1 sequence 24 ID 0xf1b93508 dest 1:

Tue Jun 30 14:04:44 2015

LOGMINER: summary for session# = 2147484161

LOGMINER: StartScn: 1466953 (0x0000.00166249)

LOGMINER: EndScn: 0 (0x0000.00000000)

LOGMINER: HighConsumedScn: 0

LOGMINER: session_flag 0x0

Tue Jun 30 14:05:47 2015

LOGMINER: Begin mining logfile for session -2147483135 thread 1 sequence 24, /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_24_bs4b0ybp_.arc

LOGMINER: End   mining logfile for session -2147483135 thread 1 sequence 24, /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_24_bs4b0ybp_.arc

Tue Jun 30 14:17:41 2015

LOGMINER: summary for session# = 2147484417

LOGMINER: StartScn: 1461366 (0x0000.00164c76)

LOGMINER: EndScn: 0 (0x0000.00000000)

LOGMINER: HighConsumedScn: 0

LOGMINER: session_flag 0x0

Tue Jun 30 14:18:18 2015

LOGMINER: Begin mining logfile for session -2147482879 thread 1 sequence 23, /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_23_bs49wylc_.arc

Tue Jun 30 14:18:48 2015LOGMINER: End   mining logfile for session -2147482879 thread 1 sequence 23, /u01/app/oracle/flash_recovery_area/OGG1/archivelog/2015_06_30/o1_mf_1_23_bs49wylc_.arc

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