Fundamental Oracle flaw revealed

A design decision made by Oracle architects long ago may have painted some of Oracle's largest customers into a corner. Patches have arrived, but how much will they correct?

By Paul Venezia, InfoWorld |  Security, database, Oracle

The SCN is crucial to normal Oracle database operation. The SCN "time stamp" is the key to maintaining data consistency in Oracle, allowing the database to respond to every user's query with the appropriate version of data at every point in time. It functions as the database's clock, so to speak. And like time, the SCN cannot decrement. It must always tick forward.

When Oracle databases link to each other, maintaining data consistency requires them to synchronize to a common SCN. This is necessarily the highest SCN carried by any participating Oracle database instance because the SCN clock cannot run backward -- so database linking causes the SCN in many databases to jump during normal operations. And only very basic permissions are required to make a connection that can cause one database to increment the SCN on another.

The architects of Oracle's flagship database application must have been well aware the SCN needed to be a massive integer. It is: a 48-bit number (281,474,976,710,656). It would take eons for an Oracle database to eclipse that number of transactions and cause problems -- or so you might think.

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.


Originally published on InfoWorld |  Click here to read the original story.
Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Answers - Powered by ITworld

ITworld Answers helps you solve problems and share expertise. Ask a question or take a crack at answering the new questions below.

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question
randomness