oracle 备份恢复 03手工完全恢复

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

一、搭建实验环境
1.创建8张表scott.lxtb1-lxtb8
create table  scott.lxtb8  tablespace lxtb  as select * from scott.emp;
2.3DML操作
insert into scott.lxtb8 select * from scott.emp;
commit;
alter system  archive log current;
insert into scott.lxtb8 select * from scott.emp;
commit;    
insert into scott.lxtb8 select * from scott.emp;
select count(1) from scott.lxtb8;

3.冷备转储脚本
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/

4.热备转储脚本
cp  /u01/app/backdir/hot_bak/o1_mf_system_bwbn1ybd_.dbf     /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/o1_mf_undotbs1_bwbn1ydc_.dbf   /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/o1_mf_sysaux_bwbn1ybj_.dbf     /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/o1_mf_users_bwbn1ysx_.dbf      /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/o1_mf_example_bwbn1ybs_.dbf    /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/lxtb01.dbf                     /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/oltp01.dbf                     /u01/app/oracle/oradata/ipemsdb/datafile/
cp  /u01/app/backdir/hot_bak/nk_16k_01.dbf                  /u01/app/oracle/oradata/ipemsdb/datafile/    
    
二、完全恢复实验
1.丢失所有数据文件和控制文件
    备份控制文件-3DML-查控制文件,数据文件-断电-删控制文件,数据文件-启库-
重建控制文件（noresetlogs）-报错-转储数据文件-再重建（noresetlogs）-启mount-查-恢复数据库-开库
show parameter dump;
alter database backup controlfile to trace as '/u01/app/oracle/admin/ipemsdb/udump/controlfile_backup.trc'; 
cd /u01/app/oracle/admin/ipemsdb/udump 
ls -lht *_ora*
select name from v$controlfile;
select name from v$datafile;
ORA-00205: error in identifying control file, check alert log for more info

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bv77pklf_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

如果开启了OMF则需要注释参数文件里控制文件的名称
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01276: Cannot add file /u01/app/oracle/oradata/ipemsdb/controlfile/o1_mf_bv76kfh1_.ctl.
  File has an Oracle Managed Files file name.
注释后通过pfile创建spfile，从spfile启动数据库或从pfile启动
startup nomount pfile= '/u01/app/oracle/product/10.2.0/db_1/dbs/initipemsdb.ora'

select FILE#,ERROR from v$recover_file;
recover database;
alter database open;

如果删除时也删除了临时数据文件，需要补充创建临时数据文件
alter tablespace temp add tempfile;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_temp_bwbnc07b_.tmp' reuse;
查询数据库启动状态
select OPEN_MODE from v$database;

2.丢失所有数据文件（一般模式）
3DML-查datafile-断电-删所有数据文件-启-查v$recover_file-转储（热备数据）-查3SCN-恢复数据库-auto-open
因系统开启了OMF功能，所以重建控制文件后，参数文件记录的控制文件和实际创建的控制文件不一致，
重建控制文件开库后-创建pfile-查询当前controlfile-正常关库-修改pfile中控制文件配置-从pfile启动-创建spfile-重启
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bv77pklf_.dbf'
会自动创建临时数据文件

3.丢失一个数据文件(一般模式)
3DML-查datafile-断电-删一个dbf-启-查-转储-恢复datafile[tablespace]-auto-open
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
select file#,error from v$recover_file;
SQL> recover datafile 6;
ORA-00279: change 3288989 generated at 07/30/2015 01:58:40 needed for thread 1
ORA-00289: suggestion : /u01/arc_dir/1_8_886071850.dbf
ORA-00280: change 3288989 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 3289453 generated at 07/30/2015 02:02:54 needed for thread 1
ORA-00289: suggestion : /u01/arc_dir/1_9_886071850.dbf
ORA-00280: change 3289453 for thread 1 is in sequence #9
ORA-00278: log file '/u01/arc_dir/1_8_886071850.dbf' no longer needed for this recovery

