遇到一个案例在数据库启动的时候报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 <name> was created with block size <num> now is <num>
-------------------------------------------------------------------------------
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 <Oracle7 Server Concepts>.
通过这里可以知道,很可能是控制文件header的db_block_size和参数文件中的db_block_size的大小不一致,从而导致了该问题,而从启动数据库的错误提示上看,是控制文件的block size 为0.
分析控制文件
--dbv检查控制文件
[oracle@zxy bdump]$ dbv file='/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>dbv file=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)
检查参数文件db_block_size
SQL> show parameter db_block_size TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
通过分析我们知道spfile中的db_block_size是正确的,而控制文件通过dbv和strings检测均为空值,证明是控制文件异常导致该问题,对于该问题可以通过重建控制文件或者还原备份控制文件来解决问题.
分析问题原因
--参数文件配置 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)
补充参数文件中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)
