在RMAN备份文件中恢复备份的归档日志文件

业务系统用logmnr做日志抽取，需要通过归档日志，但是发现有些归档日志因为保存时间太短，已经被清除。为了能还原归档日志，需要在rman备份中恢复备份的归档日志文件。

因为归档已经清除，在用DBMS_Logmnr分析备份的归档文件时报错。

SQL> exec dbms_logmnr.add_logfile(’/u02/rman/arch_db1_20090624_29_1′,DBMS_LOGMNR.NEW);
BEGIN dbms_logmnr.add_logfile(’/u02/rman/arch_db1_20090624_29_1′,DBMS_LOGMNR.NEW); END;

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

看来Logmnr是无法识别备文件的，只有把归档文件从备份文件中还原出来才可以。查了下文档，用RMAN是可以搞定的。

RMAN> run
2> {
3> SET ARCHIVELOG DESTINATION TO '/u02/archive';
4> restore archivelog sequence between 65 and 67;
5> }

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

Starting restore at 24-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/u02/archive
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=65
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=66
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=67
channel ORA_DISK_1: reading from backup piece /u02/rman/arch_db1_20090624_29_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rman/arch_db1_20090624_29_1 tag=TAG20090624T154331
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 24-JUN-09

这样就在目录/u02/archive里还原了65-67三个序号的归档文件。接着就可以用DBMS_Logmnr分析日志了。

exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_65.dbf’,DBMS_LOGMNR.NEW);
exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_66.dbf’,DBMS_LOGMNR.addfile);
exec dbms_logmnr.add_logfile(’/u02/archive/arch_1_67.dbf’,DBMS_LOGMNR.addfile);

begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;

select * from v$logmnr_contents