Learning More About Oracle Database Systems Change Number (“SCN”)

On January 17th 2012, Oracle released the January 2012 Critical Patch Update. This Critical Patch Update provided two new fixes for the Oracle Database. As usual, Oracle recommended a prompt application of the Critical Patch Update, but additionally, in the blog entry accompanying the release of the Critical Patch Update, I emphasized that Database customers should apply the Database fixes as soon as possible, explaining that the first, relatively easy to exploit, Database vulnerability could result in a complete denial of service of the Database, and that the second issue may have wider non-security implications for the databases of a very small number of customers.

In this blog entry, we are going to further discuss this second database issue, listed in the January 2012 Critical Patch Advisory as CVE-2012-0082. Note that Oracle has posted on My Oracle Support a detailed technical note on this issue along with specific recommendations for Oracle customers (See My Oracle Support Note 1376995.1).

First, let’s look at what Systems Change Numbers (SCNs) are, and why they’re important. As stated in My Oracle Support Note 1376995.1, the “System Change Number”, or SCN, is a special number used to identify database transactions. SCN values are used in many places – among other things, they are persisted within database blocks; are stored in redo records; and are used to help coordinate distributed transactions. Oracle has designed its database so that at any given point in time there is a maximum SCN value that the current SCN should not sensibly exceed – this is called the “Maximum reasonable SCN”. It is important to note that this maximum value is not a fixed value, but rather is a function of the current system time, and therefore grows over time.

In November 2011, journalists from InfoWorld contacted Oracle and stated that in a number of specific instances it appeared that the SCN of a database could grow at an excessive rate, and that this excessive SCN value could be propagated to other databases in the same environment through, among other things, database links. Oracle quickly determined that this temporary SCN exhaustion issue could have certain security implications, and as a result, in accordance with Oracle policies, Oracle handled this issue as a security bug. As a result of Oracle’s handling of the issue as a security bug, Oracle treated InfoWorld as a security researcher, and since the magazine followed responsible disclosure guidelines, InfoWorld received credit in the Critical Patch Update Advisory.

The specific conditions that could result in a temporary SCN exhaustion are complex. Oracle’s development and security teams quickly worked together to understand all the aspects of this multifaceted issue. These groups first needed to determine under which conditions SCN values could grow at an excessive rate. This meant producing diagnosing and troubleshooting scripts, documenting technical recommendations, and producing fixes for the components causing such a SCN growth to occur. In addition, this issue had to be explored from a security perspective to determine if it could be used by malicious attackers. Finally, fixes and utilities needed to be packaged for distribution (e.g. inclusion of a SCN-related Healthcheck on My Oracle Support, and patches provided through the January 2012 Critical Patch Update), and technical recommendations needed to be properly tested and documented so that they could be shared with the small number of customers who may have been at risk of running out of “SCN headroom”.

Now, let’s have a look at Oracle’s recommendations in regards to managing SCN growth in the Database environment. Oracle included in the January 2012 Critical Patch Update the “scnhealthcheck.sql” script (Patch:13498243). This script can be executed with DBA privileges and will report as to the health of the SCN growth in the database. This script is intended to provide customers with a sense of comfort that they’re not about to run out of SCN headroom, as well as potentially identify additional customers who may be running out of SCN values in their environment so that they can proactively take corrective actions.

The script will report a value of either “A”, “B”, or “C.”

If “A - SCN Headroom is good” is reported, then the SCN health in the audited database is good. The vast majority of databases are expected to fall into this group. Customers should then ensure that all their interconnected databases are patched to current level. . No additional action is required once the databases have been patched other than to set the parameter “_external_scn_rejection_threshold_hours” = 24 on some database versions. The script output will advise if this parameter needs to be set.

If “B- SCN Headroom is low” is reported, then SCN headroom is limited. Customers should then ensure that their databases are patched to the current level as soon as possible, preferably within a week, and set “_external_scn_rejection_threshold_hours” = 24 if advised to do so by the script. Once patched, customers should continue to monitor their SCN health daily by running the script, and will notice after several days or weeks that the “scnhealthcheck.sql” script will report “A”.

“C - SCN Headroom is low” will be reported in the very rare cases that customers are running out of SCN headroom. This will occur when the audited database appears to experience an excessively high rate of SCN increase. In such very rare instances, customers should immediately patch their databases to its current recommended level as listed by “My Oracle Support,” and set “_external_scn_rejection_threshold_hours” if advised to do so. In addition, Oracle recommends that these customers also follow the instructions located in My Oracle Support Note Note:1388639.1 to log a Service Request with Oracle Support so that further advice can be given and additional diagnosis performed if required.