Modify spfile
 
mkdir /logmnr
chown -R oracle:dba /logmnr
alter system set utl_file_dir='/logmnr' scope=spfile sid='*';
startup force

备份数据库rman cmdfile=/home/oracle/usualbk.rcv log=/tmp/rman.log append
rcv脚本：
connect target system/kingdee
run{
allocate channel1 d1 device type disk;
backup as compressed backupset incremental level=0 format='/oracle/rman/inc0_%d_U' tag='inc0' channel=d1 database;
backup as compressed backupset format='/oracle/rman/arch_%d_%U' tag='arch' channel=d1 archivelog all delete input;
backup as compressed backupset format='/oracle/rman/backup.ctl' tag='ctl' channel=d1 current controlfile reuse;
}

SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora',dictionary_location=>'/oracle/logmnr');

PL/SQL procedure successfully completed

SQL> begin
  2  dbms_logmnr.add_logfile(logfilename=>'/oracle/flash_recovery_area/SAAS2/archivelog/2011_07_06/o1_mf_1_4_717f1q61_.arc',options=>dbms_logmnr.new);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from v$logmnr_logs;

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/oracle/logmnr/dictionary.ora');

PL/SQL procedure successfully completed.

SQL> select count(1) from v$logmnr_contents;

  COUNT(1)
----------
      3938

SQL> create table hr.logmnr as select * from v$logmnr_contents;

Table created.

SQL> conn hr/kingdee
Connected.
SQL> select count(1) from logmnr;

  COUNT(1)
----------
      3938

Analyze logmnr_contents
username
seg_name
seg_owner
seg_type_name
timestamp
operation

用plsqldeveleop查看表

select * from hr.logmnr where peration='DDL';
然后根据scn看sql_redo
sqlplus 里面select scn,TIMESTAMP,sql_redo from logmnr where peration='DDL';

SQL> select scn,TIMESTAMP,sql_redo from logmnr where peration='DDL';

       SCN TIMESTAMP          SQL_REDO
---------- ------------------ ----------------------------------------
    508776 06-JUL-11          ALTER DATABASE OPEN;
    509375 06-JUL-11          create table liang as select * from EMPL
                              OYEES;

    509638 06-JUL-11          ALTER TABLE "HR"."LIANG" RENAME CONSTRAI
                              NT "SYS_C005424" TO "BIN$p10mz1y0j9ngQAB
                              /AQBT7Q==$0" ;

    509642 06-JUL-11          ALTER TABLE "HR"."LIANG" RENAME CONSTRAI
                              NT "SYS_C005423" TO "BIN$p10mz1y1j9ngQAB
                              /AQBT7Q==$0" ;

       SCN TIMESTAMP          SQL_REDO
---------- ------------------ ----------------------------------------

    509645 06-JUL-11          ALTER TABLE "HR"."LIANG" RENAME CONSTRAI
                              NT "SYS_C005422" TO "BIN$p10mz1y2j9ngQAB
                              /AQBT7Q==$0" ;

    509648 06-JUL-11          ALTER TABLE "HR"."LIANG" RENAME CONSTRAI
                              NT "SYS_C005421" TO "BIN$p10mz1y3j9ngQAB
                              /AQBT7Q==$0" ;

    509652 06-JUL-11          ALTER TABLE "HR"."LIANG" RENAME TO "BIN$
                              p10mz1y4j9ngQAB/AQBT7Q==$0" ;

       SCN TIMESTAMP          SQL_REDO
---------- ------------------ ----------------------------------------

    509655 06-JUL-11          drop table liang AS "BIN$p10mz1y4j9ngQAB
                              /AQBT7Q==$0" ;

    509670 06-JUL-11          create table kingdee as select * from EM
                              PLOYEES;

9 rows selected.

看来scn为509655

shutdown immediate
startup mount

rman target /
RMAN> run {
2> allocate channel d1 device type disk;
3> set until scn 509655;
4> restore database;
5> recover database;
6> }

SQL> alter database open resetlogs;