Master Note: Overview for SCN issues (文档 ID 1503937.1)

APPLIES TO: 

Oracle Database - Enterprise Edition - Version 10.1.0.5 and later

Information in this document applies to any platform.

PURPOSE

As the January 2012 CPU (and associated PSU) releases include some SCN related bug fixes, along with other recommended fixes, this document will provide some information about the System Change Number (SCN) and related issues.

SCOPE

This document is intended for Oracle DBAs.

DETAILS

What is System Change Number (SCN)?

The system change number (SCN) is a database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database. The database uses these SCNs to query and track the changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred.

There is a very large upper limit to how many SCNs an Oracle Database can use. That limit is currently 281 trillion, or specifically 281,474,976,710,656 (is 2^48) where the Oracle Database should not run out of available ones.

 

What is SCN Headroom?

The difference between the current SCN the database is using, and the "not to exceed" upper limit, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second. However, Oracle has determined that some software bugs could cause the database to attempt to exceed the current maximum SCN value (or get closer to the limit than was warranted). Generally if the database does try to exceed the current maximum SCN value, the transaction that caused this event would be cancelled by the database, and the application would see an error. The next second the limit increases, so typically the application then continues with a slight hiccough in processing. However, in some very rare cases, the database does need to shutdown to preserve its integrity. In no cases is data lost or corrupted.

Checking the SCN Limit

All the associated bugs have been fixed in the January 2012 CPU (and associated PSU). The same fixes are also available in the database Patchset Update (PSU) and the latest Oracle Exadata and Windows bundled patches. Most customers will find that their databases are not close to the limit described in the above document, but if you are still concerned on this, kindly follow the instructions below:

1- Install the "scnhealthcheck.sql" script
The "scnhealthcheck.sql" script will alert customers that they may be close to the maximum SCN limit, in which case Oracle recommends they should apply the CPU to the affected database (and interconnected databases) without delay. The script can be downloaded via .
If you install the patch, then it will create "scnhealthcheck.sql" in the $ORACLE_HOME/rdbms/admin directory.
Alternatively, you can use the script directly from the unzipped patch without actually installing it in your $ORACLE_HOME.

2- Execute "scnhealthcheck.sql"
"scnhealthcheck.sql" can be executed as any DBA user of a database. It can be used in any 10.1 or higher database, regardless of patch level. 
To execute the script:
    * Change to the directory where the "scnhealthcheck.sql" script is located.
    * Use sqlplus to execute the script spooling output to a file.
      eg: Use an appropriate spool path for your platform below.

sqlplus SYSTEM/xxxxx
spool /tmp/scncheck_out

spool off
exit

3- Interpret "scnhealthcheck.sql" Output
The output gives a snapshot of the SCN health at a given point in time. Example output might look like this:

------------------------------------------------------------
    ScnHealthCheck
    ------------------------------------------------------------
    Current Date: 2012/01/17 01:01:09
    Current SCN:  384089
    Version:      11.1.0.7.0
    ------------------------------------------------------------
    Result: A - SCN Headroom is good
    Apply the latest recommended patches
    based on your maintenance schedule
    AND set _external_scn_rejection_threshold_hours=24 after apply.
    For further information review MOS document id 1393363.1
    ------------------------------------------------------------

 

To take the appropriate action as indicated by the "Result", please refer to

 

What is this "_external_scn_rejection_threshold_hours" parameter?

The hidden parameter "_external_scn_rejection_threshold_hours" is introduced in January 2012 Critical Patch Update (CPU) and Patch Set Update (PSU) releases (and related bundles). Oracle recommends setting this parameter to the value 24 in 10g and 11.1 releases - it does not need to be set in 11.2 releases. The parameter is static and so must be set in the init.ora or spfile used to start the instance. 
eg:

In init.ora: 
# Set threshold on dd/mon/yyyy - See MOS Document 1393363.1
_external_scn_rejection_threshold_hours = 24 

In the spfile: 
alter system set "_external_scn_rejection_threshold_hours" = 24 
comment='Set threshold on dd/mon/yyyy - See MOS Document 1393363.1' 
scope=spfile ;

 

Documents and Patches Related to the SCN Issue

The document that covers the topic on SCN and how Oracle database uses it.
-- Note:1376995.1 - Information on the System Change Number (SCN) and how it is used in the Oracle Database

The document that includes SQL script to report SCN health.
-- Note:1393363.1 - Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script

The document on messages that can be reported in the alert log relating to SCN health after CPU Jan2012 applied.
-- Note:1393360.1 - ORA-19706 and Related Alert Log Messages

SCN related patches are included in the CPU Patch Jan2012.
-- Note:1374524.1 - Patch Set Update and Critical Patch Update January 2012 Availability Document

The document to use when you feel that you have an SCN problem
-- Note:1388639.1 - Evidence to supply when logging a “high SCN rate” SR

Oracle Enterprise Manager patch to allow monitoring of SCN
-- Note:1404410.1 - Enterprise Manager SCN Monitor Patch

-- Note:12371955.8 Bug 12371955 - Hot Backup can cause increased SCN growth rate leading to ORA-600 [2252] errors

REFERENCES

NOTE:1393363.1 - Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script

NOTE:1393360.1 - ORA-19706 and Related Alert Log Messages

NOTE:1374524.1 - Patch Set Update and Critical Patch Update January 2012 Availability Document

NOTE:1406574.1 - Patch Set Update and Critical Patch Update April 2012 Availability Document

NOTE:1404410.1 - Enterprise Manager SCN Monitor Patch

NOTE:1376995.1 - System Change Number (SCN), Headroom, Security and Patch Information