使用logmnr，在RMAN備份檔案中恢復備份的歸檔日誌檔案進行分析

業務系統出問題了，發現時已經過了一段時間，v$sql和閃回區都已經查詢不到當時的sql了，也沒開審計，只能用logmnr做日誌抽取，肯定需要通過歸檔日誌，檢查了v$archived_log，發現有些歸檔日誌因為已經過了儲存時間，同時因為每天都有RMAN備份，這部分歸檔檔案已經被清除。為了能還原歸檔日誌，需要先在rman備份中恢復備份的歸檔日誌檔案。

因為歸檔已經清除，在用DBMS_Logmnr分析備份的歸檔檔案時報錯。

SQL> exec dbms_logmnr.add_logfile('/db2_arch/1_18620_770040269.dbf',DBMS_LOGMNR.NEW); 
BEGIN dbms_logmnr.add_logfile('/db2_arch/1_18620_770040269.dbf',DBMS_LOGMNR.NEW); END;

* 
ERROR at line 1: 
ORA-01284: file /db2_arch/1_18620_770040269 cannot be opened 
ORA-00317: file type 0 in header is not log file 
ORA-00334: archived log: /db2_arch/1_18620_770040269 
ORA-06512: at "SYS.DBMS_LOGMNR", line 68 
ORA-06512: at line 1

Logmnr是無法識別備份檔案的，只能先把歸檔檔案從備份檔案中還原出來才行。用RMAN搞定：

RMAN> run
{
SET ARCHIVELOG DESTINATION TO '/db2_arch';
restore archivelog sequence between 18620 and 18623;
}

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 06-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=122 instance=orcl1 devtype=DISK

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/db2_arch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=18620
channel ORA_DISK_1: reading from backup piece /db1_dbbk/RMAN/backup_20130905_56431_1.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1_dbbk/RMAN/backup_20130905_56431_1.arc tag=ORCL1_2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:47
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/db2_arch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=18621
channel ORA_DISK_1: reading from backup piece /db1_dbbk/RMAN/backup_20130905_56432_1.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1_dbbk/RMAN/backup_20130905_56432_1.arc tag=ORCL1_2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/db2_arch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=18622
channel ORA_DISK_1: reading from backup piece /db1_dbbk/RMAN/backup_20130905_56433_1.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1_dbbk/RMAN/backup_20130905_56433_1.arc tag=ORCL1_2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/db2_arch
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=18623
channel ORA_DISK_1: reading from backup piece /db1_dbbk/RMAN/backup_20130905_56434_1.arc
channel ORA_DISK_1: restored backup piece 1
piece handle=/db1_dbbk/RMAN/backup_20130905_56434_1.arc tag=ORCL1_2
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 06-SEP-13

這樣就在目錄/db2_arch裡還原了18620 到 18623 序號的歸檔檔案。接著就可以用DBMS_Logmnr分析日誌了。

exec dbms_logmnr.add_logfile('/db2_arch/1_18620_770040269.dbf',DBMS_LOGMNR.NEW); 
exec dbms_logmnr.add_logfile('/db2_arch/1_18621_770040269.dbf',DBMS_LOGMNR.addfile); 
exec dbms_logmnr.add_logfile('/db2_arch/1_18622_770040269.dbf',DBMS_LOGMNR.addfile);

或

begin
  sys.dbms_logmnr.add_logfile(LogFileName => '/u01/oracle/logminer/1_18621_770040269.dbf',
                              Options     => dbms_logmnr.new);
  dbms_logmnr.add_logfile(LogFileName => '/u01/oracle/logminer/1_18622_770040269.dbf',
                          Options     => dbms_logmnr.addfile);
  dbms_logmnr.add_logfile(LogFileName => '/u01/oracle/logminer/1_18623_770040269.dbf',
                          Options     => dbms_logmnr.addfile);
end;

之後開始分析：

begin
  sys.dbms_logmnr.start_logmnr(DictFileName =>'/u01/oracle/logminer/landcenterlogmine.ora');
end;

分析指定時間：
begin
  sys.dbms_logmnr.start_logmnr (

     DictFileName =>'/u01/oracle/logminer/landcenterlogmine.ora',
     startTime => to_date('2013-09-04 16:40:00','YYYY-MM-DD HH24:MI:SS'),
     endTime => to_date('2013-09-04 17:00:00','YYYY-MM-DD HH24:MI:SS')
                              );
end;

檢視結果：

select * from v$logmnr_contents;

 

停止logmnr
   exec sys.dbms_logmnr.end_logmnr

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14184018/viewspace-772309/，如需轉載，請註明出處，否則將追究法律責任。