In addition to that hard limit, there's a soft limit imposed by Oracle itself to ensure the SCN value at any given moment is not unreasonably high, which would indicate a database malfunction. If the soft limit is exceeded, the database can become unstable and/or unavailable. And because the SCN cannot be decremented or otherwise reset, the database cannot simply be restored from a backup and replaying the logs, since they will necessarily carry the SCN elevation along with those transactions. An analogy can be made to the effect of the Y2K bug on an unpatched system as the clock struck 12 a.m. on Jan. 1, 2000.
The soft limit derives from a very simple calculation anchored to a point in time 24 years ago: Take the number of seconds since 00:00:00 01/01/1988 and multiply that figure by 16,384. If the current SCN value is below that, then all is well and processing continues as normal. To put this in simple terms, the calculation assumes that a database running constantly since 01/01/1988, processing 16,384 transactions per second, cannot exist in reality.
In reality, it doesn't. But if you alter reality, it does -- and there are several ways a breach of the SCN soft limit may occur.
The backup bug
One recent example comes in the form of a bug. Oracle databases have a feature that supports hot backups. This allows a database administrator to run a command to facilitate a backup of a live database. It's a handy function that can be run easily:
'ALTER DATABASE BEGIN BACKUP' is the command you need. You can then back up the live database until you issue an
'ALTER DATABASE END BACKUP' command that returns the database to normal operating mode. This makes it very simple for a DBA to take care of live backups of production databases.
The problem is that, due to a coding flaw, issuing the 'BEGIN BACKUP' command causes the SCN for the database instance to increase dramatically, so that the SCN continues to increase at an accelerated rate even after the 'END BACKUP' command is given. Thus, performing a hot backup can increase the SCN value by millions or billions quite quickly -- and that elevated growth continues unabated. In most cases the SCN limits are so far out of reach that the occasional jump in number isn't a cause for concern. Admins are unlikely even to notice the problem.
But when you mix the hot backup bug with large numbers of interconnected databases in a massive Oracle implementation, the combination can result in widespread SCN elevation. Some large Oracle customers have hundreds of database servers running hundreds of Oracle instances interlinked throughout the infrastructure. Each one may be tasked with a core service and a few lesser functions -- but like Kevin Bacon and the rest of Hollywood, nearly all are linked together somehow, through one, two, four, or more intermediary servers.
With all of these servers interconnected, their SCNs become synchronized at one point or another. Collectively they might be pushing more than 16,384 commits per second, but certainly not since 01/01/1988, so the SCN soft limit isn't problematic.
But what if a DBA on one part of this Oracle network runs the flawed hot backup routine? Suddenly, the SCN on his Oracle instances shoot up by, say, 700 million, and this number soon becomes the new SCN for all interlinked Oracle instances throughout the organization. Some time later, another hot backup command is issued by a DBA on the other side of the company. The SCN shoots up a few hundred million this time, also synchronized across all connected instances over time.
With the issuance of a few commands, the SCN of the entire Oracle database infrastructure has increased by hundreds of millions or even hundreds of billions in a short period. Even database instances that connect only occasionally, or for a weekly or monthly batch run, might see their SCN numbers jump by trillions.