控制檔案
==============
控制檔案中內容
==============
控制檔案主要記載了以下資訊：
（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部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1307260/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 控制檔案Oracle
- 2.6.4 指定控制檔案
- ORACLE 控制檔案（Control Files）概述Oracle
- 控制檔案損壞處理
- 【RMAN】Oracle中如何備份控制檔案？備份控制檔案的方式有哪幾種？Oracle
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- 把“點檔案”放到版本控制中
- Controlfile 重建控制檔案 noresetlogs, resetlogs..
- windwos檔案控制代碼數限制
- oracle 控制檔案及引數檔案何時自動備份Oracle
- 使用git不希望檔案被版本控制Git
- Oracle 控制檔案損壞解決方案Oracle
- 與控制檔案有關的恢復
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- Oracle 11g 重新建立控制檔案Oracle
- oracle11g修改控制檔案路徑Oracle
- git移除已經加入版本控制的檔案Git
- 修改linux最大檔案控制程式碼數Linux
- 與控制檔案有關的恢復(二)
- [20210224]控制檔案序列號滿的分析.txt
- 填報表上下載檔案控制元件可寫許可權控制控制元件
- RAC控制檔案恢復（三種不同情況）
- [20210225]控制檔案序列號滿的恢復.txt
- 清除Oracle控制檔案中的歸檔資訊v$archived_logOracleHive
- oracle快速拿到重建控制檔案語句的方法二Oracle
- rman恢復控制檔案的一個小錯誤
- 控制檔案不一致引發的“血案”
- 技術分享 | OceanBase 資料處理之控制檔案
- 支援 ACL 訪問控制、引入 HOCON 全新配置檔案格式
- oracle ORA-01189 ORA-01110（控制檔案建立報錯）Oracle
- 關於Docx動態控制word模板檔案的資料
- 透過修改控制檔案scn推進資料庫scn資料庫
- 寶塔控制皮膚php.ini檔案在哪裡找PHP
- ajax上傳檔案，spring mvc獲取檔案並處理，通過頁面按鈕傳送url,由後臺控制檔案下載SpringMVC
- 【備份】RMAN中對控制檔案的幾種備份方法
- Linux之facl----設定檔案訪問控制列表（詳解）Linux
- 使用 SAP UI5 FileUploader 控制元件上傳本地檔案UI控制元件
- [Vue]寫一個簡單的檔案上傳控制元件Vue控制元件