Oracle 00283 01110 01122,干货 | Controlfile Sequence Number耗尽引起的控制文件损坏案例

OERR:ORA-00227 corrupt block detected in controlfile: (block %s, # blocks %s) Master Note / Troubleshooting, Diagnostic and Solution (文档 ID 48808.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.0.6.0 to 12.1.0.2 [Release 8.0.6 to 12.1]

Information in this document applies to any platform.

PURPOSE

This article provides information about error ORA-00227 and possible actions.

SCOPE

This note is intended for general audience as initial starting point for beginning diagnosis of ORA-00227.

DETAILS

Error:ORA 227

Text:corrupt block detected in controlfile: (block %s, # blocks %s)

..........................................................

Cause:A block header corruption or checksum error was detected on readingthe controlfile.

Action:Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUPCONTROLFILE command.

Cause

The controlfile is corrupted. This could be caused by a problem external to Oracle like a Hardware/OS problem or an Oracle Defect.

Identify if Bug 20324049 may be causing the error ORA-227

IfBug 20324049 is causing the error ORA-227; then the maximum sequence number in the Database will be close to the maximum value (4294967295):

set numwidth 15

select max(FHCSQ),

case trunc(max(FHCSQ)/4294967295,1)

when 0.9 then 'WARNING: Reference Bug 20324049'

else 'NO Warning' end "Bug 20324049"

from x$kcvfh;

控制文件序列值(controlfile sequence number)最大值为：4294967295(即43亿左右，2的32次方减1)，可以利用上面的语句检查你的系统是否存在控制文件序列值超过极限值的风险。

Take a backup of the control files as they are now

Take a backup of the existent control files with a regular copy (cp) or any other mechanism.  This can be used for future diagnosis and in case they are needed for additional recovery.

Identify which control file is failing

ORA-00227 is normally accompanied by ORA-202 which prints the affected control file name.  Review the alert log for more details.

Execute DBVERIFY on all Control Files

DBVERIFY is a tool that is intended to identify corruptions in Datafiles but in some cases may help to identify block corruptions in Control Files.

Identify control file block size by executing dbfsize.Example:

$ dbfsize /oradata/controlfile/control1.ctl

Database file: /oradata/controlfile/control1.ctl

Database file type: file system

Database file size: 614 16384 byte blocks

In this case the control file block size is 16384.

Alternatively identify the control file block size by querying view V$CONTROLFILE.

Execute DBVERIFY on each Control File copy using the block size identified in 1.1.Example:

dbv file=/oradata/controlfile/control1.ctl blocksize=16384

Solution

Solution 1. Use another mirror copy of the control file

If it is determined that the control file is damaged (the corruption is persistent) and if the database is down, then take one of the other control files from the control_files parameter and copy it over the bad control file noted above or startup the database with one control file at a time.

Try opening the database and if error persists then go to Solution 2:

Solution 2. Recreate the control file

Use the next article to recreate the control file:

Doc ID 735106.1 How to Recreate a Controlfile

Solution 3. Restore a backup of the control file and apply media recovery

Restore a backup of control file and apply media recovery using RECOVER DATABASE USING BACKUP CONTROLFILE

If the database is on IBM AIX, make sure to review the issue described in Note 2237498.1