4.丢失一个数据文件(高可用模式)
3DML-查datafile-断电-删一个dbf-启-查-数据文件脱机-开库-转储-恢复datafile[tablespace]-auto-联机
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
alter database datafile '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf' offline;
SQL> recover tablespace lxtb;
ORA-00279: change 3288989 generated at 07/30/2015 01:58:40 needed for thread 1
ORA-00289: suggestion : /u01/arc_dir/1_8_886071850.dbf
ORA-00280: change 3288989 for thread 1 is in sequence #8

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

Log applied.
Media recovery complete.
alter database datafile 6 online;

5.不关库下实验丢失一个数据文件(高可用模式)：
3DML-删-清理缓存-退出再登陆-数据文件脱机-转储-恢复-联机 --56因为readhate下推出会隐式提交
alter system flush buffer_cache; --也会隐式提交
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
alter database datafile '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf' offline;
SQL> recover tablespace lxtb;
ORA-00279: change 3288989 generated at 07/30/2015 01:58:40 needed for thread 1
ORA-00289: suggestion : /u01/arc_dir/1_8_886071850.dbf
ORA-00280: change 3288989 for thread 1 is in sequence #8

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

Log applied.
Media recovery complete.
SQL> alter database datafile 6 online;

6.不可转储，没有备份情况下丢失部分数据文件，丢失的数据文件在控制文件改变之后
创建表空间-创建表-3DML-断电-删除dbf-启-查-脱机-开库-创建空白数据文件-恢复数据文件-联机
create tablespace test datafile '/u01/app/oracle/oradata/ipemsdb/datafile/test01.dbf' 
size 20M size 50M reuse autoextend on next 5m;;
create table scott.lxtb06 tablespace test as select * from scott.emp;
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ipemsdb/datafile/test01.dbf'
select file#,error from v$recover_file;
alter database datafile 7 offline;
alter database open;
alter database create datafile  '/u01/app/oracle/oradata/ipemsdb/datafile/test01.dbf';
如果数据文件先与控制文件创建则无重新创建数据文件
ERROR at line 1:
ORA-01178: file 6 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
recover datafile 7;
alter database datafile 7 online;

7.数据文件恢复到新位置
3DML-断电-删一个数据文件-启-脱机-开库-转储新位置-更新位置-恢复-联机
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
alter database rename file '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf' to '/u01/app/oracle/oradata/ipemsdb/datafile/newdir/lxtb01.dbf';

切到非归档模式
shutdown immediate;
startup mount
alter database noarchivelog;
alter database open;

冷备
8.日志组被未覆盖
3DML-断电-删1个dbf-启-查SCN-转储-恢复数据文件-开库
alter system switch logfile;
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
--坏块
ORA-26040: Data block was loaded using the NOLOGGING option

9.日志组有被覆盖
3DML-多次切日志-断电-删1dbf-启-查-转储1个-recover datafile-不行 
-转储所有dbf-recover database utill cancel-cancel-open resetlogs（如果还不行）
-正常关-转储数据和控制文件-mount-查3SCN-recover database utill cancel-open resetlogs
包含了直接物理还原（拷贝回所有备份的文件）的过程。
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ipemsdb/datafile/lxtb01.dbf'
恢复单个数据文件
recover datafile 6;
ORA-00279: change 6905237 generated at 11/03/2015 13:09:55 needed for thread 1
ORA-00289: suggestion : /u01/duplex/1_1_894801153.dbf
ORA-00280: change 6905237 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/duplex/1_1_894801153.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
v$datafile 的checkpoint_change# 同步，v$datafile_header的checkpoint_change#为备份时的状态
如果只转储单个数据文件，恢复数据库时也会报错
recover database until 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_bwbn1ybd_.dbf'

三、设想的集中恢复情况
非归档模式
1.没开归档，备份了dbf,ctl,redo，存放这些文件的磁盘坏了，恢复
    冷备dbf,ctl,redo，开库，3DML，断电，删所有dbf,ctl,redo，开库
