文章內容

Oracle控制文件的損壞或完全丟失的恢復辦法

發布時間: 2012/8/11 17:51:52

控制文件丟失了，需要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.

System altered.

Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
Oracle instance started.

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.

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.

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.

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

[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 ~]$ sed -i 's/^ //' /tmp/luo2.txt
[Oracle@huang ~]$ sed -i 's/^ //' /tmp/luo2.txt
[Oracle@huang ~]$ sed -i 's/^ //' /tmp/luo2.txt
[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

上面就是控制文件的幾種恢復方法。

本文出自：億恩科技【www.artduck.net】

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]