RMAN恢复控制文件-控制文件(controlfile)丢失恢复
基于控制文件的复合多路径性，它的丢失分为两种，一种是其中某个控制文件的损坏或丢失，另外一种是所有控制文件均丢失。
基于第一种情况，只需把好的控制文件复制一份在损坏或丢失的那个控制文件路径下即可。第二种情况下则需要通过备份信息来对控制文件进行恢复或手工重建控制文件。
情景一：单一控制文件丢失
查看控制文件
SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/oracle/11g/oradata/oracle/control01.ctl/oracle/11g/flash_recovery_area/oracle/control02.ctl
##或者
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/11g/oradata/oracle/con
trol01.ctl, /oracle/11g/flash_
recovery_area/oracle/control02
.ctl
模拟控制文件丢失，删除控制文件/oracle/11g/oradata/oracle/control01.ctl
[oracle@oracle ~]$ rm /oracle/11g/oradata/oracle/control01.ctl
关闭数据库，数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号，此时需要使用abort关闭
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/11g/oradata/oracle/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
启动数据库失败
SQL> startup;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
ORA-00205: error in identifying control file, check alert log for more info
使用其他的控制文件恢复
[oracle@oracle ~]$ cp -a /oracle/11g/flash_recovery_area/oracle/control02.ctl /oracle/11g/oradata/oracle/control01.ctl
[oracle@oracle ~]$ ll /oracle/11g/oradata/oracle/control01.ctl
-rw-r-----. 1 oracle oinstall 9945088 May 17 13:51 /oracle/11g/oradata/oracle/control01.ctl
重新启动
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
Database mounted.
Database opened.
情景二：全部控制文件丢失，但是有自动备份控制文件
RMAN> show all;
RMAN configuration parameters for database with db_unique_name MYSQL_MA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; ##开启自动备份控制文件
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default ##保存在闪回区中
自动备份，备份文件放在闪回区
RMAN> backup tablespace users;
...
...
Starting Control File and SPFILE Autobackup at 17-MAY-18
piece handle=/oracle/11g/flash_recovery_area/MYSQL_MA/autobackup/2018_05_17/o1_mf_s_976388621_fhw2lg7b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-MAY-18
删除所有的控制文件
[oracle@mysql-master-1003306 ~]$ rm /oracle/11g/oradata/mysql_master_1003306/control01.ctl /oracle/11g/flash_recovery_area/mysql_master_1003306/control02.ctl
关闭数据库，数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号，此时需要使用abort关闭
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/11g/oradata/oracle/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
rman连接到目标库,如果报错ORA-12528: TNS:listener: all appropriate instances are blocking new connections，则需要配置静态注册，使用静态注册的服务名来连，因为此时数据库连接是blocked的
[oracle@oracle oracle]$ rman target sys/Oracle_1@ma1 catalog rman/Rman_1@rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 17 18:38:29 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
恢复控制文件
RMAN> startup nomount;
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
RMAN> restore controlfile from autobackup; ##自动识别自动备份里面的备份
Starting restore at 17-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
recovery area destination: /oracle/11g/flash_recovery_area
database name (or database unique name) used for search: MYSQL_MA
channel ORA_DISK_1: AUTOBACKUP /oracle/11g/flash_recovery_area/MYSQL_MA/autobackup/2018_05_17/o1_mf_s_976388621_fhw2lg7b_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20180517
channel ORA_DISK_1: restoring control file from AUTOBACKUP /oracle/11g/flash_recovery_area/MYSQL_MA/autobackup/2018_05_17/o1_mf_s_976388621_fhw2lg7b_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/oracle/11g/oradata/mysql_master_1003306/control01.ctl
output file name=/oracle/11g/flash_recovery_area/mysql_master_1003306/control02.ctl
Finished restore at 17-MAY-18
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 17-MAY-18
Starting implicit crosscheck backup at 17-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 17-MAY-18
Starting implicit crosscheck copy at 17-MAY-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-MAY-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/11g/flash_recovery_area/MYSQL_MA/autobackup/2018_05_17/o1_mf_s_976388621_fhw2lg7b_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /oracle/11g/oradata/mysql_master_1003306/redo01.log
archived log file name=/oracle/11g/oradata/mysql_master_1003306/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-MAY-18
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
以上是备份文件放在闪回区内，如果备份文件没有放在闪回区内，可能需要设置DBID，但是在oracle11g中，我试验的时候不需要设置DBID也可以进行恢复；
情景三：全部控制文件丢失，没有自动备份控制文件，但是有数据库全备
查看控制文件
sys@MYSQL_MA> select name from v$controlfile;NAME-----------------------------------/oracle/11g/oradata/mysql_master_1003306/control01.ctl/oracle/11g/flash_recovery_area/mysql_master_1003306/control02.ctl
##或者
sys@MYSQL_MA> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/11g/oradata/mysql_mast
er_1003306/control01.ctl, /ora
cle/11g/flash_recovery_area/my
sql_master_1003306/control02.c
tl
RMAN没有开启自动备份控制文件
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORACLE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
...
...
全备数据库
RMAN> backup database plus archivelog delete all input;
Starting backup at 17-MAY-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=55 RECID=59 STAMP=976314177
input archived log thread=1 sequence=56 RECID=60 STAMP=976385529
channel ORA_DISK_1: starting piece 1 at 17-MAY-18
channel ORA_DISK_1: finished piece 1 at 17-MAY-18
piece handle=/oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_annnn_TAG20180517T181210_fhvzktll_.bkp tag=TAG20180517T181210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oracle/11g/flash_recovery_area/MYSQL_MA/archivelog/2018_05_16/o1_mf_1_55_fhssvy7r_.arc RECID=59 STAMP=976314177
...
...
删除所有的控制文件
[oracle@mysql-master-1003306 ~]$ rm /oracle/11g/oradata/mysql_master_1003306/control01.ctl /oracle/11g/flash_recovery_area/mysql_master_1003306/control02.ctl
停库
SQL> shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/11g/oradata/oracle/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
rman连接到目标库,如果报错ORA-12528: TNS:listener: all appropriate instances are blocking new connections，则需要配置静态注册，使用静态注册的服务名来连，因为此时数据库连接是blocked的
[oracle@oracle oracle]$ rman target sys/Oracle_1@ma1 catalog rman/Rman_1@rman
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 17 18:38:29 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
恢复控制文件
##将数据库启动到nomount状态
RMAN> startup nomount;
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
##查看含有控制文件的全备备份集
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
597 Full 13.05M DISK 00:00:01 17-MAY-18
BP Key: 600 Status: AVAILABLE Compressed: NO Tag: TAG20180517T182027
Piece Name: /oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp
SPFILE Included: Modification time: 17-MAY-18
SPFILE db_unique_name: MYSQL_MA
Control File Included: Ckp SCN: 1517178 Ckp time: 17-MAY-18
##使用该备份集恢复控制文件，自动选择最新备份集
RMAN> restore controlfile;
Starting restore at 17-MAY-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp
channel ORA_DISK_1: piece handle=/oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp tag=TAG20180517T182027
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/11g/oradata/mysql_master_1003306/control01.ctl
output file name=/oracle/11g/flash_recovery_area/mysql_master_1003306/control02.ctl
Finished restore at 17-MAY-18
##使用该备份集恢复控制文件，手动指定备份集
RMAN> restore controlfile to '/oracle/11g/oradata/mysql_master_1003306/control01.ctl' from tag 'TAG20180517T182027';
Starting restore at 17-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
output file name=/oracle/11g/oradata/mysql_master_1003306/control01.ctl
channel ORA_DISK_1: reading from backup piece /oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp
channel ORA_DISK_1: piece handle=/oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp tag=TAG20180517T182027
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-MAY-18
启动并恢复数据库
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 17-MAY-18
Starting implicit crosscheck backup at 17-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 17-MAY-18
Starting implicit crosscheck copy at 17-MAY-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-MAY-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_ncsnf_TAG20180517T182027_fhw02hlh_.bkp
File Name: /oracle/11g/flash_recovery_area/MYSQL_MA/backupset/2018_05_17/o1_mf_annnn_TAG20180517T182105_fhw02knm_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 59 is already on disk as file /oracle/11g/oradata/mysql_master_1003306/redo02.log
archived log for thread 1 with sequence 60 is already on disk as file /oracle/11g/oradata/mysql_master_1003306/redo03.log
archived log file name=/oracle/11g/oradata/mysql_master_1003306/redo02.log thread=1 sequence=59
archived log file name=/oracle/11g/oradata/mysql_master_1003306/redo03.log thread=1 sequence=60
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-MAY-18
RMAN> alter database open resetlogs; ##恢复控制文件也需要resetlogs
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
情景四：啥都没有，重建控制文件
删除所有的控制文件
[oracle@oracle ~]$ rm /oracle/11g/oradata/oracle/control01.ctl /oracle/11g/flash_recovery_area/oracle/control02.ctl
关闭数据库，数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号，此时需要使用abort关闭
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/11g/oradata/oracle/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
重建控制文件
步骤1：获取数据库名及字符集
SQL> create pfile from spfile;
File created.
##查看pfile
[oracle@oracle dbs]$ cat initorcl.ora | grep db_name
*.db_name='oracle'
##开启到nomount模式，查看字符集
SQL> start nomount;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII
步骤2：获取数据文件路径及名称
[oracle@oracle oracle]$ ll /oracle/11g/oradata/oracle/*.dbf
-rw-r-----. 1 oracle oinstall 1073750016 May 17 14:16 /oracle/11g/oradata/oracle/bigtbs.dbf
-rw-r-----. 1 oracle oinstall 650125312 May 17 14:16 /oracle/11g/oradata/oracle/sysaux01.dbf
-rw-r-----. 1 oracle oinstall 744497152 May 17 14:16 /oracle/11g/oradata/oracle/system01.dbf
-rw-r-----. 1 oracle oinstall 524296192 May 17 14:16 /oracle/11g/oradata/oracle/tbs01.dbf
-rw-r-----. 1 oracle oinstall 20979712 May 8 03:16 /oracle/11g/oradata/oracle/temp001.dbf
-rw-r-----. 1 oracle oinstall 20979712 May 8 03:16 /oracle/11g/oradata/oracle/temp002.dbf
-rw-r-----. 1 oracle oinstall 35659776 May 16 22:02 /oracle/11g/oradata/oracle/temp01.dbf
-rw-r-----. 1 oracle oinstall 41951232 May 17 14:16 /oracle/11g/oradata/oracle/undo01.dbf
-rw-r-----. 1 oracle oinstall 78651392 May 17 14:16 /oracle/11g/oradata/oracle/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5251072 May 17 14:16 /oracle/11g/oradata/oracle/users01.dbf
步骤3：生成创建控制文件脚本，注意去掉temp表空间
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "oracle" NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/oracle/11g/oradata/oracle/redo01.log' SIZE 50M,
GROUP 2 '/oracle/11g/oradata/oracle/redo02.log' SIZE 50M,
GROUP 3 '/oracle/11g/oradata/oracle/redo03.log' SIZE 50M
DATAFILE
'/oracle/11g/oradata/oracle/bigtbs.dbf',
'/oracle/11g/oradata/oracle/sysaux01.dbf',
'/oracle/11g/oradata/oracle/system01.dbf',
'/oracle/11g/oradata/oracle/tbs01.dbf',
'/oracle/11g/oradata/oracle/undo01.dbf',
'/oracle/11g/oradata/oracle/undotbs01.dbf',
'/oracle/11g/oradata/oracle/users01.dbf'
CHARACTER SET US7ASCII
;
步骤4：执行脚本
SQL> @/oracle/11g/product/11.2.0/dbhome_1/dbs/a.sql
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 331352096 bytes
Database Buffers 71303168 bytes
Redo Buffers 4325376 bytes
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com