Fundamental Oracle flaw revealed

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