oracle 控制檔案的修復小結
1控制檔案版本不一致
SYS@ENMOEDU> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/ENMOED
U/control01.ctl, /u01/app/orac
le/fast_recovery_area/ENMOEDU/
control02.ctl, /u01/app/oracle
/fast_recovery_area/ENMOEDU/co
ntrol04.ctl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/ENMOED
U/control01.ctl, /u01/app/orac
le/fast_recovery_area/ENMOEDU/
control02.ctl, /u01/app/oracle
/fast_recovery_area/ENMOEDU/co
ntrol04.ctl
[oracle@ENMOEDU trace]$ cd /u01/app/oracle/oradata/ENMOEDU/
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl example01.dbf redo01.log redo03.log redo05.log sysaux01.dbf temp01.dbf ts_lob01.dbf users01.dbf
control04.ctl LL redo02.log redo04.log redo06.log system01.dbf ts_idx01.dbf undotbs01.dbf
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl example01.dbf redo01.log redo03.log redo05.log sysaux01.dbf temp01.dbf ts_lob01.dbf users01.dbf
control04.ctl LL redo02.log redo04.log redo06.log system01.dbf ts_idx01.dbf undotbs01.dbf
[oracle@ENMOEDU ENMOEDU]$ cp control01.ctl control01.ctlbk #複製一份
[oracle@ENMOEDU ENMOEDU]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 22:46:41 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ENMOEDU> alter system checkpoint; #CKPT程式會更新控制檔案的scn
System altered.
[oracle@ENMOEDU ENMOEDU]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 22:46:41 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ENMOEDU> alter system checkpoint; #CKPT程式會更新控制檔案的scn
System altered.
[oracle@ENMOEDU ENMOEDU]$ rm control01.ctl
[oracle@ENMOEDU ENMOEDU]$ mv control01.ctlbk control01.ctl
[oracle@ENMOEDU ENMOEDU]$ mv control01.ctlbk control01.ctl
再次執行檢查點,執行正常(雖然是完全檢查點,但Oracle實際上是並未真正同步scn,還在排隊中)但是你建立表空間也會也會改變控制檔案,這是就會報錯,此時 sqlplus已經斷開連線,資料庫down 了下來,
SYS@ENMOEDU> create tablespace th_control datafile 'u01/app/oracle/oradata/ENMOEDU/th_control.dbf' size 10m;
create tablespace th_control datafile 'u01/app/oracle/oradata/ENMOEDU/th_control.dbf' size 10m
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8722
Session ID: 16 Serial number: 65
create tablespace th_control datafile 'u01/app/oracle/oradata/ENMOEDU/th_control.dbf' size 10m
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8722
Session ID: 16 Serial number: 65
SYS@ENMOEDU> startup #控制檔案不一致,導致不能起資料庫
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
ORA-00214: control file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl' version 1674
inconsistent with file '/u01/app/oracle/oradata/ENMOEDU/control01.ctl' version
1672
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
ORA-00214: control file
'/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl' version 1674
inconsistent with file '/u01/app/oracle/oradata/ENMOEDU/control01.ctl' version
1672
將那個不一致的舊版本控制檔案刪掉,從新的那拷一份
[oracle@ENMOEDU ENMOEDU]$ rm control01.ctl
[oracle@ENMOEDU ENMOEDU]$ cp control02.ctl /u01/app/oracle/oradata/ENMOEDU/control01.ctl
這時候就可以起來了
SYS@ENMOEDU> alter database mount;
Database altered.
SYS@ENMOEDU> alter database open;
Database altered.
2.控制檔案丟失;我們知道控制檔案儲存著資料庫的結構資訊,所以當資料庫新建表空間時,就會更新控制檔案,如果丟失就會報錯;
[oracle@ENMOEDU ENMOEDU]$ ls
archivelog autobackup backupset control02.ctl control04.ctl
[oracle@ENMOEDU ENMOEDU]$ rm control02.ctl
[oracle@ENMOEDU ENMOEDU]$ ls
archivelog autobackup backupset control04.ctl
archivelog autobackup backupset control02.ctl control04.ctl
[oracle@ENMOEDU ENMOEDU]$ rm control02.ctl
[oracle@ENMOEDU ENMOEDU]$ ls
archivelog autobackup backupset control04.ctl
SYS@ENMOEDU> create tablespace ts_control datafile '/u01/app/oracle/ts_control.dbf' size 10m;
create tablespace ts_control datafile '/u01/app/oracle/ts_control.dbf' size 10m
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
create tablespace ts_control datafile '/u01/app/oracle/ts_control.dbf' size 10m
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/u01/app/oracle/fast_recovery_area/ENMOEDU/control02.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@ENMOEDU> shutdown immediate; #提示控制檔案不存在,無法一致性關閉
ORA-00210: cannot open the specified control file
Additional information: 3
複製一份好的就可以了
SYS@ENMOEDU> shut abort
ORACLE instance shut down.
SYS@ENMOEDU> host
[oracle@ENMOEDU ENMOEDU]$ ls
archivelog autobackup backupset control04.ctl
[oracle@ENMOEDU ENMOEDU]$ cp control04.ctl control02.ctl
[oracle@ENMOEDU ENMOEDU]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 23:21:04 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
ORACLE instance shut down.
SYS@ENMOEDU> host
[oracle@ENMOEDU ENMOEDU]$ ls
archivelog autobackup backupset control04.ctl
[oracle@ENMOEDU ENMOEDU]$ cp control04.ctl control02.ctl
[oracle@ENMOEDU ENMOEDU]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 23:21:04 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
總結:控制檔案是一個非常容易解決的問題,只要我們知道了,控制檔案是多路複用的,就很好解決這個問題了,透過實驗我們還知道了,控制檔案的重要性,儲存著Oracle資料庫的結構資訊,號稱資料庫的大腦,一定要有多個控制檔案,一起使用。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-1250180/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 日誌檔案的修復小結
- 控制檔案修復
- 修復檔案終結者病毒破壞的檔案
- rman恢復控制檔案的一個小錯誤
- SQL Anywhere db檔案損壞修復 DB檔案修復 DB資料庫修復SQL資料庫
- Oracle備份與恢復【丟失控制檔案的恢復】Oracle
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 使用舊的控制檔案備份來恢復控制檔案
- oracle 體系結構-控制檔案(二)Oracle
- Oracle 體系結構-控制檔案(一)Oracle
- 小記基於控制檔案的scn不完全恢復
- SQL Server的MDF檔案恢復/修復方法SQLServer
- 控制檔案恢復—從trace檔案中恢復
- 循序漸進oracle第7章:備份與恢復之利用控制檔案快照恢復控制檔案Oracle
- Oracle 控制檔案Oracle
- 控制檔案的結構
- Oracle 11g RAMN恢復-控制檔案的備份和恢復Oracle
- oracle實驗記錄 (恢復-rman基於控制檔案的恢復)Oracle
- Oracle 控制檔案的重建Oracle
- 增加oracle的控制檔案Oracle
- 如何修復MySQL配置檔案?MySql
- RMAN恢復控制檔案
- 手工恢復控制檔案
- 恢復丟失的控制檔案
- 控制檔案的恢復方法(一)
- 控制檔案的恢復方法(二)
- 控制檔案的恢復方法(三)
- 控制檔案的恢復方法(四)
- win10系統dll檔案一鍵修復 缺少dll檔案修復方法Win10
- XFS檔案系統的備份、恢復、修復
- Oracle控制檔案在缺失歸檔日誌的情況下的恢復Oracle
- rman恢復--丟失控制檔案的恢復
- win10系統檔案受損如何修復 win10系統檔案修復的方法Win10
- 任意檔案上傳漏洞修復
- 修復boot.ini檔案非法的方法boot
- pg 檔案塊損壞的修復措施。
- Oracle重建控制檔案Oracle
- ORACLE 新增控制檔案Oracle