==============
控制檔案中內容
==============
控制檔案主要記載了以下資訊：
（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"，共同學習，共同成長！！！

控制檔案

控制檔案

相關文章