the best way to track data changes in oracle

Question

as the title i am talking about, what's the best way to track data changes in oracle? i just want to know which row being updated/deleted/inserted?

at first i think about the trigger, but i need to write more triggers on each table and then record down the rowid which effected into my change table, it's not good, then i search in Google, learn new concepts about materialized view log and change data capture,

materialized view log is good for me that i can compare it to original table then i can get the different records， even the different of the fields, i think the way is the same with i create/copy new table from original (but i don't know what's different?);

change data capture component is complicate for me :), so i don't want to waste my time to research it.

anybody has the experience the best way to track data changes in oracle?

Klaraklarika

"change data capture component is complicate for me :), so i don't want to waste my time to research it." What if it was the right solution for your case? – Eladiaelaeoptene

hi Rajesh, now i have a new idea. i create new table named xxx_CT from original table named xxx, the xxx_CT only have the primary key and rowserion fields, then i can get the change rows by this role: NEW: in xxx but not in xxx_CT UPDATE: both in xxx and xxx_CT, but the rowversion is different DELETE: in xxx_CT but not in xxx then i can union these there statement to get the change row(s). – Klaraklarika

Answer 1

You'll want to have a look at the AUDIT statement. It gathers all auditing records in the SYS.AUD$ table.

Example:

AUDIT insert, update, delete ON t BY ACCESS

Regards,
Rob.

Vibraharp

i research the AUDIT, it will record down very detailed information, it's for audit not me. :) – Klaraklarika

That's ok. Although it answers your "i just want to know which row being updated/deleted/inserted?" perfectly. – Vibraharp

Answer 2

You might want to take a look at Golden Gate. This makes capturing changes a snap, at a price but with good performance and quick setup.

If performance is no issue, triggers and audit could be a valid solution. If performance is an issue and Golden Gate is considered too expensive, you could also use Logminer or Change Data Capture. Given this choice, my preference would go for CDC. As you see, there are quite a few options, near realtime and offline.

Coding a solution by hand also has a price, Golden Gate is worth investigating.

Alber

thanks ik_zelf, i think Golden Gate is good tool, but i am free man. :) – Klaraklarika

Answer 3

Oracle does this for you via redo logs, it depends on what you're trying to do with this info. I'm assuming your need is replication (track changes on source instance and propagate to 1 or more target instances).

If thats the case, you may consider Oracle streams (other options such as Advanced Replication, but you'll need to consider your needs):

From Oracle:

When you use Streams, replication of a DML or DDL change typically includes three steps:

A capture process or an application creates one or more logical change records (LCRs) and enqueues them into a queue. An LCR is a message with a specific format that describes a database change. A capture process reformats changes captured from the redo log into LCRs, and applications can construct LCRs. If the change was a data manipulation language (DML) operation, then each LCR encapsulates a row change resulting from the DML operation to a shared table at the source database. If the change was a data definition language (DDL) operation, then an LCR encapsulates the DDL change that was made to a shared database object at a source database.

A propagation propagates the staged LCR to another queue, which usually resides in a database that is separate from the database where the LCR was captured. An LCR can be propagated to a number of queues before it arrives at a destination database.

At a destination database, an apply process consumes the change by applying the LCR to the shared database object. An apply process can dequeue the LCR and apply it directly, or an apply process can dequeue the LCR and send it to an apply handler. In a Streams replication environment, an apply handler performs customized processing of the LCR and then applies the LCR to the shared database object.

Macroscopic