Oracle 基于备份控制文件的恢复(unsing backup controlfile)

xydmike

2012-09-10

关注关注

通常在当前控制文件丢失，或者当前的控制文件与需要恢复的控制文件不一致的情况下，我们需要重新创建一个控制文件或者使用 unsing

backup controlfile方式来恢复控制文件。说简单点，只要是备份的控制文件与当前的控制文件不一致进行恢复数据库，就需要使用到 unsing

backup controlfile方式，而一旦使用了该方式，则需使用resetlgos选项来打开数据库。

一、基于备份控制文件的恢复注意事项(无论是否使用恢复目录catalog)

1、即使没有数据文件需要还原，当使用unsing backup controlfile 方式时必须结合 recover 命令

2、不论使用备份的控制文件进行时点恢复或完全恢复，必须使用 open resetlogs 方式打开数据库

3、如果联机日志不可访问，必须使用不完全恢复到联机日志文件中最早的一个SCN之前。这是因为RMAN并不备份联机日志文件

4、在恢复期间，RMAN自动搜索联机日志和没有记录在RMAN存储仓库中的归档日志以完成恢复

5、RMAN会根据初始化参数文件中归档位置以及控制文件联机日志信息自动寻找有效的归档日志和联机日志。使用unsing backup controlfile方

式时，在恢复期间，一旦归档目的地以及归档格式发生变化，或添加新的联机日志成员将收到RMAN-06054错误信息。

本文主要使用热备方式来完成演示

二、演示unsing backup controlfile的使用

1、控制文件全部丢失的情形(控制文件备份后发生变化)

