oracle redo查询,ORACLE UNDO REDO查看

UNDO表空间：

select * from flashback_transaction_query;

REDO日志分析：

注意：必须将所有的脚本在同一窗口执行。

1. 创建路径：

mkdir /usr/local/dbs

chown oracle:oinstall /usr/local/dbs

2. 修改数据库参数：

alter system set utl_file_dir='/usr/local/dbs' scope=spfile;

3. 重启数据库

4. 创建logmnr文件：

BEGIN

dbms_logmnr_d.build(

dictionary_filename => 'logminer_dict.dat',

dictionary_location => '/usr/local/dbs');

END;

/

5. 添加日志文件：

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.new,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo01.log');

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo02.log');

execute dbms_logmnr.add_logfile(options =>dbms_logmnr.addfile,logfilename =>'/u01/app/oracle/oradata/orcl11g/redo03.log');

6. 启动logmnr：

a. 无条件：

BEGIN

dbms_logmnr.start_logmnr(

dictfilename => '/usr/local/dbs/logminer_dict.dat');

END;

/

b. 有时间条件：

begin

dbms_logmnr.start_logmnr(

dictFileName => '/usr/local/dbs/logminer_dict.dat',

StartTime => to_date('2011-8-9 13:00:00','YYYY-MM-DD HH24:MI:SS'),

EndTime => to_date('2011-8-9 13:22:00','YYYY-MM-DD HH24:MI:SS'));

end;

/

c. 有SCN条件：

begin

dbms_logmnr.start_logmnr(

DictFileName => '/usr/local/dbs/logminer_dict.dat',

StartScn => 20,

EndScn => 50)

end;

/

7. 查询结果：

select sql_redo FROM v$logmnr_contents;