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
- The patch also fixes other SCN unpublished bugs. so this patch is highly recommended even if you are doing hot backups with RMAN.
- Best practice to apply latest security patch.
- 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.
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.
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 http://swervedba.wordpress.com/2011/05/30/oracle-patch-sets-psu-and-cpu for differences between CPU and PSU
Tagged: Bug 12371955, calculate scn soft limit, database crash because of scn jump, db link flaw, find scn soft limit, oracle scn bug, oracle scn bug infoworld, Oracle SCN flaw, Oracle security patch, PSU4, PSU5, scn bug oracle, scn db link flaw, scn dblink flaw, SCN Flaw, scn hard limit, scn head room, SCN health check, SCN jump, SCN soft limit