-->首先使用热备脚本进行备份
sys@SYBO2SZ> get db_hot_bak.sql
1 set feedback off heading off verify off
2 set pagesize 0 linesize 200
3 define dir='/u02/database/SYBO2SZ/backup/hotbak'
4 define script='/tmp/tmphotbak.sql'
5 spool &script
6 select 'ho cp '||name||' &dir' from v$datafile;
7 spool off
8 alter database begin backup;
9 start &script
10 alter database end backup;
11 alter database backup controlfile to '&dir/contlbak.ctl' reuse;
12 create pfile='&dir/initSYBO2SZ.ora' from spfile;
13* set feedback on heading on verify on pagesize 100
sys@SYBO2SZ> @db_hot_bak
sys@SYBO2SZ> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u02/database/SYBO2SZ/controlf
/cntl1SYBO2SZ.ctl, /u02/databa
se/SYBO2SZ/controlf/cntl2SYBO2
SZ.ctl, /u02/database/SYBO2SZ/
controlf/cntl3SYBO2SZ.ctl
-->为数据库添加新的表空间，此时控制文件将不同于先前备份的控制文件
sys@SYBO2SZ> create tablespace tbs datafile '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf' size 10m autoextend on;
Tablespace created.
-->为数据库添加对象
sys@SYBO2SZ> create table tb_emp tablespace tbs as select * from scott.emp ;
Table created.
sys@SYBO2SZ> select count(*) from tb_emp;
COUNT(*)
----------
14
sys@SYBO2SZ> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
3 1 116 20971520 2 NO CURRENT 1078066 08-SEP-12
4 1 115 20971520 2 YES INACTIVE 1063428 08-SEP-12
sys@SYBO2SZ> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,
-----------------
20120908 16:30:41
-->切换日志
sys@SYBO2SZ> alter system archive log current;
System altered.
-->删除部分记录用于恢复后验证
sys@SYBO2SZ> delete from tb_emp where deptno=10;
3 rows deleted.
sys@SYBO2SZ> commit;
Commit complete.
sys@SYBO2SZ> alter system archive log current;
System altered.
sys@SYBO2SZ> ho ls -hltr /u02/database/SYBO2SZ/archive
total 143M
-rw-r----- 1 oracle oinstall 15M 2012-09-08 16:20 arch_792094299_1_115.arc
-rw-r----- 1 oracle oinstall 236K 2012-09-08 16:30 arch_792094299_1_116.arc
-rw-r----- 1 oracle oinstall 9.0K 2012-09-08 16:32 arch_792094299_1_117.arc
-->异常关机
sys@SYBO2SZ> shutdown abort;
ORACLE instance shut down.
-->模拟所有控制文件丢失
sys@SYBO2SZ> ho rm -rf /u02/database/SYBO2SZ/controlf/*
sys@SYBO2SZ> ho ls /u02/database/SYBO2SZ/controlf/
-->启动后收到ORA-00205错误
sys@SYBO2SZ> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2074568 bytes
Variable Size 276826168 bytes
Database Buffers 314572800 bytes
Redo Buffers 6311936 bytes
ORA-00205: error in identifying control file, check alert log for more info
sys@SYBO2SZ> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
SYBO2SZ STARTED
sys@SYBO2SZ> select name,open_mode from v$database;
select name,open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
-->还原控制文件
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ.ctl
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ.ctl
-->mount数据库
sys@SYBO2SZ> alter database mount;
Database altered.
-->由于仅仅是丢失了控制文件，因此我们只还原控制文件
-->恢复数据库，提示需要使用BACKUP CONTROLFILE选项，因为控制文件在备份后发生了变化
sys@SYBO2SZ> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
-->提示需要实用到116归档日志
sys@SYBO2SZ> recover database using backup controlfile;
ORA-00279: change 1078785 generated at 09/08/2012 16:20:48 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc
ORA-00280: change 1078785 for thread 1 is in sequence #116
-->下面提示在介质恢复期间有未知的文件添加到控制文件，且文件id为9
-->由此可以推断文件9是记录在尾数为116的归档日志中，正好与前面查看的归档日志时间相符
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 9: '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf'
ORA-01112: media recovery not started
-->使用alter database create datafile重建数据文件
-->此处故意使用了不同于创建之前的文件名tbs.dbf，此处并没有任何影响，相对于对数据文件进行了重命名
sys@SYBO2SZ> alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs.dbf';
Database altered.
-->尝试再次恢复，需要使用为数位116的归档日志，输入auto后，尾数为116,117的不在需要
sys@SYBO2SZ> recover database using backup controlfile;
ORA-00279: change 1078817 generated at 09/08/2012 16:29:19 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc
ORA-00280: change 1078817 for thread 1 is in sequence #116
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1078886 generated at 09/08/2012 16:30:52 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_117.arc
ORA-00280: change 1078886 for thread 1 is in sequence #117
ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_1_116.arc' no longer needed for this recovery
ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc
ORA-00280: change 1078922 for thread 1 is in sequence #118
ORA-00278: log file '/u02/database/SYBO2SZ/archive/arch_792094299_1_117.arc' no longer needed for this recovery
-->提示未找尾数为118的归档日志，118本身还没有归档，因此来说此时是联机日志
ORA-00308: cannot open archived log '/u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
-->再次恢复
sys@SYBO2SZ> recover database using backup controlfile;
ORA-00279: change 1078922 generated at 09/08/2012 16:32:22 needed for thread 1
ORA-00289: suggestion : /u02/database/SYBO2SZ/archive/arch_792094299_1_118.arc
ORA-00280: change 1078922 for thread 1 is in sequence #118
-->直接指定redo日志，介质恢复成功
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/database/SYBO2SZ/redolog/log3aSYBO2SZ.log
Log applied.
Media recovery complete.
-->下面需要使用RESETLOGS选项打开数据库
sys@SYBO2SZ> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
sys@SYBO2SZ> alter database open resetlogs;
Database altered.
--> Author : Robinson Cheng -->Blog : http://blog.csdn.net/robinson_0612
-->验证新建对象的总记录数，正好等于删除后的记录数11条
sys@SYBO2SZ> select count(*) from tb_emp;
COUNT(*)
----------
11