Did you know that Oracle has determine that some software bugs (affecting databases between version 10.1.0.5 and 11.2.0.3) could cause the database to attempt to exceed the current maximum SCN value (The limit is currently 281 trillion) causing a transaction to be cancelled by the database, and the application would see an error message. But in the next attempt the limit is increased and typically allows the application to continue with a slight hiccough when processing, however in some very rare cases, the database does need to be shutdown to preserve its integrity.
The difference between the current SCN the database is using and the “not to exceed” upper limit for the number of SCN’s a database can use is known as the SCN headroom.
All bugs associated with SCN headroom have been fixed in the January, 2012 CPU (and associated PSU), and you also are able to download a specific patch (for your database version) to solve this issue from My Oracle Support (patch 13498243) and this patch contains a script (scnhealthcheck.sql) that allows you to check the available SCN headroom of your database. For more information please refer to My Oracle Support the documents 1376995.1 and 1393363.1 .
This is an example of the output when executing the scnhealthcheck.sql in a database:
SQL> @csnhealthcheck
————————————————————–
ScnHealthCheck
————————————————————–
Current Date: 2012/10/29 11:36:39
Current SCN: 10267706
Version: 11.2.0.3.0
————————————————————–
Result: A – SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
For further information review MOS document id 1393363.1
————————————————————–
SQL>
Cheers,
Francisco Munoz Alvarez