Oracle SCN Bug

Oracle February 8, 2012

Oracle community seems  to be in some what confused state after Infoworld  published an article about Oracle SCN flaw. Questions like “Am I affected” or “When should I apply the patch”  are common.  Personally I think this is a low risk bug but applying the patch to mitigate the risk is highly recommended for following reasons

  1. The patch also fixes other SCN unpublished bugs. so this patch is highly recommended even if you are doing hot backups with RMAN.
  2. Best practice to apply latest security patch.
  3. The patch introduces new checks/threshold against SCN contamination from db links.   Oracle has not disclosed the  threshold but very useful if you are not running the SCN health check on a daily routine. With the patch you are protected from databases with SCNs close to soft limits   via db links.
.
Oracle SCN
.
System Change Number or SCN  is internal timestamp used by Oracle database to comply with ACID properties of database.  Every transaction in the database is assigned a  (unique) SCN value.  Generally the SCN values assigned for each transaction is supposed to be unique but  quite often we have noticed transactions with duplicate SCN values  mostly under high concurrency.
.
Oracle SCN is incremented by 16384 for every second since 1/1/1988. This number is also called soft Limit and increase every second.  The maximum value or hard limit of SCN supported by Oracle is 281 trillion, good enough to last 500 years. At any point in time, the database SCN cannot be greater than SCN Soft limit.  This might temporarily make the database unavailable or  may cause the  database to crash with ORA-600 errors. The database will start to function normally as you start to accumulate SCNs with time. Without any bugs, it is possible but rare to use up 16384 SCN’s in 1 second. In unusual circumstances  and if required, you can also fool the database by  changing the system date to sometime in future but this may introduce data issues.
.
SCN soft limit can be calculated with following query
.

COLUMN SCN FORMAT 999999999999999999999999999

SELECT (SYSDATE – TO_DATE(’01-JAN-1988′)) * 24*60*60 *16384 AS SCN FROM DUAL;

.

Here is simple example to understand working of SCN and for sake of simplicity , lets assume the current Value of SCN at 10:00:01 AM is X.

Click on the diagram for larger view.


Now lets talk about the hot backup bug that impacts 11g databases

The hot backup bug( 12371955 ) causes SCN values to jump very high causing it to  reach the soft limit earlier than how Oracle intended.  This bug is not exposed with hot RMAN backups. As per Oracle, the bug is categorized as  hot backup bug which seems to indicate that both  “ALTER DATABASE/TABLESPACE …. BEGIN/END BACKUP command  instead of  “ALTER DATABASE BEGIN/END BACKUP”  command only.    There are other unknown triggers that may cause this jump too.

Database links and SCN Jump

Now introduce databases interconnected via database link.  When databases are interconnected via database link, Oracle synchronizes the SCN value of the database with the highest SCN to all the other interconnected databases whenever they communicate with each other. So even if one of your databases is infected with this bug, it impacts all interconnected databases irrespective of the version of database with respect to propagating the SCN values.

From the above diagram, databases db1 to db8 are interconnected by db links. So  even if one of the database gets infected with the bug, it propagates to all the databases.

The Fix

Apply Oracle recommended PSU or CPU patch. The patch fix resolves  the hot backup bug and many other unknowns but you are still not fully protected from SCN hacks or other unknown triggers. So it is recommended to run SCN health check at regular intervals to prevent any runaway growth.  The patch also collects additional diagnostic information if the issues occur due to some other undocumented reason.  Refer to  metalink doc 1393363.1 to download the  SCN health check.

Please refer to https://swervedba.wordpress.com/2011/05/30/oracle-patch-sets-psu-and-cpu  for differences between CPU and PSU

Tagged: , , , , , , , , , , , , , , , , , , ,

%d bloggers like this: