oracle缺少控制文件,Oracle控制文件的损坏或完全丢失的恢复办法

控制文件丢失了，需要DBA处理。 1，控制文件大小不对。 将正常的cp给不正常的。指定正确的来运行。 2，控制文件版本不对

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

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

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 ~]$ 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

上面就是控制文件的几种恢复方法。

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网