ORA-00205: error in identifying control file, check alert log for more info
转储ctl文件，关库再启
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
select open_mode from v$database;
select file#,error from v$recover_file;
转储dbf,redo文件，查询3SCN完全一致
select checkpoint_change# from v$database;
select checkpoint_change# from v$datafile;
select checkpoint_change# from v$datafile_header;
开库 
alter database open;
2.没开归档，备份了dbf,ctl,redo，存放dbf,ctl文件的磁盘坏了，redo文件好着，恢复
    冷备dbf,ctl,redo，开库，3DML，断电，删所有dbf,ctl，开库
ORA-00205: error in identifying control file, check alert log for more info
转储ctl文件，关库再启
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
select open_mode from v$database;
select file#,error from v$recover_file;
查询3SNC，转储所有dbf文件，再查询3SN，open database
ERROR at line 1:
ORA-00314: log 3 of thread 1, expected sequence# 3 doesn't match 7
ORA-00312: online log 3 thread 1:
'/u01/app/oracle/oradata/ipemsdb/onlinelog/o1_mf_3_bw56dvct_.log'
resetlogs开库也报错
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
删掉redo，转储redo
alter database open noresetlogs;
3.没开归档，备份了dbf,ctl,redo，存放dbf,redo文件的磁盘坏了，恢复
    冷备dbf,ctl,redo，开库，3DML，断电，删所有dbf,redo，开库
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
查3SN，查v$recover_file，转储dbf，开库
alter database open
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
恢复数据库
recover database until cancel;
ORA-00279: change 6910581 generated at 11/03/2015 16:33:01 needed for thread 1
ORA-00289: suggestion : /u01/duplex/1_3_894811869.dbf
ORA-00280: change 6910581 for thread 1 is in sequence #3
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/oradata/ipemsdb/datafile/o1_mf_system_bwbn1ybd_.dbf'
转储回redo文件，取消恢复，仍然报错
关库，转储控制文件和数据文件[日志文件]，取消恢复[直接开库]

归档模式
4.归档模式下，冷备了ctl,dbf,redo,arc文件，dbf,ctl坏了恢复
    冷备，3DML，断电，删dbf,ctl，启，转储ctl，启，查3SCN，转储dbf
ORA-00338: log 1 of thread 1 is more recent than control file
将控制文件生成trac文件，重建控制文件，将数据库启动到nomount状态，重
建控制文件，介质恢复至最后一次提交

5.归档模式下，冷备了ctl,dbf,redo,arc文件，dbf,ctl,redo坏了恢复
冷备，3DML，断电，删dbf,ctl，启
ORA-00205: error in identifying control file, check alert log for more info
shutdown abort,转储回ctl，启动到mount,生成控制文件的trac文件
alter database backup controlfile to trace as  '/u01/app/oracle/admin/ipemsdb/udump/controlfile_1203.trc';
利用脚本创建控制文件，控制文件里要求的数据文件和日志文件都要存在
recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
recover database using backup controlfile until cancel;
cancel
恢复到了最后一次提交

    归档模式下，只要有归档日志，当前联机日志，控制文件备份，就可以恢复到最后一次提交，没有当前日志
时，可能会恢复到最后一次提交

手动备份转储脚本
cp /u01/app/oracle/oradata/ipemsdb/datafile/*  /u01/app/backdir/manual/datafile/
cp /u01/app/oracle/oradata/ipemsdb/controlfile/*  /u01/app/backdir/manual/ctlfile/
cp /u01/app/oracle/oradata/ipemsdb/onlinelog/*  /u01/app/backdir/manual/logfile/

cp /u01/app/backdir/manual/datafile/* /u01/app/oracle/oradata/ipemsdb/datafile/  
cp /u01/app/backdir/manual/ctlfile/*  /u01/app/oracle/oradata/ipemsdb/controlfile/
cp /u01/app/backdir/manual/logfile/*  /u01/app/oracle/oradata/ipemsdb/onlinelog/  
 

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