恢复控制文件

最新推荐文章于 2024-10-08 17:39:57 发布

weixin_34306446

最新推荐文章于 2024-10-08 17:39:57 发布

阅读量77

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/ricci/535260

版权

一．控制文件介绍
Oracle控制文件虽然不大（最大不超过20000个数据块），但是地位很重要。Oracle数据库实例启动后（即启动到nomount模式），要通过加载控制文件确定数据文件和重做日志文件的路径（进入到mount模式），然后才能打开数据库（open）。控制文件中并不是只有数据文件和重做日志文件的路径，还包括数据库名称，数据库创建信息，表空间信息，数据文件状态，日志文件信息，备份信息，检查点信息等。改文件不仅在数据库启动过程中需要，在oracle数据库运行过程中，控制文件也需要发挥重要作用，如记录检查点的相关信息，归档文件路径，备份信息（如采用rman备份的话），以及数据库发生结构修改（如添加删除表空间，数据文件，日志文件）等操作都要同步到控制文件。
二．恢复控制文件
虽然控制文件自身有冗余（默认情况下有三份），不过也可能有损坏，比如控制文件三份冗余全部放在一块磁盘上，然后那块盘坏了。
在非恢复目录（catalog）模式下，rman的备份信息都将存储在目标数据库的控制文件中，所以一旦控制文件丢失，不仅目标数据库崩溃，而且rman备份信息也尽数丢失，这种情况下，如果你有控制文件的备份，那还有救（没有备份的话，可以通过写脚本的方式自己重建控制文件）
下面的例子是在归档模式下，控制文件丢失情况下的恢复。
在应用恢复时，必须知道目标数据库的DBID，有多种方式可查，比如我们创建自动备份时，如果没有更改其命名方式，文件名会包含DBID，或者查看之前的rman备份日志，其中登陆到rman之后会显示出目标数据库的DBID

查看控制文件的路径
SQL> show parameter contr

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/oracle
/control01.ctl, /u01/app/oracl
e/oradata/oracle/control02.ctl
, /u01/app/oracle/oradata/orac
le/control03.ctl

建立测试表test
SQL> create table test as select * from scott.emp;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
14
模拟控制文件丢失
[oracle@stu118 oracle]$ mv control01.ctl control01.ctl.bak
[oracle@stu118 oracle]$ mv control02.ctl control02.ctl.bak
[oracle@stu118 oracle]$ mv control03.ctl control03.ctl.bak
[oracle@stu118 oracle]$ pwd
/u01/app/oracle/oradata/oracle

连接到rman
[oracle@stu118 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Aug 4 10:23:37 2010

connected to target database (not started)

RMAN> set dbid 1599189771

executing command: SET DBID

将数据库启动到nomount阶段
RMAN> startup nomount;

Oracle instance started

Total System Global Area 281018368 bytes

Fixed Size 1218968 bytes
Variable Size 109053544 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 04-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORACLE
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100804
channel ORA_DISK_1: looking for autobackup on day: 20100803
channel ORA_DISK_1: looking for autobackup on day: 20100802
channel ORA_DISK_1: looking for autobackup on day: 20100801
channel ORA_DISK_1: looking for autobackup on day: 20100731
channel ORA_DISK_1: looking for autobackup on day: 20100730
channel ORA_DISK_1: looking for autobackup on day: 20100729
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 08/04/2010 10:27:11
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece ----读取不到控制文件的自动备份的路径。

RMAN> set controlfile autobackup format to '/u01/rman/%F';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "to": expecting one of: "for"
RMAN-01007: at line 1 column 35 file: standard input

RMAN> set controlfile autobackup format for device type disk to '/u01/rman/%F'; ----用set命令设置controlfile自动备份的路径
注意：在nocatalog模式下修改控制文件自动备份的路径时，不要使用configure命令，因为configure命令进行的配置为永久配置，默认是要保存到目标数据库的控制文件里，（此刻控制文件丢失）因此执行configure命令会失败。应该使用set命令配置控制文件自动备份的路径。

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup; （也可以用命令 restore controlfile from '/u01/rman/c-1599189771-20100804-04')手动指定备份集的位置

Starting restore at 04-AUG-10
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORACLE
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100804
channel ORA_DISK_1: autobackup found: /u01/rman/c-1599189771-20100804-04
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/oracle/control01.ctl
output filename=/u01/app/oracle/oradata/oracle/control02.ctl
output filename=/u01/app/oracle/oradata/oracle/control03.ctl
Finished restore at 04-AUG-10
--------------------------------------------------------------------------------------------
[oracle@stu118 oracle]$ pwd
/u01/app/oracle/oradata/oracle
[oracle@stu118 oracle]$ ls
control01.ctl example01.dbf sysaux01.dbf test333.dbf
control01.ctl.bak redo01.log system01.dbf test.dbf
control02.ctl redo02.bak temp01.dbf undotbs01.dbf
control02.ctl.bak redo02.log test111.dbf undotbs02.dbf
control03.ctl redo02.log.bak test112.dbf users01.dbf
control03.ctl.bak redo03.log test11.dbf users01.dbf.bak
可以看到3个控制文件都恢复过来了
-----------------------------------------------------------------------------

RMAN> sql 'alter database mount'; 控制文件已经恢复，将目标数据库置为加载状态

sql statement: alter database mount

RMAN> restore database; --修复数据库

Starting restore at 04-AUG-10
Starting implicit crosscheck backup at 04-AUG-10
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 13 objects
Finished implicit crosscheck backup at 04-AUG-10

Starting implicit crosscheck copy at 04-AUG-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-AUG-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/oracle/undotbs01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/oracle/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/whole/whole_backup_0slkfuaf_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/whole/whole_backup_0slkfuaf_1_1 tag=MONTHLY_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/app/oracle/oradata/oracle/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/oracle/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/whole/whole_backup_0rlkfuaf_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/whole/whole_backup_0rlkfuaf_1_1 tag=MONTHLY_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/oracle/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/whole/whole_backup_0qlkfuaf_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman/whole/whole_backup_0qlkfuaf_1_1 tag=MONTHLY_BACKUP
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 04-AUG-10

恢复数据库
RMAN> recover database;

Starting recover at 04-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file /u01/app/oracle/oradata/oracle/redo03.log
archive log thread 1 sequence 11 is already on disk as file /u01/app/oracle/oradata/oracle/redo01.log
archive log filename=/u01/app/oracle/oradata/oracle/redo03.log thread=1 sequence=10
archive log filename=/u01/app/oracle/oradata/oracle/redo01.log thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 04-AUG-10

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs
由于使用备份的控制文件恢复，该文件内不包含目标数据库redologs和数据文件头部scn信息，所以必须通过resetlogs方式open。

sqlplus / as sysdba

SQL> select count(*) from test;

COUNT(*)
----------
14
成功恢复，数据也没有丢失

注意：恢复之后别忘了重建临时表空间。（rman备份时候不备份临时表空间，但是恢复的时候自动创建一个表空间），建议立即对数据库进行一次完全备份。

RMAN> backup database format '/u01/rman/whole/whole_backup_%U' plus archivelog delete all input;

转载于:https://blog.51cto.com/ricci/535260