oracle11g 控制文件丢失重建控制文件

模拟控制文件损坏
ogg-node1-> mv /u01/app/oracle/fast_recovery_area/gdb1/control02.ctl /u01/
ogg-node1-> mv /u01/app/oracle/fast_recovery_area/gdb1/control02.ctl
ogg-node1-> sqlplus  / as  sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 18:16:50 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
执行破坏，破坏后，切换日志：
SQL> alter system switch logfile;
System altered.
关闭数据库
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/gdb1/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
发现数据库 无法关闭
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ogg-node1-> sqlplus  / as  sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 18:17:52 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
只有强制关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
在启动数据库 提示ORA-00205: error in identifying control file, check alert log for more info
SQL> startup
ORACLE instance started.
Total System Global Area  776646656 bytes
Fixed Size    2257272 bytes
Variable Size  494931592 bytes
Database Buffers  276824064 bytes
Redo Buffers    2633728 bytes
ORA-00205: error in identifying control file, check alert log for more info
查看控制文件所需要的表空间
gg-node1-> cd /u01/app/oracle/oradata/              
ogg-node1-> ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Aug 19 18:15 gdb1
ogg-node1-> cd gdb1/
ogg-node1-> ll
total 3124936
-rw-r----- 1 oracle oinstall 1572872192 Aug 19 18:14 example.dbf
-rw-r----- 1 oracle oinstall   10493952 Aug 19 18:14 ggs.dbf
-rw-r----- 1 oracle oinstall   52429312 Aug 19 18:17 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Aug 19 18:14 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Aug 19 18:16 redo03.log
-rw-r----- 1 oracle oinstall  576724992 Aug 19 18:14 sysaux01.dbf
-rw-r----- 1 oracle oinstall  786440192 Aug 19 18:14 system01.dbf
-rw-r----- 1 oracle oinstall   30416896 Aug 19 18:14 temp01.dbf
-rw-r----- 1 oracle oinstall   89137152 Aug 19 18:14 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Aug 19 18:14 users01.dbf

重建控制文件脚本
create controlfile reuse database gdb1 noresetlogs
maxinstances 8
maxloghistory 1
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
datafile 
'/u01/app/oracle/oradata/gdb1/system01.dbf',
'/u01/app/oracle/oradata/gdb1/undotbs01.dbf',
'/u01/app/oracle/oradata/gdb1/example.dbf',
'/u01/app/oracle/oradata/gdb1/ggs.dbf',
'/u01/app/oracle/oradata/gdb1/users01.dbf',
'/u01/app/oracle/oradata/gdb1/sysaux01.dbf'
logfile
group 1 '/u01/app/oracle/oradata/gdb1/redo01.log' size 50m,
group 2 '/u01/app/oracle/oradata/gdb1/redo02.log' size 50m,
group 3 '/u01/app/oracle/oradata/gdb1/redo03.log' size 50m
character set we8iso8859p1;

执行数据文件重建
ogg-node1-> sqlplus / as  sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 18:30:17 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

create controlfile reuse database gdb1 noresetlogs
  2  maxinstances 8
maxloghistory 1
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
datafile 
'/u01/app/oracle/oradata/gdb1/system01.dbf',
'/u01/app/oracle/oradata/gdb1/undotbs01.dbf',
'/u01/app/oracle/oradata/gdb1/example.dbf',
'/u01/app/oracle/oradata/gdb1/ggs.dbf',
'/u01/app/oracle/oradata/gdb1/users01.dbf',
'/u01/app/oracle/oradata/gdb1/sysaux01.dbf'
logfile
group 1 '/u01/app/oracle/oradata/gdb1/redo01.log' size 50m,
group 2 '/u01/app/oracle/oradata/gdb1/redo02.log' size 50m,
group 3 '/u01/app/oracle/oradata/gdb1/redo03.log' size 50m
 18  character set we8iso8859p1;

Control file created.

重建完控制文件后，数据库已经自动mount
SQL> select status from v$instance;

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

开数据文件却需要恢复提示恢复ORA-01110: data file 1: '/u01/app/oracle/oradata/gdb1/system01.dbf'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/gdb1/system01.dbf'

可以采用一个一个恢复的方式
SQL> recover datafile 1;
Media recovery complete.
更好的方法是：
SQL>  recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN
测试一下数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size    2257272 bytes
Variable Size  494931592 bytes
Database Buffers  276824064 bytes
Redo Buffers    2633728 bytes
Database mounted.
Database opened.

SQL> select name from v$controlfile;

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