丢失控制文件恢复

1.部分丢失控制文件
解决方法:
关闭数据库
复制存在的给予丢失的
启动数据库

2.丢失所有控制文件
解决方法：
利用备份恢复

利用脚本重建控制文件

--实验
--1.部分丢失
SYS@PROD1> select name from v$controlfile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl

SYS@PROD1> ho rm /u01/app/oracle/oradata/PROD1/control01.ctl  --删除一个控制文件

SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:28:46 2016

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database: 
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'  --显示控制文件丢失
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@ocm1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 7 20:28:53 2016

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


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

SYS@PROD1> shutdown abort     --强制关闭数据库
ORACLE instance shut down.

SYS@PROD1> startup
ORACLE instance started.

Total System Global Area  732352512 bytes
Fixed Size		    1347456 bytes
Variable Size		  318767232 bytes
Database Buffers	  411041792 bytes
Redo Buffers		    1196032 bytes
ORA-00205: error in identifying control file, check alert log for more info  


SYS@PROD1> show parameter control_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 7
control_files			     string	 /u01/app/oracle/oradata/PROD1/
						 control01.ctl, /u01/app/oracle
						 /fast_recovery_area/PROD1/cont
						 rol02.ctl
control_management_pack_access	     string	 DIAGNOSTIC+TUNING
SYS@PROD1> ho ls /u01/app/oracle/oradata/PROD1/control01.ctl  --确认哪个控制文件丢失
ls: /u01/app/oracle/oradata/PROD1/control01.ctl: No such file or directory

SYS@PROD1> ho ls /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl

SYS@PROD1> ho cp /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl /u01/app/oracle/oradata/PROD1/control01.ctl
--copy控制文件

SYS@PROD1> alter database mount;

Database altered.

SYS@PROD1> alter database open;

Database altered.

--2.完全丢失
RMAN> backup current controlfile;  --先备份一下当前的控制文件

Starting backup at 07-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting compressed 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 07-DEC-16
channel ORA_DISK_1: finished piece 1 at 07-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp tag=TAG20161207T203249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 07-DEC-16

Starting Control File and SPFILE Autobackup at 07-DEC-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_07/o1_mf_s_929997172_d4j0cnyb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-DEC-16

[oracle@ocm1 ~]$ rm /u01/app/oracle/fast_recovery_area/PROD1/control02.ctl  --删除所有控制文件
[oracle@ocm1 ~]$ rm /u01/app/oracle/oradata/PROD1/control01.ctl 
[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:33:53 2016

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database: 
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD1/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@ocm1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 7 20:33:58 2016

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


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

SYS@PROD1> shutdown abort
ORACLE instance shut down.
SYS@PROD1> startup
ORACLE instance started.

Total System Global Area  732352512 bytes
Fixed Size		    1347456 bytes
Variable Size		  318767232 bytes
Database Buffers	  411041792 bytes
Redo Buffers		    1196032 bytes
ORA-00205: error in identifying control file, check alert log for more info


SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ ls /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp  --查看备份的控制文件
/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp
[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 7 20:35:02 2016

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

connected to target database: PROD1 (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp';  --恢复控制文件

Starting restore at 07-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/PROD1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl
Finished restore at 07-DEC-16

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 07-DEC-16
Starting implicit crosscheck backup at 07-DEC-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 28 objects
Finished implicit crosscheck backup at 07-DEC-16

Starting implicit crosscheck copy at 07-DEC-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-DEC-16

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD1/archivelog/1_98_916724026.dbf
File Name: /u01/app/oracle/fast_recovery_area/PROD1/backupset/2016_12_07/o1_mf_ncnnf_TAG20161207T203249_d4j0cm0v_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_12_07/o1_mf_s_929997172_d4j0cnyb_.bkp
File Name: /u01/app/oracle/fast_recovery_area/PROD1/autobackup/2016_07_09/o1_mf_s_916722818_cr05w3h5_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 380 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD1/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/PROD1/redo02.log thread=1 sequence=380
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-DEC-16

RMAN> alter database open resetlogs;  --以前的备份全部无效需要重新备份

database opened