MAX SCN / High SCN vulnerability

What is SCN?

This is a number that increments sequentially with every database commit and is crucial to normal Oracle database operation.  The SCN is also incremented through linked database activities.

The Vulnerability?

Some customers expressed concerns that they may be getting closer to the current maximum SCN limit faster than the data processing they are doing would warrant.

What is the Max SCN?

Max SCN is not a hard limited number. The max scn constantly increases every second.

The current Max SCN limit is the number of seconds elapsed since 1988, multiplied by 16,384. So the max scn keeps increasing constantly.

What is SCN Headroom?

The difference between the current SCN the database is using, and the current MAX SCN, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.

What is the SCN Bug we are talking about?

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).

Example: When a database “A” (whose current SCN is lets say 10) has a db link to database “B”  (whose current SCN is lets say 100). When database “A” attempts to do a DML activity on one of the tables in database “B” via a DB Link, then Database “B” SCN (which is higher than A’s) is reflected in Database “A”.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

www.oracledatabase12g.com

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    5072553

SQL> select current_scn from v$database@PROD;

CURRENT_SCN

-----------

    7798262

SQL> insert into testlink@PROD values(1);

1 row created.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    7798282

b) There is also another bug found – where hot backups (alter database begin backup) causes SCN numbers to jump extremely high

Oracle released a patch to fix the SCN growth rate bug in hot backup listed as 12371955: “High SCN growth rate from ALTER DATABASE BEGIN BACKUP in 11g.”

What happens if a database does hit the Max SCN Limit?

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 shut down to preserve its integrity. In no cases is data lost or corrupted.

How to check if a database is close to hitting MAX SCN?

Instructions in metalink doc:

Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]

Fix?

Apply the Jan 2012 CPU Patch. Customers that have applied the fixes find that their SCN headroom starts to increase again.