Interim patches may be available for earlier versions - click here to check.

Symptoms:

Corruption (Logical)

Instance May Crash

HCHECK script reports this issue

Error May Occur

ORA-227 / HCKE-50

Related To:

Instance Startup

Description

The controlfile sequence number is not expected to increment so

 rapidly that it would ever reach the architectural limit during

 the database lifetime, but if the controlfile sequence number

 does reach the limit for some reason, then processes will fail

 with ORA-227 errors causing the instance to terminate, then after

 this, the database cannot be mounted again because the FG process

 doing the mount will always fail with the same ORA-00227 error.

 This fix writes regular warning messages to the alert log in the

 case where the controlfile sequence number approaches the

 architectural limit. And this fix also provides an event which

 can be set to modify the behavior of "CREATE CONTROLFILE" with

 "NORESETLOGS" so that it will reset the controlfile sequence

 number to 1 in the datafiles, the online redo logs, and the

 new controlfile.

If hcheck in Note:136697.1 is run, it may detect this issue

with error HCKE-0050, see Note:2128446.1.

Rediscovery Notes

Look for all the following:

 - various processes start reporting the following error:

     ORA-227: corrupt block detected in control file: (block 1,# blocks 1)

 - a common stack trace for the ORA-227 errors might be this:

     kcvsursuht kcc_begin_txn_internal kccocx kccchb kcccsi ...

 - then a fatal background process gets an ORA-227 error and terminates the instance

 - then the database cannot be mounted again

 - do a hex dump of the controlfile via

    $ xxd -g4 <controlfile>  > cf.hexdump

   and search forward in the hexdump for the first occurrence

   of the database name, eg "R12B" in the example below:

    ...

    0004000: 15c20000 01000000 00000000 00000104  ................

    0004010: 59320000 00000000 0000200b 24e7e472  Y2........ .$..r

    0004020: 52313242 00000000 00000000 00020000  R12B............

    ...        ^^^^^^^^

   the 3rd 32-byte word in the row with the database name is

   the controlfile sequence number, it will be zero (but it

   should never be zero, and it's the reason for the ORA-227's)

 - an "xxd" hex dump of a restored recent backup of the controlfile

   should show the controlfile sequence number is some high value

   eg e1ffffff (which is 0xffffffe1 after endian conversion).

Workaround

There is no workaround for the ORA-227 problem after it has

happened.

It is possible to proactively prevent the ORA-227 problem from

happening (before it happens) in the specific case where the

controlfile seq# is incrementing very quickly (eg many times per

second) due to storing of last scn/time of nologging operations

in the controlfile; in this specific case, the workaround is to

set:  db_unrecoverable_scn_tracking=FALSE

Solution:

After installing this fix:

1. verify the controlfile sequence# is at or above 0xFF000000:

set numwidth 15

select max(FHCSQ)

from x$kcvfh;

2. Generate Trace file to recreate the controlfile:

    alter database backup controlfile to trace noresetlogs;

3. shutdown

4. startup nomount

5. alter session set events '20324049 trace name context forever, level 1';

6. execute the commands in the tracefile generated by step#2

7. alter session set events '20324049 trace name context off';

8. confirm the controlfile sequence# is now low with the same query in 1.

Take new backups after applying this solution, otherwise recovery will not be possible

failing with these errors:

 ORA-00283: recovery session canceled due to errors

 ORA-01122: database file 1 failed verification check

 ORA-01110: data file 1: '/oracle/dbs/t_db1.f'

 ORA-01207: file is more recent than control file - old control file

CREATE CONTROLFILE is being performed with event 20324049 set, this will now reset the controlfile sequence number to 1 in the datafiles, the online redo logs, and the new controlfile.

datafile 1 header updated: controlfile sequence# reset to 1

datafile 2 header updated: controlfile sequence# reset to 1

datafile 3 header updated: controlfile sequence# reset to 1

datafile 4 header updated: controlfile sequence# reset to 1

datafile 5 header updated: controlfile sequence# reset to 1

datafile 6 header updated: controlfile sequence# reset to 1

datafile 7 header updated: controlfile sequence# reset to 1

datafile 8 header updated: controlfile sequence# reset to 1

datafile 9 header updated: controlfile sequence# reset to 1

datafile 10 header updated: controlfile sequence# reset to 1

datafile 11 header updated: controlfile sequence# reset to 1

datafile 12 header updated: controlfile sequence# reset to 1

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

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

datafile 344 header updated: controlfile sequence# reset to 1

datafile 345 header updated: controlfile sequence# reset to 1

datafile 346 header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo01-1.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo01-2.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo02-1.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo02-2.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo03-1.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo03-2.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo04-1.log header updated: controlfile sequence# reset to 1

logfile /database/xxxx/xxxx/xxxx/redo04-2.log header updated: controlfile sequence# reset to 1 

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 reading the controlfile. 

Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP CONTROLFILE 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

If Bug 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