RMAN 备份恢复系列之controlfile恢复

实验环境

  • 操作系统 Redhat5.4 x86
  • 数据库版本 oracle 11gR2 (11.2.0.1.0)
  • 实验前已经做了RMAN全量备份包括controlfile、spfile

实验模拟

部分controlfile文件损坏或丢失

案例模拟

查询数据库中控制文件信息

SQL> col name for a60
SQL> set line 180
SQL> select * from v$controlfile;
STATUS  NAME                                 IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------------ --- ---------- --------------
    /u01/app/oracle/oradata/PROD/disk1/control1          NO       16384        580
    /u01/app/oracle/oradata/PROD/disk2/control2          NO       16384        580
    /u01/app/oracle/oradata/PROD/disk3/control3          NO       16384        580

SQL> 
SQL> 
SQL> 
SQL> show parameter control

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                string  /u01/app/oracle/oradata/PROD/d
                         isk1/control1, /u01/app/oracle
                         /oradata/PROD/disk2/control2,
                         /u01/app/oracle/oradata/PROD/d
                         isk3/control3
control_management_pack_access       string  DIAGNOSTIC+TUNING
SQL> 

删除部分controlfile文件
至少保留一份controlfile文件没有被损坏或丢失。
本例删除control1 和control3 保留control2文件。

SQL> !rm /u01/app/oracle/oradata/PROD/disk1/control1

SQL> !rm /u01/app/oracle/oradata/PROD/disk3/control3

SQL> !ls /u01/app/oracle/oradata/PROD/disk1/control1
ls: /u01/app/oracle/oradata/PROD/disk1/control1: No such file or directory
SQL> 
SQL> !ls /u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control2

SQL> !ls /u01/app/oracle/oradata/PROD/disk3/control3
ls: /u01/app/oracle/oradata/PROD/disk3/control3: No such file or directory

重启数据库
重启数据库提示以下报错:

SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control1'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>                
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         658509476 bytes
Database Buffers      171966464 bytes
Redo Buffers            5160960 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> 

检查alert日志发现以下报错信息:

Fri May 05 22:03:43 2017
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk3/control3'
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/app/oracle/oradata/PROD/disk1/control1'
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...
Fri May 05 22:03:43 2017
Checker run found 2 new persistent data failures

alert 日志文件提示 control1 control3 丢失文件不存在。

案例恢复

由于三份文件中的内容是完全相同的,因此我们可以通过操作系统命令从已存在的control2,分别复制出control1,control3文件,要保持文件名以及绝对路径与损坏前相同,如果因为存储故障短时间内无法保证文件路径以及文件名一致,则需要修改spfile文件中control_files参数的值。

恢复前与恢复后文件名以及路径不变情况:
[oracle@node1 ~]$ cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk1/control1
[oracle@node1 ~]$ cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk3/control3
[oracle@node1 ~]$ ls /u01/app/oracle/oradata/PROD/disk1/control1
/u01/app/oracle/oradata/PROD/disk1/control1
[oracle@node1 ~]$ ls /u01/app/oracle/oradata/PROD/disk3/control3
/u01/app/oracle/oradata/PROD/disk3/control3
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:18:43 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;   

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

RMAN命令恢复法:

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 5 22:31:14 2017

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

connected to target database: PROD (not mounted)

RMAN>
RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
828        CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
822        CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
828        CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
822        CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Use a multiplexed copy to restore control file /u01/app/oracle/oradata/PROD/disk2/control3  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_89851306.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_89851306.hm

contents of repair script:
   # restore control file using multiplexed copy
   restore controlfile from '/u01/app/oracle/oradata/PROD/disk2/control2';
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 05-MAY-17
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/PROD/disk2/control1
output file name=/u01/app/oracle/oradata/PROD/disk2/control2
output file name=/u01/app/oracle/oradata/PROD/disk2/control3
Finished restore at 05-MAY-17

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:36:23 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ ls -la /u01/app/oracle/oradata/PROD/disk2/control*
-rw-r----- 1 oracle oinstall 9519104 May  5 22:37 /u01/app/oracle/oradata/PROD/disk2/control1
-rw-r----- 1 oracle oinstall 9519104 May  5 22:37 /u01/app/oracle/oradata/PROD/disk2/control2
-rw-r----- 1 oracle oinstall 9519104 May  5 22:37 /u01/app/oracle/oradata/PROD/disk2/control3
[oracle@node1 ~]$ 
恢复前与恢复后文件名以及路径发生变化的情况:

模拟disk1、disk3两块存放controlfile文件的磁盘都损坏了段时间内无法恢复,临时将control1、control3文件存放在disk2目录下,与control2保持一致,步骤如下:

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/disk2/control1','/u01/app/oracle/oradata/PROD/disk2/control2','/u01/app/oracle/oradata/PROD/disk2/control3' scope=spfile;

System altered.

SQL> !cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk2/control1

SQL> !cp /u01/app/oracle/oradata/PROD/disk2/control2 /u01/app/oracle/oradata/PROD/disk2/control3

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         658509476 bytes
Database Buffers      171966464 bytes
Redo Buffers            5160960 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3

SQL> 

全部controlfile文件都损坏或丢失

案例模拟

手动删除所有控制文件:

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 5 22:40:30 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk2/control1
/u01/app/oracle/oradata/PROD/disk2/control2
/u01/app/oracle/oradata/PROD/disk2/control3

SQL> !rm /u01/app/oracle/oradata/PROD/disk2/control*

SQL> !ls /u01/app/oracle/oradata/PROD/disk2/control*
ls: /u01/app/oracle/oradata/PROD/disk2/control*: No such file or directory

SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk2/control1'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL>                
SQL> shutdown abort;
ORACLE instance shut down.
SQL> 
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         658509476 bytes
Database Buffers      171966464 bytes
Redo Buffers            5160960 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> 

案例恢复

利用RMAN从之前的备份中进行恢复:

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 5 22:42:09 2017

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

connected to target database: PROD (not mounted)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1071       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
1068       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control2 is missing
1065       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1071       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control3 is missing
1068       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control2 is missing
1065       CRITICAL OPEN      05-MAY-17     Control file /u01/app/oracle/oradata/PROD/disk2/control1 is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk2/control3 was unintentionally renamed or moved, restore it
2. If file /u01/app/oracle/oradata/PROD/disk2/control2 was unintentionally renamed or moved, restore it
3. If file /u01/app/oracle/oradata/PROD/disk2/control1 was unintentionally renamed or moved, restore it
4. If a standby database is available, then perform a Data Guard failover initiated from the standby

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore a backup control file  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2346970307.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2346970307.hm

contents of repair script:
   # restore control file
   restore controlfile from autobackup;
   sql 'alter database mount';

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 05-MAY-17
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PROD
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/PROD/autobackup/2017_05_05/o1_mf_s_943223985_djs3olf9_.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/flash_recovery_area/PROD/autobackup/2017_05_05/o1_mf_s_943223985_djs3olf9_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/PROD/disk2/control1
output file name=/u01/app/oracle/oradata/PROD/disk2/control2
output file name=/u01/app/oracle/oradata/PROD/disk2/control3
Finished restore at 05-MAY-17

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

RMAN> 

至此,数据库控制文件恢复已经完成,注意每次进行玩以后及时做一次rman的全量备份。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值