控制檔案丟失了，需要DBA處理。

1，控制檔案大小不對。將正常的cp給不正常的。指定正確的來執行。

2，控制檔案版本不對千萬要記住要先cp全部的控制檔案備份後在來cp

3，控制檔案某個丟失，至少存在一個

4，控制檔案都丟失，

5，有但是很old

1，控制檔案大小不對。

下面我們修給一個控制檔案的內容使大小不一樣。

[oracle@huang ~]$ vim /sof/oracle/oradata/orcl/control01.ctl

SQL> shutdown immediate;

ORA-00227: corrupt block detected in control file: (block 1, # blocks 1)

ORA-00202: control file: '/sof/oracle/oradata/orcl/control01.ctl'

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

ORA-00205: error in identifying control file, check alert log for more info

這裡提示了控制檔案有錯誤。

SQL> show parameter control_f

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /sof/oracle/oradata/orcl/contr

ol01.ctl, /sof/oracle/oradata/

orcl/control02.ctl, /sof/oracl

e/oradata/orcl/control03.ctl

SQL> ho ls -l /sof/oracle/oradata/orcl/ 這裡我們檢視到日誌檔案中，有兩個大小一樣，說明這兩個是正常的。

total 1536352

-rw------- 1 oracle oinstall 7110639 Jan 4 16:00 control01.ctl

-rw------- 1 oracle oinstall 7389184 Jan 4 16:01 control02.ctl

-rw------- 1 oracle oinstall 7389184 Jan 4 16:01 control03.ctl

SQL> ho cp /sof/oracle/oradata/orcl/control01.ctl /sof/oracle/oradata/orcl/control01.ct.bak

SQL> ho cp /sof/oracle/oradata/orcl/control02.ctl /sof/oracle/oradata/orcl/control01.ctl

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

Database mounted.

Database opened.

現在資料庫就能正常的執行了。

2，控制檔案版本不對

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

Database mounted.

Database opened.

SQL> alter system set control_files='/sof/oracle/oradata/orcl/control02.ctl' scope=spfile;

System altered.

Database opened.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

Database mounted.

Database opened.

SQL> show parameter control_f

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /sof/oracle/oradata/orcl/contr

ol02.ctl

SQL> alter system set control_files='/sof/oracle/oradata/orcl/control02.ctl','/sof/oracle/oradata/orcl/control01.ctl','/sof/oracle/oradata/orcl/control03.ctl' scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

ORA-00214: control file '/sof/oracle/oradata/orcl/control02.ctl' version 1036

inconsistent with file '/sof/oracle/oradata/orcl/control01.ctl' version 1020

現在就提示了控制檔案的版本好不一致的問題。注意提示的版本號那個更高。

SQL> ho cp /sof/oracle/oradata/orcl/control01.ctl /sof/oracle/oradata/orcl/control01.ctl.bak

SQL> ho cp /sof/oracle/oradata/orcl/control02.ctl /sof/oracle/oradata/orcl/control01.ctl

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

ORA-00214: control file '/sof/oracle/oradata/orcl/control02.ctl' version 1036

inconsistent with file '/sof/oracle/oradata/orcl/control03.ctl' version 1020

SQL> ho cp /sof/oracle/oradata/orcl/control03.ctl /sof/oracle/oradata/orcl/control03.ctl.bak

SQL> ho cp /sof/oracle/oradata/orcl/control02.ctl /sof/oracle/oradata/orcl/control03.ctl

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

Database mounted.

Database opened.

上面就說明成了。

4，控制檔案都丟失，

5，有但是很old

SQL> alter database backup controlfile to trace as '/tmp/luo.txt'

2 ;

Database altered.

得到建立日誌檔案的指令碼，以後我們資料庫建好後就要做。

下面就是重建控制檔案。

千萬記住下面這個命令。

如果控制檔案全部丟失，版本過老，在我們重建控制檔案之前需要備份故障的狀態。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@huang ~]$ cp /sof/oracle/oradata/orcl/ /sof/oracle/oradata/orcl.bak -rf

[oracle@huang ~]$ rm /sof/oracle/oradata/orcl/control0* -rf

[oracle@huang ~]$ ls /sof/oracle/oradata/orcl

orcl/ orcl.bak/

[oracle@huang ~]$ ls /sof/oracle/oradata/orcl

orcl/ orcl.bak/

[oracle@huang ~]$ ls /sof/oracle/oradata/orcl/

example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

redo01.log redo03.log system01.dbf undotbs01.dbf

[oracle@huang ~]$ sqlplus / as sysdba

[uniread] Loaded history (945 lines)

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 4 16:57:00 2010

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

ORA-00205: error in identifying control file, check alert log for more info

去除所有的--和空格行，和開頭的空格。

[oracle@huang ~]$ grep -v ^-- /tmp/luo.txt|grep -v ^$ >/tmp/luo1.txt

[oracle@huang ~]$ vim /tmp/luo1.txt

[oracle@huang ~]$ sed -n '1,23'p /tmp/luo1.txt >/tmp/luo2.txt

擷取下面兩行和之間的內容。注意是下面行是第一次出現的時候

STARTUP NOMOUNT

..................

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

[oracle@huang ~]$ sed -i 's/^ //' /tmp/luo2.txt

[oracle@huang ~]$ vim /tmp/luo2.txt

確定時候空格和--開頭的行就刪除了。

SQL> shutdown abort;

ORACLE instance shut down.

SQL> @/tmp/luo2.txt 我們執行剛才的那個指令碼就可以重新的建立起控制檔案

ORACLE instance started.

Total System Global Area 1090519040 bytes

Fixed Size 1267020 bytes

Variable Size 704645812 bytes

Database Buffers 369098752 bytes

Redo Buffers 15507456 bytes

Control file created.

Media recovery complete.

Database altered.

Tablespace altered.

SQL> ho ls /sof/oracle/oradata/orcl/

control01.ctl example01.dbf redo03.log temp01.dbf

control02.ctl redo01.log sysaux01.dbf undotbs01.dbf

control03.ctl redo02.log system01.dbf users01.dbf

上面就是控制檔案的幾種恢復方法。

oracle職場休閒

oracle控制檔案的損壞或完全丟失的恢復辦法

相關文章