控制文件的管理维护以及注意事项

常见的控制文件故障处理
1、其中一个控制文件损坏
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl
SQL> !mv /u01/app/oracle/oradata/orcl/control03.ctl /u01/app/oracle/oradata/orcl/control03.ctl.bak
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mydb01 flashback]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 28 20:17:10 2014
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force
ORACLE instance started.
Total System Global Area  276824064 bytes
Fixed Size                  2095640 bytes
Variable Size             100664808 bytes
Database Buffers          167772160 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info
alert日志：
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control03.ctl'
ORA-27037: unable to obtain file status
将/u01/app/oracle/oradata/orcl/control03.ctl从参数文件中删掉
SQL> startup pfile='/tmp/a.txt';
ORACLE instance started.
Total System Global Area  276824064 bytes
Fixed Size                  2095640 bytes
Variable Size             100664808 bytes
Database Buffers          167772160 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
奇葩需求：新增一个controlfile
将库停掉，然后cp一个，再重新启动
SQL> create pfile='/tmp/a.txt' from spfile;
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
vi /tmp/a.txt
增加新增的控制文件
SQL> startup pfile='/tmp/a.txt';
ORACLE instance started.
Total System Global Area  276824064 bytes
Fixed Size                  2095640 bytes
Variable Size             100664808 bytes
Database Buffers          167772160 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl
SQL> create spfile from pfile='/tmp/a.txt';
File created.
2、controlfile全部失效，但有rman备份
[oracle@mydb01 orcl]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Oct 28 20:26:31 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: orcl (DBID=1103215367)
RMAN> backup current controlfile format '/u01/app/oracle/backup/%T_%U.ctl';
Starting backup at 20141028 20:27:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 20141028 20:27:49
channel ORA_DISK_1: finished piece 1 at 20141028 20:27:50
piece handle=/u01/app/oracle/backup/20141028_0qpm7dm4_1_1.ctl tag=TAG20141028T202748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 20141028 20:27:50
RMAN> exit
Recovery Manager complete.
[oracle@mydb01 orcl]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 28 20:28:02 2014
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mydb01 orcl]$ mv /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control01.ctl.bak
[oracle@mydb01 orcl]$ mv /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control02.ctl.bak
[oracle@mydb01 orcl]$ mv /u01/app/oracle/oradata/orcl/control03.ctl /u01/app/oracle/oradata/orcl/control03.ctl.bak
[oracle@mydb01 orcl]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 28 20:28:57 2014
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup force
ORACLE instance started.
Total System Global Area  276824064 bytes
Fixed Size                  2095640 bytes
Variable Size             100664808 bytes
Database Buffers          167772160 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@mydb01 orcl]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Oct 28 20:29:22 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/backup/20141028_0qpm7dm4_1_1.ctl';
Starting restore at 20141028 20:29:42
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 20141028 20:29:45

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/28/2014 20:30:04
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
3、所有控制文件损坏且不存在任何备份
http://blog.csdn.net/zhuxiaoliao/article/details/41082325
4、维护控制文件的注意事项
1）、控制文件不要创建在I/O频繁的磁盘上，容易引起controlfile parallel write，controlfile parallel read，enq:CF-contention等待事件
2）、除了CKPT、LGWR、ARCH进程以外，还有server process会更新控制文件，而在RAC中，还会有ACMS后台进程进行此操作。当控制文件更新比较频繁时，进而会引起以上等待事件，进而导致数据库性能缓慢，严重时甚至会引起数据库异常关闭。
3）、更改控制文件中的内容不写redo日志，如果控制文件从备份集中恢复回来，需要检查一下内容是否符合生产库的运行要求
a、确认在线日志文件个数以及每个在线日志大小是否能满足当前系统的要求
b、确认是否开启force logging 模式。如果系统部署了DG、OGG等复制类产品，那么数据库必须打开force logging
c、确认是否开启附加日志模式，supplemental logging,如果开启了OGG则必须开启最小日志
d、确认临时文件的大小和个数是否满足当前系统运行的要求
e、在RMAN中确认show all的配置，还是否符合要求，尤其是在搭建了NBU能备份软件的情况下
4）、使用RMAN备份的控制文件进行恢复，则必须使用resetlogs选项打开
5）、重建控制文件后，数据库闪回将会失效。在重建过程中，会根据参数文件的位置覆盖原来的文件，所以最好在备份原来的控制文件；另外在重建控制文件时需要注意resetlogs和noresetlogs这两个参数，用resetlogs选项将不会校验在线日志，打开数据库时会重建在线日志；在线日志的大小决定了数据库打开的时间。
6）、控制文件的大小主要受控于控制文件中MAXDATAFILS,MAXLOGFILES,MAXLOGMEMERS,MAXLOGHISTORY和MAXINSTANCE等参数影响。很多情况下控制文件过大时由于归档日志和RMAN备份集过大造成的
7）、数据库参数control_file_record_keep_time的值默认是7，表示控制文件的备份信息或者归档信息至少保留7天，事实上，主要控制文件不重建，Oracle会使用历史最高占用槽位来保留归档日志信息。如果所有的空闲槽位都被占完，那么控制文件会动态扩展；但是频繁扩展或导致enq:CF-contention等等待事件的发生。如果系统采用RMAN进行增量备份，需要适当调整control_file_record_keep_time的值，以防全备份信息丢失。
8）、如果在数据库中没有配置控制文件的自动备份，则数据库的物理结构发生改变时需要手动的及时备份控制文件
9）、从Oracle10g开始，控制文件的块大小是16KB，跟数据库的8K没有任何关系。当控制文件存储在裸设备上时，使用dd命令来复制时需要注意是否有偏移量
10）、主机掉电，对控制文件的杀伤性巨大，因此至少需要保留两份以上的控制文件，以防止单点故障