oracle 备份恢复 04手工不完全恢复

2019独角兽企业重金招聘Python工程师标准>>>

一、不完全恢复相关原理
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;

转载于:https://my.oschina.net/peakfang/blog/2245421