业务系统出问题了,发现时已经过了一段时间,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 catalogStarting restore at 06-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=122 instance=orcl1 devtype=DISKchannel 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14184018/viewspace-772309/