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;