RelationalDBDesign RelationalDBDesign

Database Architecture   «Prev 

Generating Change Reports from redo logs

Since all database changes end up being written to the redo log, it is possible to generate change reports by reading those files. This is called log mining[1].
Oracle never used to directly support this, and as a result several third-party tools have been developed for that purpose. With Oracle8i, Oracle delivered a feature named the LogMiner. This consists of several SQL statements that have been enhanced to allow you to read data from your log files and place that data in tables that you can query for reporting purposes.

What Is Redo?

Redo log files are crucial to the Oracle database. These are the transaction logs for the database. Oracle maintains two types of redo log files:
  1. online and
  2. archived.
They are used for recovery purposes; their main purpose in life is to be used in the event of an instance or media failure. If the power goes off on your database machine, causing an instance failure, Oracle will use the online redo logs to restore the system to exactly the committed point it was at immediately prior to the power outage. If your disk drive It is a common misconception that undo is used to restore the database physically to the way it was before the statement or transaction executed, but this is not so. The database is logically restored to the way it was, any changes are logically undone but the data structures, the database blocks themselves, may well be different after a rollback.

The reason for this lies in the fact that, in any multiuser system, there will be tens or hundreds or thousands of concurrent transactions. One of the primary functions of a database is to mediate concurrent access to its data. The blocks that our transaction modifies are, in general, being modified by many other transactions as well. Therefore, we cannot just put a block back exactly the way it was at the start of our transaction, that could undo the work of someone else.
For example, suppose our transaction executed an INSERT statement that caused the allocation of a new extent (i.e., it caused the table to grow). Our INSERT would cause us to get a new block, format it for use, and put some data into it. At that point, some other transaction might come along and insert data into this block. If we roll back our transaction, obviously we cannot unformat and unallocate this block. Therefore, when Oracle rolls back, it is really doing the logical equivalent of the opposite of what we did in the first place. For every INSERT, Oracle will do a DELETE and for every DELETE, Oracle will do an INSERT.
For every UPDATE, Oracle will do an anti-UPDATE, or an UPDATE that puts the row back the way it was prior to our modification.

[1] log mining: The process of generating reports by reading files.