oracle-redo日志分析

logmnr分析日志３步骤

＋　查询redoe日志文件路径

select member from v$logfile;

• 添加要查看的日志文件

exec dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/app/oradata/orcl/redo01.log',options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/app/oradata/orcl/redo02.log',options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/opt/oracle/app/oradata/orcl/redo03.log',options=>dbms_logmnr.new);

• 开始分析日志命令

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

• 查看日志

select timestamp,sql_redo,sql_undo from v$logmnr_contents where table_name='你要查看的表名';

• 结束分析

exec dbms_logmnr.end_logmnr;

###　其他命令

select * from dba_users; 
select * from v$flash_recovery_area_usage; --查看空间占用率，如果 ARCHIVED LOG 超过90%，Oracle随时有宕机的危险
 
select * from v$recovery_file_dest; --查看归档日志大小及使用情况
 
select group#, bytes, status from v$log; --查看现有日志
 
select sequence#,first_time from v$loghist;--列出所有归档redo日志文件的顺序和产生的时间
 
select * from v$archived_log;  --查看v$archive_log
 
select count(*) from v$archived_log where archived='YES' and deleted='NO'; --查看所有归档，未删除的归档日志

shutdown immediate
startup mount;
alter database open;
alter database archivelog;
archive log list;
show parameter db_recovery