oracle log block size,案例:Oracle无法启动报错ORA-00218: block size 0 重建控制文件

天萃荷净

oracle数据库无法启动报错ORA-00218故障，通过重建控制文件解决文件损坏问题

遇到一个案例在数据库启动的时候报ORA-00218错误,而这个故障的引起原因是因为重建控制文件的时候,有一个控制文件无法创建,而导致了原有的控制文件被破坏,提醒:创建控制文件之前,最好对原有控制文件进行备份

数据库启动报ORA-00218错误

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 2020224 bytes

Variable Size 92277888 bytes

Database Buffers 188743680 bytes

Redo Buffers 2170880 bytes

ORA-00218: block size 0 of control file

'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)

思路分析ORA-00218错误

Oracle10g Release 1 Message

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error: ORA-00218 (ORA-218)

Text: block size %s of controlfile '%s' does not match DB_BLOCK_SIZE

(%s)

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

Cause:The block size as stored in the controlfile header is different

from the value of the initialization parameter DB_BLOCK_SIZE. This

might be due to an incorrect setting of DB_BLOCK_SIZE, or else

might indicate that the controlfile has either been corrupted or

belongs to a different database.

Action:Restore a good copy of the controlfile. If the controlfile is

known to be clean set the DB_BLOCK_SIZE to match controlfile

headers block size value.

Oracle 9.2 or Earlier Error Message

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error: ORA 218

Text: control file was created with block size now is -------------------------------------------------------------------------------

Cause: The physical block size, stored in the control file header, was

different in physical block size returned by the O/S.

This usually indicates that the control file was corrupted.

Action: Restore a good copy of the control file.

For more information about control files and recovery, see the index

entries on "control files," "control files, backing up," "control

files, recovery and" in .

通过这里可以知道,很可能是控制文件header的db_block_size和参数文件中的db_block_size的大小不一致,从而导致了该问题,而从启动数据库的错误提示上看,是控制文件的block size 为0.

分析oralce数据库控制文件

--dbv检查控制文件

[oracle@zxy bdump]$ dbvfile='/u01/app/oracle/oradata/zxy/control01.ctl' blocksize=16384

DBVERIFY: Release 10.2.0.1.0 - Production on Sun Jan 6 23:39:32 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/zxy/control01.ctl

DBVERIFY - Verification complete

Total Pages Examined : 450

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 0

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 450

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 0 (0.0)

--strings读控制文件

[oracle@zxy bdump]$ strings/u01/app/oracle/oradata/zxy/control01.ctl

}|{z

--正常库dbv检查控制文件

E:\oracle\oradata\xifenfei>dbvfile=CONTROL01.CTL blocksize=16384

DBVERIFY: Release 11.2.0.3.0 - Production on 星期一 1月 7 10:26:46 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - 开始验证: FILE = E:\ORACLE\ORADATA\XIFENFEI\CONTROL01.CTL

DBVERIFY - 验证完成

检查的页总数: 600

处理的页总数 (数据): 0

失败的页总数 (数据): 0

处理的页总数 (索引): 0

失败的页总数 (索引): 0

处理的页总数 (其他): 65

处理的总页数 (段) : 0

失败的总页数 (段) : 0

空的页总数: 535

标记为损坏的总页数: 0

流入的页总数: 0

加密的总页数 : 0

最高块 SCN : 39198 (65535.39198)

检查oralce数据库参数文件db_block_size

SQL> show parameter db_block_size

TYPE VALUE

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

db_block_size integer 8192

通过分析我们知道spfile中的db_block_size是正确的,而控制文件通过dbv和strings检测均为空值,证明是控制文件异常导致该问题,对于该问题可以通过重建控制文件或者还原备份控制文件来解决问题.

分析ora-00218问题原因

--参数文件配置

control_files = /u01/app/oracle/oradata/zxy/control01.ctl, /tmp/oradata/control04.ctl

--alert日志

Sun Jan 6 21:42:50 2013

CREATE CONTROLFILE REUSE DATABASE "ZXY" RESETLOGS NOARCHIVELOG

…………

CHARACTER SET AL32UTF8

Sun Jan 6 21:42:50 2013

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command

Default Temporary Tablespace will be necessary for a locally managed database in future release

Sun Jan 6 21:42:53 2013

Errors in file /u01/app/oracle/admin/zxy/udump/zxy_ora_3898.trc:

ORA-00200: control file could not be created

ORA-00202: control file: '/tmp/oradata/control04.ctl'

ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory

--再次启动

control_files = /u01/app/oracle/oradata/zxy/control01.ctl

--日志

ALTER DATABASE MOUNT

Sun Jan 6 21:56:31 2013

ORA-00218: block size 0 of control file

'/u01/app/oracle/oradata/zxy/control01.ctl' does not match DB_BLOCK_SIZE (0)

Sun Jan 6 21:56:31 2013

ORA-218 signalled during: ALTER DATABASE MOUNT...

创建控制文件,因为/tmp/oradata/目录不存在或者没有权限导致创建控制文件失败,而导致原来有的控制文件也失败

故障重现

--正常启动

control_files='/u01/oracle/oradata/XFF/control01.ctl'

SQL> startup pfile=/tmp/pfile

ORACLE instance started.

Total System Global Area 306184192 bytes

Fixed Size 1267164 bytes

Variable Size 109054500 bytes

Database Buffers 188743680 bytes

Redo Buffers 7118848 bytes

Database mounted.

Database opened.

SQL> alter database backup controlfile to trace as '/tmp/ctl';

Database altered.

--尝试重建控制文件

control_files='/u01/oracle/oradata/XFF/control01.ctl','/tmp/xifenfei/con.ctl'

SQL> CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/u01/oracle/oradata/XFF/redo01.log' SIZE 50M,

9 GROUP 2 '/u01/oracle/oradata/XFF/redo02.log' SIZE 50M,

10 GROUP 3 '/u01/oracle/oradata/XFF/redo03.log' SIZE 50M

11 DATAFILE

12 '/u01/oracle/oradata/XFF/system01.dbf',

13 '/u01/oracle/oradata/XFF/undotbs01.dbf',

14 '/u01/oracle/oradata/XFF/sysaux01.dbf',

15 '/u01/oracle/oradata/XFF/users01.dbf',

16 '/u01/oracle/oradata/XFF/xifenfei01.dbf',

17 '/u01/oracle/oradata/XFF/users03.dbf'

18 CHARACTER SET ZHS16GBK

19 ;

CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-00200: control file could not be created

ORA-00202: control file: '/tmp/xifenfei/con.ctl'

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

--使用原控制文件启动库

control_files='/u01/oracle/oradata/XFF/control01.ctl'

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup pfile='/tmp/pfile'

ORACLE instance started.

Total System Global Area 306184192 bytes

Fixed Size 1267164 bytes

Variable Size 109054500 bytes

Database Buffers 188743680 bytes

Redo Buffers 7118848 bytes

ORA-00218: block size 0 of control file '/u01/oracle/oradata/XFF/control01.ctl'

does not match DB_BLOCK_SIZE (0)

补充oracle参数文件中DB_BLOCK_SIZE不正确导致后果

DB_BLOCK_SIZE从8192修改为16384

SQL> startup pfile='/tmp/pfile'

ORACLE instance started.

Total System Global Area 306184192 bytes

Fixed Size 1267164 bytes

Variable Size 109054500 bytes

Database Buffers 188743680 bytes

Redo Buffers 7118848 bytes

ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle无法启动报错ORA-00218: block size 0 重建控制文件