logmner

1、利用备份数据

RMAN> run {
2> allocate channel d1 device type disk;
3> backup as compressed backupset
4> incremental level=0
5> format='/u01/rmanbak/inc0_%d_%U'
6> tag='inc0'
7> channel=d1
8> database;
9> backup as compressed backupset
10> format='/u01/rmanbak/arch_%d_%U'
11> tag='arch'
12> channel=d1
13> archivelog all delete input;
14> backup as compressed backupset
15> format='/u01/rmanbak/backup.ctl'
16> tag='ctl'
17> channel=d1
18> current controlfile reuse;
19> }

 

2、修改spfile

SQL> alter system set utl_file_dir='/logmnr' scope=spfile;

 

3、重启数据库

SQL> startup force;

 

4、删除表

SQL> drop table t1;

 

5、redo文件归档

SQL> alter system switch logfile;

SQL> select name from v$archived_log;

 

6、使用logmnr

     导出数据字典

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

     SQL> commit;

 

     添加归档日志

    SQL> begin   
    2  dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/rac1/archive/1_84_785370967.arc',options=>dbms_logmnr.new);
    3  end;
    4  /

    

     查看添加日志

     SQL> select * from v$logmnr_logs;

   

     启用logmnr分析

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

     查看内容
     SQL> select count(*) from v$logmnr_contents;

     将内容导入到一张表中
     SQL> create table scott.tb_logmnr as select * from v$logmnr_contents;

     

      利用plsql工具查看该表

      select scn,sql_redo from tb_logmnr where username = 'SCOTT' and operation='DDL'

      

 

7、使用rman进行不完全恢复

    关闭数据库，并启动到mount状态

     SQL> shutdown immediate

    SQL> startup mount

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

8、打开数据库

       SQL> alter database open resetlogs;