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
If hcheck in Note:136697.1 is run, it may detect this issue
with error HCKE-0050, see Note:2128446.1.
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).
There is no workaround for the ORA-227 problem after it has
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
After installing this fix:
1. verify the controlfile sequence# is at or above 0xFF000000:
set numwidth 15
2. Generate Trace file to recreate the controlfile:
alter database backup controlfile to trace noresetlogs;
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