Thursday, January 19, 2012

2012 January PSU and the growth in the System Change Number (SCN) bug.

With 2012 January PSU/CPU Oracle has patched the SCN growth bug, the case when a database is having a high rate change for SCN. Computer world has an comprehensive article about this issue.

Following metalink notes provides means to test if the database is affected by this bug.
Information on the System Change Number (SCN) and how it is used in the Oracle Database [ID 1376995.1]
Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]
Evidence to collect when reporting "high SCN rate" issues to Oracle Support [ID 1388639.1]

scnhealthcheck.sql mentioned in 1393363.1 could be used to verify if the database is affected.
SQL> @scnhealthcheck.sql
Current Date: 2012/01/19 10:39:57
Current SCN:  4165911
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
Recommendations are if the output is Result: A - SCN Headroom is good then system SCN health is good apply the recommended patches (756671.1) during the normal maintenance schedule.

If the output is Result: B - SCN Headroom is low then apply the recommended patches immediately outside the normal maintenance schedule and continue to monitor the SCN health. Once patched headroom will increase overtime (could take days or weeks) to script to report Result: A - SCN Headroom is good.

If the output is Result: C - SCN Headroom is low then apply the recommended patches immediately and collect required information listed on 1388639.1 and raise a SR so additional advice could be given.

With this PSU a new hidden parameter called "_external_scn_rejection_threshold_hours" has been introduced in connection with this bug. Once the patch is applied Oracle recommend setting this to 24 on 10g and 11gR1 systems. Value of _external_scn_rejection_threshold_hours after patched applied on 11gR1 (
To set the value to 24
alter system set "_external_scn_rejection_threshold_hours" = 24 
   comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1' 
   scope=spfile ;
On 11gR2 systems this set to 24 by default once the patch is applied.
Apart from the the test cases mentioned in 1376995.1 IOUG newsletter mentioned another "way to test is to look at the backups with a restore process to validate SCN values and recoverability which is part of a best practice for database administrators. With these tests and test plans for applying the Patch Set Updates (PSU) or traditional Critical Patch Update patches, the vulnerabilities can be validated with appropriate fixes of the environment."

More clarifications on the issue from computer world