一、不完全恢复相关原理

1.只适用于归档模式，可以将数据库恢复到过去的某个时间点。 2.用于解决误操作，找回过去某个时间点数据 3.误操作解决：逻辑备份-闪回-不完全恢复     冷备-不完全恢复到过去时间点-导出需要的数-冷备恢复-导入需要的数据；     搭建测试库，从测试库不完全恢复，导入正式库；      二、LOGMNR挖掘日志 1.激活补充日志，充分利用logmnr的特性，查询确认 alter database add supplemental log data; select log_mode, supplemental_log_data_min  from v$database; 2.设置字典数据目录，并重启使之生效 alter system set utl_file_dir='/u01/app/utl/' scope=spfile ; 3.建立字典文件，记录表字典信息，只有表包含在该文件里才会挖掘出DML否则只有DDL语句，主要用于挖掘DDL语句使用 execute dbms_logmnr_d.build ('mydict.ora','/u01/app/utl',dbms_logmnr_d.store_in_flat_file); 4.添加分析日志 begin dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ipemsdb/onlinelog/o1_mf_3_bw56dvct_.log', options=>dbms_logmnr.new) ; dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ipemsdb/onlinelog/o1_mf_2_bw56cw90_.log', options=>dbms_logmnr.addfile) ; dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/utl/mydict.ora',dbms_logmnr.DDL_DICT_TRACKING);    --指定logmnr字典文件挖掘DDL使用 end; --10g以后的版本还可以使用在线数据字典，挖掘DML使用 EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);  5.分析日志v$logmnr_contents里的数据只在会话里有效，可以创建表保存 create table logmnr_tmp tablespace users as select * from v$logmnr_contents; alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col seg_name for a10                                                                                                           col sql_redo for a50 col seg_owner for a10                                                                                                          col table_name for a10  select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='LXTB4' order by scn; 6.结束分析 EXECUTE DBMS_LOGMNR.END_LOGMNR;    

三、实验环境及转储脚本

1.冷备转储脚本 cp /u01/app/backdir/cold_bak/lxtb01.dbf                   /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/cold_bak/o1_mf_example_bv77pklo_.dbf  /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/cold_bak/o1_mf_sysaux_bv77pkly_.dbf   /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/cold_bak/o1_mf_system_bv77pklf_.dbf   /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/cold_bak/o1_mf_undotbs1_bv77pkm9_.dbf /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/cold_bak/o1_mf_users_bv77pkmz_.dbf    /u01/app/oracle/oradata/ipemsdb/datafile/

cp /u01/app/backdir/cold_bak/o1_mf_bvk6hfdo_.ctl          /u01/app/oracle/flash_recovery_area/ipemsdb/controlfile/

cp /u01/app/backdir/cold_bak/o1_mf_bvk6hd3p_.ctl          /u01/app/oracle/oradata/ipemsdb/controlfile/

2.热备转储脚本

cp /u01/app/backdir/hot_bak/lxtb01.dbf                    /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/hot_bak/o1_mf_example_bv77pklo_.dbf   /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/hot_bak/o1_mf_sysaux_bv77pkly_.dbf    /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/hot_bak/o1_mf_system_bv77pklf_.dbf    /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/hot_bak/o1_mf_undotbs1_bv77pkm9_.dbf  /u01/app/oracle/oradata/ipemsdb/datafile/ cp /u01/app/backdir/hot_bak/o1_mf_users_bv77pkmz_.dbf     /u01/app/oracle/oradata/ipemsdb/datafile/

3.实验操作

create table  scott.lxtb3  tablespace lxtb  as select * from scott.emp; insert into scott.lxtb3 select * from scott.emp where rownum <=3; select count(1) from scott.lxtb3; @/u01/app/backdir/cold_bak.sql @/u01/app/backdir/hot_bak.sql

四、备份恢复实验

基于时间点恢复 主动 基于改变恢复 基于取消的恢复 被动 基于备份的控制文件恢复  表空间 1.DML语句不完全恢复 误操作-查日志（v$log,v$logfile,v$archived_log）-logmnr找到时间点和SCN-查数据文件-关库-转储所有备份-启mount -不完全恢复-open resetlogs; v$log视图可以确定当前日志是否归档 startup mount recover database until time '2015-07-30 10:10:13'; alter database open resetlogs; 2.DDL语句不完全恢复 误操作-查日志（v$log,v$logfile,v$archived_log）-logmnr找到时间点和SCN-查数据文件-关库-转储所有备份-启mount -不完全恢复-open resetlogs; 3471084 2015-07-30 10:48:33 recover database until change  3471084; 3.基于cancel不完全恢复 3DML-查日志，数据文件-断电-删一个数据文件，最新归档文件，current日志成员-启-查v$recove_file-转储一个-恢复一个数据文件 -恢复database until cancel- cancel-关库-转储所有数据文件-启mount-恢复database cancel cancel-open resetlogs-备份 SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/flash_recovery_area/ipemsdb/onlinelog/o1_mf_3_bv4d37kl_.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ipemsdb/onlinelog/o1_mf_3_bv4d3421_.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

SQL> recover database until cancel

