使用rman恢复控制文件

控制文件(controlfile)丢失恢复

基于控制文件的复合多路径性，它的丢失分为两种，一种是其中某个控制文件的损坏或丢失，另外一种是所有控制文件均丢失。基于第一种情况，只需把好的控制文件复制一份在损坏或丢失的那个控制文件路径下即可。第二种情况下则需要通过备份信息来对控制文件进行恢复或手工重建控制文件。

• 丢失单一控制文件的判断及恢复

/u01/app/oracle/oradata/test0924/control01.ctl

 

数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号

 

sys@TEST0924> shutdown immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

必须强制关闭数据库

 

sys@TEST0924> shutdown abort;

ORACLE instance shut down.

 

1、启动数据库报控制文件验证失败，检查告警日志文件。

sys@TEST0924> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1543507328 bytes

Database Buffers 1778384896 bytes

Redo Buffers 16326656 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

2、查看告警日志，报提示找不到control01.ctl

 

ri Oct 11 22:39:57 2013

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

 

3、从上面的信息我们可以得出是由于控制文件丢失导致了数据库无法正常的启动和关闭，下面我们要做的就是对控制文件进行做恢复，因为我们知道控制文件具有重复多路径属性，11g默认会有两个控制文件。现在日志中看到的是控制文件1丢失，找不到，我们可以通过控制文件2来恢复1。

 

基于正常控制文件恢复损坏的控制文件

 

1、查看控制文件存在路径

sys@TEST0924> show parameter control_file

NAME TYPE

------------------------------------ ---------------------------------

VALUE

------------------------------

control_file_record_keep_time integer

7

control_files string

/u01/app/oracle/oradata/test09

24/control01.ctl, /u01/app/ora

cle/fast_recovery_area/test092

4/control02.ctl

 

我们可以从如上看到，该套数据库存在2个控制文件其中一个控制文件存放在

 

/u01/app/oracle/oradata/test0924/control01.ctl，另外1个控制文件存在/u01/app/oracle/fast_recovery_area/test0924/control02.ctl

 

，从上面刚才的信息中我们可以得之是control01.ctl控制文件丢失导致数据库故障。

 

2、检查下控制文件是不存在还是损坏了

[oracle@rtest test0924]$ ls /u01/app/oracle/oradata/test0924/

example01.dbf fla_tbs02.dbf redo01.log redo03.log sysaux01.dbf temp01.dbf undotbs02.dbf

fla_tbs01.dbf inventory01.dbf redo02.log rman_cbt.log system01.dbf undotbs01.dbf users01.dbf

[oracle@rtest test0924]$ ls /u01/app/oracle/oradata/test0924/control01.ctl

ls: /u01/app/oracle/oradata/test0924/control01.ctl: No such file or directory

 

看看控制文件2是否存在。

 

[oracle@rtest test0924]$ ls /u01/app/oracle/fast_recovery_area/test0924/control02.ctl

/u01/app/oracle/fast_recovery_area/test0924/control02.ctl

 

controlfile2还是存在的，这样我们就可以通过controlfile2来恢复controlfile1了。

 

3、关闭数据库

 

sys@TEST0924> shutdown abort

ORACLE instance shut down.

 

4、恢复损坏丢失的控制文件

 

[oracle@rtest test0924]$ cp /u01/app/oracle/fast_recovery_area/test0924/control02.ctl /u01/app/oracle/oradata/test0924/control01.ctl

 

5、启动数据库

sys@TEST0924> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1543507328 byte

Database Buffers 1778384896 bytes

Redo Buffers 16326656 bytes

Database mounted.

Database opened.

• 所有控制文件全部丢失

[oracle@rtest test0924]$ rm /u01/app/oracle/fast_recovery_area/test0924/control02.ctl

[oracle@rtest test0924]$ rm /u01/app/oracle/oradata/test0924/control01.ctl

 

数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号。

 

sys@TEST0924> shutdowm immediate;

SP2-0734: unknown command beginning "shutdowm i..." - rest of line ignored.

sys@TEST0924> shutdown immediate;

Database closed.

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

必须强制关闭数据库

 

sys@TEST0924> shutdown abort;

ORACLE instance shut down.

 

启动数据库报控制文件验证失败，检查告警日志文件

 

sys@TEST0924> startup

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1543507328 bytes

Database Buffers 1778384896 bytes

Redo Buffers 16326656 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

检查告警日志，两个控制文件都找不到了，丢失了：

 

Fri Oct 11 22:51:44 2013

ALTER DATABASE MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/test0924/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/test0924/control01.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE MOUNT...

Fri Oct 11 22:51:45 2013

Checker run found 1 new persistent data failures

Time drift detected. Please check VKTM trace file for more details.

 

通过RMAN来进行控制文件的恢复：

 

1、强制启动数据库到nomount状态。

 

sys@TEST0924> startup force nomount;

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size 2232960 bytes

Variable Size 1543507328 bytes

Database Buffers 1778384896 bytes

Redo Buffers 16326656 bytes

 

2、另开一个窗口，连接rman，执行restore控制文件恢复。

 

[oracle@rtest ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 11 22:54:28 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TEST0924 (not mounted)

 

RMAN>  restore controlfile from autobackup;

Starting restore at 2013-10-11:22:55:45

using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: TEST0924

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkp found in the recovery area

AUTOBACKUP search with format "%F" not attempted because DBID was not set

channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkp

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=/u01/app/oracle/oradata/test0924/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/test0924/control02.ctl

Finished restore at 2013-10-11:22:55:50

 

3、装载数据库

 

SQL>alter database mount;

Database altered.

 

4、恢复数据库

 

RMAN> recover database;

Starting recover at 2013-10-11:22:58:48

Starting implicit crosscheck backup at 2013-10-11:22:58:48

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=189 device type=DISK

Crosschecked 2 objects

Finished implicit crosscheck backup at 2013-10-11:22:58:51

Starting implicit crosscheck copy at 2013-10-11:22:58:51

using channel ORA_DISK_1

Crosschecked 1 objects

Finished implicit crosscheck copy at 2013-10-11:22:58:52

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/backupset/2013_10_06/o1_mf_nnnd0_TAG20131006T204117_9540sgxc_.bkp

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arc

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arc

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arc

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arc

File Name: /u01/app/oracle/fast_recovery_area/TEST0924/autobackup/2013_10_11/o1_mf_s_828559212_95k1xfbj_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 149 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arc

archived log for thread 1 with sequence 150 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arc

archived log for thread 1 with sequence 151 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arc

archived log for thread 1 with sequence 152 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arc

archived log for thread 1 with sequence 153 is already on disk as file /u01/app/oracle/oradata/test0924/redo03.log

archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_149_95k4l193_.arc thread=1 sequence=149

archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_150_95kcbqv2_.arc thread=1 sequence=150

archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_151_95kchq3m_.arc thread=1 sequence=151

archived log file name=/u01/app/oracle/fast_recovery_area/TEST0924/archivelog/2013_10_11/o1_mf_1_152_95kg22cg_.arc thread=1 sequence=152

archived log file name=/u01/app/oracle/oradata/test0924/redo03.log thread=1 sequence=153

media recovery complete, elapsed time: 00:00:31

Finished recover at 2013-10-11:22:59:27

 

5、打开数据库

 

RMAN> alter database open resetlogs;

database opened