控制文件
==============
控制文件中内容
==============
控制文件主要记载了以下信息：
（1）数据库名称
（2）数据文件名称和位置
（3）重做日志名称和位置
（4）表空间名称
（5）当前日志序列号
（6）检查点信息
（7）日志历史信息
（8）RMAN信息
其中：
MAXLOGFILES：用于指定Oracle数据库的最大日志组个数
MAXLOGMEMBERS：用于指定每个日志组的最大日志成个数
MAXDATAFILES：用于指定Oracle数据库的最大数据文件个数
MAXINSTANCES：用于指定可以同时访问数据库的最大例程个数
MAXLOGHISTORY ：用于指定控制文件可记载日志历史的最大个数
使用RMAN执行备份操作时，RMAN备份信息会被记录到控制文件中。初始化参数 control_file_record_keep_time指定了RMAN备份信息在控制文件中的保留时间，其默认值为7。
=================
控制文件多路冗余
=================
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl' scope=spfile;
SQL> startup force;
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
================
控制文件手动备份
================
1、将控制文件备份为二进制文件
SQL> alter database backup controlfile to '/home/oracle/a.ctl';
Database altered.
2、默认将控制文件备份为文本文件(备份到oracle\base\admin\sid\udump目录下的跟踪文件中，将在跟踪文件中生成一个SQL脚本)
SQL> alter database backup controlfile to trace as '/home/oracle/b.ctl';
Database altered.
==================
控制文件故障与恢复
==================
一 损坏单个控制文件
恢复方法1：损坏单个控制文件是比较容易恢复的，因为一般的数据库系统，控制文件都不止一个，而且所有的控制文件都互为镜相，只要拷贝一个好的控制文件替换坏的控制文件就可以了。
恢复方法2：修改参数文件中的控制文件参数，取消这个坏的控制文件。
1：
[oracle@chen orcl]$ echo 1111111 >control01.ctl
SQL> shutdown immediate;
ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
SQL> exit
[oracle@chen orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@chen orcl]$ cp control02.ctl control01.ctl
SQL> shutdown immediate;
ORA-03113: end-of-file on communication channel
Process ID: 4989
Session ID: 36 Serial number: 71
SQL> exit
[oracle@chen orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@chen orcl]$ sqlplus
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 805309328 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.
2：
SQL> shutdown immediate
[oracle@chen orcl]$ echo 000 >control03.ctl
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 805309328 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@chen trace]$ vim alert_orcl.log
ALTER DATABASE MOUNT
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
Linux-x86_64 Error: 2: No such file or directory
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup
二：所有控制文件都损坏
恢复方法1:重建控制文件
恢复方法2:通过rman备份恢复控制文件
SQL> alter database backup controlfile to trace as '/home/oracle/1021.ctl';
[oracle@chen orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@chen orcl]$ mv control01.ctl /home/oracle/
[oracle@chen orcl]$ mv control02.ctl /home/oracle/
[oracle@chen orcl]$ mv control03.ctl /home/oracle/
1
(1)起初数据库是关闭的
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@chen orcl]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Sat Aug 23 23:42:24 2014
Checker run found 1 new persistent data failures
重建控制文件
[oracle@chen ~]$ vim cc.sql ---来至于1021.ctl
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET ZHS16GBK
;
我这里使用的是noresetlogs，所以直接open数据库就可以了
如果是resetlogs 创建的控制文件，那么我们就需要使用：
SQL>alter database open resetlogs;
来打开DB.
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> @/home/oracle/cc.sql
Control file created.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl MOUNTED
SQL> alter database open;
Database altered.
(2) 起初数据库是开启的
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/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
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@chen trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@chen trace]$ vim alert_orcl.log
Sat Aug 23 23:07:04 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_13287.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
..............
Sat Aug 23 23:10:25 2014
ALTER DATABASE MOUNT
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
Linux-x86_64 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/orcl/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 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/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
重建控制文件
[oracle@chen ~]$ vim cc.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET ZHS16GBK
;
我这里使用的是noresetlogs，所以直接open数据库就可以了
如果是resetlogs 创建的控制文件，那么我们就需要使用：
SQL>alter database open resetlogs;
来打开DB.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> @/home/oracle/cc.sql
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
Control file created.
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/orcl/system01.dbf'
SQL> recover data file 1;
ORA-00905: missing keyword
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
SQL> recover datafile 2;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
SQL> recover datafile 3;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
重建控制文件的时候，不能写上临时表空间，等控制文件创建完毕之后，在手工的执行SQL加上临时表空间。
SQL> select file_name,tablespace_name from dba_temp_files;
no rows selected
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 10M reuse autoextend off;
Tablespace altered.
SQL> col file_name for a50
SQL> col tablespace_name for a5
SQL> select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLE
-------------------------------------------------- -----
/u01/app/oracle/oradata/orcl/temp01.dbf TEMP
注意：
不到最后时刻，如三个控制文件都已损坏，又没有控制文件的备份。还是不要重建控制文件，处理不好就会有数据丢失。
如果使用resetlogs 打开的数据库，就需要对DB做一次备份。
resetlogs命令表示一个数据库逻辑生存期的结束和另一个数据库逻辑生存期的开始，每次使用resetlogs命令的时候，SCN不会被重置，
不过oracle会重置日志序列号，而且会重置联机重做日志内容. 这样做是为了防止不完全恢复后日志序列会发生冲突（因为现有日志和数据文件间有了时间差）。
SQL> startup mount force
[oracle@chen ~]$ rman target / nocatalog
RMAN> run{
2> allocate channel d1 type disk;
3> backup current controlfile format '/home/oracle/1022.ctl';
4> }
[oracle@chen orcl]$ mv control01.ctl /home/oracle/
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 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
------------
STARTED
[oracle@chen ~]$ rman target / nocatalog
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 24 01:26:42 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
using target database control file instead of recovery catalog
RMAN> run{
2> allocate channel d1 type disk;
3> restore controlfile from '/home/oracle/1022.ctl';
4> }
allocated channel: d1
channel d1: SID=20 device type=DISK
Starting restore at 24-AUG-14
channel d1: restoring control file
channel d1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 24-AUG-14
released channel: d1
RMAN> alter database mount;
database mounted
RMAN> recover database;
.................
Finished recover at 24-AUG-14
RMAN> alter database open resetlogs;
database opened
2 通过rman的自动备份恢复控制文件
[oracle@chen orcl]$ mv control01.ctl /home/oracle/
SQL> shutdown abort;
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@chen orcl]$ rman target / nocatalog
................
connected to target database: ORCL (not mounted)
RMAN> run{
2> allocate channel d1 type disk;
3> restore controlfile from '/home/oracle/control01.ctl';
4> }
allocated channel: d1
channel d1: SID=1 device type=DISK
Starting restore at 23-AUG-14
channel d1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 23-AUG-14
released channel: d1
RMAN> alter database mount;
database mounted
RMAN> alter database open;
database opened
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29785807/viewspace-1307260/ ，如需转载，请注明出处，否则将追究法律责任。
