Oracle bug announcement - the sky is not falling...

...well not yet...

An Infoworld article was released today describing an Oracle vulnerability. The SCN space can be exhausted via bugs or attackers exploiting bugs. Metalink doc: 1376995.1 [Bulletin : Information on the System Change Number (SCN) and how it is used in the Oracle Database] covers a technical description for what SCNs are used for.

You can use this query to find out how close you are to the SCN limit.

# Show the amount of SCN keyspace we have used so far on this database
# By default the SCN max on a 10g/11g instance is a 48-bit integer (281,474,976,710,656)
SELECT NAME, 
       (current_scn/281474976710656)*100 as PCT_OF_SCN_KEYSPACE_USED, 
       ROUND(SYSDATE-CREATED) as DAYS_SINCE_DB_CREATION,
       ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)) AS EST_DAYS_BEFORE_SCN_EXHAUSTED,
       ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)/365) AS EST_YEARS_BEFORE_SCN_EXHAUSTED 
  FROM v$database;

We have about 600 databases where I currently work. I found the busiest/largest databases and ran my query on them. All were in the 3.5% SCN keyspace used area. Depending on the life of the database I estimated all had 50+ years of normal operation at current transaction levels before hitting their head on the SCN limit.

At first I thought it was weird they were all showing up in the 3.5% ballpark... but they do share some amount of distributed transactions so this makes sense. The SCN that 'sticks' is the highest SCN involved in the distributed transaction.

Add new comment