Best way to track data changes in oracle

Question

like the title I'm talking about what is the best way to track data changes in oracle? I just want to know which row is updated / deleted / inserted?

at first I think of a trigger, but I need to write more triggers on each table, and then write down the row that was added to my change table, this is not very good, then I search on Google, I learn new concepts about the materialized representation of the log and change data

The materialized browsing log is good for me, that I can compare it with the original table, then I can get different records, even different fields, I think the same way with creating / copying a new table from the original (but I don’t know what else?);

changing the data capture component is more difficult for me :), so I don’t want to spend my time researching.

Does anyone have a better way to track data changes in oracle?

Answer 1

You need to take a look at AUDIT . It collects all audit records in the SYS.AUD $ table.

Example:

AUDIT insert, update, delete ON t BY ACCESS 

Respectfully,
Rob

Answer 2

You might want to take a look at the Golden Gate. This allows you to record changes in price, but with good performance and quick setup.

If performance is not an issue, triggers and auditing may be a reasonable solution. If performance is a problem and Golden Gate is considered too expensive, you can also use Logminer or Change Data Capture. Given this choice, my preference will be for CDC. As you can see, there are quite a few options that are close to real and offline messages.

Manually coding a solution also comes at a price; the Golden Gate is worth exploring.

Answer 3

Oracle does this for you through redo logs, it depends on what you are trying to do with this information. I assume that you need replication (track changes in the source instance and distribute to 1 or more target instances).

If so, you can consider Oracle threads (other options, such as advanced replication, but you will need to consider your needs):

From Oracle:

When you use threads, replication Changing DML or DDL usually involves three steps:

The capture process or application creates one or more logical record changes (LCR) and inserts them into the queue. LCR is a message with a specific format that describes a database change. The capture process changes the reformats that are captured in the re-entry to the LCR and applications can build LCR. If this change was a data manipulation language (DML), then each LCR encapsulates a row change as a result of a DML operation with a shared table on the source database. If the change was a Data Definition Language (DDL) operation, then the LCR encapsulates the DDL change that was made for the shared database object in the database source.

Distribution propagates LCR in stages to another queue, which is usually located in a separate separate database from the database where the LCR was captured. The LCR can extend to the number of queues before it arrives at the destination database.

In the destination database, the application process is spending the change on applying the LCR to the shared database object. The application process can remove the LCR and apply it directly, or the application process can LCR and send it to the application processor. In the Streams replication environment, the application handler customizes the LCR processing and then applies the LCR to the shared database object.