丢失全部控制文件后从RMAN备份集中恢复示例

最新推荐文章于 2024-07-07 14:08:34 发布

Yushan Bai

最新推荐文章于 2024-07-07 14:08:34 发布

阅读量4.8k

点赞数

分类专栏： ORACLE RMAN备份与恢复

本文链接：https://blog.csdn.net/haibusuanyun/article/details/13206681

版权

ORACLE RMAN备份与恢复专栏收录该内容

33 篇文章 0 订阅

订阅专栏

丢失全部控制文件后用RMAN恢复的大致步骤可以写为：

startup nomount;
restore controlfile from autobackup;或者指定的有控制文件备份的备份集
alter database mount;
recover database;
alter database open resetlogs;

为什么要用resetlogs打开数据库呢？是因为用了备份控制文件，recover命令只能修复截止到控制文件备份时的数据库物理结构信息，而无法修改控制文件中的当前重做日志的序列号等信息，recover命令执行完毕后，控制文件中当前在线日志序列号还是陈旧的（是当初备份时的）。若按常规方式打开数据库，将报错，Oracle采用重设日志功能，日志序列号就从1重新开始。

1.先备份当前控制文件

[oracle@bys001 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 27 19:01:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BYS1 (DBID=3957527513)
RMAN> backup current controlfile;

Starting backup at 2013/10/27 19:02:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2013/10/27 19:02:03
channel ORA_DISK_1: finished piece 1 at 2013/10/27 19:02:06
piece handle=/u01/flash_recovery_area/BYS1/backupset/2013_10_27/o1_mf_ncnnf_TAG20131027T190202_96swoch8_.bkp tag=TAG20131027T190202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finshed backup at 2013/10/27 19:02:06

RMAN> exit

Recovery Manager complete.

2.关库并移除控制文件后打开数据库-出错

[oracle@bys001 ~]$ sqlplus / as sysdba
SYS@bys1>shutdown abort;
ORACLE instance shut down.
SYS@bys1>exit

[oracle@bys001 flash_recovery_area]$ cd bys1/
[oracle@bys001 bys1]$ ls
control02.ctl
[oracle@bys001 bys1]$ mv control02.ctl control02.ctla

[oracle@bys001 oradata]$ cd bys1/
[oracle@bys001 bys1]$ ls
control01.ctl redo01.log redo03.log    system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf
[oracle@bys001 bys1]$ mv control01.ctl control01.ctla

[oracle@bys001 bys1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 27 19:08:50 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SYS@bys1>startup
ORACLE instance started.
Total System Global Area 631914496 bytes
Fixed Size                  1338364 bytes
Variable Size             385876996 bytes
Database Buffers          239075328 bytes
Redo Buffers                5623808 bytes
ORA-00205: error in identifying control file, check alert log for more info
SYS@bys1>exit

3.使用RMAN恢复控制文件并打开数据库

[oracle@bys001 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 27 19:11:54 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BYS1 (not mounted)
因为未MOUNT，也就无法使用控制文件，此时RMAN将不能从控制文件中找到备份信息的元数据，所以报错并指出未在MOUNT。

RMAN> list backup;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 10/27/2013 19:10:26
ORA-01507: database not mounted

RMAN> restore controlfile from autobackup;

Starting restore at 2013/10/27 19:12:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

recovery area destination: /u01/flash_recovery_area
database name (or database unique name) used for search: BYS1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set        ----从这句报错，可以看到，使用自动备份来恢复控制文件，需要先设置DBID，不然RMAN无法根据自动备份文件名来搜索备份集用来进行恢复。
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/27/2013 19:12:13
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> set dbid 3957527513    此时就需要设置DBID，DBID可以从之前RMAN的日志中查看，或者控制文件之类的备份时使用系统默认的格式-含DBID或者平时自己记录。详见： 在数据库各种状态下查询DBID的五大类十种方法汇总
executing command: SET DBID
RMAN> restore controlfile from autobackup;      此时仍未找到自动备份文件--可能因为在RMAN的CONFIGURE中未设置控制文件为自动备份也没设置备份目录。
Starting restore at 2013/10/27 19:13:18
using channel ORA_DISK_1

recovery area destination: /u01/flash_recovery_area
database name (or database unique name) used for search: BYS1
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131027
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131026
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131025
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131024
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131023
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131022
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131021
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/27/2013 19:13:20
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

直接指定RMAN从备份的控制文件集中恢复
RMAN> restore controlfile from '/u01/flash_recovery_area/BYS1/backupset/2013_10_27/o1_mf_ncnnf_TAG20131027T190202_96swoch8_.bkp';

Starting restore at 2013/10/27 19:14:14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oradata/bys1/control01.ctl
output file name=/u01/flash_recovery_area/bys1/control02.ctl

Finished restore at 2013/10/27 19:14:17

可以看到恢复完成，并都恢复到了原来的控制文件存在的位置。

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 2013/10/27 19:17:38
Starting implicit crosscheck backup at 2013/10/27 19:17:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2013/10/27 19:17:39

Starting implicit crosscheck copy at 2013/10/27 19:17:39
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2013/10/27 19:17:39

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/flash_recovery_area/BYS1/backupset/2013_10_27/o1_mf_ncnnf_TAG20131027T190202_96swoch8_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 88 is already on disk as file /u01/oradata/bys1/redo01.log
archived log for thread 1 with sequence 89 is already on disk as file /u01/oradata/bys1/redo02.log
archived log file name=/u01/oradata/bys1/redo01.log thread=1 sequence=88
archived log file name=/u01/oradata/bys1/redo02.log thread=1 sequence=89
media recovery complete, elapsed time: 00:00:01
Finished recover at 2013/10/27 19:17:41

可以看到恢复时自动应用了归档日志。

RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/27/2013 19:18:06
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

此时需要用RESETLOG方式打开了。

RMAN> alter database open resetlogs;
database opened

4.恢复期间ALERT日志

[oracle@bys001 ~]$ tail -n 150 alert_bys1.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/flash_recovery_area/bys1/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/bys1/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Sun Oct 27 19:08:58 2013
Checker run found 2 new persistent data failures
#########################################################################
Sun Oct 27 19:17:20 2013
alter database mount
Sun Oct 27 19:17:24 2013
Successful mount of redo thread 1, with mount id 3965132800
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
#############################################################################
Sun Oct 27 19:17:39 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
alter database recover logfile '/u01/oradata/bys1/redo01.log'
Media Recovery Log /u01/oradata/bys1/redo01.log
ORA-279 signalled during: alter database recover logfile '/u01/oradata/bys1/redo01.log'...
alter database recover logfile '/u01/oradata/bys1/redo02.log'
Media Recovery Log /u01/oradata/bys1/redo02.log
Incomplete recovery applied all redo ever generated.
Recovery completed through change 2541691 time 10/27/2013 19:02:16
Media Recovery Complete (bys1)
Completed: alter database recover logfile '/u01/oradata/bys1/redo02.log'
#########################################################################
Sun Oct 27 19:18:06 2013
alter database open
Errors in file /u01/diag/rdbms/bys1/bys1/trace/bys1_ora_27082.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
#########################################################################
###########################################################################
alter database open resetlogs
Expanded controlfile section 11 from 28 to 56 records
Requested to grow by 28 records; added 1 blocks of records
Archived Log entry 87 added for thread 1 sequence 88 ID 0xebe3b9d9 dest 1:
Archived Log entry 88 added for thread 1 sequence 89 ID 0xebe3b9d9 dest 1:
Archived Log entry 89 added for thread 1 sequence 87 ID 0xebe3b9d9 dest 1:
RESETLOGS after complete recovery through change 2541691
Resetting resetlogs activation ID 3957570009 (0xebe3b9d9)