ORA-00279: change 3486235 generated at 07/30/2015 17:14:33 needed for thread 1 ORA-00289: suggestion : /u01/arc_dir/1_2_886417272.dbf ORA-00280: change 3486235 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bv77pklf_.dbf'

ORA-01112: media recovery not started

SQL> shutdown immediate SQL> startup mount; SQL> recover database until cancel; ORA-00279: change 3485508 generated at 07/30/2015 17:06:09 needed for thread 1 ORA-00289: suggestion : /u01/arc_dir/1_1_886417272.dbf ORA-00280: change 3485508 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel Media recovery cancelled. SQL> alter database open resetlogs;

4.基于旧的控制文件恢复

备份控制文件-3DML-删表空间和数据文件-查看表空间，数据文件信息，日志信息，归档信息- logmnr分析drop时间-关库-转储控制文件-启mount-转储数据文件-recover until timme using backup controlfile-auto-再恢复-给联机日志-open resetlogs ALTER DATABASE BACKUP CONTROLFILE TO 'C:\oracle\product\10.2.0\oradata\exp\backup20141030.ctl';     3489721 2015-07-30 18:59:38 SQL> recover database until time '2015-07-30 18:59:38' using backup controlfile ORA-00279: change 3486993 generated at 07/30/2015 17:57:36 needed for thread 1 ORA-00289: suggestion : /u01/arc_dir/1_1_886441062.dbf ORA-00280: change 3486993 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto ORA-00279: change 3487383 generated at 07/30/2015 18:08:54 needed for thread 1 ORA-00289: suggestion : /u01/arc_dir/1_2_886441062.dbf ORA-00280: change 3487383 for thread 1 is in sequence #2 ORA-00278: log file '/u01/arc_dir/1_1_886441062.dbf' no longer needed for this recovery

ORA-00279: change 3489451 generated at 07/30/2015 18:56:35 needed for thread 1

ORA-00289: suggestion : /u01/arc_dir/1_3_886441062.dbf ORA-00280: change 3489451 for thread 1 is in sequence #3 ORA-00278: log file '/u01/arc_dir/1_2_886441062.dbf' no longer needed for this recovery

ORA-00308: cannot open archived log '/u01/arc_dir/1_3_886441062.dbf'

ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3

SQL>  recover database until time '2015-07-30 18:59:38' using backup controlfile

ORA-00279: change 3489451 generated at 07/30/2015 18:56:35 needed for thread 1 ORA-00289: suggestion : /u01/arc_dir/1_3_886441062.dbf ORA-00280: change 3489451 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/ipemsdb/onlinelog/o1_mf_2_bv8kxh7g_.log Log applied. Media recovery complete. alter database open resetlogs; --56,42

四、闪回

1.开闪回 create pfile from spfile; shutdown immediate; mkdir flashback 修改pfile中的     db_recovery_file_dest_size==2147483648     db_recovery_file_dest='/u01/app/flashback'     recyclebin=on startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initipemsdb.ora'; alter database flashback on; select FLASHBACK_ON from v$database; alter database open; 自己的方式开闪回 alter system set db_recovery_file_dest_size=4G scope =spfile; alter system set db_recovery_file_dest='/u01/app/myflash' scope =spfile; alter system set recyclebin=on scope=spfile; 关库，启动到mount， alter database flashback on; select flashback_on from v$database; alter database open;

2.闪回drop，利用recyclebin

drop表-查recyclebin-查回收站表-按时间顺序倒着闪回 show recyclebin;     flashback table  LXTB1  to before drop  rename to fl_lxtb1; 3.闪回查询，利用undo show parameter undo; 设置retention alter tablespace UNDOTBS1  retention guarantee; 删数据-插入数据-logmnr挖掘删除时间-查过去时间点表中数据-建表保存查询结果-清空原表-插入保存数据-删掉保存表; select count(1) from lxtb2 as of timestamp to_timestamp('2015-07-30 20:45:53','yyyy-mm-dd hh24:mi:ss'); select count(1) from lxtb3 as of scn 3494705; alter table lxtb3 enable row movement; flashback table scott.lxtb3 to scn 3505679;  ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-25153: Temporary Tablespace is Empty

select name from v$datafile; 

select tablespace_name,status from dba_tablespaces;  select tablespace_name,file_name  from dba_temp_files; alter tablespace temp add tempfile '/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_temp_bvncs3ns_.tmp'; 如果使用了OMF alter tablespace temp add tempfile; 执行DDL操作后无法闪回查询 ERROR at line 1: ORA-01466: unable to read data - table definition has changed

4.闪回数据库（闪回日志）时间减1秒，DDL操作

truncate table-DML-logmnr挖掘到时间点-关库-启mount-闪回到truncate前-只读打开确认-关库-启mount-open resetlogs; 3506687 2015-07-30 22:38:02  减1秒 2015-07-25 14:03:50 shutdown immediate; startup mount; flashback database to timestamp to_timestamp('2015-07-30 22:38:01','yyyy-mm-dd hh24:mi:ss'); alter database open read only; alter database open resetlogs;

5.闪回归档，create table .. as select ..原理

drop table t purge;--彻底删除

--SYS用户对象不能闪回，truncate不能闪回

6.检查flash recovery area的使用情况

    select * from V$FLASH_RECOVERY_AREA_USAGE; 7.计算flash recovery area已经占用的空间     select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;