Re: Database Change Notification

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Fri, 10 Apr 2009 23:20:29 UT
Message-Id: <1239405629.32654.1309985229_at_webmail.messagingengine.com>

Capturing the PKEY using Before Delete / Before Update trigger should help. You can generate a sequence to capture the "order" in which the DMLs took place. I had done this before in production to capture the Changes and push them to Sybase from Oracle. In addition you can try
[1]www.oracle.com/technology/deploy/availability/htdocs/LogMinerO verview.htm. I am giving it below should Oracle remove the contents of the URL at a later date(!)

LogMiner™ is a powerful audit tool for Oracle databases, allowing administrators to easily locate changes in the database, enabling sophisticated data analyses, and providing undo capabilities to rollback logical data corruptions or user errors.

LogMiner offers two interfaces:

• A SQL interface, which calls built-in PL/SQL packages to setup a data dictionary, specify redo logs, and execute queries (this interface is applicable to SQL*Plus, command-line scripts, or custom applications).
• LogMiner Viewer, an intuitive GUI which allows the administrator to setup a data dictionary, specify the redo logs, specify query criteria, and view/save redo log data.

LogMiner can also be leveraged for:

• Ex post facto auditing of DML statements, the order in which transactions were committed and the user responsible for the updates.
• Historical analysis of data access patterns for database tuning and capacity planning.
• Tracking schema evolution and the impact on data structure.

LogMiner directly accesses the Oracle redo logs, which are complete records of all activities performed on the database, and the associated data dictionary, which is used to translate internal object identifiers and types to external names and data formats. Using a dynamic view V$LOGMNR_CONTENTS, LogMiner populates a row in this view with each logical operation performed on the database, thus offering familiar, relational-based access for ad-hoc querying or custom application consumption. Each row contains a SQL UNDO statement, which can be used to rollback the change, and SQL REDO statement, which details the original operation. Log analysis can be conducted with the online catalog as the dictionary source, or an offline dictionary that has been extracted to a set of relevant redo logs, or a standalone flat file. With offline analysis, LogMiner can be run on a separate database, using archived redo logs and the associated dictionary from the source database, thus alleviating resource consumption on the source system.

HTH
GovindanK
On Sat, 4 Apr 2009 12:00:32 -0400, "Brady, Mark" <[2]Mark.Brady_at_Constellation.Com> said:
> So DCN gives a collection of ROWIDs that have changed and what
affected
> them, but we are left to go back and retrieve the row
ourselves. So has
> anyone built a wrapping package that handles deletes and
updates by
> retrieving prior images from flashback or some other mechanism?
>
>
> >>> This e-mail and any attachments are confidential, may
contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1
>
> --
> [3]www.freelists.org/webpage/oracle-l
>
>

References

1. http://www.oracle.com/technology/deploy/availability/htdocs/LogMinerOverview.htm
2. mailto:Mark.Brady_at_Constellation.Com
3. file://localhost/tmp/linkstmp/www.freelists.org/webpage/oracle

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 10 2009 - 18:20:29 CDT