WEB开发网
开发学院数据库Oracle Oracle 基于备份控制文件的恢复(unsing backup co... 阅读

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

 2012-09-10 12:20:10 来源:WEB开发网   
核心提示: 通常在当前控制文件丢失,或者当前的控制文件与需要恢复的控制文件不一致的情况下,Oracle 基于备份控制文件的恢复(unsing backup controlfile),我们需要重新创建一个控制文件或者使用 unsingbackup controlfile方式来恢复控制文件,说简单点,118本身还没有归档,因此来说此

 通常在当前控制文件丢失,或者当前的控制文件与需要恢复的控制文件不一致的情况下,我们需要重新创建一个控制文件或者使用 unsing
backup controlfile方式来恢复控制文件。说简单点,只要是备份的控制文件与当前的控制文件不一致进行恢复数据库,就需要使用到 unsing
backup controlfile方式,而一旦使用了该方式,则需使用resetlgos选项来打开数据库。

有关控制文件的介绍请参考: Oracle 控制文件(CONTROLFILE)

一、基于备份控制文件的恢复注意事项(无论是否使用恢复目录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、控制文件全部丢失的情形(控制文件备份后发生变化)

-->首先使用热备脚本进行备份
[email protected]> 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
 
[email protected]> @db_hot_bak 

[email protected]> 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
                                                 
-->为数据库添加新的表空间,此时控制文件将不同于先前备份的控制文件                                                  
[email protected]> create tablespace tbs datafile '/u02/database/SYBO2SZ/oradata/tbs_tmp.dbf' size 10m autoextend on;

Tablespace created.

-->为数据库添加对象
[email protected]> create table tb_emp tablespace tbs as select * from scott.emp ;

Table created.

[email protected]> select count(*) from tb_emp;

  COUNT(*)
----------
        14

[email protected]> 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

[email protected]> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20120908 16:30:41

-->切换日志
[email protected]> alter system archive log current;

System altered.

-->删除部分记录用于恢复后验证
[email protected]> delete from tb_emp where deptno=10;

3 rows deleted.

[email protected]> commit;

Commit complete.

[email protected]> alter system archive log current;

System altered.

[email protected]> 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

-->异常关机
[email protected]> shutdown abort;
ORACLE instance shut down.

-->模拟所有控制文件丢失
[email protected]> ho rm -rf /u02/database/SYBO2SZ/controlf/*

[email protected]> ho ls /u02/database/SYBO2SZ/controlf/

-->启动后收到ORA-00205错误
[email protected]> 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

[email protected]> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
SYBO2SZ          STARTED

[email protected]> select name,open_mode from v$database;
select name,open_mode from v$database
                           *
ERROR at line 1:
ORA-01507: database not mounted

-->还原控制文件
[email protected]> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl

[email protected]> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl2SYBO2SZ.ctl

[email protected]> ho cp /u02/database/SYBO2SZ/backup/hotbak/contlbak.ctl /u02/database/SYBO2SZ/controlf/cntl3SYBO2SZ.ctl

-->mount数据库
[email protected]> alter database mount;

Database altered.

-->由于仅仅是丢失了控制文件,因此我们只还原控制文件
-->恢复数据库,提示需要使用BACKUP CONTROLFILE选项,因为控制文件在备份后发生了变化
[email protected]> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

-->提示需要实用到116归档日志
[email protected]> 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,此处并没有任何影响,相对于对数据文件进行了重命名
[email protected]> alter database create datafile 9 as '/u02/database/SYBO2SZ/oradata/tbs.dbf';

Database altered.

-->尝试再次恢复,需要使用为数位116的归档日志,输入auto后,尾数为116,117的不在需要
[email protected]> 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

-->再次恢复
[email protected]> 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选项打开数据库
[email protected]> alter database open;  
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

[email protected]> alter database open resetlogs;

Database altered.
--> Author : Robinson Cheng    -->Blog : http://blog.csdn.net/robinson_0612
-->验证新建对象的总记录数,正好等于删除后的记录数11条
[email protected]> select count(*) from tb_emp;

  COUNT(*)
----------
        11

1 2  下一页

Tags:Oracle 基于 备份

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接