遇到一个案例在数据库启动的时候报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)

数据库版本(exadata x2 1/4)

SQL> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

使用delete expired backup报ORA-19606错

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_4 channel at 12/05/2012 13:35:07
ORA-19606: Cannot copy or restore to snapshot control file

报错原因

The snapshot controlfile is cataloged as a controlfile coy and is now obsolete.
RMAN cannot delete this file as it is used by rman.

处理方法[ID 1215493.1]

Since RMAN will continue to use that file as it's snapshot copy,
you must change the location/name that RMAN is using before it will allow you to delete the file.
1. Set new name (or location) for RMAN to use for snapshot controlfile:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCF_temp.ORA';
2. Remove the snapshot controlfile from the RMAN information as a controlfile copy.
delete 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA' on operating system.
3. Crosscheck and delete the file from RMAN:
crosscheck controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
delete expired controlfilecopy 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
4. Set the snapshot controlfile name (or location) to original:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFAXMPRD.ORA';
5. Or to set the snapshot controlfile name back to default value:
CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

处理方法就是把SNAPSHOT CONTROLFILE的配置路径改变,然后删除控制文件快照

在想分析数据库性能的关键时刻,突然发现awr不能正常的工作,那就和你上了战场突然发现枪没有子弹一样的郁闷,今天就遇到了11.2.0.3在win的环境中awr生成html不能正常工作.通过查询mos发现该问题出现在各种平台中(win,linux,aix等),提醒大家注意该问题.
数据库版本

SQL> SELECT * FROM V$VERSION;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

awr报错(html)

SQL> @?/rdbms/admin/awrrpt.sql
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: 在 line 1

设置errorstack

SQL> alter session set events '6502 trace name errorstack level 12';
会话已更改。

分析错误

----- Error Stack Dump -----
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
----- Current SQL Statement for this session (sql_id=572fbaj0fdw2b) -----
select output from table(dbms_workload_repository.awr_report_html( :dbid,
                                                            :inst_num,
                                                            :bid, :eid,
                                                            :rpt_options ))
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
94348684       919  package body SYS.DBMS_WORKLOAD_REPOSITORY
983BAD54         1  anonymous block
----- Call Stack Trace -----
_skdstdst()+121      CALLrel  _kgdsdst()           19D99520 2
_ksedst1()+93        CALLrel  _skdstdst()          19D99520 0 1 485816 4863B2
                                                   485816
_ksedst()+49         CALLrel  _ksedst1()           0 1
_dbkedDefDump()+368  CALLrel  _ksedst()            0
6
_ksedmp()+44         CALLrel  _dbkedDefDump()      C 0
_dbkdaKsdActDriver(  CALLreg  00000000             C
)+4209
…………

通过查询mos发现Bug 13575143一致，可以确定是该bug,但是通过进一步测试证明不光是awrrpt会出现该错误,awr的相关报告中,只要是展示html结果的都有可能出现类此错误(比如awrrpti.sql/awrddrpt.sql/awrddrpi.sql等等).同时这里通过进一步分析发现其实该bug的起源是Bug 6458801(REPLACE on a CLOB can corrupt multibyte data ID 6458801.8),不过该bug说明已经在11.2.0.1中修复,其实通过这里的分析发现并没有真正的在11.2.0.3中修复该bug,针对该问题没有官方没有提供较好解决方法,只能是用过WORKAROUND来临时解决

They are able to generate the AWR report in the .txt format

在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei as
  2   select * from dba_objects where rownum<3000;
Table created.
SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
  2  FROM dba_extents
  3  WHERE segment_name='T_XIFENFEI' AND owner='CHF';
OWNER                          SEGMENT_NAME     EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF                            T_XIFENFEI               0          4         57          8
CHF                            T_XIFENFEI               1          4         65          8
CHF                            T_XIFENFEI               2          4         73          8
CHF                            T_XIFENFEI               3          4         81          8
CHF                            T_XIFENFEI               4          4         89          8
CHF                            T_XIFENFEI               5          4         97          8
6 rows selected.
SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
  2  dbms_rowid.rowid_relative_fno(rowid) file#
  3   FROM t_xifenfei
  4   ORDER BY 2,1;
      BLK#      FILE#
---------- ----------
        60          4
        61          4
        62          4
        63          4
        64          4
        65          4
        66          4
        67          4
        68          4
        69          4
        70          4
      BLK#      FILE#
---------- ----------
        71          4
        72          4
        74          4
        75          4
        76          4
        77          4
        78          4
        79          4
        80          4
        81          4
        82          4
      BLK#      FILE#
---------- ----------
        83          4
        84          4
        85          4
        86          4
        87          4
        88          4
        90          4
        91          4
        92          4
        93          4
        94          4
      BLK#      FILE#
---------- ----------
        95          4
        96          4
        97          4
        98          4
37 rows selected.

模拟ORA-08103

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s
SQL> STARTUP
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
                         *
ERROR at line 1:
ORA-08103: object no longer exists
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50

测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作

找到出现ORA-08103数据块

SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL>  alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL>  alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
……………………
ERROR:
ORA-08103: object no longer exists
2700 rows selected.
--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f
SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;
TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
                            16777311
SQL> select
  2  dbms_utility.data_block_address_file(16777311) FILE_NO,
  3  dbms_utility.data_block_address_block(16777311) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4         95

bbed继续破坏异常块

BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/users01.dbf
BBED> set block 95
        BLOCK#          95
BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95                                    Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> set count 32
        COUNT           32
BBED> d
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets:    0 to   31           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 1
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> d offset 8180
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01010101
 <32 bytes per line>
BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000

测试修改为坏块效果

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
      2918

通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的