oracle oracle中跟踪数据变化的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5726473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
the best way to track data changes in oracle
提问by GBK
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?
正如我正在谈论的标题，在 oracle 中跟踪数据更改的最佳方法是什么？我只想知道哪一行被更新/删除/插入？
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,
一开始我想到了触发器，但我需要在每个表上写更多的触发器，然后记录影响我的更改表的 rowid，这不好，然后我在谷歌搜索，学习有关物化视图日志和更改的新概念数据抓取，
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?
任何人都有在oracle中跟踪数据变化的最佳方法的经验？
回答by Rob van Wijk
回答by ik_zelf
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.
如果性能没有问题，触发器和审计可能是一个有效的解决方案。如果性能是一个问题并且 Golden Gate 被认为过于昂贵，您还可以使用 Logminer 或 Change Data Capture。鉴于这个选择，我更喜欢 CDC。如您所见，有很多选项，近乎实时和离线。
Coding a solution by hand also has a price, Golden Gate is worth investigating.
手工编写一个解决方案也是有代价的，Golden Gate 值得研究。
回答by tbone
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).
Oracle 通过重做日志为您执行此操作，这取决于您尝试使用此信息执行的操作。我假设您需要的是复制（跟踪源实例上的更改并传播到 1 个或多个目标实例）。
If thats the case, you may consider Oracle streams (other options such as Advanced Replication, but you'll need to consider your needs):
如果是这种情况，您可以考虑 Oracle 流（其他选项，例如高级复制，但您需要考虑您的需求）：
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.
当您使用 Streams 时，DML 或 DDL 更改的复制通常包括三个步骤：
捕获进程或应用程序创建一个或多个逻辑更改记录 (LCR) 并将它们排入队列。LCR 是具有描述数据库更改的特定格式的消息。捕获过程将从重做日志捕获的更改重新格式化为 LCR，并且应用程序可以构建 LCR。如果更改是数据操作语言 (DML) 操作，则每个 LCR 会将 DML 操作导致的行更改封装到源数据库中的共享表。如果更改是数据定义语言 (DDL) 操作，则 LCR 将封装对源数据库中的共享数据库对象所做的 DDL 更改。
传播将暂存的 LCR 传播到另一个队列，该队列通常驻留在与捕获 LCR 的数据库分开的数据库中。LCR 可以在到达目标数据库之前传播到多个队列。
在目标数据库中，应用进程通过将 LCR 应用到共享数据库对象来使用更改。应用进程可以使 LCR 出列并直接应用它，或者应用进程可以使 LCR 出列并将其发送到应用处理程序。在 Streams 复制环境中，应用处理程序执行 LCR 的自定义处理，然后将 LCR 应用到共享数据库对象。
